WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

パラメータ化クエリ

by WebSurfer 2. February 2012 22:47

パラメータ化クエリについて少々(かなり?)誤解してました。どのように誤解していたかは恥ずかしいので秘密です。(笑) 調べたことを備忘録として書いておきます。

パラメータ化クエリの説明図

パラメータ化の説明で、パラメータの入力はリテラルとして扱われるから SQL インジェクション攻撃を防ぐことができると言われています。

そこがキーポイントですが、そのリテラルというのが何だか分らないと話が始まりませんので、まずクエリの要素の名前の説明をします。以下の SELECT クエリを例に取ります。

SELECT id, Product FROM TableA WHERE Category='server'

クエリを構成する要素には、キーワード、演算子、識別子、リテラルなどがあり、上記の SELECT クエリではそれぞれ以下のようになります。

キーワード SELECT FROM WHERE
演算子 =
識別子 id Product Category TableA
リテラル 'server'

クエリをパラメータ化するというのはリテラルの部分をプレースホルダを使って記述することです。上記の SELECT クエリの例では以下のようになります。

SELECT id, Product FROM TableA WHERE Category=@Category

プレースホルダの構文はデータソースに依存します。SQL Server の場合は @parametername 形式の名前付きパラメータが使用されます。(頭に @ を付けるのは単なる命名規則です)

パラメータ名は必ずしも識別子と同じ名前にする必要はありませんが、同じにしておいた方が混乱がなさそうです。(例えば、識別子の名前が Category ならパラメータ名は @Category とする)

ADO.NET のコマンドオブジェクトは、パラメータを使用して SQL ステートメントまたはストアドプロシージャに値を渡すことを可能にしています。SQL Server の場合で、上記 SELECT クエリを例に取ると以下のようになります。

string connectionString = 
  WebConfigurationManager.
  ConnectionStrings["MyDB"].ConnectionString;

string query = 
  "SELECT id, Product FROM TableA WHERE Category=@Category";

using (SqlConnection connection =
           new SqlConnection(connectionString))
{
    SqlCommand command =
        new SqlCommand(query, connection);
    SqlParameter param = 
        new SqlParameter("@Category", SqlDbType.VarChar, 50);
    command.Parameters.Add(param);

    command.Parameters["@Category"].Value = TextBox1.Text;

    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    // 中略
}

SQL Server の場合、Parameters コレクションに追加したパラメータの名前は、クエリのパラメータ名前と一致している必要があります。(Access 等に使われる OleDb プロバイダの場合は、疑問符 (?) で指定される位置パラメータマーカーが使用されますがその話は割愛します。詳しくは、パラメータおよびパラメータのデータ型の構成 (ADO.NET) の「パラメーターのプレースホルダーの使用」セクションを参照してください)

プレースホルダの方式には静的と動的があって、SQL Server の場合は静的プレースホルダを使用します。静的/動的の違いの説明については安全なSQLの呼び出し方 - IPA 独立行政法人 情報処理推進機構を参照してください。

動作は次のとおりです(上の図も参照ください)。まず、プレースホルダのままのクエリをデータベースエンジン側にあらかじめ送信して、実行前にクエリのコンパイルなどの準備をしておきます。クエリの実行の段階で、Parameters コレクションに追加したパラメータの値をデータベースに送信し、データベースがバインド処理します。

ユーザー入力から直接クエリを組み立ててコマンドテキストとして渡すのとは異なり、パラメータの入力は実行可能なコードとしてではなく、リテラル値として扱われます。これにより、攻撃者がサーバーのセキュリティを侵害するコマンドを "注入" しても、注入した値はリテラルの外にはみ出すことはない(上の SELECT クエリの例で言うと、WHERE 句の条件が true または false になるだけ)ので、SQL インジェクション攻撃を防ぐことができます。

なお、パラメータ化するとエスケープ処理されるという話を時々聞きますが、少なくとも SQL Server の場合はそれは誤解です(エスケープ処置はされません。静的プレースホルダ方式なので処置する必要がありません)。

パラメータ化のもう一つのメリットにパフォーマンスの向上があります。それについては 第 4 回 アドホック クエリのパラメータ化 が参考になると思います。このページには、SqlParameter クラスを利用した場合、SQL Server の内部的にどのようにパラメータ化クエリが実行されるかも書いてあって(sp_executesql に変換されて実行される)、興味深いと思いました。

-------- 2016/6/5 追記 --------

上に述べたように、SQL インジェクションの防止とパフォーマンスの向上がパラメータ化クエリを使う主な目的ですが、それ以外にも照合順序の違いによって文字化けに悩むことがなくなるという副次的な効用もあります。

詳しくは別の記事「パラメータ化の副次的な効用」に書きましたので、興味がありましたら見てください。

Tags:

ADO.NET

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  June 2021  >>
MoTuWeThFrSaSu
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011

View posts in large calendar