MochiuWiki : SUSE, EC, PCB
案内
メインページ
最近の更新
おまかせ表示
MediaWiki についてのヘルプ
ツール
リンク元
関連ページの更新状況
特別ページ
ページ情報
We ask for
Donations
検索
個人用ツール
ログイン
Toggle dark mode
名前空間
ページ
議論
表示
閲覧
ソースを閲覧
履歴を表示
C Sharpとデータベース - パラメタライズドクエリのソースを表示
提供: MochiuWiki : SUSE, EC, PCB
←
C Sharpとデータベース - パラメタライズドクエリ
あなたには「このページの編集」を行う権限がありません。理由は以下の通りです:
この操作は、次のグループのいずれかに属する利用者のみが実行できます:
管理者
、new-group。
このページのソースの閲覧やコピーができます。
== 概要 == パラメタライズドクエリ (パラメータ化クエリとも呼ばれる) は、SQLインジェクション攻撃を防止するための重要なセキュリティ対策である。<br> <br> 通常のSQL文では、文字列連結によってSQL文を組み立てるため、悪意あるSQL文字列が入力された場合に意図しないSQL操作が実行されるリスクがある。<br> パラメタライズドクエリは、SQL文に直接値を埋め込む代わりに、プレースホルダを使用してパラメータとして値を渡すことで、このリスクを根本的に排除する。<br> <br> データベースエンジンは、SQL文の構文解析をパラメータ値の処理とは独立して行う。<br> これにより、パラメータとして渡された値がSQL文の一部として解釈されることはなく、常にデータとして扱われる。<br> <br> 各DBMSではプレースホルダの形式が異なる。<br> * SQL Server *: <code>@パラメータ名</code> 形式を使用する。 * Oracle Database *: <code>:パラメータ名</code> 形式を使用する。 * MySQL *: <code>@パラメータ名</code> 形式を使用する。 <br> パラメータの型は、<code>AddWithValue</code> メソッドで値から推測させるよりも、<code>Add</code> メソッドで <code>DbType</code> を明示的に指定することが推奨される。<br> 明示的な型指定により、意図しない暗黙の型変換を防止し、パフォーマンスと安全性を向上させることができる。<br> <br> Commandオブジェクトを再利用する場合、パラメータが保持されたままとなるため、パラメータのキー名が重複しないように注意が必要である。<br> <br> また、パラメタライズドクエリはデータベースエンジンが実行計画をキャッシュしやすくなるため、パフォーマンス面でもメリットがある。<br> <br><br> == プレースホルダ形式の比較 == 下表に、各DBMSで使用するプレースホルダの形式と関連クラスを示す。<br> <br> <center> {| class="wikitable" |+ プレースホルダ形式の比較 ! 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 |} </center> <br><br> == SQL Server == ==== 基本的な使用方法 ==== SQL ServerへのINSERT操作でパラメタライズドクエリを使用する例を以下に示す。<br> <br> <syntaxhighlight lang="csharp"> 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(); } } } </syntaxhighlight> <br> SELECT操作でパラメタライズドクエリを使用する例を以下に示す。<br> <br> <syntaxhighlight lang="csharp"> 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(); } } } </syntaxhighlight> <br> ==== データ型の指定 ==== <code>Parameters.Add</code> メソッドで <code>SqlDbType</code> を明示的に指定することが推奨される。<br> <br> <code>AddWithValue</code> メソッドは値から型を推測するため、意図しない型変換が発生する可能性がある。<br> <br> * Add + SqlDbType (推奨) *: <code>command.Parameters.Add("@ID", SqlDbType.VarChar, 50).Value = id;</code> *: 型とサイズを明示的に指定するため、暗黙の型変換が発生しない。 * AddWithValue (非推奨) *: <code>command.Parameters.AddWithValue("@ID", id);</code> *: 値から型を推測するため、文字列が <code>nvarchar</code> として扱われ、インデックスが使用されない場合がある。 <br> SQL Serverで使用する主な <code>SqlDbType</code> を以下に示す。<br> <br> <center> {| class="wikitable" |+ 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[] |} </center> <br><br> == Oracle Database == ==== 基本的な使用方法 ==== Oracle DatabaseへのINSERT操作でパラメタライズドクエリを使用する例を以下に示す。<br> <br> Oracle Databaseでは、プレースホルダに <code>:</code> (コロン) プレフィックスを使用する。<br> また、<code>BindByName</code> プロパティを <code>true</code> に設定することが推奨される。<br> <br> <syntaxhighlight lang="csharp"> 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(); } } } </syntaxhighlight> <br> SELECT操作でパラメタライズドクエリを使用する例を以下に示す。<br> <br> <syntaxhighlight lang="csharp"> 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(); } } } </syntaxhighlight> <br> ==== BindByNameプロパティ ==== <code>OracleCommand</code> の <code>BindByName</code> プロパティは、パラメータのバインド方法を制御する。<br> <br> * BindByName = true (名前バインド、推奨) *: パラメータをSQL文中のプレースホルダ名で照合してバインドする。 *: パラメータの追加順序がSQL文の順序と異なっていても正しくバインドされる。 * BindByName = false (デフォルト、位置バインド) *: パラメータをSQL文中に登場する順序でバインドする。 *: パラメータ名は無視され、追加した順序のみが重要となる。 <br> 位置バインドと名前バインドの比較例を以下に示す。<br> <br> <syntaxhighlight lang="csharp"> // 名前バインド (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番目 </syntaxhighlight> <br> 保守性と可読性の観点から、常に <code>BindByName = true</code> を設定することを推奨する。<br> <br> ==== データ型の指定 ==== 下表に、Oracle Databaseで使用する主な <code>OracleDbType</code> を示す。<br> <br> <center> {| class="wikitable" |+ 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 |} </center> <br><br> == MySQL == ==== 基本的な使用方法 ==== MySQLへのINSERT操作でパラメタライズドクエリを使用する例を以下に示す。<br> <br> MySQLでは <code>MySqlConnector</code> ライブラリを使用する。<br> プレースホルダの形式はSQL Serverと同じく <code>@パラメータ名</code> 形式である。<br> <br> <syntaxhighlight lang="csharp"> 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(); } } } </syntaxhighlight> <br> SELECT操作でパラメタライズドクエリを使用する例を以下に示す。<br> <br> <syntaxhighlight lang="csharp"> 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(); } } } </syntaxhighlight> <br> ==== データ型の指定 ==== 下表に、MySQLで使用する主な <code>MySqlDbType</code> を示す。<br> <br> <center> {| class="wikitable" |+ 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[] |} </center> <br><br> == NULLパラメータの処理 == C#の <code>null</code> をそのままパラメータ値に設定しても、データベース側ではNULLとして認識されない。<br> <br> データベースにNULLを挿入または更新する場合は、<code>DBNull.Value</code> を使用する必要がある。<br> <br> この動作はSQL Server、Oracle Database、MySQLの全てで共通である。<br> <br> <syntaxhighlight lang="csharp"> // 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; </syntaxhighlight> <br> Oracle Databaseの場合も同様である。<br> <br> <syntaxhighlight lang="csharp"> // Oracle Databaseの例 command.Parameters.Add(new OracleParameter(":ROLE_NAME", OracleDbType.Varchar2, 20) { Value = (object)roleName ?? DBNull.Value }); </syntaxhighlight> <br> MySQLの場合も同様である。<br> <br> <syntaxhighlight lang="csharp"> // MySQLの例 command.Parameters.Add("@ROLE_NAME", MySqlDbType.VarChar, 20).Value = (object)roleName ?? DBNull.Value; </syntaxhighlight> <br><br> == IN句でのパラメータ化 == IN句では、パラメータとして配列や複数の値を直接渡すことはできない。<br> <br> IN句をパラメータ化するには、値の数に応じて動的にパラメータを生成し、SQL文を組み立てる必要がある。<br> <br> SQL Serverを例としたIN句のパラメータ化の方法を以下に示す。<br> <br> <syntaxhighlight lang="csharp"> 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(); } } } </syntaxhighlight> <br> Oracle DatabaseおよびMySQLでも同様の手法が使用できる。<br> Oracle Databaseの場合はプレースホルダを <code>:ID0</code>、<code>:ID1</code> の形式に変更する。<br> <br> <syntaxhighlight lang="csharp"> // 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)})"; </syntaxhighlight> <br><br> == ORDER BY句に関する注意 == ORDER BY句では、カラム名や並び順をパラメータとして直接渡すことができない。<br> <br> これは、データベースエンジンがパラメータをデータとして扱うためであり、SQL文の構造 (カラム名やキーワード) をパラメータで置き換えることはできない仕様による。<br> <br> <syntaxhighlight lang="csharp"> // 以下は動作しない (パラメータはデータとして扱われるため) 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"; </syntaxhighlight> <br> ORDER BY句のカラム名を動的に変更する場合は、ホワイトリスト方式による検証を行い、プログラム側で条件分岐してSQL文を組み立てることが推奨される。<br> <br> <syntaxhighlight lang="csharp"> // ホワイトリスト方式によるカラム名の検証 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(); } } } </syntaxhighlight> <br> ホワイトリスト方式では、許可されたカラム名のみを <code>HashSet</code> または配列で管理し、入力値がリストに含まれる場合のみSQL文に埋め込む。<br> 並び順 (ASC / DESC) についても、<code>bool</code> 型のフラグで受け取り、プログラム側で文字列に変換することで安全に処理できる。<br> <br><br> == 参考リンク == * [https://learn.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlparameter Microsoft Docs - SqlParameterクラス] * [https://learn.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlcommand.parameters Microsoft Docs - SqlCommand.Parametersプロパティ] * [https://docs.oracle.com/en/database/oracle/oracle-database/21/odpnt/OracleParameterClass.html Oracle Docs - OracleParameter Class] * [https://mysqlconnector.net/api/mysqlconnector/mysqlparametertype/ MySqlConnector - MySqlParameter] <br><br> {{#seo: |title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki |keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板 |description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux |image=/resources/assets/MochiuLogo_Single_Blue.png }} __FORCETOC__ [[カテゴリ:C_Sharp]]
C Sharpとデータベース - パラメタライズドクエリ
に戻る。
案内
メインページ
最近の更新
おまかせ表示
MediaWiki についてのヘルプ
ツール
リンク元
関連ページの更新状況
特別ページ
ページ情報
We ask for
Donations
Collapse