動的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ファンクション」を使用して、それらを切り替えることができます。
ここでのトピック
PL/SQLでは、次のSQLを実行するために動的SQLが必要となります。
コンパイル時に不明なテキストが含まれているSQL
たとえば、コンパイル時に不明な識別子(表名など)が含まれているSELECT
文、コンパイル時に副次句の数が不明なWHERE
句などです。
静的SQLとしてサポートされていないSQL
つまり、「静的SQLの説明」で示されていないSQL構文のことです。
動的SQLが必要ない場合は、静的SQLを使用します。静的SQLには、次のようなメリットがあります。
コンパイルが正常に完了すると、静的SQL文が有効なデータベース・オブジェクトを参照していること、およびそれらのオブジェクトへのアクセスに必要な権限が存在していることが保証されます。
コンパイルが正常に完了すると、スキーマ・オブジェクトの依存性が作成されます。
スキーマ・オブジェクトの依存性の詳細は、『Oracle Database開発ガイド』を参照してください。
PL/SQLでの静的SQL文の使用方法の詳細は、「PL/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
文は、システム固有の動的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; /
動的SQL文が複数行を戻すSELECT
文である場合は、システム固有の動的SQLを使用してその文を次のように処理できます。
OPEN
FOR
文を使用して、カーソル変数を動的SQL文に関連付けます。OPEN
FOR
文のUSING
句で、動的SQL文内の各プレースホルダにバインド変数を指定します。
USING
句に、リテラルNULL
を含めることはできません。この制限を回避するには、例7-7に示すように、NULL
を使用する場所に未初期化変数を使用します。
FETCH
文を使用して、結果セットの行を一度に1行、複数行またはすべて取り出します。
CLOSE
文を使用して、カーソル変数をクローズします。
コレクションが「コレクションの問合せ」に示す条件を満たしていれば、動的SQL文でコレクションを問い合せることができます。
関連項目:
OPEN FOR文の構文の詳細は、「OPEN FOR文」を参照してください
FETCH文の構文の詳細は、「FETCH文」を参照してください
CLOSE文の構文の詳細は、「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; /
動的SQL文内でプレースホルダ名を繰り返す場合は、プレースホルダをバインド変数に関連付ける方法が動的SQL文の種類によって異なるということに注意してください。
ここでのトピック
動的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文が無名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;
/
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
サブプログラムはリモートで起動できます。
関連項目:
システム固有の動的SQLの詳細は、「システム固有の動的SQL」を参照してください
入力変数または出力変数の数が不明な動的SQL文の実行方法(「メソッド4」)を含むDBMS_SQL
パッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください
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
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: 011.44.1644.429265 Titles: - Sales Manager (01-JAN-07 - 31-DEC-07) - Sales Representative (24-MAR-06 - 31-DEC-06) PL/SQL procedure successfully completed.
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; /
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; /
SQLインジェクションでは、SQL文内でクライアントから提供されるデータを使用するアプリケーションを悪用することによってデータベースに不正にアクセスし、制限付きデータを表示または操作します。
この項では、PL/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);
文の変更とは、アプリケーション開発者が意図していない方法で動的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.
文のインジェクションとは、動的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.
あまり知られていない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.
PL/SQLアプリケーションで動的SQLを使用する場合は、入力テキストをチェックして、それが意図したとおりのものであることを確認する必要があります。
次の方法を使用できます。
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
ユーザー入力が意図したとおりのものになっていることを確認するために、常にプログラムでユーザー入力を検証する必要があります。
たとえば、ユーザーが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
セキュリティ面からのみではなく、動的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