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パッケージを使用する必要があります。また、ストアド・サブプログラムが問合せ結果を暗黙的に(OUT REF CURSORパラメータを使用するのではなく)戻すようにする場合にも、DBMS_SQLパッケージを使用する必要があります。

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

ここでのトピック

• 動的SQLが必要な場合

• システム固有の動的SQL

• DBMS_SQLパッケージ

• SQLインジェクション

7.1 動的SQLが必要な場合

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

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

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

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

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

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

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

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

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

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

7.2 システム固有の動的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文内でのプレースホルダ名の繰返し

7.2.1 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文がサブプログラムを起動する場合は、次のことを確認します。

  • サブプログラムが、スキーマ・レベルで作成されているか、またはパッケージ仕様部で宣言および定義されていること。

  • サブプログラム・パラメータのプレースホルダに対応しているすべてのバインド変数のパラメータ・モードがそのサブプログラム・パラメータと同じであること、およびこのバインド変数のデータ型がそのサブプログラム・パラメータのデータ型と互換性があること。

  • いずれのバインド変数も予約語NULLではないこと。

    この制限を回避するには、例7-7に示すように、NULLを使用する場所に未初期化変数を使用します。

  • いずれのバインド変数も、SQLでサポートされないデータ型(文字列により索引付けされる連想配列など)を持たないこと。

    データ型がコレクションまたはレコード型である場合は、パッケージ仕様部で宣言されている必要があります。

注意:

バインド変数はどのような順序でも評価できます。プログラムにより評価の順序が決定される場合は、プログラムで決定が行われる時点で動作は定義されません。

例7-4、例7-5および例7-6では、動的PL/SQLブロックは、PL/SQLコレクション型の仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。コレクション型はSQLデータ型ではありません。各例では、コレクション型はパッケージ仕様部で宣言され、サブプログラムはパッケージ仕様部で宣言され、パッケージ本体で定義されます。

関連項目:

• スキーマ・レベルでのファンクション作成の詳細は、「CREATE FUNCTION文」を参照してください

• スキーマ・レベルでのプロシージャ作成の詳細は、「CREATE PROCEDURE文」を参照してください

• パッケージの詳細は、「PL/SQLパッケージ」を参照してください

• パッケージ内のサブプログラムの宣言の詳細は、「CREATE PACKAGE文」を参照してください

• パッケージ内のサブプログラムの宣言および定義の詳細は、「CREATE PACKAGE BODY文」を参照してください

• パッケージ仕様での型の宣言の詳細は、「CREATE PACKAGE文」を参照してください

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

• コレクション型の詳細は、「PL/SQLのコレクションおよびレコード」を参照してください

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

この例では、動的PL/SQLブロックは、スキーマ・レベルで作成されたサブプログラムを起動する無名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
) AUTHID DEFINER 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 BOOLEAN仮パラメータを持つサブプログラムの動的起動

この例では、動的PL/SQLブロックは、PL/SQL(SQLではなく)データ型BOOLEANの仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。

CREATE OR REPLACE PROCEDURE p (x BOOLEAN) AUTHID DEFINER AS
BEGIN
  IF x THEN
    DBMS_OUTPUT.PUT_LINE('x is true');
  END IF;
END;
/

DECLARE
  dyn_stmt VARCHAR2(200);
  b        BOOLEAN := TRUE;
BEGIN
  dyn_stmt := 'BEGIN p(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING b;
END;
/

結果:

x is true

例7-3 RECORD仮パラメータを持つサブプログラムの動的起動

この例では、動的PL/SQLブロックは、PL/SQL(SQLではなく)データ型RECORDの仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。レコード型はパッケージ仕様部で宣言され、サブプログラムはパッケージ仕様部で宣言され、パッケージ本体で定義されます。

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
 
  TYPE rec IS RECORD (n1 NUMBER, n2 NUMBER);
 
  PROCEDURE p (x OUT rec, y NUMBER, z NUMBER);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
 
  PROCEDURE p (x OUT rec, y NUMBER, z NUMBER) AS
  BEGIN
    x.n1 := y;
    x.n2 := z;
  END p;
END pkg;
/
DECLARE
  r       pkg.rec;
  dyn_str VARCHAR2(3000);
BEGIN
  dyn_str := 'BEGIN pkg.p(:x, 6, 8); END;';
 
  EXECUTE IMMEDIATE dyn_str USING OUT r;
 
  DBMS_OUTPUT.PUT_LINE('r.n1 = ' || r.n1);
  DBMS_OUTPUT.PUT_LINE('r.n2 = ' || r.n2);
END;
/

例7-4 連想を持つサブプログラムの動的起動配列仮パラメータ

この例では、動的PL/SQLブロックは、PLS_INTEGERで索引付けされる連想配列のPL/SQLコレクション型の仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。

注意:

このコンテキストで使用される連想配列は、PLS_INTEGERを使用して索引付けする必要があります。

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
 
  TYPE number_names IS TABLE OF VARCHAR2(5)
    INDEX BY PLS_INTEGER;
 
  PROCEDURE print_number_names (x number_names);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print_number_names (x number_names) IS
  BEGIN
    FOR i IN x.FIRST .. x.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(x(i));
    END LOOP;
  END;
END pkg;
/
DECLARE  
  digit_names  pkg.number_names;
  dyn_stmt     VARCHAR2(3000);
BEGIN
  digit_names(0) := 'zero';
  digit_names(1) := 'one';
  digit_names(2) := 'two';
  digit_names(3) := 'three';
  digit_names(4) := 'four';
  digit_names(5) := 'five';
  digit_names(6) := 'six';
  digit_names(7) := 'seven';
  digit_names(8) := 'eight';
  digit_names(9) := 'nine';
 
  dyn_stmt := 'BEGIN pkg.print_number_names(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING digit_names;
END;
/

例7-5 ネストした表仮パラメータを持つサブプログラムの動的起動

この例では、動的PL/SQLブロックは、ネストした表のPL/SQLコレクション型の仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
 
  TYPE names IS TABLE OF VARCHAR2(10);
 
  PROCEDURE print_names (x names);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print_names (x names) IS
  BEGIN
    FOR i IN x.FIRST .. x.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(x(i));
    END LOOP;
  END;
END pkg;
/
DECLARE
  fruits   pkg.names;
  dyn_stmt VARCHAR2(3000);
BEGIN
  fruits := pkg.names('apple', 'banana', 'cherry');
  
  dyn_stmt := 'BEGIN pkg.print_names(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING fruits;
END;
/

例7-6 VARRAY仮パラメータを持つサブプログラムの動的起動

この例では、動的PL/SQLブロックは、PL/SQLコレクション型VARRAYの仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
 
  TYPE foursome IS VARRAY(4) OF VARCHAR2(5);
 
  PROCEDURE print_foursome (x foursome);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print_foursome (x foursome) IS
  BEGIN
    IF x.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Empty');
    ELSE 
      FOR i IN x.FIRST .. x.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(x(i));
      END LOOP;
    END IF;
  END;
END pkg;
/
DECLARE
  directions pkg.foursome;
  dyn_stmt VARCHAR2(3000);
BEGIN
  directions := pkg.foursome('north', 'south', 'east', 'west');
  
  dyn_stmt := 'BEGIN pkg.print_foursome(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING directions;
END;
/

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

この例では、未初期化変数を使用して、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;
/

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

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

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

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

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

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

コレクションが「コレクションの問合せ」に示す条件を満たしていれば、動的SQL文でコレクションを問い合せることができます。

関連項目:

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

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

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

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

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

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;
/

例7-9 システム固有の動的SQLを使用したコレクションの問合せ

この例は例6-30と似ていますが、コレクション変数v1がバインド変数である点が異なります。

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
  TYPE rec IS RECORD(f1 NUMBER, f2 VARCHAR2(30));
  TYPE mytab IS TABLE OF rec INDEX BY pls_integer;
END;
/

DECLARE
  v1 pkg.mytab;  -- collection of records
  v2 pkg.rec;
  c1 SYS_REFCURSOR;
BEGIN
  OPEN c1 FOR 'SELECT * FROM TABLE(:1)' USING v1;
  FETCH c1 INTO v2;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE('Values in record are ' || v2.f1 || ' and ' || v2.f2);
END;
/

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

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

ここでのトピック

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

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

7.2.3.1 動的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)

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

動的SQL文が無名PL/SQLブロックまたはCALL文を表している場合、プレースホルダ名の繰返しは重要です。

一意のプレースホルダ名が、それぞれUSING句内に対応するバインド変数を持っている必要があります。プレースホルダ名を繰り返す場合、それに対応するバインド変数を繰り返す必要はありません。そのプレースホルダ名に対するすべての参照がUSING句内の1つのバインド変数に対応します。

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

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

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;
/

7.3 DBMS_SQLパッケージ

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

次のいずれかが該当する場合は、DBMS_SQLパッケージを使用して、動的SQL文を実行する必要があります。

• 実行時までSELECTリストが不明。

• 実行時まで、SELECTまたはDML文でバインドする必要があるプレースホルダが不明。

• ストアド・サブプログラムが問合せ結果を暗黙的に(OUT REF CURSORパラメータを使用するのではなく)戻すようにする必要があり、それにはDBMS_SQL.RETURN_RESULTプロシージャが必要である。

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

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

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

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

ここでのトピック

• DBMS_SQL.RETURN_RESULTプロシージャ

• DBMS_SQL.GET_NEXT_RESULTプロシージャ

• DBMS_SQL.TO_REFCURSORファンクション

• DBMS_SQL.TO_CURSOR_NUMBERファンクション

注意:

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

関連項目:

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

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

7.3.1 DBMS_SQL.RETURN_RESULTプロシージャ

DBMS_SQL.RETURN_RESULTプロシージャは、ストアド・サブプログラムが問合せ結果をクライアント・プログラム(サブプログラムを間接的に起動する)またはサブプログラムの直接のコール元に暗黙的に戻すことを可能にします。DBMS_SQL.RETURN_RESULTによって結果が戻された後、受信者のみがこれにアクセスできます。

DBMS_SQL.RETURN_RESULTには次の2つのオーバーロードがあります。

PROCEDURE RETURN_RESULT (rc IN OUT SYS_REFCURSOR,
                         to_client IN BOOLEAN DEFAULT TRUE);

PROCEDURE RETURN_RESULT (rc IN OUT INTEGER,
                         to_client IN BOOLEAN DEFAULT TRUE);

rcパラメータは、オープンされているカーソル変数(SYS_REFCURSOR)またはオープン・カーソルのカーソル番号(INTEGER)です。カーソルをオープンしてのそのカーソル番号を取得するには、DBMS_SQL.OPEN_CURSORファンクション(『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照)を起動します。

to_clientパラメータがTRUEの場合(デフォルト)、DBMS_SQL.RETURN_RESULTプロシージャは問合せ結果を(サブプログラムを間接的に起動する)クライアント・プログラムに戻します。このパラメータがFALSEの場合、プロシージャは問合せ結果をサブプログラムの直接のコール元に戻します。

関連項目:

• DBMS_SQL.RETURN_RESULTの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

• Cおよび.NETでの暗黙的問合せ結果のサポートの詳細は、Oracle Call Interfaceプログラマーズ・ガイドを参照してください。

• 暗黙的問合せ結果に対するSQL*Plusのサポートの詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください

• 暗黙的問合せ結果を使用するサブプログラムの移行の詳細は、『Oracle Database移行ガイド』を参照してください。

例7-11 DBMS_SQL.RETURN_RESULTプロシージャ

この例では、プロシージャpがオプションのto_clientパラメータ(デフォルトではTRUE)なしで、DBMS_SQL.RETURN_RESULTを起動します。したがって、DBMS_SQL.RETURN_RESULTは問合せ結果をサブプログラム・クライアント(pを起動する無名ブロック)に戻します。pから無名ブロックに戻された結果には、その無名ブロックのみがアクセスできます。

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
  c1 SYS_REFCURSOR;
  c2 SYS_REFCURSOR;
BEGIN
  OPEN c1 FOR
    SELECT first_name, last_name
    FROM employees
    WHERE employee_id = 176;
 
  DBMS_SQL.RETURN_RESULT (c1);
  -- Now p cannot access the result.
 
  OPEN c2 FOR
    SELECT city, state_province
    FROM locations
    WHERE country_id = 'AU';
 
  DBMS_SQL.RETURN_RESULT (c2);
  -- Now p cannot access the result.
END;
/
BEGIN
  p;
END;
/

結果:

ResultSet #1

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jonathon             Taylor

ResultSet #2

CITY                           STATE_PROVINCE
------------------------------ -------------------------
Sydney                         New South Wales

7.3.2 DBMS_SQL.GET_NEXT_RESULTプロシージャ

DBMS_SQL.GET_NEXT_RESULTプロシージャは、DBMS_SQL.RETURN_RESULTプロシージャにより受信者に戻された次の結果を取得します。2つのプロシージャは結果を同じ順序で戻します。

DBMS_SQL.GET_NEXT_RESULTには次の2つのオーバーロードがあります。

PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT SYS_REFCURSOR);

PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT INTEGER);

cパラメータは、DBMS_SQL.RETURN_RESULTプロシージャを使用して問合せ結果を暗黙的に戻すサブプログラムを直接的または間接的に起動するオープン・カーソルのカーソル番号です。

カーソルをオープンしてのそのカーソル番号を取得するには、DBMS_SQL.OPEN_CURSORファンクションを起動します。DBMS_SQL.OPEN_CURSORにはオプションのパラメータtreat_as_client_for_resultsがあります。このパラメータがFALSEの場合(デフォルト)、このカーソルをオープンする(サブプログラムを起動するために)コール元は、DBMS_SQL.RETURN_RESULTを使用するサブプログラムからクライアントに対する問合せ結果を受け取るクライアントとして扱われません。これらの問合せ結果はかわりに上位層のクライアントに戻されます。このパラメータがTRUEの場合、コール元はクライアントとして扱われます。DBMS_SQL.OPEN_CURSORファンクションの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

rcパラメータは、カーソル変数(SYS_REFCURSOR)またはオープン・カーソルのカーソル番号(INTEGER)です。

例7-12では、プロシージャget_employee_infoはDBMS_SQL.RETURN_RESULTを使用して2つの問合せ結果をクライアント・プログラムに戻し、また無名ブロック<<main>>により動的に起動されています。<<main>>はget_employee_infoが戻す2つの問合せ結果を受け取る必要があるため、<<main>>はパラメータtreat_as_client_for_resultsをTRUEに設定したDBMS_SQL.OPEN_CURSORを使用して、get_employee_infoを起動するためのカーソルをオープンします。したがって、DBMS_SQL.GET_NEXT_RESULTは結果を<<main>>に戻し、<<main>>はカーソルrcを使用してその結果をフェッチします。

例7-12 DBMS_SQL.GET_NEXT_RESULTプロシージャ

CREATE OR REPLACE PROCEDURE get_employee_info (id IN VARCHAR2) AUTHID DEFINER AS
  rc  SYS_REFCURSOR;
BEGIN
  -- Return employee info
 
  OPEN rc FOR SELECT first_name, last_name, email, phone_number
              FROM employees
              WHERE employee_id = id;
  DBMS_SQL.RETURN_RESULT(rc);
 
  -- Return employee job history
 
  OPEN RC FOR SELECT job_title, start_date, end_date
              FROM job_history jh, jobs j
              WHERE jh.employee_id = id AND
                    jh.job_id = j.job_id
              ORDER BY start_date DESC;
  DBMS_SQL.RETURN_RESULT(rc);
END;
/
<<main>>
DECLARE
  c            INTEGER;
  rc           SYS_REFCURSOR;
  n            NUMBER;
 
  first_name   VARCHAR2(20);
  last_name    VARCHAR2(25);
  email        VARCHAR2(25);
  phone_number VARCHAR2(20);
 
  job_title    VARCHAR2(35);
  start_date   DATE;
  end_date     DATE;
 
BEGIN
 
  c := DBMS_SQL.OPEN_CURSOR(true);
  DBMS_SQL.PARSE(c, 'BEGIN get_employee_info(:id); END;', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(c, ':id', 176);
  n := DBMS_SQL.EXECUTE(c);
 
  -- Get employee info
 
  dbms_sql.get_next_result(c, rc);
  FETCH rc INTO first_name, last_name, email, phone_number;
 
  DBMS_OUTPUT.PUT_LINE('Employee: '||first_name || ' ' || last_name);
  DBMS_OUTPUT.PUT_LINE('Email: ' ||email);
  DBMS_OUTPUT.PUT_LINE('Phone: ' ||phone_number);
 
  -- Get employee job history
 
  DBMS_OUTPUT.PUT_LINE('Titles:');
  DBMS_SQL.GET_NEXT_RESULT(c, rc);
  LOOP
    FETCH rc INTO job_title, start_date, end_date;
    EXIT WHEN rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE
      ('- '||job_title||' ('||start_date||' - ' ||end_date||')');
  END LOOP;
 
  DBMS_SQL.CLOSE_CURSOR(c);
END main;
/

結果:

Employee: Jonathon Taylor
Email: JTAYLOR
Phone: [phone]
Titles:
- Sales Manager (01-JAN-07 - 31-DEC-07)
- Sales Representative (24-MAR-06 - 31-DEC-06)
 
PL/SQL procedure successfully completed.

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

例7-13 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
) AUTHID DEFINER
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;
/

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

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

CREATE OR REPLACE PROCEDURE do_query_2 (
  sql_stmt VARCHAR2
) AUTHID DEFINER
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;
/

7.4 SQLインジェクション

SQLインジェクションでは、SQL文内でクライアントから提供されるデータを使用するアプリケーションを悪用することによってデータベースに不正にアクセスし、制限付きデータを表示または操作します。

この項では、PL/SQLでのSQLインジェクションの脆弱性、およびそれらの回避方法について説明します。

ここでのトピック

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

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

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

例を試すには、次の文を実行します。

Live SQL:

この例は、Oracle Live SQLの「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);

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

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

ここでのトピック

• 文の変更

• 文のインジェクション

• データ型変換

7.4.1.1 文の変更

文の変更とは、アプリケーション開発者が意図していない方法で動的SQL文が実行されるように、その動的SQL文を故意に変更することを意味します。

通常、ユーザーは、SELECT文のWHERE句を変更するか、UNION ALL句を挿入することによって不正データを取り出します。この方法の典型的な例は、WHERE句が常にTRUEになるようにしてパスワード認証をバイパスする方法です。

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

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

Live SQL:

この例は、Oracle Live SQLの「SQLインジェクションのデモ」で表示および実行できます

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

CREATE OR REPLACE PROCEDURE get_record (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2,
  rec          OUT VARCHAR2
) AUTHID DEFINER
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.

7.4.1.2 文のインジェクション

文のインジェクションとは、動的SQL文に対してユーザーが1つ以上のSQL文を追加することを意味します。

無名PL/SQLブロックは、この方法に対して脆弱です。

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

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

Live SQL:

この例は、Oracle Live SQLの「SQLインジェクションのデモ」で表示および実行できます

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

CREATE OR REPLACE PROCEDURE p (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2
) AUTHID DEFINER
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.

7.4.1.3 データ型変換

あまり知られていない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-18に示すように、悪用される可能性があります。

例7-18 データ型変換による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
) AUTHID DEFINER
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.

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

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

次の方法を使用できます。

• バインド変数

• 妥当性チェック

• 明示的な書式モデル

7.4.2.1 バインド変数

PL/SQLコードをSQLインジェクション攻撃に対して強固にする最も効率的な方法は、バインド変数を使用することです。

データベースでは、バインド変数の値が排他的に使用され、その内容は解釈されません。(バインド変数を使用すると、パフォーマンスも向上します。)

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

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

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

CREATE OR REPLACE PROCEDURE get_record_2 (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2,
  rec          OUT VARCHAR2
) AUTHID DEFINER
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 15
ORA-06512: at line 4

7.4.2.2 妥当性チェック

ユーザー入力が意図したとおりのものになっていることを確認するために、常にプログラムでユーザー入力を検証する必要があります。

たとえば、ユーザーがDELETE文に対して部門番号を渡した場合は、departments表から選択することによって、この部門番号の妥当性をチェックします。同様に、ユーザーが削除対象の表の名前を入力した場合は、静的データ・ディクショナリ・ビューALL_TABLESから選択することによって、この表が存在していることを確認します。

注意:

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

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

注意:

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

関連項目:

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

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

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

CREATE OR REPLACE PROCEDURE raise_emp_salary (
  column_value  NUMBER,
  emp_column    VARCHAR2,
  amount NUMBER ) AUTHID DEFINER
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

7.4.2.3 明示的な書式モデル

セキュリティ面からのみではなく、動的SQL文がすべてのグローバリゼーション環境で正常に実行されるようにするためにも、ロケールに依存しない明示的な書式モデルを使用してSQL文を構成することをお薦めします。

SQL文またはPL/SQL文のテキストに連結されている日時値または数値を使用しており、これらの値をバインド引数として渡すことができない場合は、実行中のセッションのNLSパラメータの値に依存しない明示的な書式モデルを使用して、これらの値をテキストに変換します。変換された値がSQLの日時リテラルまたは数値リテラルの書式になっているかどうかを確認します。

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

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

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

-- 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
) AUTHID DEFINER
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