クエリパラメータ化の基礎

Webアプリケーションがユーザー提供のデータを使用する場合、SQLインジェクション攻撃のリスクが生じる可能性があります。SQLインジェクションとは、ユーザー入力を操作してSQL文を改変する手法です。具体的には、攻撃者が悪意のあるSQLコードをデータベースに送信し、セキュリティ対策を回避してデータへの不正アクセスを試みます。その結果は深刻で、機密情報の漏えいからシステムの完全な喪失に至ることもあります。  

SQLインジェクション攻撃を防ぐ方法はいくつかありますが、重要な手法の1つがSQLクエリのパラメータ化です。これは、プレースホルダー（パラメーター）を使ってSQLコードとユーザー入力値を分離し、入力をSQLコードではなくデータとして扱う方法です。重要なのは、パラメーター化クエリと組み合わせてプリペアドステートメントを使うことで、大きな効果が得られる点です。

セキュリティをさらに強化するには、パラメーター化されたストアドプロシージャを使用できます。ストアドプロシージャとは、データベースに保存されたプリコンパイル済みSQL文です。ストアドプロシージャの実行権限を付与しながら、基盤となるテーブルへの直接アクセスを制限できます。プロシージャを使う際は、アプリケーションから必要なパラメーターを渡します。

この記事では、SQLクエリのパラメータ化やストアドプロシージャを活用してインジェクション攻撃を防ぐ方法と、コードを安全に保つための追加のセキュリティ対策を実践的に解説します。

クエリのパラメータ化を実装する

クエリをパラメータ化するには、SQLクエリ内でプレースホルダーを使い、ユーザー入力を表します。プレースホルダーは一時的なマーカーで、SQLエンジンが実際の変数値を割り当てます。クエリの実行時にSQLエンジンが実際の変数をプレースホルダーにバインドしてから実行するため、変数はリテラルデータとして扱われます。

クエリのパラメータ化は、プリペアドステートメントを使っても使わなくても実行できます。この2つの方法の違いは、プリペアドステートメントは事前にコンパイルされ、異なるパラメーターを使って複数回呼び出せることです。 

さらに重要なのは、プリペアドステートメントを使うと、データベースが文を事前にコンパイルするため、より効果的にクエリをパラメータ化でき、実行時間を短縮できる点です。また、実行計画があらかじめ作成されるため、パラメーターが実行計画に影響することはありません。これはセキュリティ体制の強化にもつながります。

クエリを複数回呼び出す必要がある場合、データベースで再コンパイルする必要はありません。プリペアドステートメントに使用するパラメーターを渡すだけです。また、バインドパラメーターを使うと、クエリ全体ではなくパラメーターだけがサーバーに送信されるため、帯域幅を節約できます。 

以下のセクションでは、Webアプリケーションでクエリのパラメータ化を実装する方法を説明します。

前提条件

このデモに沿って進めるには、以下が必要です。

• .NETの基本的な知識

• SQLクエリの作成に関する基本的な知識

• Visual Studioとインストール済みのSnyk拡張機能

プリペアドステートメントを使わないクエリのパラメータ化 

このデモでは、コマンドラインからユーザー入力を受け取り、その入力をSQLクエリ文の値の1つとして使用する.NETコンソールアプリケーションを使います。

まず、プリペアドステートメントを使わずにクエリをパラメータ化する方法を見てみましょう。 

以下のコードでは、`Console.Readline()`でユーザー名を取得し、クエリを定義します。ユーザー名をクエリに連結する代わりに、`@`文字で示すプレースホルダーを使います。その後、クエリを実行する前に`AddWithValue()`メソッドでユーザー名をバインドします。

1try
2{
3    string? username = Console.ReadLine();
4    string query = "INSERT INTO Users (pk_id, name) VALUES (@id, @username)";
5    using (SqlConnection connection = new SqlConnection(connectionString))
6    {
7        connection.Open();
8        SqlCommand command = new SqlCommand(query, connection);
9
10        // Bind the SQL parameters.
11        command.Parameters.AddWithValue("@username", username);
12        command.Parameters.AddWithValue("@id", 20);
13
14        // Execute the query
15        command.ExecuteNonQuery();
16        connection.Close();
17    }
18
19}
20catch (Exception ex)
21{
22    //display error message
23    Console.WriteLine("Exception: " + ex.Message);
24}

クエリを定義した後にパラメーター値を渡すことで、SQL Serverがこれらの値をリテラルとして解釈するようにできます。

プリペアドステートメントを使ったクエリのパラメータ化

同じシナリオを使って、検索クエリをプリペアドステートメントに変更する方法を見てみましょう。

クエリ文字列は変わらず、`@id`と`@username`のプレースホルダーを引き続き使用します。ただし、上の例とは異なり、パラメーターを設定した後、`SqlCommand`オブジェクトの`Prepare`メソッドを呼び出します。

`Prepare()`を呼び出す前に、文の各パラメーターのデータ型を指定してください。また、エラーを防ぐため、サイズのプロパティを最大値に設定する必要があります。

次に、`ExecuteNonQuery`メソッドを使って、指定したパラメーターで`INSERT`文を実行し、以下の例のようにユーザーデータを`Users`テーブルに挿入します。

1try
2{
3    string? username = Console.ReadLine();
4
5    string query = "INSERT INTO Users (pk_id, name) VALUES (@id, @username)";
6
7    using (SqlConnection connection = new SqlConnection(connectionString))
8    {
9        connection.Open();
10        SqlCommand command = new SqlCommand(query, connection);
11
12        // Bind the SQL parameters.
13        SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
14        SqlParameter nameParam = new SqlParameter("@username", SqlDbType.Text, 100);
15        idParam.Value = 8;
16        nameParam.Value = "First Region";
17        command.Parameters.Add(idParam);
18        command.Parameters.Add(nameParam);
19
20        // Call Prepare 
21        command.Prepare();
22        command.ExecuteNonQuery();
23
24        // Change parameter values and call ExecuteNonQuery again.
25        command.Parameters[0].Value = 21;
26        command.Parameters[1].Value = "Alex";
27        command.ExecuteNonQuery();
28    }
29
30}
31catch (Exception ex)
32{
33    //display error message
34    Console.WriteLine("Exception: " + ex.Message);
35}

プリペアドステートメントでは、`Prepare()`メソッドを呼び出すのは1回だけです。その後はパラメーター値を変更して、再コンパイルせずにクエリを複数回実行できます。 

ストアドプロシージャの使用 

プリペアドステートメントを使うと、アプリケーション内でクエリ文を再利用できます。しかし、ストアドプロシージャのように、複数のアプリケーションからプリペアドステートメントを呼び出したり、アクセス制御を実装したりすることはできません。

ストアドプロシージャはデータベースサーバーに保存されたプリコンパイル済みクエリで、複数のアプリケーションから繰り返し呼び出せます。正しく実装すれば、プリペアドステートメントと同等のSQLインジェクション対策になります。ストアドプロシージャの実行権限を管理し、基盤となるテーブルへの直接アクセスを制限することもできます。これにより、SQLインジェクションが発生した場合の影響を軽減できます。

プリペアドステートメントと同様に、ストアドプロシージャもパラメーター化クエリに対応しており、ユーザー入力をSQLコードではなくデータとして扱います。また、データベースはプロシージャに渡されたパラメーターを自動的にサニタイズします。この処理により、攻撃者が悪意のあるコードをデータベースに送信するのを防ぎます。

動的な値をクエリに直接埋め込むのではなく、クエリパラメーターと併せてストアドプロシージャを使う必要があります。ユーザー入力とSQL文を明確に分離することで、SQLエンジンが入力をコードとして解釈するのを防げます。 

例として、データベースに`Id`と`Name`の値を受け取るストアドプロシージャを作成しましょう。

1USE db;  
2GO  
3CREATE PROCEDURE InsertUser
4    @Id INT,
5    @Name VARCHAR(50)  
6AS   
7    INSERT INTO Users (pk_id, Name)
8    VALUES (@Id, @Name);
9GO

SQL Server Management Studioを使用する場合は、クエリエディターでプロシージャを実行し、dbをデータベース名に置き換えてください。

以下は、ストアドプロシージャを使って新しいユーザーをテーブルに追加する例です。

まず、`Console.Readline()`を使って、コマンドラインからユーザー名を入力するよう促します。次に、`SQLConnection`コマンドでデータベースに接続します。その後、上の例で作成した`InsertUser`ストアドプロシージャを呼び出します。 

ストアドプロシージャにパラメーターをバインドするには、`Parameters.AddWithValue`メソッドを使います。この例では、`@id`パラメーターにハードコードした値を指定し、ユーザー入力から取得したユーザー名を`@name`パラメーターに渡します。

1try
2{
3    string? username = Console.ReadLine();
4    using (SqlConnection connection = new SqlConnection(connectionString))
5    {
6        connection.Open();
7
8        // Call the stored procedure
9        SqlCommand command = new SqlCommand("InsertUser", connection);
10        command.CommandType = CommandType.StoredProcedure;
11
12        // Bind the SQL parameters.
13        command.Parameters.AddWithValue("@id", SqlDbType.Int).Value = 134;
14        command.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = username;
15
16        // Execute the query
17        command.ExecuteNonQuery();
18    }
19}
20catch (Exception ex)
21{
22    // Display error message
23    Console.WriteLine("Exception: " + ex.Message);
24}

ストアドプロシージャを呼び出して変数を渡した後、`ExecuteNonQuery`メソッドでクエリを実行します。

データベースクエリに関する追加のセキュリティ対策 

プリペアドステートメントやストアドプロシージャでパラメータ化する以外にも、SQLクエリを安全に保つためのセキュリティ対策があります。

入力のサニタイズと検証を実施する

前述のとおり、攻撃者は入力データを悪用して、データベース上で悪意のあるコードを実行できます。そのため、入力のサニタイズと検証は非常に重要です。 

サニタイズでは、ユーザー提供データから不要な文字（例：`{}`、`'`、`/`）や安全でないHTMLを取り除きます。一方、検証ではユーザー提供データがデータベースで想定される形式かどうかを確認します。たとえば、入力の長さを確認し、長すぎる入力を拒否できます。また、メールアドレスや日付の形式を適用することもできます。この手法により、SQL文を含むよう細工された入力値が攻撃者から送信されるのを防ぎます。

入力のサニタイズと検証は、SQLクエリで使用する入力を制御するのに役立ちます。しかし、万全ではありません。攻撃者は二重エンコードなどの手法で、対策を回避できます。たとえば、入力を一度しかデコードしないフィルターを回避するために、シングルクォート（`'`）を一般的な`%27`ではなく`%2527`にエンコードする可能性があります。そのため、SQLインジェクション対策として入力のサニタイズに頼るのは避け、クエリをパラメータ化したうえで、追加のセキュリティ対策としてサニタイズを行いましょう。

出力をエンコードする

Webアプリケーションにユーザー入力を表示する際は、必ず事前にエンコードしてください。出力エンコードにより、ユーザーデータがHTML、JavaScript、SQLとして解釈されるのを防げます。

許可リストを使う

クエリを常にパラメータ化できるとは限りません。テーブル名やカラム名など、SQLクエリの一部には対応していません。そのような場合は、許可リストを使って入力を検証するのが効果的です。許可リストを使うと、入力で認める文字やパターンを厳密に定義できるため、悪意のある入力を拒否できます。

SQLインジェクション攻撃を防ぐには、拒否リストより許可リストの方が効果的です。許可リストでは許可する入力の種類を明示的に定義する一方、拒否リストには抜け道があり、攻撃者は入力を変化させて簡単に回避方法を見つけられます。許可リストを使えば、受け入れる入力をより適切に管理できます。

最小権限の原則を実践する

SQLインジェクション攻撃が成功した場合の影響を最小限に抑えるため、常に最小権限の原則（PoLP）を適用してください。PoLPでは、ユーザーのアクセス権を業務に必要な範囲に制限します。

たとえば、ユーザーアカウントにテーブルの読み取り権限だけが必要な場合、PoLPに従って、そのテーブルに対する読み取り専用権限のみを付与します。テーブル内の一部のデータだけにアクセスが必要なら、そのデータだけを含むビューを作成し、テーブル全体ではなくビューへのアクセス権をアカウントに付与できます。 

ストアドプロシージャを使用する場合は、アプリケーションに必要なストアドプロシージャだけに実行権限を制限してください。こうすれば、SQLインジェクションが発生しても、攻撃者のアクセスを制限し、被害の可能性を最小限に抑えられます。

Snykでコードを保護する

SQLインジェクションのテストを実施すると、SQLインジェクションの脆弱性に対する保護を強化できます。静的アプリケーションセキュリティテスト（SAST）を自動化するSnyk Codeなどのツールを使えば、セキュリティ上の問題を早期に発見できます。

Snykには、IDE内でコードを分析する脆弱性スキャナーがあります。また、拡張機能マネージャーからVisual Studio Code拡張機能を入手できます。手順に沿ってインストールし、設定してください。

設定が完了したら、サイドバーのSnykロゴをクリックして使用を開始できます。以下の画像のようなメニューバーが表示されます。各メニュー項目にはドロップダウン矢印があり、クリックすると利用を開始できます。ここではCODE SECURITYを使用します。

パラメーター化クエリを使用していない.NETアプリケーションでコードセキュリティ分析を実行すると、プロジェクトにSQLインジェクションの脆弱性があることをSnykが通知します。

次のメソッドは、文字列の書式設定を使ってユーザー入力をクエリに連結しています。

1public void withoutParameters(string dataSource, string initialCat, string persistSecurity, string userId, string password)
2    {
3        string connectionString = $"Data Source={dataSource};Initial Catalog={initialCat};Persist Security Info={persistSecurity};User ID={userId};Password={password}";
4        try
5        {
6
7            string username = Console.ReadLine();
8
9            using (SqlConnection connection = new SqlConnection(connectionString))
10            {
11            connection.Open();
12            string query = $"INSERT INTO Users(pk_id, name) VALUES (48,'{username}');";
13                using (SqlCommand command = new SqlCommand(query, connection))
14                {
15                    command.Parameters.AddWithValue("@username", username);
16
17                    using (SqlDataReader reader = command.ExecuteReader())
18                    {
19                        while (reader.Read())
20                        {
21                            Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
22                        }
23                    }
24                }
25            }
26        }
27        catch (Exception ex)
28            {
29                //display error message
30                Console.WriteLine("Exception: " + ex.Message);
31            }
32 }

上記のコードでコードセキュリティ分析を実行すると、以下のスクリーンショットのように、Snykが脆弱性を1件検出します。

分析レポートは、コマンドライン引数から取得した未サニタイズの入力がSQLクエリで使用されており、SQLインジェクションの脆弱性につながる可能性があることを示しています。Snykは、脆弱性を含む行とファイルを特定します。また、修正例も提示するため、脆弱性をより迅速に修正できます。

まとめ

クエリのパラメータ化は、SQLインジェクション攻撃を防ぐうえで不可欠です。ユーザー入力を実行可能なコードではなくデータとして扱うことで、悪意のあるSQLインジェクションのリスクを効果的に軽減できます。 

これまで、プリペアドステートメントを使用する場合と使用しない場合の、クエリパラメータ化の利点について説明してきました。最初の方法は、SQLロジックとユーザー入力を分離することで、手動でのエスケープやサニタイズを不要にし、より高いセキュリティを実現します。また、クエリが事前にコンパイルされるため、プリペアドステートメントはパフォーマンスの向上にも役立ちます。

プリペアドステートメントに代わる方法として、ストアドプロシージャがあります。クエリパラメータと組み合わせて使用すれば、アクセス権を管理できるため、セキュリティをさらに強化できます。

アプリケーションのセキュリティをさらに高めるには、開発ワークフローにSnykの脆弱性スキャナーを組み込むことができます。コードベースを定期的にスキャンすることで、SQLインジェクションにつながる可能性のある脆弱性を特定できます。このプラクティスにより、開発中に潜在的なセキュリティリスクに対処し、アプリケーションが攻撃者にさらされるリスクを軽減できます。