7 PL/SQLの動的SQL

動的SQLは、実行時にSQL文を生成して実行するためのプログラミング方法です。この方法は、非定型の問合せシステムのような柔軟性がある汎用目的のプログラムを記述する場合、データベース定義言語(DDL)文を実行する必要があるプログラムを記述する場合、またはコンパイル時にSQL文のテキスト全体またはそのSQL文の入力変数および出力変数の数またはデータ型が不明な場合に有効です。

PL/SQLには、動的SQLを記述する次の2つの方法が用意されています。

• 動的SQL文を作成および実行するためのPL/SQL言語(つまり、システム固有の)機能であるシステム固有の動的SQL。

• 動的SQL文を作成、実行および記述するためのAPIであるDBMS_SQLパッケージ。

システム固有の動的SQLコードの方が、DBMS_SQLパッケージを使用する同等のコードより読み書きが簡単であり、大幅に高速で実行されます(特に、コンパイラによって最適化できる場合)。ただし、システム固有の動的SQLコードを記述するには、コンパイル時に動的SQL文の入力変数および出力変数の数およびデータ型がわかっている必要があります。コンパイル時にこの情報が不明な場合は、DBMS_SQLパッケージを使用する必要があります。

DBMS_SQLパッケージとシステム固有の動的SQLの両方が必要な場合は、DBMS_SQL.TO_REFCURSORファンクションとDBMS_SQL.TO_CURSOR_NUMBERファンクションを使用して、それらを切り替えることができます。

ここでのトピック

• 動的SQLが必要な場合

• システム固有の動的SQL

• DBMS_SQLパッケージ

• SQLインジェクション

動的SQLが必要な場合

PL/SQLでは、次のSQLを実行するために動的SQLが必要となります。

• コンパイル時に不明なテキストが含まれているSQL

  たとえば、コンパイル時に不明な識別子(表名など)が含まれているSELECT文、コンパイル時に副次句の数が不明なWHERE句などです。

• 静的SQLとしてサポートされていないSQL

  つまり、「静的SQLの説明」で示されていないSQL構文のことです。

動的SQLが必要ない場合は、静的SQLを使用します。静的SQLには、次のようなメリットがあります。

• コンパイルが正常に完了すると、静的SQL文が有効なデータベース・オブジェクトを参照していること、およびそれらのオブジェクトへのアクセスに必要な権限が存在していることが保証されます。

• コンパイルが正常に完了すると、スキーマ・オブジェクトの依存性が作成されます。

  スキーマ・オブジェクトの依存性の詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。

PL/SQLでの静的SQL文の使用方法の詳細は、第6章「PL/SQLの静的SQL」を参照してください。

システム固有の動的SQL

システム固有の動的SQLでは、EXECUTE IMMEDIATE文を使用してほとんどの動的SQL文を処理します。

動的SQL文が複数行を戻すSELECT文である場合、システム固有の動的SQLでは、次のいずれかを選択できます。

• BULK COLLECT INTO句を指定してEXECUTE IMMEDIATE文を使用します。

• OPEN FOR文、FETCH文およびCLOSE文を使用します。

SQLのカーソル属性は、システム固有の動的SQLのINSERT文、UPDATE文、DELETE文、MERGE文および単一行のSELECT文の後では、それらの文が静的SQL文に対して動作する場合と同様に動作します。SQLのカーソル属性の詳細は、「カーソル」を参照してください。

ここでのトピック

• EXECUTE IMMEDIATE文

• OPEN FOR文、FETCH文およびCLOSE文

• 動的SQL文内でのプレースホルダ名の繰返し

EXECUTE IMMEDIATE文

EXECUTE IMMEDIATE文は、システム固有の動的SQLがほとんどの動的SQL文を処理する際に使用する手段です。

動的SQL文が自己完結型である場合(つまり、バインド変数のプレースホルダがなく、戻すことができる結果がエラーのみである場合)、EXECUTE IMMEDIATE文に句は不要です。

動的SQL文にバインド変数のプレースホルダが含まれている場合、各プレースホルダは、次のように、EXECUTE IMMEDIATE文の適切な句内に対応するバインド変数を持っている必要があります。

• 動的SQL文が最大で1行を戻すことができるSELECT文である場合、アウトバインド変数(定義)をINTO句に、インバインド変数をUSING句に含めます。

• 動的SQL文が複数行を戻すことができるSELECT文である場合、アウトバインド変数(定義)をBULK COLLECT INTO句に、インバインド変数をUSING句に含めます。

• 動的SQL文が、RETURNING INTO句が指定されていないDML文(SELECT以外)である場合、すべてのバインド変数をUSING句に含めます。

• 動的SQL文がRETURNING INTO句が指定されているDML文である場合、インバインド変数をUSING句に、アウトバインド変数をRETURNING INTO句に含めます。

• 動的SQL文が無名PL/SQLブロックまたはCALL文である場合、すべてのバインド変数をUSING句に含めます。

  動的SQL文がサブプログラムを起動する場合は、次のことを確認します。

  • サブプログラム・パラメータのプレースホルダに対応しているすべてのバインド変数のパラメータ・モードがそのサブプログラム・パラメータと同じであること(例7-1を参照)、およびこのバインド変数のデータ型がそのサブプログラム・パラメータのデータ型と互換性があること。(互換性のあるデータ型の詳細は、「サブプログラムの仮パラメータと実パラメータ」を参照してください。)

  • SQLでサポートされていないデータ型(例7-2で示されているBOOLEANなど)を持つバインド変数がないこと。

USING句に、リテラルNULLを含めることはできません。この制限を回避するには、例7-3に示すように、NULLを使用する場所に未初期化変数を使用します。

EXECUTE IMMEDIATE文の構文の詳細は、「EXECUTE IMMEDIATE文」を参照してください。

例7-1 動的PL/SQLブロックからのサブプログラムの起動

-- Subprogram that dynamic PL/SQL block invokes:
CREATE OR REPLACE PROCEDURE create_dept (
  deptid IN OUT NUMBER,
  dname  IN     VARCHAR2,
  mgrid  IN     NUMBER,
  locid  IN     NUMBER
) AS
BEGIN
  deptid := departments_seq.NEXTVAL;

  INSERT INTO departments (
    department_id,
    department_name,
    manager_id,
    location_id
  )
  VALUES (deptid, dname, mgrid, locid);
END;
/
DECLARE
  plsql_block VARCHAR2(500);
  new_deptid  NUMBER(4);
  new_dname   VARCHAR2(30) := 'Advertising';
  new_mgrid   NUMBER(6)    := 200;
  new_locid   NUMBER(4)    := 1700;
BEGIN
 -- Dynamic PL/SQL block invokes subprogram:
  plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';

 /* Specify bind variables in USING clause.
    Specify mode for first parameter.
    Modes of other parameters are correct by default. */

  EXECUTE IMMEDIATE plsql_block
    USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/

例7-2 システム固有の動的SQLではサポートされていないデータ型

DECLARE
  dyn_stmt VARCHAR2(200);
  b1       BOOLEAN;

  FUNCTION f (x INTEGER)
    RETURN BOOLEAN
  AS
  BEGIN
    NULL;
  END f;

BEGIN
  dyn_stmt := 'BEGIN :b := f(5); END;';
  EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
END;
/

結果:

  EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
                                       *
ERROR at line 15:
ORA-06550: line 15, column 40:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 15, column 3:
PL/SQL: Statement ignored

例7-3 USING句でのNULLを表現する未初期化変数

CREATE TABLE employees_temp AS SELECT * FROM EMPLOYEES;

DECLARE
  a_null  CHAR(1);  -- Set to NULL automatically at run time
BEGIN
  EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x'
    USING a_null;
END;
/

OPEN FOR文、FETCH文およびCLOSE文

動的SQL文が複数行を戻すSELECT文である場合は、システム固有の動的SQLを使用してその文を次のように処理できます。

1. OPEN FOR文を使用して、カーソル変数を動的SQL文に関連付けます。OPEN FOR文のUSING句で、動的SQL文内の各プレースホルダにバインド変数を指定します。

   USING句に、リテラルNULLを含めることはできません。この制限を回避するには、例7-3に示すように、NULLを使用する場所に未初期化変数を使用します。

   構文の詳細は、「OPEN FOR文」を参照してください。

2. FETCH文を使用して、結果セットの行を一度に1行、複数行またはすべて取り出します。

   構文の詳細は、「FETCH文」を参照してください。

3. CLOSE文を使用して、カーソル変数をクローズします。

   構文の詳細は、「CLOSE文」を参照してください。

例7-4では、結果セットの行を一度に1行ずつ取り出して、マネージャであるすべての従業員を表示しています。

例7-4 OPEN FOR文、FETCH文およびCLOSE文を使用したシステム固有の動的SQL

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      employees%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         employees.job%TYPE;
BEGIN
  -- Dynamic SQL statement with placeholder:
  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';

  -- Open cursor & specify bind variable in USING clause:
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH v_emp_cursor INTO emp_record;
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;

  -- Close cursor:
  CLOSE v_emp_cursor;
END;
/

動的SQL文内でのプレースホルダ名の繰返し

動的SQL文内でプレースホルダ名を繰り返す場合は、プレースホルダをバインド変数に関連付ける方法が動的SQL文の種類によって異なるということに注意してください。

ここでのトピック

• 動的SQL文が無名ブロックまたはCALL文でない

• 動的SQL文が無名ブロックまたはCALL文

動的SQL文が無名ブロックまたはCALL文でない

動的SQL文が無名PL/SQLブロックまたはCALL文を表していない場合、プレースホルダ名の繰返しは重要ではありません。プレースホルダは、名前ではなく位置によって、USING句内のバインド変数に関連付けられます。

たとえば、次の動的SQL文では、:xという名前の繰返しは重要ではありません。

sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

対応するUSING句では、4つのバインド変数を指定する必要があります。それらは異なっていてもかまいません。次に例を示します。

EXECUTE IMMEDIATE sql_stmt USING a, b, c, d;

前述のEXECUTE IMMEDIATE文は、次のSQL文を実行します。

INSERT INTO payroll VALUES (a, b, c, d)

:xが出現するたびに、それぞれに同じバインド変数を関連付けるには、そのバインド変数を繰り返す必要があります。次に例を示します。

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

前述のEXECUTE IMMEDIATE文は、次のSQL文を実行します。

INSERT INTO payroll VALUES (a, a, b, a)

動的SQL文が無名ブロックまたはCALL文

動的SQL文が無名PL/SQLブロックまたはCALL文を表している場合、プレースホルダ名の繰返しは重要です。一意のプレースホルダ名が、それぞれUSING句内に対応するバインド変数を持っている必要があります。プレースホルダ名を繰り返す場合、それに対応するバインド変数を繰り返す必要はありません。そのプレースホルダ名に対するすべての参照がUSING句内の1つのバインド変数に対応します。

例7-5では、最初の一意のプレースホルダ名:xに対するすべての参照がUSING句内の最初のバインド変数aに関連付けられており、2番目の一意のプレースホルダ名:yがUSING句内の2番目のバインド変数bに関連付けられています。

例7-5 動的PL/SQLブロックで繰り返されるプレースホルダ名

CREATE PROCEDURE calc_stats (
  w NUMBER,
  x NUMBER,
  y NUMBER,
  z NUMBER )
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
BEGIN
  plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b;  -- calc_stats(a, a, b, a)
END;
/

DBMS_SQLパッケージ

DBMS_SQLパッケージは、SQLカーソル番号と呼ばれるエンティティを定義します。SQLカーソル番号は、PL/SQLの整数であるため、コール境界を越えて渡し、格納することができます。

実行時まで次の情報がいずれも不明な場合は、DBMS_SQLパッケージを使用して、動的SQL文を実行する必要があります。

• SELECTリスト

• SELECTまたはDML文でバインドする必要があるプレースホルダ

次の状況では、DBMS_SQLパッケージではなく、システム固有の動的SQLを使用する必要があります。

• 動的SQL文が行を取り出してレコードに入れる場合。

• INSERT文、UPDATE文、DELETE文、MERGE文または単一行のSELECT文である動的SQL文の発行後に、SQLのカーソル属性%FOUND、%ISOPEN、%NOTFOUNDまたは%ROWCOUNTを使用する場合。

システム固有の動的SQLの詳細は、「システム固有の動的SQL」を参照してください。

DBMS_SQLパッケージとシステム固有の動的SQLの両方が必要な場合は、次のものを使用して、それらを切り替えることができます。

• DBMS_SQL.TO_REFCURSORファンクション

• DBMS_SQL.TO_CURSOR_NUMBERファンクション

注意: DBMS_SQLサブプログラムはリモートで起動できます。

関連項目: 入力変数または出力変数の数が不明な動的SQL文の実行方法(「メソッド4」)を含むDBMS_SQLパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

DBMS_SQL.TO_REFCURSORファンクション

DBMS_SQL.TO_REFCURSORファンクションは、SQLカーソル番号を、システム固有の動的SQL文で使用できる弱いカーソル変数に変換します。

SQLカーソル番号をDBMS_SQL.TO_REFCURSORファンクションに渡す前に、この番号に対してOPEN、PARSEおよびEXECUTEを実行する必要があります(そうしない場合、エラーが発生します)。

SQLカーソル番号をREF CURSOR変数に変換した後、DBMS_SQLの操作では、SQLカーソル番号としてではなく、REF CURSOR変数としてのみこの値にアクセスできます。たとえば、DBMS_SQL.IS_OPENファンクションを使用して、変換されたSQLカーソル番号がまだオープンしているかどうかを確認すると、エラーが発生します。

例7-6では、DBMS_SQL.TO_REFCURSORファンクションを使用して、DBMS_SQLパッケージからシステム固有の動的SQLに切り替えています。

例7-6 DBMS_SQLパッケージからシステム固有の動的SQLへの切替え

CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200);
/
CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE do_query_1 (
  placeholder vc_array,
  bindvars vc_array,
  sql_stmt VARCHAR2
)
IS
  TYPE curtype IS REF CURSOR;
  src_cur     curtype;
  curid       NUMBER;
  bindnames   vc_array;
  empnos      numlist;
  depts       numlist;
  ret         NUMBER;
  isopen      BOOLEAN;
BEGIN
  -- Open SQL cursor number:
  curid := DBMS_SQL.OPEN_CURSOR;

  -- Parse SQL cursor number:
  DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);

  bindnames := placeholder;

  -- Bind variables:
  FOR i IN 1 .. bindnames.COUNT LOOP
    DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i));
  END LOOP;

  -- Run SQL cursor number:
  ret := DBMS_SQL.EXECUTE(curid);

  -- Switch from DBMS_SQL to native dynamic SQL:
  src_cur := DBMS_SQL.TO_REFCURSOR(curid);
  FETCH src_cur BULK COLLECT INTO empnos, depts;

  -- This would cause an error because curid was converted to a REF CURSOR:
  -- isopen := DBMS_SQL.IS_OPEN(curid);

  CLOSE src_cur;
END;
/

DBMS_SQL.TO_CURSOR_NUMBERファンクション

DBMS_SQL.TO_CURSOR_NUMBERファンクションは、(強いか弱いかに関係なく)REF CURSOR変数を、DBMS_SQLサブプログラムに渡すことができるSQLカーソル番号に変換します。

REF CURSOR変数をDBMS_SQL.TO_CURSOR_NUMBERファンクションに渡す前に、この変数に対してOPENを実行する必要があります。

REF CURSOR変数をSQLカーソル番号に変換した後、システム固有の動的SQL操作では、この値にアクセスできなくなります。

例7-7では、DBMS_SQL.TO_CURSOR_NUMBERファンクションを使用して、システム固有の動的SQLからDBMS_SQLパッケージに切り替えています。

例7-7 システム固有の動的SQLからDBMS_SQLパッケージへの切替え

CREATE OR REPLACE PROCEDURE do_query_2 (
  sql_stmt VARCHAR2
)
IS
  TYPE curtype IS REF CURSOR;
  src_cur   curtype;
  curid     NUMBER;
  desctab   DBMS_SQL.DESC_TAB;
  colcnt    NUMBER;
  namevar   VARCHAR2(50);
  numvar    NUMBER;
  datevar   DATE;
  empno     NUMBER := 100;
BEGIN
  -- sql_stmt := SELECT ... FROM employees WHERE employee_id = :b1';

  -- Open REF CURSOR variable:
  OPEN src_cur FOR sql_stmt USING empno;

  -- Switch from native dynamic SQL to DBMS_SQL package:
  curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);

  -- Define columns:
  FOR i IN 1 .. colcnt LOOP
    IF desctab(i).col_type = 2 THEN
      DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
    ELSIF desctab(i).col_type = 12 THEN
      DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
      -- statements
    ELSE
      DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);
    END IF;
  END LOOP;

  -- Fetch rows with DBMS_SQL package:
  WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
    FOR i IN 1 .. colcnt LOOP
      IF (desctab(i).col_type = 1) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
      ELSIF (desctab(i).col_type = 2) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
      ELSIF (desctab(i).col_type = 12) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
        -- statements
      END IF;
    END LOOP;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(curid);
END;
/

SQLインジェクション

SQLインジェクションでは、SQL文内でクライアントから提供されるデータを使用するアプリケーションを悪用することによってデータベースに不正にアクセスし、制限付きデータを表示または操作します。この項では、PL/SQLでのSQLインジェクションの脆弱性、およびそれらの回避方法について説明します。

この項の例を実行してみるには、HRスキーマに接続し、例7-8の文を実行します。

例7-8 SQLインジェクションの例のための設定

DROP TABLE secret_records;
CREATE TABLE secret_records (
  user_name    VARCHAR2(9),
  service_type VARCHAR2(12),
  value        VARCHAR2(30),
  date_created DATE
);

INSERT INTO secret_records (
  user_name, service_type, value, date_created
)
VALUES ('Andy', 'Waiter', 'Serve dinner at Cafe Pete', SYSDATE);
 
INSERT INTO secret_records (
  user_name, service_type, value, date_created
)
VALUES ('Chuck', 'Merger', 'Buy company XYZ', SYSDATE);

ここでのトピック

• SQLインジェクション方法

• SQLインジェクションの回避

SQLインジェクション方法

すべてのSQLインジェクション方法は、1つの脆弱性(文字列入力が正常に検証されずに動的SQL文に連結される状況)を悪用しています。

ここでのトピック

• 文の変更

• 文のインジェクション

• データ型変換

文の変更

文の変更とは、アプリケーション開発者が意図していない方法で動的SQL文が実行されるように、その動的SQL文を故意に変更することを意味します。通常、ユーザーは、SELECT文のWHERE句を変更するか、UNION ALL句を挿入することによって不正データを取り出します。この方法の典型的な例は、WHERE句が常にTRUEになるようにしてパスワード認証をバイパスする方法です。

例7-9では、文の変更に対して脆弱なプロシージャを作成してから、そのプロシージャを文の変更がある場合とない場合で起動します。文の変更がある場合、プロシージャはシークレット・レコードを戻します。

例7-9 文の変更に対して脆弱なプロシージャ

脆弱なプロシージャの作成:

CREATE OR REPLACE PROCEDURE get_record (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2,
  rec          OUT VARCHAR2
)
IS
  query VARCHAR2(4000);
BEGIN
  -- Following SELECT statement is vulnerable to modification
  -- because it uses concatenation to build WHERE clause.
  query := 'SELECT value FROM secret_records WHERE user_name='''
           || user_name 
           || ''' AND service_type=''' 
           || service_type 
           || '''';
  DBMS_OUTPUT.PUT_LINE('Query: ' || query);
  EXECUTE IMMEDIATE query INTO rec ;
  DBMS_OUTPUT.PUT_LINE('Rec: ' || rec );
END;
/

SQLインジェクションが行われないプロシージャの例:

SET SERVEROUTPUT ON;

DECLARE
  record_value VARCHAR2(4000);
BEGIN
  get_record('Andy', 'Waiter', record_value);
END;
/

結果:

Query: SELECT value FROM secret_records WHERE user_name='Andy' AND
service_type='Waiter'
Rec: Serve dinner at Cafe Pete
 

文の変更の例:

DECLARE
  record_value VARCHAR2(4000);
BEGIN
  get_record(
  'Anybody '' OR service_type=''Merger''--',
  'Anything',
  record_value);
END;
/

結果:

Query: SELECT value FROM secret_records WHERE user_name='Anybody ' OR
service_type='Merger'--' AND service_type='Anything'
Rec: Buy company XYZ

PL/SQL procedure successfully completed.

文のインジェクション

文のインジェクションとは、動的SQL文に対してユーザーが1つ以上のSQL文を追加することを意味します。無名PL/SQLブロックは、この方法に対して脆弱です。

例7-10では、文のインジェクションに対して脆弱なプロシージャを作成してから、そのプロシージャを文のインジェクションがある場合とない場合で起動します。文のインジェクションがある場合、プロシージャは例7-9にあるシークレット・レコードを削除します。

例7-10 文のインジェクションに対して脆弱なプロシージャ

脆弱なプロシージャの作成:

CREATE OR REPLACE PROCEDURE p (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2
)
IS
  block1 VARCHAR2(4000);
BEGIN
  -- Following block is vulnerable to statement injection
  -- because it is built by concatenation.
  block1 :=
    'BEGIN
    DBMS_OUTPUT.PUT_LINE(''user_name: ' || user_name || ''');'
    || 'DBMS_OUTPUT.PUT_LINE(''service_type: ' || service_type || ''');
    END;';

  DBMS_OUTPUT.PUT_LINE('Block1: ' || block1);
  
  EXECUTE IMMEDIATE block1;
END;
/

SQLインジェクションが行われないプロシージャの例:

SET SERVEROUTPUT ON;

BEGIN
  p('Andy', 'Waiter');
END;
/

結果:

Block1: BEGIN
         DBMS_OUTPUT.PUT_LINE('user_name: Andy');
         DBMS_OUTPUT.PUT_LINE('service_type: Waiter');
       END;
user_name: Andy
service_type: Waiter

SQL*Plus書式設定コマンド:

COLUMN date_created FORMAT A12;

問合せ:

SELECT * FROM secret_records ORDER BY user_name;

結果:

USER_NAME SERVICE_TYPE VALUE                          DATE_CREATED
--------- ------------ ------------------------------ ------------
Andy      Waiter       Serve dinner at Cafe Pete      28-APR-10
Chuck     Merger       Buy company XYZ                28-APR-10

文の変更の例:

BEGIN
  p('Anybody', 'Anything'');
  DELETE FROM secret_records WHERE service_type=INITCAP(''Merger');
END;
/

結果:

Block1: BEGIN
       DBMS_OUTPUT.PUT_LINE('user_name: Anybody');
       DBMS_OUTPUT.PUT_LINE('service_type: Anything');
       DELETE FROM secret_records WHERE service_type=INITCAP('Merger');
     END;
user_name: Anybody
service_type: Anything

PL/SQL procedure successfully completed.

問合せ:

SELECT * FROM secret_records;

結果:

USER_NAME SERVICE_TYPE VALUE                          DATE_CREATED
--------- ------------ ------------------------------ ------------
Andy      Waiter       Serve dinner at Cafe Pete      18-MAR-09
 
1 row selected.

データ型変換

あまり知られていないSQLインジェクション方法として、NLSセッション・パラメータを使用してSQL文を変更またはインジェクトする方法があります。

動的SQL文のテキストに連結されている日時値または数値は、VARCHAR2データ型に変換する必要があります。この変換は、暗黙的(値が連結演算子のオペランドの場合)または明示的(値がTO_CHARファンクションの引数の場合)のいずれかで行われます。このデータ型変換は、動的SQL文を実行するデータベース・セッションのNLS設定によって異なります。日時値の変換では、特定の日時データ型に応じて、NLS_DATE_FORMATパラメータ、NLS_TIMESTAMP_FORMATパラメータまたはNLS_TIMESTAMP_TZ_FORMATパラメータで指定されている書式モデルが使用されます。数値の変換では、NLS_NUMERIC_CHARACTERSパラメータで指定されている小数点およびグループ・セパレータが適用されます。

日時書式モデルの1つとして、"text"があります。textは、変換結果にコピーされます。たとえば、NLS_DATE_FORMATの値が'"Month:" Month'の場合、6月にはTO_CHAR(SYSDATE)によって'Month: June'が戻されます。この日時書式モデルは、例7-11に示すように、悪用される可能性があります。

例7-11 データ型変換によるSQLインジェクションに対して脆弱なプロシージャ

SELECT * FROM secret_records;
 

結果:

USER_NAME SERVICE_TYPE VALUE                          DATE_CREATE
--------- ------------ ------------------------------ -----------
Andy      Waiter       Serve dinner at Cafe Pete      28-APR-2010
Chuck     Merger       Buy company XYZ                28-APR-2010

脆弱なプロシージャの作成:

-- Return records not older than a month

CREATE OR REPLACE PROCEDURE get_recent_record (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2,
  rec          OUT VARCHAR2
)
IS
  query VARCHAR2(4000);
BEGIN
  /* Following SELECT statement is vulnerable to modification
     because it uses concatenation to build WHERE clause
     and because SYSDATE depends on the value of NLS_DATE_FORMAT. */

  query := 'SELECT value FROM secret_records WHERE user_name='''
           || user_name
           || ''' AND service_type='''
           || service_type
           || ''' AND date_created>'''
           || (SYSDATE - 30)
           || '''';

  DBMS_OUTPUT.PUT_LINE('Query: ' || query);
  EXECUTE IMMEDIATE query INTO rec;
  DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
END;
/

SQLインジェクションが行われないプロシージャの例:

SET SERVEROUTPUT ON;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

DECLARE
  record_value VARCHAR2(4000);
BEGIN
  get_recent_record('Andy', 'Waiter', record_value);
END;
/

結果:

Query: SELECT value FROM secret_records WHERE user_name='Andy' AND
service_type='Waiter' AND date_created>'29-MAR-2010'
Rec: Serve dinner at Cafe Pete
  

文の変更の例:

ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"';

DECLARE
  record_value VARCHAR2(4000);
BEGIN
  get_recent_record('Anybody', 'Anything', record_value);
END;
/

結果:

Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND
service_type='Anything' AND date_created>'' OR service_type='Merger'
Rec: Buy company XYZ
 
PL/SQL procedure successfully completed.

SQLインジェクションの回避

PL/SQLアプリケーションで動的SQLを使用する場合は、入力テキストをチェックして、それが意図したとおりのものであることを確認する必要があります。次の方法を使用できます。

• バインド変数

• 妥当性チェック

• 明示的な書式モデル

バインド変数

PL/SQLコードをSQLインジェクション攻撃に対して強固にする最も効率的な方法は、バインド変数を使用することです。データベースでは、バインド変数の値が排他的に使用され、その内容は解釈されません。(バインド変数を使用すると、パフォーマンスも向上します。)

例7-12に示すプロシージャは、(例7-9の脆弱なプロシージャのように連結を使用するのではなく)バインド変数を使用して動的SQL文を作成するため、SQLインジェクションに対して強固です。これと同じバインド方法で、例7-10に示した脆弱なプロシージャを修正できます。

例7-12 SQLインジェクションを回避するためのバインド変数

強固なプロシージャの作成:

CREATE OR REPLACE PROCEDURE get_record_2 (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2,
  rec          OUT VARCHAR2
)
IS
  query VARCHAR2(4000);
BEGIN
  query := 'SELECT value FROM secret_records
            WHERE user_name=:a
            AND service_type=:b';
 
  DBMS_OUTPUT.PUT_LINE('Query: ' || query);
 
  EXECUTE IMMEDIATE query INTO rec USING user_name, service_type;
 
  DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
END;
/
 

SQLインジェクションが行われないプロシージャの例:

SET SERVEROUTPUT ON;
DECLARE
  record_value VARCHAR2(4000);
BEGIN
  get_record_2('Andy', 'Waiter', record_value);
END;
/

結果:

Query: SELECT value FROM secret_records
            WHERE user_name=:a
            AND service_type=:b
Rec: Serve dinner at Cafe Pete
 
PL/SQL procedure successfully completed.
 

文の変更の試行:

DECLARE
  record_value VARCHAR2(4000);
BEGIN
  get_record_2('Anybody '' OR service_type=''Merger''--',
               'Anything',
               record_value);
END;
/

結果:

Query: SELECT value FROM secret_records
            WHERE user_name=:a
            AND service_type=:b
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "HR.GET_RECORD_2", line 14
ORA-06512: at line 4

妥当性チェック

ユーザー入力が意図したとおりのものになっていることを確認するために、常にプログラムでユーザー入力を検証する必要があります。たとえば、ユーザーがDELETE文に対して部門番号を渡した場合は、departments表から選択することによって、この部門番号の妥当性をチェックします。同様に、ユーザーが削除対象の表の名前を入力した場合は、静的データ・ディクショナリ・ビューALL_TABLESから選択することによって、この表が存在していることを確認します。

注意: ユーザー名とそのパスワードの妥当性をチェックする場合は、無効な項目に関係なく、常に同じエラーを戻してください。そうしない場合、エラー・メッセージ「無効なパスワード」を受信し、「無効なユーザー名」は受信していない(またはその逆の状況の)悪意のあるユーザーが、これらのうちの1つについては推測がうまく当たったことに気付く可能性があります。

妥当性チェック・コードでは、多くの場合、DBMS_ASSERTパッケージ内のサブプログラムが有効です。たとえば、例7-13のようにDBMS_ASSERT.ENQUOTE_LITERALファンクションを使用して、文字列リテラルを引用符で囲むことができます。これによって、悪意のあるユーザーが、開き引用符とそれに対応する閉じ引用符の間にテキストをインジェクトできなくなります。

注意: DBMS_ASSERTのサブプログラムは妥当性コードで有効ですが、妥当性コードに置き換わるものではありません。たとえば、入力文字列は、(DBMS_ASSERT.QUALIFIED_SQL_NAMEによって検証された)修飾SQL名であっても、不正なパスワードである可能性があります。

関連項目: DBMS_ASSERTサブプログラムの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

例7-13では、プロシージャraise_emp_salaryはemployees表を更新する前に、渡された列名の妥当性をチェックします。その後、無名ブロックによって、動的PL/SQLブロックと動的SQL文の両方からこのプロシージャが起動されます。

例7-13 SQLインジェクションを回避するための妥当性チェック

CREATE OR REPLACE PROCEDURE raise_emp_salary (
  column_value  NUMBER,
  emp_column    VARCHAR2,
  amount NUMBER )
IS
  v_column  VARCHAR2(30);
  sql_stmt  VARCHAR2(200);
BEGIN
  -- Check validity of column name that was given as input:
  SELECT column_name INTO v_column
  FROM USER_TAB_COLS
  WHERE TABLE_NAME = 'EMPLOYEES'
  AND COLUMN_NAME = emp_column;

  sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '
    || DBMS_ASSERT.ENQUOTE_NAME(v_column,FALSE) || ' = :2';

  EXECUTE IMMEDIATE sql_stmt USING amount, column_value;

  -- If column name is valid:
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Salaries were updated for: '
      || emp_column || ' = ' || column_value);
  END IF;

  -- If column name is not valid:
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/

DECLARE
  plsql_block  VARCHAR2(500);
BEGIN
  -- Invoke raise_emp_salary from a dynamic PL/SQL block:
  plsql_block :=
    'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';

  EXECUTE IMMEDIATE plsql_block
    USING 110, 'DEPARTMENT_ID', 10;

  -- Invoke raise_emp_salary from a dynamic SQL statement:
  EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
    USING 112, 'EMPLOYEE_ID', 10;
END;
/

結果:

Salaries were updated for: DEPARTMENT_ID = 110
Salaries were updated for: EMPLOYEE_ID = 112

明示的な書式モデル

SQL文またはPL/SQL文のテキストに連結されている日時値または数値を使用しており、これらの値をバインド引数として渡すことができない場合は、実行中のセッションのNLSパラメータの値に依存しない明示的な書式モデルを使用して、これらの値をテキストに変換します。変換された値がSQLの日時リテラルまたは数値リテラルの書式になっているかどうかを確認します。セキュリティ面からのみではなく、動的SQL文がすべてのグローバリゼーション環境で正常に実行されるようにするためにも、ロケールに依存しない明示的な書式モデルを使用してSQL文を構成することをお薦めします。

例7-14に示すプロシージャは、SQLインジェクションに対して強固です。例7-11の脆弱なプロシージャのように暗黙的ではなく、TO_CHARファンクションおよびロケールに依存しない書式モデルを使用して、日時パラメータ値のSYSDATE - 30をVARCHAR2値に明示的に変換するためです。

例7-14 SQLインジェクションを回避するための明示的な書式モデル

強固なプロシージャの作成:

-- Return records not older than a month

CREATE OR REPLACE PROCEDURE get_recent_record (
  user_name     IN  VARCHAR2,
  service_type  IN  VARCHAR2,
  rec           OUT VARCHAR2
)
IS
  query VARCHAR2(4000);
BEGIN
  /* Following SELECT statement is vulnerable to modification
     because it uses concatenation to build WHERE clause. */

  query := 'SELECT value FROM secret_records WHERE user_name='''
           || user_name 
           || ''' AND service_type=''' 
           || service_type 
           || ''' AND date_created> DATE ''' 
           || TO_CHAR(SYSDATE - 30,'YYYY-MM-DD') 
           || '''';

  DBMS_OUTPUT.PUT_LINE('Query: ' || query);
  EXECUTE IMMEDIATE query INTO rec;
  DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
END;
/

文の変更の試行:

ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"'; 

DECLARE
  record_value VARCHAR2(4000);
BEGIN
  get_recent_record('Anybody', 'Anything', record_value);
END;
/

結果:

Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND 
service_type='Anything' AND date_created> DATE '2010-03-29' 
DECLARE 
* 
ERROR at line 1: 
ORA-01403: no data found 
ORA-06512: at "SYS.GET_RECENT_RECORD", line 21 
ORA-06512: at line 4