【SQL Server】動的SQLを sp_executesql で安全に書く落とし穴5選（インジェクション対策）

みなさんこんにちは、ヒロポンです！

検索画面で「入力された項目だけ WHERE に足す」やつ。業務系だと山ほどありますよね。で、条件を + で文字列連結して SQL を組み立ててる。動いてはいる。

でもこれ、SQL Server の動的SQLの中でも一番タチの悪い温床なんですよね。

俺も昔、可変検索の画面を引き継いだ時に、WHERE 句が全部文字列連結で組まれてるのを見て血の気が引いた記憶があります。テスト環境で ' OR '1'='1 を検索欄に入れたら、普通に全件返ってきた。ん？これ本番に出てるやつだよね？？ってなった。

今回は SQL Server の動的SQLを sp_executesql で安全に書くための落とし穴を5つ、実機で確認しながらまとめます。EXEC (@sql) の素の実行がなぜ危ないか、そして sp_executesql の @params で何が変わるか。コピペで動く T-SQL で見ていきます。

はじめに: なぜこの5つなのか

対象は「検索条件が可変で、動的SQLを書かざるを得ない業務SE」。以下は全部、俺か周りが実際に踏んだやつです。

• インジェクションの入り口（1・2）
• 型・識別子で「安全なつもり」が漏れる（2・3）
• 動的SQLのコスト＝実行計画（4）
• そもそも動的SQLを減らす設計（5）

先に、書き換えの重さの目安だけ。

題材のテーブルはこれ。

CREATE TABLE dbo.Employee (
    EmpId    INT           PRIMARY KEY,
    DeptCode NVARCHAR(10)  NOT NULL,
    EmpName  NVARCHAR(50)  NOT NULL
);

INSERT INTO dbo.Employee (EmpId, DeptCode, EmpName) VALUES
    (1, N'D01', N'佐藤'),
    (2, N'D01', N'鈴木'),
    (3, N'D02', N'高橋');

1. 値を文字列連結して EXEC する（インジェクションの本丸）

結論: 外部入力の「値」は、文字列連結せず sp_executesql の @params に渡す。

まず要点だけ先に。危ない形と安全な形の差はここ。

これを、実際に攻撃入力を食わせて動きで確かめます。まずは、やっちゃいけない形。ユーザーの入力を + で連結して EXEC に流す。

-- ❌ 危険: 入力値をそのまま連結
DECLARE @keyword NVARCHAR(50) = N''' OR ''1''=''1';  -- 攻撃入力の例
DECLARE @badSql  NVARCHAR(MAX) =
    N'SELECT EmpId, EmpName FROM dbo.Employee WHERE EmpName = N''' + @keyword + N'''';

PRINT @badSql;   -- 組み上がった SQL を見てみる
EXEC (@badSql);  -- WHERE が骨抜きになり、全件（3行）返ってしまう

@keyword に仕込まれた ' OR '1'='1 が、SQL の構造そのものを書き換える。結果、WHERE EmpName = N'' OR '1'='1' になる。

後半の '1'='1' は常に真。だから条件が骨抜きになって、全件（3行）出てくる。1件も一致しないはずの検索で全件返る。これがインジェクションです。

安全な形はこう。

-- ✅ 安全: 値はパラメータとして渡す
DECLARE @keyword NVARCHAR(50) = N''' OR ''1''=''1';
DECLARE @stmt    NVARCHAR(MAX) = N'SELECT EmpId, EmpName FROM dbo.Employee WHERE EmpName = @kw';

EXEC sp_executesql @stmt, N'@kw NVARCHAR(50)', @kw = @keyword;
-- @kw は「値」として扱われる。EmpName が『' OR '1'='1』という名前の行を探すので、0件

sp_executesql は「SQL文」と「パラメータ定義」と「実際の値」を分けて渡す。値は SQL の構造に混ざらない。

だから何を入れられても、ただの検索値として扱われるだけ。こんな感じで値をパラメータに逃がすのが基本です。

一行教訓: 値の + 連結を見たら、まず疑う。

2. 値は守れても「識別子」は守れない（列名・テーブル名の連結）

結論: 動的にする列名・テーブル名は QUOTENAME() で囲み、さらに許可リストで検証する。

ここ、sp_executesql を使い始めた人が一番見落とすところ。@params で守れるのは値だけなんですよね。

列名やテーブル名（識別子）はパラメータにできない。動的にしたければ結局は文字列連結するしかない。つまり、そこはインジェクションの穴が残ったまま。

たとえば「ソート列をユーザーが選ぶ」画面。

DECLARE @sortCol SYSNAME = N'EmpName';  -- 画面から来る想定

-- ✅ 許可リストで弾く（これが本丸の防御）
IF @sortCol NOT IN (N'EmpId', N'DeptCode', N'EmpName')
    THROW 50000, N'不正なソート列が指定されました', 1;

-- ✅ さらに QUOTENAME で [ ] で囲む
DECLARE @stmt NVARCHAR(MAX) =
    N'SELECT EmpId, DeptCode, EmpName FROM dbo.Employee ORDER BY ' + QUOTENAME(@sortCol);

EXEC sp_executesql @stmt;

QUOTENAME('EmpName') は [EmpName] を返す。中に ] が混ざってても ]] にエスケープしてくれるので、識別子として閉じ込められる。これでいい感じに守れます。

ただし QUOTENAME だけに頼るのは危ない。許可リスト（ホワイトリスト）で弾くのが本命です。列は数が知れてるので、IN で許可リストを持てる。

一行教訓: 識別子はパラメータにできない。QUOTENAME＋許可リストで守る。

3. N プレフィックスと @params の型宣言をサボる

結論: sp_executesql に渡す SQL文とパラメータ定義は nvarchar（N'...'）。パラメータの型は実際の列に合わせる。

sp_executesql の第1引数と第2引数は nvarchar 型です。N を付け忘れて varchar で渡すと、日本語を含むクエリで文字化けしたり、そもそもエラーになったりする。

もう1つ、@params の型宣言を実際の列とズラすと、地味に効いてくる。

-- ❌ EmpName は NVARCHAR(50) なのに、パラメータを別の型で宣言
EXEC sp_executesql
    N'SELECT * FROM dbo.Employee WHERE EmpName = @kw',
    N'@kw VARCHAR(50)',   -- ← NVARCHAR の列に VARCHAR パラメータ
    @kw = N'佐藤';

型が違うと暗黙の型変換が挟まって、インデックスが効かなくなる（SARGable でなくなる）ことがある。パラメータの型は、比較する列の型ときっちり合わせる。

さらにえげつないのが、SQL_Latin1_General 系の照合順序の環境。VARCHAR パラメータに渡した日本語（例: 佐藤）が ? に化けて、検索が0件になる。

エラーも出ないから、「なぜかヒットしない」で延々ハマるやつです。これ、原因にたどり着くまで丸一日って人、たぶん多くない？

一行教訓: N'...' を忘れず、@params の型は列に合わせる。

4. EXEC の文字列連結は実行計画が再利用されない

結論: 値ごとに SQL文字列が変わる EXEC はプランキャッシュが肥大する。sp_executesql でパラメータ化するとプランが再利用される。

EXEC (@sql) で値を連結すると、検索するたびに SQL の文字列が変わる。WHERE EmpName = N'佐藤' と = N'鈴木' は別のテキストですからね。

SQL Server はテキスト単位で実行計画をキャッシュする。だから値が変わるたびに別プランがキャッシュに積まれて肥大する。

sp_executesql でパラメータ化すると、SQL文のテキストは WHERE EmpName = @kw で固定。値が変わってもテキストは同じなので、1つのプランが再利用される。安全なだけじゃなく、プランキャッシュにも優しい。

-- パラメータ化されているので、値が変わってもプランは1つ
EXEC sp_executesql
    N'SELECT EmpId FROM dbo.Employee WHERE DeptCode = @d',
    N'@d NVARCHAR(10)', @d = N'D01';

一行教訓: パラメータ化はセキュリティとプラン再利用の一石二鳥。

5. 可変検索を「全部連結」で組む（catch-all で書き直す）

結論: 条件を連結で足すのではなく、(@p IS NULL OR col = @p) で全条件を静的に書き、使わない条件には NULL を渡す。

可変検索の「入力された項目だけ絞る」を、IF と文字列連結で組んでる現場は多い。でもそれこそ、インジェクションと組み合わせ爆発の温床。

SQL文は固定にして、パラメータの NULL で ON/OFF する形（catch-all パターン）にできます。

DECLARE @deptCode NVARCHAR(10) = N'D01';  -- 指定あり
DECLARE @empName  NVARCHAR(50) = NULL;    -- この条件は使わない

DECLARE @stmt NVARCHAR(MAX) = N'
    SELECT EmpId, DeptCode, EmpName
    FROM dbo.Employee
    WHERE (@dept IS NULL OR DeptCode = @dept)
      AND (@name IS NULL OR EmpName = @name)
    OPTION (RECOMPILE);';

EXEC sp_executesql @stmt,
    N'@dept NVARCHAR(10), @name NVARCHAR(50)',
    @dept = @deptCode, @name = @empName;