OTR_StaticParameterizedQuery

静的パラメタライズド・クエリ

• TOP > 使い方 > 静的パラメタライズド・クエリ
  • 静的パラメタライズド・クエリ
  • 動的パラメタライズド・クエリ

概要

• 通常のパラメタライズド・クエリを、SQL 定義ファイル（.sql）に書いて実行する。
• SQL の構造が実行時に変わらないなら、こちらを使う（値はパラメタで渡せる）。
  構造が変わる場合だけ動的パラメタライズド・クエリにする。

詳細

定義の例

SELECT
  *
FROM
  Employees
WHERE
  FirstName = @FN
  AND LastName = @LN1
  AND EmployeeID IN (SELECT EmployeeID FROM Employees WHERE LastName = @LN2)
  AND ReportsTo IN (@P1, @P2)
ORDER BY %COLUMN% %SEQUENCE%

/*PARAM* FN, String, Nancy *PARAM*/
/*PARAM* LN1, String, Davolio *PARAM*/
/*PARAM* LN2, String, Davolio *PARAM*/
/*PARAM* P1, String, 2 *PARAM*/
/*PARAM* P2, String, 5 *PARAM*/
/*PARAM* COLUMN, EmployeeID *PARAM*/
/*PARAM* SEQUENCE, DESC *PARAM*/

• /*PARAM* … *PARAM*/ は、動的パラメタライズド・クエリ分析ツール（DPQuery_Tool）でテスト実行するときの値。
• 実際のプログラムからは、API でパラメタを設定する。

パラメタとユーザ・パラメタ

	パラメタ	ユーザ・パラメタ
構文	@名前	%名前%
API	SetParameter("名前", 値)	SetUserParameter("名前", 値)
仕組み	パラメタライズド・クエリのパラメタ	SQL 文字列への置換
用途	値	列名・ソート順など、パラメタにできない箇所
ユーザ入力	渡してよい	渡してはならない（SQL インジェクション）

CmnDao dao = new CmnDao(this.GetDam());
dao.SQLFileName = "SelectEmployees.sql";
dao.SetParameter("FN", "Nancy");
dao.SetUserParameter("COLUMN", " EmployeeID ");   // 許可リストから選んだ値だけを渡す
dao.SetUserParameter("SEQUENCE", " DESC ");

DataTable dt = new DataTable();
dao.ExecSelectFill_DT(dt);

DBMS による違い

• パラメタの接頭辞は DBMS で違う（SQL Server は @P1、Oracle は :P1）。SQL 定義ファイルは DBMS ごとに用意する。
• コード側（SetParameter("P1", …)）は接頭辞なしで、DBMS に依存しない。
• SQL Server で string のパラメタを varchar の列と比較すると、nvarchar への暗黙の型変換でインデックスが効かないことがある。
  SQL 側で CAST(@P1 AS varchar(10)) のように列の型に合わせる。

コーディング・エージェントに任せる場合

• スキル opentouryo-query-definition を使わせる。
• レビューでは、%名前% / SetUserParameter にユーザ入力が渡っていないかを必ず見る。

参考

• 動的パラメタライズド・クエリ
• D層（データアクセス層）の実装
• 脆弱性対策のポイント

---

Tags: 再構築