【PL/SQL】動的SQLとBIND変数の最適設計:SQL Injection防止とキャッシュ効率の両立

【PL/SQL】動的SQLとBIND変数の最適設計:SQL Injection防止とキャッシュ効率の両立 PL/SQL

埋め込み値を文字列連結してSQLを生成する手法は、機密情報の漏洩や意図しないデータ改変を引き起こすだけでなく、カーソル共有を阻害してハードパースを多発させるため、可用性と性能の両面で致命的な設計欠陥になる。PL/SQLで動的SQLを扱う際は、実行計画の再利用を最大化するバインド設計と、識別子・リテラルの安全化を両立させる必要がある。本稿ではEXECUTE IMMEDIATEとDBMS_SQLの使い分けから、DBMS_ASSERTとホワイトリストによる安全化、FORALLによる一括バインド、カーソル共有率向上の具体技法までを実装視点で解説する。

EXECUTE IMMEDIATEとDBMS_SQLの使い分け

動的SQLの最初の選択はAPIであり、単純なDMLや一回限りの実行で戻り値の型が明確なときはEXECUTE IMMEDIATEが簡潔で安全に書ける。一方で列数やデータ型が実行時に決まる問い合わせ、可変長のSELECT結果を走査する必要がある場合、あるいは多数のバインドを名前で厳密に扱いたい場合はDBMS_SQLが適する。DBMS_SQLはパースとバインド、定義、フェッチを段階的に行うため冗長だが、任意性の高いクエリに耐性がある。


-- 単純DMLならEXECUTE IMMEDIATEで十分(末尾にRETURNING INTOも可)
DECLARE
  v_id NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'INSERT INTO app.orders(order_id, customer_id, amount) VALUES(orders_seq.NEXTVAL, :1, :2) RETURNING order_id INTO :3'
    USING 1101, 1200.50, OUT v_id;
  DBMS_OUTPUT.put_line('created order_id='||v_id);
END;
/

-- 列構成が実行時に決まるSELECTはDBMS_SQLが堅牢
DECLARE
  c            INTEGER;
  col_cnt      INTEGER;
  desc_tab     DBMS_SQL.desc_tab2;
  v_value      VARCHAR2(4000);
  v_sql        VARCHAR2(32767) := 'SELECT '||:p_cols||' FROM '||:p_owner||'.'||:p_tab||' WHERE created_at >= :dt';
BEGIN
  -- 名前解決と安全化は別章参照(ここでは事前にホワイトリスト済みとする)
  c := DBMS_SQL.open_cursor;
  DBMS_SQL.parse(c, v_sql, DBMS_SQL.native);
  DBMS_SQL.bind_variable(c, ':dt', SYSTIMESTAMP - INTERVAL '1' DAY);
  DBMS_SQL.describe_columns2(c, col_cnt, desc_tab);
  FOR i IN 1..col_cnt LOOP
    DBMS_SQL.define_column(c, i, v_value, 4000);
  END LOOP;
  IF DBMS_SQL.execute(c) >= 0 THEN
    WHILE DBMS_SQL.fetch_rows(c) > 0 LOOP
      FOR i IN 1..col_cnt LOOP
        DBMS_SQL.column_value(c, i, v_value);
        DBMS_OUTPUT.put_line(desc_tab(i).col_name||'='||v_value);
      END LOOP;
    END LOOP;
  END IF;
  DBMS_SQL.close_cursor(c);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.is_open(c) THEN DBMS_SQL.close_cursor(c); END IF;
    RAISE;
END;
/

SQL Injection防止の原則とDBMS_ASSERT

リテラルは必ずバインドし、識別子は連結を避けることが第一の原則である。識別子(スキーマ・テーブル・列・順序など)を可変にせざるを得ない場合は、DBMS_ASSERTで形式チェックを行い、かつホワイトリストで実体を限定する。DBMS_ASSERT.SIMPLE_SQL_NAMEはダブルクォートを含まない単純名の検査、ENQUOTE_NAMEは名前を正しく引用する。


CREATE OR REPLACE PACKAGE pkg_sql_guard AS
  FUNCTION safe_owner(p_owner IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION safe_object(p_name IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION safe_column(p_name IN VARCHAR2) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_sql_guard AS
  FUNCTION safe_owner(p_owner IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN DBMS_ASSERT.ENQUOTE_NAME(DBMS_ASSERT.SIMPLE_SQL_NAME(p_owner), FALSE);
  END;
  FUNCTION safe_object(p_name IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN DBMS_ASSERT.ENQUOTE_NAME(DBMS_ASSERT.SIMPLE_SQL_NAME(p_name), FALSE);
  END;
  FUNCTION safe_column(p_name IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN DBMS_ASSERT.ENQUOTE_NAME(DBMS_ASSERT.SIMPLE_SQL_NAME(p_name), FALSE);
  END;
END;
/

-- ホワイトリストで識別子を制限(アプリ側で管理するテーブルのみ許可)
CREATE OR REPLACE PACKAGE pkg_whitelist AS
  FUNCTION allow_table(p_table IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION allow_column(p_table IN VARCHAR2, p_col IN VARCHAR2) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_whitelist AS
  FUNCTION allow_table(p_table IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    IF UPPER(p_table) IN ('ORDERS','CUSTOMERS','INVOICES') THEN
      RETURN pkg_sql_guard.safe_object(UPPER(p_table));
    ELSE
      RAISE_APPLICATION_ERROR(-20000, 'table not allowed');
    END IF;
  END;
  FUNCTION allow_column(p_table IN VARCHAR2, p_col IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    IF UPPER(p_table)='ORDERS' AND UPPER(p_col) IN ('ORDER_ID','AMOUNT','CREATED_AT') THEN
      RETURN pkg_sql_guard.safe_column(UPPER(p_col));
    ELSE
      RAISE_APPLICATION_ERROR(-20001, 'column not allowed');
    END IF;
  END;
END;
/

ORDER BYや列選択の安全な可変化

ORDER BYをユーザー入力に委ねる場合、列名の直接連結は禁物である。列名はホワイトリストで列挙し、方向はCASE式やDECODEでパラメータ化する。これにより句全体を静的に保てない状況でも、注入の余地を排除できる。


DECLARE
  v_table  VARCHAR2(30) := pkg_whitelist.allow_table('ORDERS');
  v_col    VARCHAR2(30) := pkg_whitelist.allow_column('ORDERS', 'CREATED_AT');
  v_dir    VARCHAR2(4)  := CASE UPPER(:p_dir) WHEN 'DESC' THEN 'DESC' ELSE 'ASC' END;
  v_sql    VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT order_id, amount, created_at FROM '||v_table||
           ' ORDER BY '||v_col||' '||v_dir||' FETCH FIRST :n ROWS ONLY';
  EXECUTE IMMEDIATE v_sql USING :n;
END;
/

INリストの可変長条件は結合で解決する

IN (:1,:2,…) を動的に増減させる連結はカーソル共有を破壊する。コレクション型や一時表、またはJSON_TABLEで値集合を表現し、結合で解決するほうが安全かつ高速である。


-- コレクションでIN条件を表現
CREATE TYPE t_num_list IS TABLE OF NUMBER;
/

DECLARE
  v_ids t_num_list := t_num_list(1001,1002,1003);
  v_sql VARCHAR2(4000) :=
    'SELECT o.order_id, o.amount FROM app.orders o WHERE o.order_id IN (SELECT COLUMN_VALUE FROM TABLE(:ids))';
BEGIN
  FOR r IN (EXECUTE IMMEDIATE v_sql USING v_ids) LOOP
    NULL;
  END LOOP;
END;
/

バインドの型整合とNLS影響の遮断

文字列を数値や日付に暗黙変換させるとNLS設定によって実行計画が不安定になり、カーソル共有が分断される。NUMBERやDATE/TIMESTAMPは適切なPL/SQL型でバインドし、文字列に落とさない。どうしても文字を数値に変換する必要がある場合は、TO_NUMBERと第三引数のNLSパラメータで明示する。


-- 悪い例:文字列バインドで暗黙変換
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM orders WHERE amount > :x' INTO :cnt USING '1200.50';

-- 良い例:NUMBERで型一致
DECLARE v_amt NUMBER := 1200.50; BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM orders WHERE amount > :x' INTO :cnt USING v_amt;
END;
/

FORALL + EXECUTE IMMEDIATEで一括DML

FORALLは動的DMLにも対応しており、複数行のバインドを一度に送ることでネットワーク往復とパース回数を削減できる。SAVE EXCEPTIONSで失敗行を後処理に回す設計と相性が良い。


TYPE t_order_rec IS RECORD(order_id NUMBER, amount NUMBER);
TYPE t_order_tab IS TABLE OF t_order_rec INDEX BY PLS_INTEGER;

DECLARE
  v_rows t_order_tab;
  v_sql  VARCHAR2(200) := 'UPDATE app.orders SET amount = :amt WHERE order_id = :id';
  dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
  v_rows(1).order_id := 1001; v_rows(1).amount := 1500;
  v_rows(2).order_id := 1002; v_rows(2).amount := 1800;

  FORALL i IN INDICES OF v_rows SAVE EXCEPTIONS
    EXECUTE IMMEDIATE v_sql USING v_rows(i).amount, v_rows(i).order_id;

EXCEPTION
  WHEN dml_errors THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      pkg_logger.log_error('row#'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX||
                           ' err='||SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
    END LOOP;
END;
/

RETURNING句と一括取得の設計

INSERT/UPDATE/DELETEのRETURNINGはEXECUTE IMMEDIATEでも利用可能であり、FORALLと組み合わせれば一括取得ができる。生成されたIDや影響行のキーをロギング・後段処理へ安全に引き渡せる。


TYPE t_id_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
DECLARE
  v_ids  t_id_tab;
  v_cust t_num_list := t_num_list(10,11,12);
  v_amt  t_num_list := t_num_list(100,200,300);
  v_sql  VARCHAR2(4000) := 'INSERT INTO app.orders(order_id, customer_id, amount)
                            VALUES(orders_seq.NEXTVAL, :c, :a) RETURNING order_id INTO :o';
BEGIN
  v_ids.DELETE;
  FORALL i IN 1..v_cust.COUNT
    EXECUTE IMMEDIATE v_sql USING v_cust(i), v_amt(i), OUT v_ids(i);

  -- 取得したIDを走査
  FOR i IN v_ids.FIRST..v_ids.LAST LOOP
    IF v_ids.EXISTS(i) THEN
      DBMS_OUTPUT.put_line('inserted '||v_ids(i));
    END IF;
  END LOOP;
END;
/

カーソル共有とキャッシュ効率の最大化

同一テキストのSQLに対して、リテラル差分をバインドへ置換するだけで共有率は劇的に改善する。CURSOR_SHARING=EXACTが原則だが、アプリ側が十分にバインド対応できる設計であることが前提であり、問題のあるリテラルが混在する場合にFORCEへ頼るのは最終手段である。共有率を下げる主因はバインドミスマッチであり、データ型と長さ、NLS影響、USE_FEEDBACK_STATSによるフィードバック分岐、Bind PeekingとAdaptive Cursor Sharing(ACS)のヒストグラム依存などが関与する。高カーディナリティの選択項目ではプランが分岐しやすいため、安定化が必要ならSQL Plan Baselineの適用やヒント管理を併用する。ステートメントキャッシュはsession_cached_cursorsの適切設定で効果が出やすく、同一セッション内の再実行でハードパースを避けられる。


-- 問診用の共有率とハードパース確認
SELECT executions, parse_calls, loads, invalidations, sql_id, substr(sql_text,1,60) txt
  FROM v$sqlarea
 WHERE parsing_schema_name = 'APP'
 ORDER BY executions DESC FETCH FIRST 20 ROWS ONLY;

動的DDLは権限境界の内側で最小化する

動的DDLは必ずDefiner Rightsの管理パッケージに閉じ、オブジェクト名はDBMS_ASSERTで検証する。アプリケーションユーザーにCREATE/ALTER権限を渡さず、必要な操作のみを限定APIとして露出する。


CREATE OR REPLACE PACKAGE pkg_ddl_guard AUTHID DEFINER AS
  PROCEDURE add_col_vc2(p_tab IN VARCHAR2, p_col IN VARCHAR2, p_len IN PLS_INTEGER);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_ddl_guard AS
  PROCEDURE add_col_vc2(p_tab IN VARCHAR2, p_col IN VARCHAR2, p_len IN PLS_INTEGER) IS
    v_tab VARCHAR2(261) := pkg_whitelist.allow_table(p_tab);
    v_col VARCHAR2(261) := pkg_sql_guard.safe_column(p_col);
  BEGIN
    IF p_len < 1 OR p_len > 4000 THEN
      RAISE_APPLICATION_ERROR(-20010,'length out of range');
    END IF;
    EXECUTE IMMEDIATE 'ALTER TABLE app.'||v_tab||' ADD '||v_col||' VARCHAR2('||TO_CHAR(p_len)||')';
  END;
END;
/

可観測性とトラブルシュートの要点

動的SQLはテキストの一意性が低く原因追跡が難しいため、DBMS_APPLICATION_INFOでMODULE/ACTIONを必ず設定し、ログには相関IDとともに実行断片とバインドの概略を残す。異常なハードパースやカーソル肥大を検知したら、v$sql_bind_captureでバインドの型・長さ、v$sql_shared_cursorで共有失敗理由を確認し、型の統一とリテラル排除を徹底する。計画がブレる場合はヒストグラムやACSの影響を疑い、選択度の偏りが大きい条件はSQL側でスカラサブクエリの抑制やヒントで安定化を図る。


-- 共有失敗の理由(ACSや型不一致など)を確認
SELECT sql_id, reason
  FROM v$sql_shared_cursor
 WHERE sql_id = :sql_id
   AND (UNBOUND_CURSOR = 'Y' OR BIND_MISMATCH = 'Y' OR OPTIMIZER_MISMATCH = 'Y');

まとめ

動的SQLの安全化は、値を必ずバインドすることと、識別子をホワイトリスト+DBMS_ASSERTで制御することが出発点になる。EXECUTE IMMEDIATEを基本に据え、結果構造が可変ならDBMS_SQLを選択する。INリストはコレクション結合へ書き換え、FORALLとSAVE EXCEPTIONSで一括DMLとエラーハンドリングを両立させる。型一致とNLS影響の排除でカーソル共有を確実にし、必要に応じて計画安定化を補助する。これらの原理を徹底すれば、SQL Injection耐性とキャッシュ効率の双方を高水準で満たす堅牢な実装が成立する。