【PL/SQL】動的SQLのセキュアな書き方完全ガイド｜USINGバインド・DBMS_ASSERT・ホワイトリスト・監査・攻撃テスト

-- ❌ 脆弱：文字列連結でWHEREを組み立てている
CREATE OR REPLACE FUNCTION authenticate_bad(
  p_user IN VARCHAR2,
  p_pass IN VARCHAR2
) RETURN BOOLEAN AS
  v_cnt NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM users
       WHERE username = ''' || p_user || '''
         AND password = ''' || p_pass || ''''
    INTO v_cnt;
  RETURN v_cnt > 0;
END;
/

-- 攻撃: パスワードに ' OR '1'='1 を入力されると...
-- 生成されるSQL：
--   SELECT COUNT(*) FROM users
--    WHERE username = 'alice' AND password = '' OR '1'='1
-- → 必ず1件以上ヒットして認証通過してしまう

-- ✅ セキュア：USING句でバインド
CREATE OR REPLACE FUNCTION authenticate_safe(
  p_user IN VARCHAR2,
  p_pass IN VARCHAR2
) RETURN BOOLEAN AS
  v_cnt NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM users
       WHERE username = :u
         AND password = :p'
    INTO v_cnt
    USING p_user, p_pass;       -- ← 値はUSINGで渡す
  RETURN v_cnt > 0;
END;
/

-- 攻撃: パスワードに ' OR '1'='1 を入力しても...
-- バインド値そのものとして扱われるため
-- WHERE password = (文字列値そのまま) になり常にFALSE
-- → 認証は安全に拒否される

-- ① IN（デフォルト）：値をSQLに渡す
EXECUTE IMMEDIATE
  'UPDATE products SET price = :p WHERE id = :i'
  USING 1500, 100;

-- ② OUT：SQLから値を受け取る（INSERT ... RETURNING や PL/SQL関数呼び出し）
DECLARE
  v_id NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'INSERT INTO orders(order_id, customer_id, amount)
     VALUES(orders_seq.NEXTVAL, :c, :a)
     RETURNING order_id INTO :id'
    USING 100, 5000, OUT v_id;     -- OUT を明示
  DBMS_OUTPUT.PUT_LINE('採番: ' || v_id);
END;
/

-- ③ IN OUT：値を渡しつつ更新後の値を受け取る
DECLARE
  v_qty NUMBER := 10;
BEGIN
  EXECUTE IMMEDIATE
    'BEGIN
       :q := :q * 2;     -- 受け取って2倍にして返す
     END;'
    USING IN OUT v_qty;
  DBMS_OUTPUT.PUT_LINE('更新後: ' || v_qty);  -- 20
END;
/

-- ④ SELECT INTO 形式（単一行）：INTO句 + USING句 で組み合わせ
DECLARE
  v_name VARCHAR2(100);
BEGIN
  EXECUTE IMMEDIATE
    'SELECT product_name FROM products WHERE id = :i'
    INTO v_name
    USING 100;
END;
/

-- ⑤ BULK COLLECT INTO 形式：複数行をまとめて取得
DECLARE
  TYPE t_names IS TABLE OF VARCHAR2(100);
  v_names t_names;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT product_name FROM products WHERE category = :c'
    BULK COLLECT INTO v_names
    USING 'BOOK';
END;
/

CREATE OR REPLACE PACKAGE pkg_safe_dyn AS
  -- 許可テーブルの集合（ホワイトリスト）
  TYPE t_allow IS TABLE OF VARCHAR2(30);
  c_allowed_tabs CONSTANT t_allow := t_allow(
    'PRODUCTS', 'ORDERS', 'CUSTOMERS', 'INVENTORY'
  );

  FUNCTION resolve_table(p_name VARCHAR2) RETURN VARCHAR2;
  PROCEDURE truncate_safe(p_table VARCHAR2);
END pkg_safe_dyn;
/

CREATE OR REPLACE PACKAGE BODY pkg_safe_dyn AS

  FUNCTION resolve_table(p_name VARCHAR2) RETURN VARCHAR2 AS
    v_upper VARCHAR2(30);
  BEGIN
    -- ① DBMS_ASSERTで形式チェック（不正名は例外）
    v_upper := UPPER(DBMS_ASSERT.SIMPLE_SQL_NAME(p_name));

    -- ② ホワイトリスト判定（事前許可された名前か）
    FOR i IN 1 .. c_allowed_tabs.COUNT LOOP
      IF v_upper = c_allowed_tabs(i) THEN
        RETURN v_upper;     -- 安全な名前を返す
      END IF;
    END LOOP;

    RAISE_APPLICATION_ERROR(-20001,
      '許可されていないテーブル名: ' || p_name);
  END;

  PROCEDURE truncate_safe(p_table VARCHAR2) AS
    v_safe VARCHAR2(30);
  BEGIN
    v_safe := resolve_table(p_table);  -- 必ずホワイトリスト経由
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_safe;
  END;
END pkg_safe_dyn;
/

-- 使い方
BEGIN
  pkg_safe_dyn.truncate_safe('PRODUCTS');     -- OK
  -- pkg_safe_dyn.truncate_safe('SYS.AUD$');  -- ORA-20001 で拒否
  -- pkg_safe_dyn.truncate_safe('A--; DROP'); -- DBMS_ASSERTで例外
END;
/

CREATE OR REPLACE PACKAGE pkg_admin_dyn AUTHID CURRENT_USER AS
  -- 許可される操作のみ列挙
  TYPE t_op IS TABLE OF VARCHAR2(20);
  c_ops CONSTANT t_op := t_op('TRUNCATE', 'REBUILD');

  PROCEDURE do_ddl(p_op IN VARCHAR2, p_target IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_admin_dyn AS
  PROCEDURE do_ddl(p_op IN VARCHAR2, p_target IN VARCHAR2) AS
    v_op  VARCHAR2(20);
    v_tgt VARCHAR2(30);
    v_sql VARCHAR2(500);
    v_ok  BOOLEAN := FALSE;
  BEGIN
    -- 操作のホワイトリスト
    v_op := UPPER(DBMS_ASSERT.SIMPLE_SQL_NAME(p_op));
    FOR i IN 1 .. c_ops.COUNT LOOP
      IF v_op = c_ops(i) THEN v_ok := TRUE; EXIT; END IF;
    END LOOP;
    IF NOT v_ok THEN
      RAISE_APPLICATION_ERROR(-20002, '許可されない操作: ' || p_op);
    END IF;

    -- 対象のホワイトリスト（pkg_safe_dyn を再利用）
    v_tgt := pkg_safe_dyn.resolve_table(p_target);

    -- 監査ログ
    INSERT INTO dyn_audit(op, target, who, at_ts)
    VALUES(v_op, v_tgt, USER, SYSTIMESTAMP);

    -- 実行
    CASE v_op
      WHEN 'TRUNCATE' THEN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_tgt;
      WHEN 'REBUILD' THEN
        EXECUTE IMMEDIATE 'ALTER INDEX IDX_' || v_tgt || ' REBUILD';
    END CASE;
  END;
END;
/

-- 監査表
CREATE TABLE dyn_sql_log(
  log_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  exec_at   TIMESTAMP   DEFAULT SYSTIMESTAMP,
  who       VARCHAR2(64) DEFAULT USER,
  app_id    VARCHAR2(64),       -- アプリ識別子（CONTEXTで埋める）
  category  VARCHAR2(20),       -- DML/DDL/QUERY
  sql_text  CLOB,
  bind_args CLOB,               -- JSONで構造化
  err_code  NUMBER,
  err_msg   VARCHAR2(4000)
);

-- ラッパー
CREATE OR REPLACE PROCEDURE exec_safe(
  p_sql      IN VARCHAR2,
  p_category IN VARCHAR2,
  p_args     IN CLOB DEFAULT NULL    -- JSONで渡す
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_log_id NUMBER;
BEGIN
  -- 実行前に監査ログ
  INSERT INTO dyn_sql_log(category, sql_text, bind_args)
  VALUES(p_category, p_sql, p_args)
  RETURNING log_id INTO v_log_id;
  COMMIT;

  -- 実行は呼び出し元のコンテキストで（ここでは型省略の例示）
  EXECUTE IMMEDIATE p_sql;
EXCEPTION
  WHEN OTHERS THEN
    UPDATE dyn_sql_log
       SET err_code = SQLCODE,
           err_msg  = SUBSTR(SQLERRM,1,4000)
     WHERE log_id = v_log_id;
    COMMIT;
    RAISE;
END;
/

-- ① 1分間に同一ユーザが100回以上動的SQLを叩いている
SELECT who, COUNT(*) cnt
  FROM dyn_sql_log
 WHERE exec_at > SYSTIMESTAMP - INTERVAL '1' MINUTE
 GROUP BY who
HAVING COUNT(*) >= 100
 ORDER BY cnt DESC;

-- ② 危険キーワードを含むSQL（既に防いでいるはずだが二重チェック）
SELECT log_id, who, sql_text
  FROM dyn_sql_log
 WHERE REGEXP_LIKE(sql_text, '(UNION|--|;\s*DROP|;\s*DELETE|sys\.)', 'i')
   AND exec_at > SYSDATE - 1;

-- ③ 通常使われない表へのアクセス（ホワイトリスト外）
SELECT log_id, who, sql_text
  FROM dyn_sql_log
 WHERE NOT REGEXP_LIKE(sql_text,
       'PRODUCTS|ORDERS|CUSTOMERS|INVENTORY', 'i')
   AND category IN ('DML','DDL')
   AND exec_at > SYSDATE - 1;

DECLARE
  TYPE t_payloads IS TABLE OF VARCHAR2(200);
  v_payloads t_payloads := t_payloads(
    Q'[' OR '1'='1]',                                  -- 1: 認証バイパス
    Q'[admin'--]',                                     -- 2: コメント切り
    Q'['; DROP TABLE products--]',                     -- 3: DDL注入
    Q'[' UNION SELECT username,password FROM users--]', -- 4: UNION窃取
    Q'[1; DELETE FROM orders WHERE 1=1--]',            -- 5: DML注入
    Q'[' OR (SELECT COUNT(*) FROM users)>0--]',        -- 6: ブラインド
    Q'[%' OR password LIKE '%a%' OR ']',                -- 7: LIKEワイルド
    Q'[admin' AND SYS_CONTEXT('USERENV','SID')>0--]',  -- 8: 関数経由
    Q'[' || (SELECT MAX(id) FROM users) || ']',        -- 9: 連結試行
    Q'[NULL OR 1=1--]'                                 -- 10: NULL含み
  );
  v_ok  PLS_INTEGER := 0;
  v_ng  PLS_INTEGER := 0;
BEGIN
  FOR i IN 1 .. v_payloads.COUNT LOOP
    BEGIN
      -- 認証関数（authenticate_safe）が攻撃で通ってしまわないことを確認
      IF authenticate_safe('alice', v_payloads(i)) THEN
        v_ng := v_ng + 1;
        DBMS_OUTPUT.PUT_LINE('NG: payload #' || i || ' bypassed!');
      ELSE
        v_ok := v_ok + 1;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        v_ok := v_ok + 1;  -- 例外も「拒否」として正常
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('OK=' || v_ok || ' / NG=' || v_ng);
  IF v_ng > 0 THEN
    RAISE_APPLICATION_ERROR(-20003, 'セキュリティテスト失敗');
  END IF;
END;
/