SQL Serverの動的SQL｜EXEC・sp_executesqlと値・列名の安全な渡し方

この記事はSQL ServerのT-SQLを対象に、EXEC／EXECUTEとsys.sp_executesqlを使う動的SQLを説明します。大切なのは値はパラメーター、テーブル名・列名は許可リストとQUOTENAMEに分けることです。SELECT @columnは列名を置き換えず、パラメーターの値を返します。PostgreSQLやMySQLのEXECUTEとは構文・用途が異なります。

EXECUTE文の基本概要

EXECUTE文は、文字列として構築されたSQLコマンドを実行するために使用されます。これは、SQLコマンドが実行時に動的に決定される場合に特に役立ちます。以下に、EXECUTE文の基本構文を示します。

EXECUTE (string_expression)

ここで、string_expressionは実行したいSQLコマンドを表す文字列です。EXECUTE文を使うことで、事前に決定できないクエリを実行する柔軟性を持つことができます。例えば、テーブル名や列名が変動するようなクエリを動的に生成して実行する際に役立ちます。

プレースホルダーの利用方法

WHERE条件やINSERTする値は、SQL文字列へ連結せず型付きパラメーターとして渡します。テーブル名や列名を、値のパラメーターで置き換えることはできません。識別子が固定で条件値だけが違うなら、動的SQLを使わない通常のパラメーター化クエリも検討してください。

以下はdbo.EmployeesにEmployeeID int IDENTITY、FirstName／LastName nvarchar(50)、Age int列がある想定の説明用コードです。INSERT／UPDATE例はデータを変更するので、必ず検証用データベースで確認してください。本記事のコードは実DBでの実行結果を示すものではありません。

DECLARE @sql nvarchar(max) =
    N'SELECT EmployeeID, LastName
      FROM dbo.Employees WHERE EmployeeID = @id;';
EXEC sys.sp_executesql @sql, N'@id int', @id = 1;

この例は@idをSQL文字列の外から渡します。sp_executesqlを使うだけで安全になるのではなく、値を文字列へ連結しないことが重要です。Unicode文字列にはNを付け、パラメーター型は対象列に合わせます。

動的クエリの作成例

動的クエリを作成する際には、変数を利用してクエリ文字列を組み立て、EXECUTE文を使ってそのクエリを実行します。以下に、動的クエリの具体的な作成例を示します。

例1: 動的なSELECTクエリ

以下の例は、許可した列名を切り替えるSELECTです。テーブル名はdbo.Employeesに固定しています。

DECLARE @columnInput nvarchar(4000) = N'LastName';
IF @columnInput IS NULL
   OR @columnInput NOT IN (N'FirstName', N'LastName')
   OR LEN(@columnInput) > 128
    THROW 50001, N'許可されていない列名です。', 1;

DECLARE @sql nvarchar(max) =
    N'SELECT ' + QUOTENAME(@columnInput)
    + N' FROM dbo.Employees WHERE EmployeeID = @id;';
EXEC sys.sp_executesql @sql, N'@id int', @id = 1;

QUOTENAMEは識別子を区切り、識別子内の区切り文字をエスケープします。任意のテーブルや列へのアクセスを許可する機能ではありません。入力を許可した名前のリストと照合し、スキーマ名とオブジェクト名は個別に区切ります。入力は最大128文字で、超えるとNULLになるため、長さも確認してください。

例2: 動的なINSERTクエリ

次に、動的にINSERTクエリを作成する例を示します。

-- データが追加されます。検証用DBで実行してください。
DECLARE @sql nvarchar(max) =
    N'INSERT INTO dbo.Employees (FirstName, LastName, Age)
      VALUES (@first, @last, @age);';
EXEC sys.sp_executesql @sql,
    N'@first nvarchar(50), @last nvarchar(50), @age int',
    @first = N'John', @last = N'Doe', @age = 30;

INSERTする値は@first、@last、@ageとして渡します。列名と対象テーブルはこの例では固定です。実際に名前を可変にする必要があるときだけ、SELECT例と同様に許可リストとQUOTENAMEで組み立てます。

例3: 動的なUPDATEクエリ

最後に、動的にUPDATEクエリを作成する例です。

-- データが変更されます。検証用DBで実行してください。
DECLARE @sql nvarchar(max) =
    N'UPDATE dbo.Employees
      SET LastName = @last WHERE EmployeeID = @id;';
EXEC sys.sp_executesql @sql,
    N'@last nvarchar(50), @id int', @last = N'Smith', @id = 1;

UPDATEも更新値と検索条件値を分離します。SET句やWHERE句の文字列を外部入力から丸ごと受け取らないでください。対象件数を事前確認し、本番ではトランザクションと復旧手順を設計します。

これらの例を参考に、さまざまなシナリオで動的クエリを利用することができます。EXECUTE文と動的クエリを組み合わせることで、柔軟かつ強力なデータベース操作が可能になります。

エラーハンドリング

動的クエリを実行する際には、エラーが発生する可能性があります。これらのエラーを適切に処理することで、システムの信頼性とユーザーエクスペリエンスを向上させることができます。以下に、動的クエリ実行時のエラーハンドリング方法を説明します。

TRY…CATCH構文の使用

SQL Serverでは、TRY…CATCH構文を使用してエラーをキャッチし、適切な処理を行うことができます。以下は捕捉と再送出の例です。すべてのエラーや接続切断を捕捉できるわけではなく、CATCHに入っただけで更新が自動ロールバックされるわけでもありません。

BEGIN TRY
    DECLARE @sql nvarchar(max) =
        N'SELECT EmployeeID, LastName FROM dbo.Employees
          WHERE EmployeeID = @id;';
    EXEC sys.sp_executesql @sql, N'@id int', @id = 1;
END TRY
BEGIN CATCH
    -- 必要なログ処理を行った後、元の例外を再送出します。
    THROW;
END CATCH;

エラー情報のロギング

エラーが発生した際に、その詳細をログに記録することも重要です。以下は記録前に必要な診断情報を取得する例です。

BEGIN TRY
    EXEC sys.sp_executesql
        N'SELECT EmployeeID FROM dbo.Employees WHERE EmployeeID = @id;',
        N'@id int', @id = 1;
END TRY
BEGIN CATCH
    -- 検証時の診断表示。公開画面へそのまま返さないでください。
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_SEVERITY() AS ErrorSeverity,
           ERROR_STATE() AS ErrorState,
           ERROR_MESSAGE() AS ErrorMessage,
           XACT_STATE() AS TransactionState;
    THROW;
END CATCH;

この例は診断情報の取得方法です。実運用のログテーブルへの書き込みは別途設計し、失敗したトランザクションの状態をXACT_STATE()で確認します。コミット不能な状態では書き込みを続けられないため、ロールバックとログ記録の順序を設計してください。SQLやエラー文字列に個人情報・機密値が含まれる場合は、保存範囲と閲覧権限にも注意します。

エラーハンドリングを適切に行うことで、動的クエリ実行時のトラブルシューティングが容易になり、システムの信頼性が向上します。

ベストプラクティス

動的クエリの実行は非常に強力ですが、適切な方法で実装しないとセキュリティリスクやパフォーマンス問題を引き起こす可能性があります。ここでは、動的クエリを安全かつ効率的に実行するためのベストプラクティスを紹介します。

1. SQLインジェクション対策

値のパラメーター化と識別子の検証は別です。次のSELECT @columnは列を取得するコードではなく、渡した文字列を返す例です。列名を可変にしたい場合は、先の許可リストとQUOTENAMEの例を使います。

-- @columnは識別子ではなく「値」です。
EXEC sys.sp_executesql N'SELECT @column AS SuppliedValue;',
    N'@column nvarchar(50)', @column = N'LastName';
-- LastNameという文字列を1行返す例で、列参照ではありません。
-- 列を切り替える場合は上の許可リスト＋QUOTENAMEの例を参照。

2. クエリ構築時の入力検証

ユーザー入力を使用してクエリを構築する際は、その入力を厳密に検証する必要があります。テーブル名や列名などの識別子は、事前に定義されたリストから選択するようにすることが推奨されます。

-- 有効なテーブル名のリストを定義
DECLARE @validTables TABLE (TableName NVARCHAR(50));
INSERT INTO @validTables VALUES ('Employees'), ('Departments');

-- ユーザー入力の検証
DECLARE @inputTable NVARCHAR(50);
SET @inputTable = 'Employees';

IF EXISTS (SELECT 1 FROM @validTables WHERE TableName = @inputTable)
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT * FROM dbo.' + QUOTENAME(@inputTable);
    EXECUTE(@sql);
END
ELSE
BEGIN
    PRINT 'Invalid table name.';
END

3. エラーハンドリングの徹底

前述したTRY…CATCH構文を使用し、エラー発生時に適切な処理を行うことが重要です。エラーメッセージのロギングや、必要に応じて管理者への通知を行うことで、問題発生時の対応が迅速に行えます。

4. パフォーマンスの最適化

sp_executesqlはSQL本文が同じで値だけが違う場合、実行プランの再利用を期待できます。ただし必ず速くなる保証はなく、値の偏りや型、インデックスなどで結果は変わります。実行プランと計測結果で確認してください。以下の名前は固定した説明用の値です。外部入力に置き換える場合は、同様に許可リストで検証します。

-- 動的クエリのパフォーマンスを最適化
DECLARE @sql NVARCHAR(MAX);
DECLARE @tableName NVARCHAR(50) = 'Employees';
DECLARE @indexColumn NVARCHAR(50) = 'EmployeeID';

-- クエリ文字列の組み立て
SET @sql = N'SELECT * FROM ' + QUOTENAME(@tableName) + ' WHERE ' + QUOTENAME(@indexColumn) + ' = @id';

-- クエリの実行
EXEC sp_executesql @sql, N'@id INT', @id = 1;

5. 定期的なレビューとテスト

動的クエリの実装は複雑になることが多いため、定期的にコードをレビューし、テストを行うことが重要です。セキュリティリスクやパフォーマンス問題を早期に発見し、修正するために、継続的なモニタリングと改善を行いましょう。

これらのベストプラクティスを遵守することで、動的クエリを安全かつ効率的に実行することができます。セキュリティとパフォーマンスを考慮し、システムの信頼性を高めることが重要です。

まとめ

EXECUTE文を使った動的クエリの実行方法について解説しました。動的クエリは、複雑な条件や動的に変わるパラメータに対応するために非常に有効です。しかし、その強力さゆえにセキュリティリスクも伴います。適切なエラーハンドリングや入力検証、SQLインジェクション対策を実施することで、安全かつ効率的に動的クエリを運用することができます。ベストプラクティスを守りながら、動的クエリの利便性を最大限に活用しましょう。

Microsoft公式資料

sp_executesql／QUOTENAME／SQLインジェクション対策／TRY…CATCH