動的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章「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文がサブプログラムを起動する場合は、次のことを確認します。
サブプログラム・パラメータのプレースホルダに対応しているすべてのバインド変数のパラメータ・モードがそのサブプログラム・パラメータと同じであること(例7-1を参照)、およびこのバインド変数のデータ型がそのサブプログラム・パラメータのデータ型と互換性があること。(互換性のあるデータ型の詳細は、「サブプログラムの仮パラメータと実パラメータ」を参照してください。)
SQLでサポートされていないデータ型(例7-2
で示されているBOOLEANなど)を持つバインド変数がないこと。
USING
句に、リテラルNULL
を含めることはできません。この制限を回避するには、例7-3
に示すように、NULLを使用する場所に未初期化変数を使用します。
EXECUTE
IMMEDIATE
文の構文の詳細は、「EXECUTE IMMEDIATE文」を参照してください。
例7-1 動的PL/SQLブロックからのサブプログラムの起動
-- Subprogram that dynamic PL/SQL block invokes: CREATE OR REPLACE PROCEDURE create_dept ( deptid IN OUT NUMBER, dname IN VARCHAR2, mgrid IN NUMBER, locid IN NUMBER ) AS BEGIN deptid := departments_seq.NEXTVAL; INSERT INTO departments ( department_id, department_name, manager_id, location_id ) VALUES (deptid, dname, mgrid, locid); END; / DECLARE plsql_block VARCHAR2(500); new_deptid NUMBER(4); new_dname VARCHAR2(30) := 'Advertising'; new_mgrid NUMBER(6) := 200; new_locid NUMBER(4) := 1700; BEGIN -- Dynamic PL/SQL block invokes subprogram: plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;'; /* Specify bind variables in USING clause. Specify mode for first parameter. Modes of other parameters are correct by default. */ EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptid, new_dname, new_mgrid, new_locid; END; /
例7-2 システム固有の動的SQLではサポートされていないデータ型
DECLARE dyn_stmt VARCHAR2(200); b1 BOOLEAN; FUNCTION f (x INTEGER) RETURN BOOLEAN AS BEGIN NULL; END f; BEGIN dyn_stmt := 'BEGIN :b := f(5); END;'; EXECUTE IMMEDIATE dyn_stmt USING OUT b1; END; /
結果:
EXECUTE IMMEDIATE dyn_stmt USING OUT b1; * ERROR at line 15: ORA-06550: line 15, column 40: PLS-00457: expressions have to be of SQL types ORA-06550: line 15, column 3: PL/SQL: Statement ignored
動的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 variable in USING clause: OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER'; -- Fetch rows from result set one at a time: LOOP FETCH v_emp_cursor INTO emp_record; EXIT WHEN v_emp_cursor%NOTFOUND; END LOOP; -- Close cursor: CLOSE v_emp_cursor; END; /
動的SQL文内でプレースホルダ名を繰り返す場合は、プレースホルダをバインド変数に関連付ける方法が動的SQL文の種類によって異なるということに注意してください。
ここでのトピック
動的SQL文が無名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の整数であるため、コール境界を越えて渡し、格納することができます。
実行時まで次の情報がいずれも不明な場合は、DBMS_SQL
パッケージを使用して、動的SQL文を実行する必要があります。
SELECT
リスト
SELECT
またはDML文でバインドする必要があるプレースホルダ
次の状況では、DBMS_SQL
パッケージではなく、システム固有の動的SQLを使用する必要があります。
動的SQL文が行を取り出してレコードに入れる場合。
INSERT
文、UPDATE
文、DELETE
文、MERGE
文または単一行のSELECT
文である動的SQL文の発行後に、SQLのカーソル属性%FOUND
、%ISOPEN
、%NOTFOUND
または%ROWCOUNT
を使用する場合。
システム固有の動的SQLの詳細は、「システム固有の動的SQL」を参照してください。
DBMS_SQL
パッケージとシステム固有の動的SQLの両方が必要な場合は、次のものを使用して、それらを切り替えることができます。
注意: DBMS_SQL サブプログラムはリモートで起動できます。 |
DBMS_SQL
.TO_REFCURSOR
ファンクションは、SQLカーソル番号を、システム固有の動的SQL文で使用できる弱いカーソル変数に変換します。
SQLカーソル番号をDBMS_SQL
.TO_REFCURSOR
ファンクションに渡す前に、この番号に対してOPEN
、PARSE
およびEXECUTE
を実行する必要があります(そうしない場合、エラーが発生します)。
SQLカーソル番号をREF
CURSOR
変数に変換した後、DBMS_SQL
の操作では、SQLカーソル番号としてではなく、REF
CURSOR
変数としてのみこの値にアクセスできます。たとえば、DBMS_SQL
.IS_OPEN
ファンクションを使用して、変換されたSQLカーソル番号がまだオープンしているかどうかを確認すると、エラーが発生します。
例7-6では、DBMS_SQL
.TO_REFCURSOR
ファンクションを使用して、DBMS_SQL
パッケージからシステム固有の動的SQLに切り替えています。
例7-6 DBMS_SQLパッケージからシステム固有の動的SQLへの切替え
CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200); / CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER; / CREATE OR REPLACE PROCEDURE do_query_1 ( placeholder vc_array, bindvars vc_array, sql_stmt VARCHAR2 ) IS TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; bindnames vc_array; empnos numlist; depts numlist; ret NUMBER; isopen BOOLEAN; BEGIN -- Open SQL cursor number: curid := DBMS_SQL.OPEN_CURSOR; -- Parse SQL cursor number: DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE); bindnames := placeholder; -- Bind variables: FOR i IN 1 .. bindnames.COUNT LOOP DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i)); END LOOP; -- Run SQL cursor number: ret := DBMS_SQL.EXECUTE(curid); -- Switch from DBMS_SQL to native dynamic SQL: src_cur := DBMS_SQL.TO_REFCURSOR(curid); FETCH src_cur BULK COLLECT INTO empnos, depts; -- This would cause an error because curid was converted to a REF CURSOR: -- isopen := DBMS_SQL.IS_OPEN(curid); CLOSE src_cur; END; /
DBMS_SQL
.TO_CURSOR_NUMBER
ファンクションは、(強いか弱いかに関係なく)REF
CURSOR
変数を、DBMS_SQL
サブプログラムに渡すことができるSQLカーソル番号に変換します。
REF
CURSOR
変数をDBMS_SQL
.TO_CURSOR_NUMBER
ファンクションに渡す前に、この変数に対してOPEN
を実行する必要があります。
REF
CURSOR
変数をSQLカーソル番号に変換した後、システム固有の動的SQL操作では、この値にアクセスできなくなります。
例7-7では、DBMS_SQL
.TO_CURSOR_NUMBER
ファンクションを使用して、システム固有の動的SQLからDBMS_SQL
パッケージに切り替えています。
例7-7 システム固有の動的SQLからDBMS_SQLパッケージへの切替え
CREATE OR REPLACE PROCEDURE do_query_2 ( sql_stmt VARCHAR2 ) IS TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; desctab DBMS_SQL.DESC_TAB; colcnt NUMBER; namevar VARCHAR2(50); numvar NUMBER; datevar DATE; empno NUMBER := 100; BEGIN -- sql_stmt := SELECT ... FROM employees WHERE employee_id = :b1'; -- Open REF CURSOR variable: OPEN src_cur FOR sql_stmt USING empno; -- Switch from native dynamic SQL to DBMS_SQL package: curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur); DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab); -- Define columns: FOR i IN 1 .. colcnt LOOP IF desctab(i).col_type = 2 THEN DBMS_SQL.DEFINE_COLUMN(curid, i, numvar); ELSIF desctab(i).col_type = 12 THEN DBMS_SQL.DEFINE_COLUMN(curid, i, datevar); -- statements ELSE DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50); END IF; END LOOP; -- Fetch rows with DBMS_SQL package: WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP FOR i IN 1 .. colcnt LOOP IF (desctab(i).col_type = 1) THEN DBMS_SQL.COLUMN_VALUE(curid, i, namevar); ELSIF (desctab(i).col_type = 2) THEN DBMS_SQL.COLUMN_VALUE(curid, i, numvar); ELSIF (desctab(i).col_type = 12) THEN DBMS_SQL.COLUMN_VALUE(curid, i, datevar); -- statements END IF; END LOOP; END LOOP; DBMS_SQL.CLOSE_CURSOR(curid); END; /
SQLインジェクションでは、SQL文内でクライアントから提供されるデータを使用するアプリケーションを悪用することによってデータベースに不正にアクセスし、制限付きデータを表示または操作します。この項では、PL/SQLでのSQLインジェクションの脆弱性、およびそれらの回避方法について説明します。
この項の例を実行してみるには、HR
スキーマに接続し、例7-8の文を実行します。
例7-8 SQLインジェクションの例のための設定
DROP TABLE secret_records; CREATE TABLE secret_records ( user_name VARCHAR2(9), service_type VARCHAR2(12), value VARCHAR2(30), date_created DATE ); INSERT INTO secret_records ( user_name, service_type, value, date_created ) VALUES ('Andy', 'Waiter', 'Serve dinner at Cafe Pete', SYSDATE); INSERT INTO secret_records ( user_name, service_type, value, date_created ) VALUES ('Chuck', 'Merger', 'Buy company XYZ', SYSDATE);
ここでのトピック
すべてのSQLインジェクション方法は、1つの脆弱性(文字列入力が正常に検証されずに動的SQL文に連結される状況)を悪用しています。
ここでのトピック
文の変更とは、アプリケーション開発者が意図していない方法で動的SQL文が実行されるように、その動的SQL文を故意に変更することを意味します。通常、ユーザーは、SELECT
文のWHERE
句を変更するか、UNION
ALL
句を挿入することによって不正データを取り出します。この方法の典型的な例は、WHERE
句が常にTRUE
になるようにしてパスワード認証をバイパスする方法です。
例7-9では、文の変更に対して脆弱なプロシージャを作成してから、そのプロシージャを文の変更がある場合とない場合で起動します。文の変更がある場合、プロシージャはシークレット・レコードを戻します。
例7-9 文の変更に対して脆弱なプロシージャ
脆弱なプロシージャの作成:
CREATE OR REPLACE PROCEDURE get_record ( user_name IN VARCHAR2, service_type IN VARCHAR2, rec OUT VARCHAR2 ) IS query VARCHAR2(4000); BEGIN -- Following SELECT statement is vulnerable to modification -- because it uses concatenation to build WHERE clause. query := 'SELECT value FROM secret_records WHERE user_name=''' || user_name || ''' AND service_type=''' || service_type || ''''; DBMS_OUTPUT.PUT_LINE('Query: ' || query); EXECUTE IMMEDIATE query INTO rec ; DBMS_OUTPUT.PUT_LINE('Rec: ' || rec ); END; /
SQLインジェクションが行われないプロシージャの例:
SET SERVEROUTPUT ON; DECLARE record_value VARCHAR2(4000); BEGIN get_record('Andy', 'Waiter', record_value); END; /
結果:
Query: SELECT value FROM secret_records WHERE user_name='Andy' AND service_type='Waiter' Rec: Serve dinner at Cafe Pete
文の変更の例:
DECLARE
record_value VARCHAR2(4000);
BEGIN
get_record(
'Anybody '' OR service_type=''Merger''--',
'Anything',
record_value);
END;
/
結果:
Query: SELECT value FROM secret_records WHERE user_name='Anybody ' OR service_type='Merger'--' AND service_type='Anything' Rec: Buy company XYZ PL/SQL procedure successfully completed.
文のインジェクションとは、動的SQL文に対してユーザーが1つ以上のSQL文を追加することを意味します。無名PL/SQLブロックは、この方法に対して脆弱です。
例7-10では、文のインジェクションに対して脆弱なプロシージャを作成してから、そのプロシージャを文のインジェクションがある場合とない場合で起動します。文のインジェクションがある場合、プロシージャは例7-9にあるシークレット・レコードを削除します。
例7-10 文のインジェクションに対して脆弱なプロシージャ
脆弱なプロシージャの作成:
CREATE OR REPLACE PROCEDURE p ( user_name IN VARCHAR2, service_type IN VARCHAR2 ) IS block1 VARCHAR2(4000); BEGIN -- Following block is vulnerable to statement injection -- because it is built by concatenation. block1 := 'BEGIN DBMS_OUTPUT.PUT_LINE(''user_name: ' || user_name || ''');' || 'DBMS_OUTPUT.PUT_LINE(''service_type: ' || service_type || '''); END;'; DBMS_OUTPUT.PUT_LINE('Block1: ' || block1); EXECUTE IMMEDIATE block1; END; /
SQLインジェクションが行われないプロシージャの例:
SET SERVEROUTPUT ON; BEGIN p('Andy', 'Waiter'); END; /
結果:
Block1: BEGIN DBMS_OUTPUT.PUT_LINE('user_name: Andy'); DBMS_OUTPUT.PUT_LINE('service_type: Waiter'); END; user_name: Andy service_type: Waiter
SQL*Plus書式設定コマンド:
COLUMN date_created FORMAT A12;
問合せ:
SELECT * FROM secret_records ORDER BY user_name;
結果:
USER_NAME SERVICE_TYPE VALUE DATE_CREATED --------- ------------ ------------------------------ ------------ Andy Waiter Serve dinner at Cafe Pete 28-APR-10 Chuck Merger Buy company XYZ 28-APR-10
文の変更の例:
BEGIN p('Anybody', 'Anything''); DELETE FROM secret_records WHERE service_type=INITCAP(''Merger'); END; /
結果:
Block1: BEGIN DBMS_OUTPUT.PUT_LINE('user_name: Anybody'); DBMS_OUTPUT.PUT_LINE('service_type: Anything'); DELETE FROM secret_records WHERE service_type=INITCAP('Merger'); END; user_name: Anybody service_type: Anything PL/SQL procedure successfully completed.
問合せ:
SELECT * FROM secret_records;
結果:
USER_NAME SERVICE_TYPE VALUE DATE_CREATED
--------- ------------ ------------------------------ ------------
Andy Waiter Serve dinner at Cafe Pete 18-MAR-09
1 row selected.
あまり知られていないSQLインジェクション方法として、NLSセッション・パラメータを使用してSQL文を変更またはインジェクトする方法があります。
動的SQL文のテキストに連結されている日時値または数値は、VARCHAR2
データ型に変換する必要があります。この変換は、暗黙的(値が連結演算子のオペランドの場合)または明示的(値がTO_CHAR
ファンクションの引数の場合)のいずれかで行われます。このデータ型変換は、動的SQL文を実行するデータベース・セッションのNLS設定によって異なります。日時値の変換では、特定の日時データ型に応じて、NLS_DATE_FORMAT
パラメータ、NLS_TIMESTAMP_FORMAT
パラメータまたはNLS_TIMESTAMP_TZ_FORMAT
パラメータで指定されている書式モデルが使用されます。数値の変換では、NLS_NUMERIC_CHARACTERS
パラメータで指定されている小数点およびグループ・セパレータが適用されます。
日時書式モデルの1つとして、"
text
"
があります。text
は、変換結果にコピーされます。たとえば、NLS_DATE_FORMAT
の値が'"Month:" Month'
の場合、6月にはTO_CHAR(SYSDATE)
によって'Month: June'
が戻されます。この日時書式モデルは、例7-11に示すように、悪用される可能性があります。
例7-11 データ型変換によるSQLインジェクションに対して脆弱なプロシージャ
SELECT * FROM secret_records;
結果:
USER_NAME SERVICE_TYPE VALUE DATE_CREATE --------- ------------ ------------------------------ ----------- Andy Waiter Serve dinner at Cafe Pete 28-APR-2010 Chuck Merger Buy company XYZ 28-APR-2010
脆弱なプロシージャの作成:
-- Return records not older than a month CREATE OR REPLACE PROCEDURE get_recent_record ( user_name IN VARCHAR2, service_type IN VARCHAR2, rec OUT VARCHAR2 ) IS query VARCHAR2(4000); BEGIN /* Following SELECT statement is vulnerable to modification because it uses concatenation to build WHERE clause and because SYSDATE depends on the value of NLS_DATE_FORMAT. */ query := 'SELECT value FROM secret_records WHERE user_name=''' || user_name || ''' AND service_type=''' || service_type || ''' AND date_created>''' || (SYSDATE - 30) || ''''; DBMS_OUTPUT.PUT_LINE('Query: ' || query); EXECUTE IMMEDIATE query INTO rec; DBMS_OUTPUT.PUT_LINE('Rec: ' || rec); END; /
SQLインジェクションが行われないプロシージャの例:
SET SERVEROUTPUT ON;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
DECLARE
record_value VARCHAR2(4000);
BEGIN
get_recent_record('Andy', 'Waiter', record_value);
END;
/
結果:
Query: SELECT value FROM secret_records WHERE user_name='Andy' AND service_type='Waiter' AND date_created>'29-MAR-2010' Rec: Serve dinner at Cafe Pete
文の変更の例:
ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"';
DECLARE
record_value VARCHAR2(4000);
BEGIN
get_recent_record('Anybody', 'Anything', record_value);
END;
/
結果:
Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND service_type='Anything' AND date_created>'' OR service_type='Merger' Rec: Buy company XYZ PL/SQL procedure successfully completed.
PL/SQLアプリケーションで動的SQLを使用する場合は、入力テキストをチェックして、それが意図したとおりのものであることを確認する必要があります。次の方法を使用できます。
PL/SQLコードをSQLインジェクション攻撃に対して強固にする最も効率的な方法は、バインド変数を使用することです。データベースでは、バインド変数の値が排他的に使用され、その内容は解釈されません。(バインド変数を使用すると、パフォーマンスも向上します。)
例7-12に示すプロシージャは、(例7-9の脆弱なプロシージャのように連結を使用するのではなく)バインド変数を使用して動的SQL文を作成するため、SQLインジェクションに対して強固です。これと同じバインド方法で、例7-10に示した脆弱なプロシージャを修正できます。
例7-12 SQLインジェクションを回避するためのバインド変数
強固なプロシージャの作成:
CREATE OR REPLACE PROCEDURE get_record_2 ( user_name IN VARCHAR2, service_type IN VARCHAR2, rec OUT VARCHAR2 ) IS query VARCHAR2(4000); BEGIN query := 'SELECT value FROM secret_records WHERE user_name=:a AND service_type=:b'; DBMS_OUTPUT.PUT_LINE('Query: ' || query); EXECUTE IMMEDIATE query INTO rec USING user_name, service_type; DBMS_OUTPUT.PUT_LINE('Rec: ' || rec); END; /
SQLインジェクションが行われないプロシージャの例:
SET SERVEROUTPUT ON; DECLARE record_value VARCHAR2(4000); BEGIN get_record_2('Andy', 'Waiter', record_value); END; /
結果:
Query: SELECT value FROM secret_records WHERE user_name=:a AND service_type=:b Rec: Serve dinner at Cafe Pete PL/SQL procedure successfully completed.
文の変更の試行:
DECLARE record_value VARCHAR2(4000); BEGIN get_record_2('Anybody '' OR service_type=''Merger''--', 'Anything', record_value); END; /
結果:
Query: SELECT value FROM secret_records WHERE user_name=:a AND service_type=:b DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at "HR.GET_RECORD_2", line 14 ORA-06512: at line 4
ユーザー入力が意図したとおりのものになっていることを確認するために、常にプログラムでユーザー入力を検証する必要があります。たとえば、ユーザーがDELETE
文に対して部門番号を渡した場合は、departments
表から選択することによって、この部門番号の妥当性をチェックします。同様に、ユーザーが削除対象の表の名前を入力した場合は、静的データ・ディクショナリ・ビューALL_TABLES
から選択することによって、この表が存在していることを確認します。
注意: ユーザー名とそのパスワードの妥当性をチェックする場合は、無効な項目に関係なく、常に同じエラーを戻してください。そうしない場合、エラー・メッセージ「無効なパスワード」を受信し、「無効なユーザー名」は受信していない(またはその逆の状況の)悪意のあるユーザーが、これらのうちの1つについては推測がうまく当たったことに気付く可能性があります。 |
妥当性チェック・コードでは、多くの場合、DBMS_ASSERT
パッケージ内のサブプログラムが有効です。たとえば、例7-13
のようにDBMS_ASSERT
.ENQUOTE_LITERALファンクションを使用して、文字列リテラルを引用符で囲むことができます。これによって、悪意のあるユーザーが、開き引用符とそれに対応する閉じ引用符の間にテキストをインジェクトできなくなります。
注意: DBMS_ASSERT のサブプログラムは妥当性コードで有効ですが、妥当性コードに置き換わるものではありません。たとえば、入力文字列は、(DBMS_ASSERT .QUALIFIED_SQL_NAME によって検証された)修飾SQL名であっても、不正なパスワードである可能性があります。 |
関連項目: DBMS_ASSERTサブプログラムの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』 を参照してください。 |
例7-13では、プロシージャraise_emp_salary
はemployees
表を更新する前に、渡された列名の妥当性をチェックします。その後、無名ブロックによって、動的PL/SQLブロックと動的SQL文の両方からこのプロシージャが起動されます。
例7-13 SQLインジェクションを回避するための妥当性チェック
CREATE OR REPLACE PROCEDURE raise_emp_salary ( column_value NUMBER, emp_column VARCHAR2, amount NUMBER ) IS v_column VARCHAR2(30); sql_stmt VARCHAR2(200); BEGIN -- Check validity of column name that was given as input: SELECT column_name INTO v_column FROM USER_TAB_COLS WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column; sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' || DBMS_ASSERT.ENQUOTE_NAME(v_column,FALSE) || ' = :2'; EXECUTE IMMEDIATE sql_stmt USING amount, column_value; -- If column name is valid: IF SQL%ROWCOUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('Salaries were updated for: ' || emp_column || ' = ' || column_value); END IF; -- If column name is not valid: EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column); END raise_emp_salary; / DECLARE plsql_block VARCHAR2(500); BEGIN -- Invoke raise_emp_salary from a dynamic PL/SQL block: plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'; EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10; -- Invoke raise_emp_salary from a dynamic SQL statement: EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;' USING 112, 'EMPLOYEE_ID', 10; END; /
結果:
Salaries were updated for: DEPARTMENT_ID = 110 Salaries were updated for: EMPLOYEE_ID = 112
SQL文またはPL/SQL文のテキストに連結されている日時値または数値を使用しており、これらの値をバインド引数として渡すことができない場合は、実行中のセッションのNLSパラメータの値に依存しない明示的な書式モデルを使用して、これらの値をテキストに変換します。変換された値がSQLの日時リテラルまたは数値リテラルの書式になっているかどうかを確認します。セキュリティ面からのみではなく、動的SQL文がすべてのグローバリゼーション環境で正常に実行されるようにするためにも、ロケールに依存しない明示的な書式モデルを使用してSQL文を構成することをお薦めします。
例7-14に示すプロシージャは、SQLインジェクションに対して強固です。例7-11
の脆弱なプロシージャのように暗黙的ではなく、TO_CHAR
ファンクションおよびロケールに依存しない書式モデルを使用して、日時パラメータ値のSYSDATE
-
30
をVARCHAR2値に明示的に変換するためです。
例7-14 SQLインジェクションを回避するための明示的な書式モデル
強固なプロシージャの作成:
-- Return records not older than a month
CREATE OR REPLACE PROCEDURE get_recent_record (
user_name IN VARCHAR2,
service_type IN VARCHAR2,
rec OUT VARCHAR2
)
IS
query VARCHAR2(4000);
BEGIN
/* Following SELECT statement is vulnerable to modification
because it uses concatenation to build WHERE clause. */
query := 'SELECT value FROM secret_records WHERE user_name='''
|| user_name
|| ''' AND service_type='''
|| service_type
|| ''' AND date_created> DATE '''
|| TO_CHAR(SYSDATE - 30,'YYYY-MM-DD')
|| '''';
DBMS_OUTPUT.PUT_LINE('Query: ' || query);
EXECUTE IMMEDIATE query INTO rec;
DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
END;
/
文の変更の試行:
ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"';
DECLARE
record_value VARCHAR2(4000);
BEGIN
get_recent_record('Anybody', 'Anything', record_value);
END;
/
結果:
Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND service_type='Anything' AND date_created> DATE '2010-03-29' DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at "SYS.GET_RECENT_RECORD", line 21 ORA-06512: at line 4