埋め込み値を文字列連結して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耐性とキャッシュ効率の双方を高水準で満たす堅牢な実装が成立する。