【Oracle】DBMS_ASSERT完全ガイド｜動的 SQL の SQL インジェクション対策・入力値の検証方法まで解説

-- NG: テーブル名を検証なしに直接連結する（SQLインジェクションの危険あり）
CREATE OR REPLACE PROCEDURE unsafe_count(p_table_name VARCHAR2) AS
    v_sql VARCHAR2(500);
    v_cnt NUMBER;
BEGIN
    -- p_table_name が 'employees' なら安全だが...
    -- p_table_name が 'employees WHERE 1=1 UNION SELECT password FROM users--' なら危険
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
    EXECUTE IMMEDIATE v_sql INTO v_cnt;
    DBMS_OUTPUT.PUT_LINE('件数: ' || v_cnt);
END;
/
-- 攻撃例:
-- EXEC unsafe_count('employees WHERE 1=1; DROP TABLE orders;--');
-- → DROP TABLE orders が実行される可能性がある

-- OK: DBMS_ASSERT でテーブル名を検証してから使う
CREATE OR REPLACE PROCEDURE safe_count(p_table_name VARCHAR2) AS
    v_sql        VARCHAR2(500);
    v_cnt        NUMBER;
    v_safe_name  VARCHAR2(100);
BEGIN
    -- SQL_OBJECT_NAME: データベースに存在するオブジェクト名かどうかを検証する
    -- 存在しない・不正な識別子の場合は例外（ORA-44002）が発生する
    v_safe_name := DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);

    v_sql := 'SELECT COUNT(*) FROM ' || v_safe_name;
    EXECUTE IMMEDIATE v_sql INTO v_cnt;
    DBMS_OUTPUT.PUT_LINE('件数: ' || v_cnt);
END;
/

DECLARE
    v_result VARCHAR2(200);
BEGIN
    -- 基本的な使い方: 識別子をダブルクォートで囲む
    v_result := DBMS_ASSERT.ENQUOTE_NAME('employees');
    DBMS_OUTPUT.PUT_LINE(v_result);  -- → "EMPLOYEES"（大文字変換あり）

    -- 第2引数 FALSE: 大文字変換しない
    v_result := DBMS_ASSERT.ENQUOTE_NAME('employees', FALSE);
    DBMS_OUTPUT.PUT_LINE(v_result);  -- → "employees"（そのまま）

    -- スペースを含む識別子もダブルクォートで安全に扱える
    v_result := DBMS_ASSERT.ENQUOTE_NAME('my table', FALSE);
    DBMS_OUTPUT.PUT_LINE(v_result);  -- → "my table"

    -- ダブルクォートが含まれる場合は "" にエスケープされる
    v_result := DBMS_ASSERT.ENQUOTE_NAME('col"name', FALSE);
    DBMS_OUTPUT.PUT_LINE(v_result);  -- → "col""name"
END;
/

-- EXECUTE IMMEDIATE でのテーブル名の安全な使い方
DECLARE
    v_table VARCHAR2(100) := 'employees';   -- 外部から受け取るテーブル名
    v_sql   VARCHAR2(500);
    v_cnt   NUMBER;
BEGIN
    -- SQL_OBJECT_NAME で存在確認 + ENQUOTE_NAME でクォート
    v_sql := 'SELECT COUNT(*) FROM '
          || DBMS_ASSERT.ENQUOTE_NAME(DBMS_ASSERT.SQL_OBJECT_NAME(v_table));
    EXECUTE IMMEDIATE v_sql INTO v_cnt;
    DBMS_OUTPUT.PUT_LINE(v_cnt);
END;
/

DECLARE
    v_result VARCHAR2(200);
    v_name   VARCHAR2(100) := 'O''Brien';  -- シングルクォートを含む名前
    v_sql    VARCHAR2(500);
BEGIN
    -- シングルクォートを含む文字列を安全にエスケープする
    v_result := DBMS_ASSERT.ENQUOTE_LITERAL(v_name);
    DBMS_OUTPUT.PUT_LINE(v_result);
    -- → 'O''Brien' （シングルクォートが ''' にエスケープされた状態）

    -- WHERE 句の文字列条件を安全に組み立てる
    v_sql := 'SELECT * FROM employees WHERE last_name = '
          || DBMS_ASSERT.ENQUOTE_LITERAL(v_name);
    DBMS_OUTPUT.PUT_LINE(v_sql);
    -- → SELECT * FROM employees WHERE last_name = 'O''Brien'

    -- NOOP: 何もしない（値をそのまま返す。ホワイトリスト済みの値に使う）
    v_result := DBMS_ASSERT.NOOP('already_safe_value');
    -- 処理を見やすくするために DBMS_ASSERT.NOOP() でラップしてレビューアに意図を示す

END;
/

DECLARE
    v_schema   VARCHAR2(100);
    v_obj_name VARCHAR2(100);
    v_qual     VARCHAR2(200);
    v_simple   VARCHAR2(100);
BEGIN
    -- SCHEMA_NAME: スキーマ（ユーザー）が存在するかを検証する
    -- 存在しない場合: ORA-44001 (invalid schema name) が発生する
    v_schema := DBMS_ASSERT.SCHEMA_NAME('HR');
    DBMS_OUTPUT.PUT_LINE('スキーマ: ' || v_schema);  -- → HR

    -- SQL_OBJECT_NAME: データベースに存在するオブジェクト名（スキーマ修飾可）を検証する
    -- 存在しない場合: ORA-44002 (invalid object name) が発生する
    v_obj_name := DBMS_ASSERT.SQL_OBJECT_NAME('HR.EMPLOYEES');
    DBMS_OUTPUT.PUT_LINE(v_obj_name);   -- → HR.EMPLOYEES

    -- QUALIFIED_SQL_NAME: スキーマ.オブジェクト の形式が正しいかを構文検証する
    -- （オブジェクトが実際に存在するかは確認しない）
    v_qual := DBMS_ASSERT.QUALIFIED_SQL_NAME('HR.EMPLOYEES');
    DBMS_OUTPUT.PUT_LINE(v_qual);   -- → HR.EMPLOYEES

    -- SIMPLE_SQL_NAME: 単純な識別子構文（英数字・_・$・#）かどうかを検証する
    -- 存在確認はしない。不正な文字が含まれていれば ORA-44003 が発生する
    v_simple := DBMS_ASSERT.SIMPLE_SQL_NAME('EMPLOYEE_ID');
    DBMS_OUTPUT.PUT_LINE(v_simple);  -- → EMPLOYEE_ID

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('検証エラー: ' || SQLERRM);
        -- ORA-44001: invalid schema name
        -- ORA-44002: invalid object name
        -- ORA-44003: invalid SQL name
        -- ORA-44004: invalid qualified SQL name
END;
/