C Sharpとデータベース - パラメタライズドクエリ

概要

パラメタライズドクエリ (パラメータ化クエリとも呼ばれる) は、SQLインジェクション攻撃を防止するための重要なセキュリティ対策である。

通常のSQL文では、文字列連結によってSQL文を組み立てるため、悪意あるSQL文字列が入力された場合に意図しないSQL操作が実行されるリスクがある。
パラメタライズドクエリは、SQL文に直接値を埋め込む代わりに、プレースホルダを使用してパラメータとして値を渡すことで、このリスクを根本的に排除する。

データベースエンジンは、SQL文の構文解析をパラメータ値の処理とは独立して行う。
これにより、パラメータとして渡された値がSQL文の一部として解釈されることはなく、常にデータとして扱われる。

各DBMSではプレースホルダの形式が異なる。

SQL Server
@パラメータ名 形式を使用する。
Oracle Database
:パラメータ名 形式を使用する。
MySQL
@パラメータ名 形式を使用する。

パラメータの型は、AddWithValue メソッドで値から推測させるよりも、Add メソッドで DbType を明示的に指定することが推奨される。
明示的な型指定により、意図しない暗黙の型変換を防止し、パフォーマンスと安全性を向上させることができる。

Commandオブジェクトを再利用する場合、パラメータが保持されたままとなるため、パラメータのキー名が重複しないように注意が必要である。

また、パラメタライズドクエリはデータベースエンジンが実行計画をキャッシュしやすくなるため、パフォーマンス面でもメリットがある。

プレースホルダ形式の比較

下表に、各DBMSで使用するプレースホルダの形式と関連クラスを示す。

プレースホルダ形式の比較
DBMS	プレースホルダ形式	パラメータクラス	使用例	NuGet / ライブラリ
SQL Server	@パラメータ名	SqlParameter	WHERE ID = @ID	System.Data.SqlClient
Oracle Database	:パラメータ名	OracleParameter	WHERE ID = :ID	Oracle.ManagedDataAccess.Client
MySQL	@パラメータ名	MySqlParameter	WHERE ID = @ID	MySqlConnector

SQL Server

基本的な使用方法

SQL ServerへのINSERT操作でパラメタライズドクエリを使用する例を以下に示す。

 using System;
 using System.Configuration;
 using System.Data;
 using System.Data.SqlClient;
 
 public void Insert(string id, string password, string roleName)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD, ROLE_NAME) VALUES (@ID, @PASSWORD, @ROLE_NAME)";
          command.Parameters.Add("@ID", SqlDbType.VarChar, 50).Value = id;
          command.Parameters.Add("@PASSWORD", SqlDbType.VarChar, 100).Value = password;
          command.Parameters.Add("@ROLE_NAME", SqlDbType.VarChar, 20).Value = roleName;
 
          command.ExecuteNonQuery();
       }
       catch (SqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

SELECT操作でパラメタライズドクエリを使用する例を以下に示す。

 using System;
 using System.Configuration;
 using System.Data;
 using System.Data.SqlClient;
 
 public void Select(string id)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandText = "SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ID = @ID";
          command.Parameters.Add("@ID", SqlDbType.VarChar, 50).Value = id;
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                var userId   = reader.GetString(reader.GetOrdinal("ID"));
                var roleNm   = reader.GetString(reader.GetOrdinal("ROLE_NAME"));
                Console.WriteLine($"ID: {userId}, ROLE: {roleNm}");
             }
          }
       }
       catch (SqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

データ型の指定

Parameters.Add メソッドで SqlDbType を明示的に指定することが推奨される。

AddWithValue メソッドは値から型を推測するため、意図しない型変換が発生する可能性がある。

Add + SqlDbType (推奨)
command.Parameters.Add("@ID", SqlDbType.VarChar, 50).Value = id;

型とサイズを明示的に指定するため、暗黙の型変換が発生しない。
AddWithValue (非推奨)
command.Parameters.AddWithValue("@ID", id);

値から型を推測するため、文字列が nvarchar として扱われ、インデックスが使用されない場合がある。

SQL Serverで使用する主な SqlDbType を以下に示す。

SqlDbType の主要な型マッピング
SqlDbType	SQL Server データ型	C# 型
VarChar	VARCHAR	string
NVarChar	NVARCHAR	string
Int	INT	int
BigInt	BIGINT	long
Decimal	DECIMAL	decimal
Bit	BIT	bool
DateTime	DATETIME	DateTime
DateTime2	DATETIME2	DateTime
UniqueIdentifier	UNIQUEIDENTIFIER	Guid
VarBinary	VARBINARY	byte[]

Oracle Database

基本的な使用方法

Oracle DatabaseへのINSERT操作でパラメタライズドクエリを使用する例を以下に示す。

Oracle Databaseでは、プレースホルダに : (コロン) プレフィックスを使用する。
また、BindByName プロパティを true に設定することが推奨される。

 using System;
 using System.Configuration;
 using Oracle.ManagedDataAccess.Client;
 
 public void Insert(string id, string password, string roleName)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.BindByName = true;
          command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD, ROLE_NAME) VALUES (:ID, :PASSWORD, :ROLE_NAME)";
          command.Parameters.Add(new OracleParameter(":ID", OracleDbType.Varchar2, 50) { Value = id });
          command.Parameters.Add(new OracleParameter(":PASSWORD", OracleDbType.Varchar2, 100) { Value = password });
          command.Parameters.Add(new OracleParameter(":ROLE_NAME", OracleDbType.Varchar2, 20) { Value = roleName });
 
          command.ExecuteNonQuery();
       }
       catch (OracleException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

SELECT操作でパラメタライズドクエリを使用する例を以下に示す。

 using System;
 using System.Configuration;
 using Oracle.ManagedDataAccess.Client;
 
 public void Select(string id)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.BindByName = true;
          command.CommandText = "SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ID = :ID";
          command.Parameters.Add(new OracleParameter(":ID", OracleDbType.Varchar2, 50) { Value = id });
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                var userId = reader.GetString(reader.GetOrdinal("ID"));
                var roleNm = reader.GetString(reader.GetOrdinal("ROLE_NAME"));
                Console.WriteLine($"ID: {userId}, ROLE: {roleNm}");
             }
          }
       }
       catch (OracleException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

BindByNameプロパティ

OracleCommand の BindByName プロパティは、パラメータのバインド方法を制御する。

BindByName = true (名前バインド、推奨)
パラメータをSQL文中のプレースホルダ名で照合してバインドする。

パラメータの追加順序がSQL文の順序と異なっていても正しくバインドされる。
BindByName = false (デフォルト、位置バインド)
パラメータをSQL文中に登場する順序でバインドする。

パラメータ名は無視され、追加した順序のみが重要となる。

位置バインドと名前バインドの比較例を以下に示す。

 // 名前バインド (BindByName = true, 推奨)
 // パラメータ名で照合するため、追加順序は問わない
 command.BindByName = true;
 command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD, ROLE_NAME) VALUES (:ID, :PASSWORD, :ROLE_NAME)";
 command.Parameters.Add(new OracleParameter(":ROLE_NAME", OracleDbType.Varchar2, 20) { Value = roleName }); // 順序が異なっても動作する
 command.Parameters.Add(new OracleParameter(":ID", OracleDbType.Varchar2, 50) { Value = id });
 command.Parameters.Add(new OracleParameter(":PASSWORD", OracleDbType.Varchar2, 100) { Value = password });
 
 // 位置バインド (BindByName = false, デフォルト)
 // SQL文の ":ID", ":PASSWORD", ":ROLE_NAME" の出現順序 と Parameters.Add の順序を一致させる必要がある
 command.BindByName = false;
 command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD, ROLE_NAME) VALUES (:ID, :PASSWORD, :ROLE_NAME)";
 command.Parameters.Add(new OracleParameter(":ID", OracleDbType.Varchar2, 50) { Value = id });          // 1番目
 command.Parameters.Add(new OracleParameter(":PASSWORD", OracleDbType.Varchar2, 100) { Value = password }); // 2番目
 command.Parameters.Add(new OracleParameter(":ROLE_NAME", OracleDbType.Varchar2, 20) { Value = roleName }); // 3番目

保守性と可読性の観点から、常に BindByName = true を設定することを推奨する。

データ型の指定

下表に、Oracle Databaseで使用する主な OracleDbType を示す。

OracleDbType の主要な型マッピング
OracleDbType	Oracleデータ型	C#型
Varchar2	VARCHAR2	string
NVarchar2	NVARCHAR2	string
Char	CHAR	string
Int32	NUMBER(10,0)	int
Int64	NUMBER(19,0)	long
Decimal	NUMBER(p,s)	decimal
Single	FLOAT	float
Double	FLOAT	double
Date	DATE	DateTime
TimeStamp	TIMESTAMP	DateTime
Clob	CLOB	string
NClob	NCLOB	string
Blob	BLOB	byte[]
RefCursor	REF CURSOR	OracleDataReader
XmlType	XMLTYPE	string

MySQL

基本的な使用方法

MySQLへのINSERT操作でパラメタライズドクエリを使用する例を以下に示す。

MySQLでは MySqlConnector ライブラリを使用する。
プレースホルダの形式はSQL Serverと同じく @パラメータ名 形式である。

 using System;
 using System.Configuration;
 using MySqlConnector;
 
 public void Insert(string id, string password, string roleName)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD, ROLE_NAME) VALUES (@ID, @PASSWORD, @ROLE_NAME)";
          command.Parameters.Add("@ID", MySqlDbType.VarChar, 50).Value = id;
          command.Parameters.Add("@PASSWORD", MySqlDbType.VarChar, 100).Value = password;
          command.Parameters.Add("@ROLE_NAME", MySqlDbType.VarChar, 20).Value = roleName;
 
          command.ExecuteNonQuery();
       }
       catch (MySqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

SELECT操作でパラメタライズドクエリを使用する例を以下に示す。

 using System;
 using System.Configuration;
 using MySqlConnector;
 
 public void Select(string id)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandText = "SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ID = @ID";
          command.Parameters.Add("@ID", MySqlDbType.VarChar, 50).Value = id;
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                var userId = reader.GetString(reader.GetOrdinal("ID"));
                var roleNm = reader.GetString(reader.GetOrdinal("ROLE_NAME"));
                Console.WriteLine($"ID: {userId}, ROLE: {roleNm}");
             }
          }
       }
       catch (MySqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

データ型の指定

下表に、MySQLで使用する主な MySqlDbType を示す。

MySqlDbTypeの主要な型マッピング
MySqlDbType	MySQLデータ型	C#型
VarChar	VARCHAR	string
String	CHAR	string
Int32	INT	int
Int64	BIGINT	long
Decimal	DECIMAL	decimal
Float	FLOAT	float
Double	DOUBLE	double
Bit	BIT(1)	bool
DateTime	DATETIME	DateTime
Date	DATE	DateTime
Timestamp	TIMESTAMP	DateTime
Text	TEXT	string
MediumText	MEDIUMTEXT	string
LongText	LONGTEXT	string
Blob	BLOB	byte[]
LongBlob	LONGBLOB	byte[]

NULLパラメータの処理

C#の null をそのままパラメータ値に設定しても、データベース側ではNULLとして認識されない。

データベースにNULLを挿入または更新する場合は、DBNull.Value を使用する必要がある。

この動作はSQL Server、Oracle Database、MySQLの全てで共通である。

 // NULLを設定する場合 (SQL Server の例)
 command.Parameters.Add("@ROLE_NAME", SqlDbType.VarChar, 20).Value = DBNull.Value;
 
 // 変数がnullの可能性がある場合 (C# の null 合体演算子を使用)
 command.Parameters.Add("@ROLE_NAME", SqlDbType.VarChar, 20).Value =
    (object)roleName ?? DBNull.Value;
 
 // null条件演算子を使用するパターン
 string roleValue = roleName != null ? roleName : null;
 command.Parameters.Add("@ROLE_NAME", SqlDbType.VarChar, 20).Value =
    roleValue is null ? (object)DBNull.Value : roleValue;

Oracle Databaseの場合も同様である。

 // Oracle Databaseの例
 
 command.Parameters.Add(new OracleParameter(":ROLE_NAME", OracleDbType.Varchar2, 20)
 {
    Value = (object)roleName ?? DBNull.Value
 });

MySQLの場合も同様である。

 // MySQLの例
 
 command.Parameters.Add("@ROLE_NAME", MySqlDbType.VarChar, 20).Value = (object)roleName ?? DBNull.Value;

IN句でのパラメータ化

IN句では、パラメータとして配列や複数の値を直接渡すことはできない。

IN句をパラメータ化するには、値の数に応じて動的にパラメータを生成し、SQL文を組み立てる必要がある。

SQL Serverを例としたIN句のパラメータ化の方法を以下に示す。

 using System;
 using System.Collections.Generic;
 using System.Configuration;
 using System.Data;
 using System.Data.SqlClient;
 
 public void SelectByIds(string[] ids)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          // パラメータ名のリストを動的に生成する
          var parameterNames = new List<string>();
 
          for (int i = 0; i < ids.Length; i++)
          {
             string paramName = $"@ID{i}";
             parameterNames.Add(paramName);
             command.Parameters.Add(paramName, SqlDbType.VarChar, 50).Value = ids[i];
          }
 
          // IN句にパラメータ名を展開する
          command.CommandText = $"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ID IN ({string.Join(", ", parameterNames)})";
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                var userId = reader.GetString(reader.GetOrdinal("ID"));
                Console.WriteLine($"ID: {userId}");
             }
          }
       }
       catch (SqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

Oracle DatabaseおよびMySQLでも同様の手法が使用できる。
Oracle Databaseの場合はプレースホルダを :ID0、:ID1 の形式に変更する。

 // Oracle DatabaseのIN句パラメータ化
 var parameterNames = new List<string>();
 
 for (int i = 0; i < ids.Length; i++)
 {
    string paramName = $":ID{i}";
    parameterNames.Add(paramName);
    command.Parameters.Add(new OracleParameter(paramName, OracleDbType.Varchar2, 50) { Value = ids[i] });
 }
 
 command.BindByName = true;
 command.CommandText = $"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ID IN ({string.Join(", ", parameterNames)})";

ORDER BY句に関する注意

ORDER BY句では、カラム名や並び順をパラメータとして直接渡すことができない。

これは、データベースエンジンがパラメータをデータとして扱うためであり、SQL文の構造 (カラム名やキーワード) をパラメータで置き換えることはできない仕様による。

 // 以下は動作しない (パラメータはデータとして扱われるため)
 
 command.CommandText = "SELECT * FROM T_USER ORDER BY @ColumnName @SortOrder";
 command.Parameters.Add("@ColumnName", SqlDbType.VarChar).Value = "ID";
 command.Parameters.Add("@SortOrder", SqlDbType.VarChar).Value = "ASC";

ORDER BY句のカラム名を動的に変更する場合は、ホワイトリスト方式による検証を行い、プログラム側で条件分岐してSQL文を組み立てることが推奨される。

 // ホワイトリスト方式によるカラム名の検証
 
 private static readonly HashSet<string> AllowedColumns = new HashSet<string>
 {
    "ID", "PASSWORD", "ROLE_NAME"
 };
 
 public void SelectWithOrder(string sortColumn, bool ascending)
 {
    // ホワイトリストにないカラム名はデフォルト値に置き換える
    if (!AllowedColumns.Contains(sortColumn))
    {
       sortColumn = "ID";
    }
 
    string sortOrder = ascending ? "ASC" : "DESC";
 
    // 検証済みのカラム名と並び順を文字列として埋め込む
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          // ホワイトリストで検証済みの値を文字列として埋め込む
          command.CommandText = $"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER ORDER BY {sortColumn} {sortOrder}";
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                Console.WriteLine(reader.GetString(reader.GetOrdinal("ID")));
             }
          }
       }
       catch (SqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

ホワイトリスト方式では、許可されたカラム名のみを HashSet または配列で管理し、入力値がリストに含まれる場合のみSQL文に埋め込む。
並び順 (ASC / DESC) についても、bool 型のフラグで受け取り、プログラム側で文字列に変換することで安全に処理できる。

参考リンク