パラメータ化クエリについてまとめてみた

概要

パラメータ化クエリについて、まとめてみました。

目次

• パラメータ化クエリとは
• 歴史
• SQLインジェクション対策について
• ADO.NETとは
• サンプルコード

パラメータ化クエリとは

パラメータ化クエリとは、SQL文の中で値を直接埋め込まず、プレースホルダ (?や@paramなど) を使って外部から値を渡す (バインドする) 仕組みです。SQL文とデータを分離することで、入力値がSQL文として解釈されるのを防ぐ技術で「SQLインジェクション」対策になります。

SQLインジェクションは、ユーザー入力をそのままSQL文に組み込むことで、意図しないクエリが実行されてしまう攻撃手法です。

歴史

SQLが開発された当初では、SQL文は主に静的に記述されており、ユーザー入力を直接SQL文に埋め込むスタイルが一般的でした。
ただ、1990年代後半〜2000年代初頭にかけてのWebアプリケーションの普及に伴い、SQLインジェクションという攻撃による脆弱性が深刻な問題として認識されるようになりました。
この問題への対策として、パラメータ化クエリが登場しました。

SQLインジェクション対策について

下記だと、ユーザー入力がそのままSQL文に連結されます。

string user = "admin";
string pass = "1234";
string sql = "SELECT * FROM Users WHERE username = '" + user + "' AND password = '" + pass + "'";

なので攻撃者が、user (ユーザー名) に

• admin --
  と入力するだけで、下記のようになってしまいます。

SELECT * FROM Users WHERE username = admin --' AND password = ''

--はSQLのコメント記号なので、後半のパスワードチェックが無効化され、認証を突破できます。

一方でパラメータ化クエリでは、SQL文と値を別々に送信します。
DBのエンジンは、SQL構文を先に解析・コンパイルして、その後に値を「ただのデータ」としてバインドします。
そのため、想定外の入力も文字列として扱われるだけで、SQL構文に影響を与えません。

そのほか、値とSQL構造を分離できるので「コードの可読性・保守性向上」が期待できます。
※プレースホルダの書き方はDBやライブラリによって異なります。

ADO.NETとは

ActiveX Data Objectsの略で、.NET環境で動作するデータアクセス技術の総称です。ADO.NETという名前空間やDLLがあるわけではなく、データアクセス技術の総称です。

ADO.NETの実体は複数の名前空間・DLLに分かれています。

例)

• System.Data.dll (共通基盤)
• System.Data.SqlClient.dll (SQL Server用)
• System.Data.OleDb.dll (OleDb用)
• System.Data.Common.dll (共通抽象クラス)

※これらのDLLは ADO.NET以外の用途でも参照されることがあるため、これらのDLLを参照しているだけでは「ADO.NETを使っている」とは言い切れません。

もともとADOは、OLE DBを簡単に扱うためのCOMベースのオブジェクトモデルで、VBやASPで広く利用されていました。その後、ADO.NETという完全に新しいアーキテクチャが用いられ、.NET Framework 1.0と同時にリリースされました。

.NETの公式データアクセス技術はADO.NETになっています。そのため、開発者が直接ADO.NETのAPIを書くことはなくても、低レベルではほぼ必ずADO.NETの仕組みが使われています。

サンプルコード

DBはAccessを想定しています。

public DataTable Query(string sql, IEnumerable<OleDbParameter> parameters)
{
    // usingによりスコープ終了時に例外時含めて必ずDisposeされる
    using (var connection = new OleDbConnection(_connectionString))  // 接続オブジェクトを生成
    using (var command = new OleDbCommand(sql, connection))          // コマンドを生成。SQLと接続をひも付け
    using (var adapter = new OleDbDataAdapter(command))              // OleDbDataAdapterを使ってSELECT結果をDataTableに流し込むためのアダプタを作成
    {
        command.CommandType = CommandType.Text;  // 実行するのは生のテキストSQL 
        command.CommandTimeout = 30;             // コマンドのタイムアウト設定

        // OleDbは「追加順 = 置換順」になる
        if (parameters != null)
        {
            foreach (var p in parameters)        // ここでの「列挙順」がSQLの?の順番に対応させる必要がある
            {
                //　これがないと「nullの扱い」で例外になったり、意図通りにならないことがある
                if (p.Value == null) p.Value = DBNull.Value;
                command.Parameters.Add(p);
            }
        }

        var table = new DataTable();
        connection.Open();    // DB接続をオープン
        adapter.Fill(table);  // クエリを実行し、結果を引数のtableに入れる
        return table;
    }
}

ポイント

OleDbConnectionについて

データベースとアプリをつなぐ回線やトンネルのイメージです。接続文字列を持っていて、Open()やClose()で接続セッションを管理します。このクラスがないとそもそもDBに接続ができません。

OleDbCommandについて

回線の先に送る指示書 (SQL) ＋ 同封資料 (Parameters) を持っているイメージです。SELECT/INSERT/UPDATE/DELETE 等のSQL・コマンド本体を保持し、実行します。
このクラスがないと何を実行するかが決まらず、クエリが実行できません。

OleDbDataAdapterについて

DBとDataTable/DataSetの間でデータを運ぶ運搬車のイメージです。ただこのクラスは必須ではありません(DataReader＋DataTable.Load() でも代替可) 。

if (p.Value == null) p.Value = DBNull.Value;について

C#のnullとDBのnullは別物のため、C#のnullをDBのnullに置き換えているイメージです。この処理はADO.NETを使うすべてのデータベースで共通で必要になってきます。

DBがAccessではなくPostgreSQLやOracleなど他のDBになると、using部分で使用しているクラスやプレースホルダ、ワイルドカードの書き方が少し異なってきます。

以下は、上記のQuery()メソッドを呼び出すサンプルコードです。

// 検索条件をまとめるパラメータオブジェクト
// null以外なら検索条件に入れる、nullなら検索条件に入れない
public sealed class ProductSearchParams 
{
    public int? CategoryId { get; set; }          // カテゴリID
    public string Name { get; set; }              // 製品名 (前方一致で検索)
    public decimal? PriceMin { get; set; }        // 価格の下限
    public decimal? PriceMax { get; set; }        // 価格の上限
    public bool InStockOnly { get; set; }         // 在庫ありのみを絞り込むフラグ
    public string FreeWord { get; set; }          // 製品名/説明/SKU のいずれかに前方一致
    public DateTime? AvailableFrom { get; set; }  // 発売日等の下限
    public DateTime? AvailableTo   { get; set; }  // 発売日等の上限
}

// 製品の検索結果を DataTable で返すメソッド
public DataTable SelectProductsTable(ProductSearchParams searchPrm = null) 
{
    var sb = new StringBuilder();
    sb.AppendLine("select");                                    
    sb.AppendLine("  c.CategoryName as カテゴリ,");               
    sb.AppendLine("  p.ProductName  as 製品名,");                
    sb.AppendLine("  p.SKU          as SKU,");                    
    sb.AppendLine("  p.Price        as 価格,");                   
    sb.AppendLine("  p.Stock        as 在庫数");                  
    sb.AppendLine("from Tbl_Product as p");                       
    sb.AppendLine("inner join Mst_Category as c");                
    sb.AppendLine("  on p.Category_ID = c.Category_ID");           
    
    var where = new List<string>();                                
    var parameters = new List<OleDbParameter>();                   // パラメータ (?に対応) を格納するリスト

    // それぞれ指定されている場合のみ条件に入れる
    if (searchPrm?.CategoryId != null)                             
    {
        where.Add("p.Category_ID = ?"); 
        parameters.Add(new OleDbParameter                         
        {
            OleDbType = OleDbType.Integer,                        
            Value = searchPrm.CategoryId                           
        });
    }

    if (!string.IsNullOrWhiteSpace(searchPrm?.Name))  
    {
        where.Add("p.ProductName like ?");                         // Access 既定は * がワイルドカード
        parameters.Add(new OleDbParameter                        
        {
            OleDbType = OleDbType.VarWChar,                      
            Size = 128,                                            // 列サイズの目安を指定 (必須ではない)
            
            Value = searchPrm.Name + "*"
        });
    }

    if (searchPrm?.PriceMin != null)                              
    {
        where.Add("p.Price >= ?");                               
        parameters.Add(new OleDbParameter                          
        {
            OleDbType = OleDbType.Currency,                       
            Value = searchPrm.PriceMin                             
        });
    }

    if (searchPrm?.PriceMax != null)                             
    {
        where.Add("p.Price <= ?");                                 
        parameters.Add(new OleDbParameter                          
        {
            OleDbType = OleDbType.Currency,                        
            Value = searchPrm.PriceMax                            
        });
    }

    if (searchPrm?.AvailableFrom != null)                         
    {
        where.Add("p.AvailableDate >= ?");                       
        parameters.Add(new OleDbParameter                        
        {
            OleDbType = OleDbType.DBDate, 
            Value = searchPrm.AvailableFrom                      
        });
    }

    if (searchPrm?.AvailableTo != null)                         
    {
        where.Add("p.AvailableDate < ?");                        
        parameters.Add(new OleDbParameter                         
        {
            OleDbType = OleDbType.DBDate,                         
            Value = searchPrm.AvailableTo                         
        });
    }

    if (searchPrm?.InStockOnly == true)                          
    {            
        where.Add("p.Stock > 0");                                  
    }

    if (!string.IsNullOrWhiteSpace(searchPrm?.FreeWord))           // フリーワードが指定されていれば下記3項目の列で前方一致とする
    {
        where.Add("(p.ProductName like ? or p.Description like ? or p.SKU like ?)"); 
        var v = searchPrm.FreeWord + "*";                          
        parameters.Add(new OleDbParameter {                       
            OleDbType = OleDbType.VarWChar,                        
            Size = 128,                                           
            Value = v             
        });
        parameters.Add(new OleDbParameter {                       
            OleDbType = OleDbType.LongVarWChar,                    
            Value = v                                              
        });
        parameters.Add(new OleDbParameter {                       
            OleDbType = OleDbType.VarWChar,                        
            Size = 64,                                             
            Value = v
        });
    }

    if (where.Count > 0)                                           // 条件が 1 つ以上ある場合のみ WHERE 句を出力
    {
        sb.AppendLine("where " + string.Join(" and ", where));     // "and" で連結して 1 本の WHERE に成形
    }

    sb.AppendLine("order by c.CategoryName, p.ProductName");       // 並び順整列

    return Query(sb.ToString(), parameters);                  // 組み立てた SQL とパラメータを Query(...) に渡して実行し、結果を返す
}

想定テーブル

上記のサンプルコードを使用する際の、想定しているテーブルです。

Mst_Category

Category_ID	CategoryName
1	家電
2	パソコン
3	キッチン用品

Tbl_Product

Product_ID	Category_ID	ProductName	SKU	Price	Stock	Description	AvailableDate
101	1	電気ケトル	EK-100	2980	15	1L容量の電気ケトル	2024-11-01
102	1	トースター	TS-200	3980	8	2枚焼きトースター	2024-10-15
201	2	ノートPC 14インチ	NB-14	79800	5	軽量ノートPC、SSD256GB搭載	2025-01-10
202	2	ゲーミングPC	GP-500	158000	2	高性能GPU搭載	2025-02-01
301	3	フライパン 26cm	FP-26	1980	30	焦げ付きにくいコーティング	2024-09-20
302	3	包丁セット	KN-SET	4980	12	三徳包丁＋ペティナイフ	2024-08-05

画面イメージ

下記のような画面で検索条件を入力してもらい、結果レコードを返すイメージです。

補足

SELECT文以外のINSERT文やUPDATE文、DELETE文を実行する場合は下記のメソッドを使用します。

public int NonQuery(string sql, IEnumerable<OleDbParameter> parameters)
{
    using (var connection = new OleDbConnection(_connectionString))
    using (var command = new OleDbCommand(sql, connection))
    {
        command.CommandType = CommandType.Text;
        command.CommandTimeout = 30;

        if (parameters != null)
        {
            foreach (var p in parameters)
            {
                if (p.Value == null) p.Value = DBNull.Value;
                command.Parameters.Add(p);
            }
        }

        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
            command.Transaction = transaction;

            try
            {
                int affectedRows = command.ExecuteNonQuery();
                transaction.Commit();
                return affectedRows;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

なぜQuery()とNonQuery()で分けるのか

SELECT文は戻り値が必要で、その他の変更系のDMLはDataTableなどの戻り値は必要ありません (影響行数は取得可能)。そのため、分けることでそれぞれのコードがシンプルになります。
ADO.NET 自体もExecuteReader／ExecuteScalar／ExecuteNonQueryのように役割別に API が分かれています。
SELECT文でのadapter.Fill(table);は内部ではExecuteReader()を使用しており、加えて読みだした行をDataTableに詰めるところまでやってくれています。

終わりに

パラメータ化クエリという小さな工夫が大きなリスク回避につながることがわかりました。今後のプロジェクトにも積極的に取り入れていきたいと思います。