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