| Oracle Database PL/SQL言語リファレンス 11g リリース1（11.1） E05670-03 |
|
 戻る |
 次へ |
動的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ファンクションを使用して、それらを切り替えることができます。
ここでのトピック:
PL/SQLでは、次のSQLを実行するために動的SQLが必要となります。
コンパイル時に不明なテキストが含まれているSQL
たとえば、コンパイル時に不明な識別子（表名など）が含まれているSELECT文、コンパイル時に副次句の数が不明なWHERE句などです。
静的SQLとしてサポートされていないSQL
つまり、「静的SQLの説明」で示されていないSQL構文のことです。
動的SQLが必要ない場合は、静的SQLを使用します。静的SQLには、次のようなメリットがあります。
コンパイルが正常に完了すると、静的SQL文が有効なデータベース・オブジェクトを参照していること、およびそれらのオブジェクトへのアクセスに必要な権限が存在していることが保証されます。
コンパイルが正常に完了すると、スキーマ・オブジェクトの依存性が作成されます。
スキーマ・オブジェクトの依存性の詳細は、『Oracle Database概要』を参照してください。
PL/SQLでの静的SQL文の使用方法の詳細は、第6章「静的SQLの使用」を参照してください。
システム固有の動的SQLでは、EXECUTE IMMEDIATE文を使用してほとんどの動的SQL文を処理します。
動的SQL文が複数行を戻すSELECT文である場合、システム固有の動的SQLでは、次のいずれかを選択できます。
BULK COLLECT INTO句を指定してEXECUTE IMMEDIATE文を使用します。
OPEN-FOR文、FETCH文およびCLOSE文を使用します。
SQLのカーソル属性は、システム固有の動的SQLのINSERT文、UPDATE文、DELETE文および単一行のSELECT文の後では、それらの文が静的SQL文に対して動作する場合と同様に動作します。 SQLのカーソル属性の詳細は、「PL/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文がサブプログラムを起動する場合は、次のことを確認します。
サブプログラム・パラメータのプレースホルダに対応しているすべてのバインド引数のパラメータ・モードがそのサブプログラム・パラメータと同じであること（例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 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 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 arguments 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; /
動的SQL文が複数行を戻すSELECT文である場合は、システム固有の動的SQLを使用してその文を次のように処理できます。
OPEN-FOR文を使用して、カーソル変数を動的SQL文に関連付けます。 OPEN-FOR文のUSING句で、動的SQL文内の各プレースホルダにバインド引数を指定します。
USING句に、リテラルNULLを含めることはできません。 この制限を回避するには、例7-3に示すように、NULLを使用する場所に未初期化変数を使用します。
構文の詳細は、「OPEN-FOR文」を参照してください。
FETCH文を使用して、結果セットの行を一度に1行、複数行またはすべて取り出します。
構文の詳細は、「FETCH文」を参照してください。
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 argument 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文が無名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-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パッケージは、SQLカーソル番号と呼ばれるエンティティを定義します。 SQLカーソル番号は、PL/SQLの整数であるため、コール境界を越えて渡し、格納することができます。 また、SQLカーソル番号を使用して、実行中のSQL文に関する情報を取得することもできます。
実行時まで次の情報がいずれも不明な場合は、DBMS_SQLパッケージを使用して、動的SQL文を実行する必要があります。
SELECTリスト
SELECTまたはDML文でバインドする必要があるプレースホルダ
次の状況では、DBMS_SQLパッケージではなく、システム固有の動的SQLを使用する必要があります。
動的SQL文が行を取り出してレコードに入れる場合。
INSERT文、UPDATE文、DELETE文または単一行のSELECT文である動的SQL文の発行後に、SQLのカーソル属性%FOUND、%ISOPEN、%NOTFOUNDまたは%ROWCOUNTを使用する場合。
システム固有の動的SQLの詳細は、「システム固有の動的SQLの使用」を参照してください。
DBMS_SQLパッケージとシステム固有の動的SQLの両方が必要な場合は、次のものを使用して、それらを切り替えることができます。
|
注意: DBMS_SQLサブプログラムはリモートで起動できます。 |
DBMS_SQL.TO_REFCURSORファンクションは、SQLカーソル番号を、システム固有の動的SQL文で使用できるPL/SQLのデータ型REF CURSORの弱い型指定の変数に変換します。
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 arguments: FOR i IN 1 .. bindnames.COUNT LOOP DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i)); END LOOP; -- Execute 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ファンクションは、（強い型指定か弱い型指定かに関係なく）REF CURSOR変数を、DBMS_SQLサブプログラムに渡すことができるSQLカーソル番号に変換します。
REF CURSOR変数をDBMS_SQL.TO_CURSORファンクションに渡す前に、この変数に対してOPENを実行する必要があります。
REF CURSOR変数をSQLカーソル番号に変換した後、システム固有の動的SQL操作では、この値にアクセスできなくなります。
FETCH操作の開始後、DBMS_SQLのカーソル番号をDBMS_SQL.TO_REFCURSORファンクションまたはDBMS_SQL.TO_CURSORファンクションに渡すと、エラーが発生します。
例7-7では、DBMS_SQL.TO_CURSORファンクションを使用して、システム固有の動的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文内でクライアントから提供されるデータを使用するアプリケーションを悪用し、それによって、制限付きデータを表示または操作するためにデータベースに不正にアクセスする方法です。 この項では、PL/SQLでのSQLインジェクションの脆弱性、およびそれらの回避方法について説明します。
この項の例を実行してみるには、HRスキーマに接続し、例7-8の文を実行します。
例7-8 SQLインジェクションの例のための設定
CREATE TABLE secret_records (
user_name VARCHAR2(9),
service_type VARCHAR2(12),
value VARCHAR2(30),
date_created DATE);
INSERT INTO secret_records
VALUES ('Andy', 'Waiter', 'Serve dinner at Cafe Pete', SYSDATE);
INSERT INTO secret_records
VALUES ('Chuck', 'Merger', 'Buy company XYZ', SYSDATE);
ここでのトピック:
様々なSQLインジェクション方法がありますが、それらはすべて単一の脆弱性（文字列入力が正常に検証されずに動的SQL文に連結される状況）を悪用しています。 ここでは、SQLインジェクション攻撃を次のように分類します。
文の変更とは、アプリケーション開発者が意図していない方法で動的SQL文が実行されるように、その動的SQL文を故意に変更することを意味します。 通常、ユーザーは、SELECT文のWHERE句を変更するか、UNION ALL句を挿入することによって不正データを取り出します。 この方法の典型的な例は、WHERE句が常にTRUEになるようにしてパスワード認証をバイパスする方法です。
例7-9のSQL*Plusスクリプトでは、文の変更に対して脆弱なプロシージャを作成してから、そのプロシージャを文の変更がある場合とない場合で起動します。 文の変更がある場合、プロシージャはシークレット・レコードを戻します。
例7-9 文の変更に対して脆弱なプロシージャ
SQL> REM Create vulnerable procedure SQL> SQL> CREATE OR REPLACE PROCEDURE get_record (user_name IN VARCHAR2, service_type IN VARCHAR2, record 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 record; DBMS_OUTPUT.PUT_LINE('Record: ' || record); END; / Procedure created. SQL> REM Demonstrate procedure without SQL injection SQL> SQL> SET SERVEROUTPUT ON; SQL> SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_record('Andy', 'Waiter', record_value); 5 END; 6 / Query: SELECT value FROM secret_records WHERE user_name='Andy' AND service_type='Waiter' Record: Serve dinner at Cafe Pete PL/SQL procedure successfully completed. SQL> SQL> REM Example of statement modification SQL> SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_record( 5 'Anybody '' OR service_type=''Merger''--', 6 'Anything', 7 record_value); 8 END; 9 / Query: SELECT value FROM secret_records WHERE user_name='Anybody ' OR service_type='Merger'--' AND service_type='Anything' Record: Buy company XYZ PL/SQL procedure successfully completed. SQL>
文のインジェクションとは、動的SQL文に対してユーザーが1つ以上の新規SQL文を追加することを意味します。 無名PL/SQLブロックは、この方法に対して脆弱です。
例7-10のSQL*Plusスクリプトでは、文のインジェクションに対して脆弱なプロシージャを作成してから、そのプロシージャをインジェクションの変更がある場合とない場合で起動します。 文のインジェクションがある場合、プロシージャは例7-9にあるシークレット・レコードを削除します。
例7-10 文のインジェクションに対して脆弱なプロシージャ
SQL> REM Create vulnerable procedure SQL> SQL> CREATE OR REPLACE PROCEDURE p 2 (user_name IN VARCHAR2, 3 service_type IN VARCHAR2) 4 IS 5 block VARCHAR2(4000); 6 BEGIN -- Following block is vulnerable to statement injection -- because it is built by concatenation. 7 block := 8 'BEGIN 9 DBMS_OUTPUT.PUT_LINE(''user_name: ' || user_name || ''');' 10 || 'DBMS_OUTPUT.PUT_LINE(''service_type: ' || service_type || '''); 11 END;'; 12 13 DBMS_OUTPUT.PUT_LINE('Block: ' || block); 14 15 EXECUTE IMMEDIATE block; 16 END; 17 / Procedure created. SQL> SQL> REM Demonstrate procedure without SQL injection SQL> SQL> SET SERVEROUTPUT ON; SQL> SQL> BEGIN 2 p('Andy', 'Waiter'); 3 END; 4 / Block: BEGIN DBMS_OUTPUT.PUT_LINE('user_name: Andy'); DBMS_OUTPUT.PUT_LINE('service_type: Waiter'); END; user_name: Andy service_type: Waiter PL/SQL procedure successfully completed. SQL> REM Example of statement modification SQL> SQL> SELECT * FROM secret_records; USER_NAME SERVICE_TYPE VALUE --------- ------------ ------------------------------ Andy Waiter Serve dinner at Cafe Pete Chuck Merger Buy company XYZ 2 rows selected. SQL> SQL> BEGIN 2 p('Anybody', 'Anything''); 3 DELETE FROM secret_records WHERE service_type=INITCAP(''Merger'); 4 END; 5 / Block: 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. SQL> SELECT * FROM secret_records; USER_NAME SERVICE_TYPE VALUE --------- ------------ ------------------------------ Andy Waiter Serve dinner at Cafe Pete 1 row selected. SQL>
あまり知られていない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インジェクションに対して脆弱なプロシージャ
SQL> REM Create vulnerable procedure SQL> REM Return records not older than a month SQL> SQL> CREATE OR REPLACE PROCEDURE get_recent_record (user_name IN VARCHAR2, service_type IN VARCHAR2, record 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 record; DBMS_OUTPUT.PUT_LINE('Record: ' || record); END; / . Procedure created. . SQL> REM Demonstrate procedure without SQL injection SQL> SQL> SET SERVEROUTPUT ON; SQL> SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY'; . Session altered. . SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_recent_record('Andy', 'Waiter', record_value); 5 END; 6 / Query: SELECT value FROM secret_records WHERE user_name='Andy' AND service_type='Waiter' AND date_created>'27-MAY-2008' Record: Serve dinner at Cafe Pete PL/SQL procedure successfully completed. SQL> SQL> REM Example of statement modification SQL> SQL> ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"'; . Session altered. . SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_recent_record('Anybody', 'Anything', record_value); 5 END; 6 / Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND service_type='Anything' AND date_created>'' OR service_type='Merger' Record: 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インジェクションの回避
SQL> REM Create invulnerable procedure SQL> SQL> CREATE OR REPLACE PROCEDURE get_record_2 2 (user_name IN VARCHAR2, 3 service_type IN VARCHAR2, 4 record OUT VARCHAR2) 5 IS 6 query VARCHAR2(4000); 7 BEGIN 8 query := 'SELECT value FROM secret_records 9 WHERE user_name=:a 10 AND service_type=:b'; 11 12 DBMS_OUTPUT.PUT_LINE('Query: ' || query); 13 14 EXECUTE IMMEDIATE query INTO record USING user_name, service_type; 15 16 DBMS_OUTPUT.PUT_LINE('Record: ' || record); 17 END; 18 / Procedure created. SQL> REM Demonstrate procedure without SQL injection SQL> SQL> SET SERVEROUTPUT ON; SQL> SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_record_2('Andy', 'Waiter', record_value); 5 END; 6 / Query: SELECT value FROM secret_records WHERE user_name=:a AND service_type=:b Record: Serve dinner at Cafe Pete PL/SQL procedure successfully completed. SQL> SQL> REM Attempt statement modification SQL> SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_record_2('Anybody '' OR service_type=''Merger''--', 5 'Anything', 6 record_value); 7 END; 8 / 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 SQL>
ユーザー入力が意図したとおりのものになっていることを確認するために、常にプログラムでユーザー入力を検証する必要があります。 たとえば、ユーザーが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ブロックによって、動的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; /
SQL文またはPL/SQL文のテキストに連結されている日時値または数値を使用しており、これらの値をバインド引数として渡すことができない場合は、実行中のセッションのNLSパラメータの値に依存しない明示的な書式モデルを使用して、これらの値をテキストに変換します。 変換された値がSQLの日時リテラルまたは数値リテラルの書式になっているかどうかを確認します。 セキュリティ面からのみではなく、動的SQL文がすべてのグローバリゼーション環境で正常に実行されるようにするためにも、ロケールに依存しない明示的な書式モデルを使用してSQL文を構成することをお薦めします。
例7-14に示すプロシージャは、SQLインジェクションに対して強固です。例7-11の脆弱なプロシージャのように暗黙的ではなく、TO_CHARファンクションおよびロケールに依存しない書式モデルを使用して、日時パラメータ値のSYSDATE - 30をVARCHAR2値に明示的に変換するためです。
例7-14 明示的な書式モデルを使用したSQLインジェクションの回避
SQL> REM Create invulnerable procedure SQL> REM Return records not older than a month SQL> SQL> CREATE OR REPLACE PROCEDURE get_recent_record (user_name IN VARCHAR2, service_type IN VARCHAR2, record 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 record; DBMS_OUTPUT.PUT_LINE('Record: ' || record); END; / . Procedure created. . SQL> SQL> REM Attempt statement modification SQL> SQL> ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"'; . Session altered. . SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_recent_record('Anybody', 'Anything', record_value); 5 END; 6 / Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND service_type='Anything' AND date_created> DATE '2008-05-27' DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at "SYS.GET_RECENT_RECORD", line 18 ORA-06512: at line 4 . SQL>
