10gリリース2（10.2）
B19248-02
 目次 |
 索引 |
| Oracle Database アプリケーション開発者ガイド-基礎編 10gリリース2（10.2） B19248-02 |
|
この章の内容は次のとおりです。
何度実行されても変化しない静的SQLとは異なり、動的SQLを使用すると実行時に文字列としてSQL文を作成できます。この文字列にはSQL文またはPL/SQLブロックのテキストが含まれ、バインド引数のプレースホルダを含めることもできます。
SQL文のすべてのテキストはコンパイル時にわかっているわけではないため、動的SQLを使用すると、汎用的で柔軟なアプリケーションを作成できます。動的SQLは、PL/SQL、Pro*C/C++およびJavaなど、複数の異なる開発環境で使用できます。
動的SQLを使用するアプリケーションの例として、データ・ウェアハウス環境のレポート・アプリケーションでは、実行時まで表の名前がわからない場合があります。これらの表には、inv_01_2003、inv_04_2003、inv_07_2003、inv_10_2003、inv_01_2004など、四半期の開始月と年に従った名前が割り当てられます。この場合、レポート・アプリケーションで動的SQLを使用し、表の名前を実行時に指定するようにできます。
また、ユーザーが選択するソート順で複合問合せを実行する場合もあります。それぞれ異なるORDER BY句を使用して問合せのコーディングを2回行うかわりに、指定のORDER BY句を含む問合せを動的に作成できます。
一貫性を持たせるために、この章では主にPL/SQLの観点から動的SQLについて説明します。ほとんどの動的SQL文を処理するには、EXECUTE IMMEDIATE文を使用します。PL/SQLプロシージャで複数行問合せを処理するには、OPEN-FOR文、FETCH文およびCLOSE文を使用します。
Oracle Databaseでは、PL/SQLアプリケーションに次の方法で動的SQL文を実装できます。
この章では、動的SQLに対するPL/SQLサポートについて説明しましたが、その他の言語でも動的SQLをコールできます。
動的SQLの実行にOCI、Pro*C/C++またはPro*COBOLを使用するプログラムの場合は、PL/SQLストアド・プロシージャおよびストアド・ファンクション内のネイティブ動的SQLに切り替えることを考慮してください。クライアント側アプリケーションからの動的SQL操作に必要なネットワーク・ラウンドトリップは、パフォーマンスを低下させる場合があります。ストアド・プロシージャはサーバーに常駐できるため、ネットワークのオーバヘッドをなくすことができます。PL/SQLストアド・プロシージャおよびストアド・ファンクションは、OCI、Pro*C/C++またはPro*COBOLのアプリケーションからコールできます。
動的SQLと静的SQLには、それぞれメリットとデメリットがあります。静的SQL文はコンパイル時に完全なテキストがわかっているもので、そのメリットは次のとおりです。
静的SQLには前述のメリットがありますが、次のように動的SQLで解決できる制約もあります。
原則として、静的SQLでは目的が達成できない場合、または静的SQLでは使用が煩雑になる場合のみ、動的SQLを使用する必要があります。次の各項では、動的SQLを使用する必要のある典型的な状況について説明します。
次の種類の文をPL/SQLプログラム・ユニット内で実行できるのは、動的SQLのみです。
次のネイティブ動的SQLの例では、SELECT文をTABLE句とともに使用しています。
-- Create an object t_emp and a datatype t_emplist as a table of type t_emp CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20)) / CREATE TYPE t_emplist AS TABLE OF t_emp / -- Create a table with a nested table of type t_emplist CREATE TABLE dept_new (id NUMBER, emps t_emplist) NESTED TABLE emps STORE AS emp_table; -- Populate the dept_new table with data INSERT INTO dept_new VALUES ( 10, t_emplist ( t_emp(1, 'SCOTT'), t_emp(2, 'BRUCE') ) ); -- Write a PL/SQL block that queries table dept_new and nested table emps -- SELECT ... FROM ... TABLE is not allowed in static SQL in PL/SQL DECLARE v_deptid NUMBER; v_ename VARCHAR2(20); BEGIN EXECUTE IMMEDIATE 'SELECT d.id, e.name FROM dept_new d, TABLE(d.emps) e WHERE e.id = 1' INTO v_deptid, v_ename; END; /
動的SQLを使用すると、動的問合せ（実行時まで完全なテキストが使用できない問合せ）を実行するアプリケーションを作成できます。動的問合せは、次のような様々なアプリケーションで使用する必要があります。
多くのアプリケーションでは、定期的に生成されるデータと対話する必要があります。たとえば、表定義はコンパイル時にわかっても、表の名前はわからない場合があります。動的SQLを使用すると、実行時に表の名前を指定できます。
「動的SQLの概要」で説明したデータ・ウェアハウス・アプリケーションの例では、システムにより四半期ごとに新規の表が生成されます。この場合、次のサンプル・プロシージャのような動的SQL問合せを使用すると、実行時にユーザーが表の名前を指定できます。
CREATE OR REPLACE PROCEDURE query_invoice (p_month VARCHAR2, p_year VARCHAR2) IS TYPE cur_typ IS REF CURSOR; v_inv_cursor cur_typ; -- Declare a cursor variable v_inv_query VARCHAR2(200); v_inv_num NUMBER; v_inv_cust VARCHAR2(20); v_inv_amt NUMBER; BEGIN -- Write dynamic query receiving month and year as parameters -- and using these values to form the table name, for example, inv_APR_2004 v_inv_query := 'SELECT num, cust, amt FROM inv_' || p_month || '_' || p_year || ' WHERE v_inv_num = :g_id'; -- Open a cursor variable OPEN v_inv_cursor FOR v_inv_query USING v_inv_num; -- Fetch row into variables LOOP FETCH v_inv_cursor INTO v_inv_num, v_inv_cust, v_inv_amt; EXIT WHEN v_inv_cursor%NOTFOUND; -- process row here END LOOP; CLOSE v_inv_cursor; END; /
動的SQLを使用すると、ヒントを動的にSQL文に連結して実行を最適化するSQL文を作成できます。これによって、再コンパイルしなくても、現在のデータベースの詳細情報に基づいてヒントを変更できます。たとえば、次のサンプル・プロシージャでは、p_hintという変数を使用して、ユーザーがSELECT文にヒント・オプションを渡せるようになっています。
CREATE OR REPLACE PROCEDURE query_emp (p_hint VARCHAR2) IS TYPE cur_typ IS REF CURSOR; v_emp_cursor cur_typ; BEGIN OPEN v_emp_cursor FOR 'SELECT ' || p_hint ||' empno, ename, sal, job FROM emp WHERE empno = 7566'; -- process ... CLOSE v_emp_cursor; END; /
例8-3では、ユーザーはp_hintとして次のような値を渡すことができます。
p_hint = '/*+ ALL_ROWS */' p_hint = '/*+ FIRST_ROWS */' p_hint = '/*+ CHOOSE */'
無名PL/SQLブロックを実行するには、EXECUTE IMMEDIATE文を使用できます。このように、実行時にブロックの内容を構成することによって、柔軟性を向上できます。
たとえば、イベント番号を受け入れ、それをそのイベントのハンドラにディスパッチするアプリケーションを作成します。ハンドラの名前はEVENT_HANDLER_event_numという形式で、event_numがイベントの番号です。1つの方法として、ディスパッチャをスイッチ文として実装する方法があります。この場合、該当するハンドラにコードが静的コールを実行して、それぞれのイベントを処理します。このコードは、新しいイベント用のハンドラが追加されるたびにディスパッチャ・コードを更新する必要があるため、あまり拡張可能ではありません。
CREATE OR REPLACE PROCEDURE event_handler_1 (p_handle NUMBER) IS BEGIN -- process event 1 RETURN; END; / CREATE OR REPLACE PROCEDURE event_handler_2 (p_handle NUMBER) IS BEGIN -- process event 2 RETURN; END; / CREATE OR REPLACE PROCEDURE event_handler_3 (p_handle NUMBER) IS BEGIN -- process event 3 RETURN; END; / CREATE OR REPLACE PROCEDURE event_dispatcher (p_event_num NUMBER, p_handle NUMBER) IS BEGIN IF (p_event_num = 1) THEN EVENT_HANDLER_1(p_handle); ELSIF (p_event_num = 2) THEN EVENT_HANDLER_2(p_handle); ELSIF (p_event_num = 3) THEN EVENT_HANDLER_3(p_handle); END IF; END; /
ネイティブ動的SQLを使用すると、次の例に示すように、より小さく、柔軟なイベント・ディスパッチャを作成できます。
CREATE OR REPLACE PROCEDURE event_dispatcher (p_event_num NUMBER, p_handle NUMBER) IS BEGIN EXECUTE IMMEDIATE 'BEGIN EVENT_HANDLER_' || TO_CHAR(p_event_num) || '(:1); END;' USING p_handle; END; /
動的SQLとともに実行者権限の機能を使用すると、実行者権限およびスキーマに基づいて動的SQL文を発行するアプリケーションを作成できます。実行者権限および動的SQLという2つの機能によって、実行者のデータおよびモジュールに対して操作およびアクセスできる再利用可能なアプリケーション・サブコンポーネントを作成できます。
この使用例には、ネイティブ動的SQLを使用して次の操作を実行する方法が説明されています。
この使用例では、データベースが人事管理に使用されています。officesというマスター表には、この企業のすべての事務所のリストが含まれています。offices表は、次のように定義されています。
Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)
emp_location表は複数あり、これには社員情報が含まれています。locationは事務所のある都市の名前です。たとえば、emp_houstonという表には、ヒューストン事務所の社員の情報が含まれ、emp_bostonという表には、ボストン事務所の社員の情報が含まれています。
各emp_location表は、次のように定義されています。
Column Name Null? Type EMPNO NOT_NULL NUMBER(4) ENAME NOT_NULL VARCHAR2(10) JOB NOT_NULL VARCHAR2(9) SAL NOT_NULL NUMBER(7,2) DEPTNO NOT_NULL NUMBER(2)
次の項では、hrデータベース内のデータに対して実行できる様々なネイティブ動的SQL操作を説明します。
次のネイティブ動的SQLプロシージャによって、特定の役職を持つ社員全員の給料が増額されます。
CREATE OR REPLACE PROCEDURE salary_raise (p_raise_percent NUMBER, p_job VARCHAR2) IS TYPE loc_array_type IS TABLE OF VARCHAR2(40) INDEX BY binary_integer; v_dml_str VARCHAR2(200); v_loc_array loc_array_type; BEGIN -- bulk fetch the list of office locations SELECT location BULK COLLECT INTO v_loc_array FROM offices; -- for each location, give a raise to employees with the given 'job' FOR i IN v_loc_array.first..v_loc_array.last LOOP v_dml_str := 'UPDATE emp_' || v_loc_array(i) || ' SET sal = sal * (1+(:p_raise_percent/100))' || ' WHERE p_job = :g_job_title'; EXECUTE IMMEDIATE v_dml_str USING p_raise_percent, p_job; END LOOP; END; / SHOW ERRORS;
EXECUTE IMMEDIATE文は、DDL操作を実行できます。たとえば、次のプロシージャは、事務所の所在地を追加します。
CREATE OR REPLACE PROCEDURE add_location (p_loc VARCHAR2) IS BEGIN -- Insert new location in master table, for example, detroit INSERT INTO offices VALUES (p_loc); -- Create an employee information table EXECUTE IMMEDIATE -- Use the parameter value to concatenate the table name, as in emp_detroit 'CREATE TABLE emp_' || p_loc || '( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2), deptno NUMBER(2) )'; END; /
SHOW ERRORS;
次のプロシージャでは、同じ連結方法を使用して表を削除します。
CREATE OR REPLACE PROCEDURE drop_location (p_loc VARCHAR2) IS BEGIN -- Drop the employee table for location 'p_loc', for example, emp_detroit EXECUTE IMMEDIATE 'DROP TABLE ' || 'emp_' || p_loc; -- Remove location from master table DELETE FROM offices WHERE location = p_loc; END; / SHOW ERRORS;
EXECUTE IMMEDIATE文は、動的な単一行問合せを実行できます。USING句にバインド変数を指定し、この文のINTO句に指定されているターゲットに結果の行をフェッチできます。次の関数は、特定の所在地で特定の職務を遂行している社員数を取得します。
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) RETURN NUMBER IS v_query_str VARCHAR2(1000); v_num_of_employees NUMBER; BEGIN -- Use concatenation to form the table name in the SELECT statement v_query_str := 'SELECT COUNT(*) FROM emp_' || p_loc || ' WHERE job = :1'; -- Execute the query and put the result row in a variable EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job; RETURN v_num_of_employees; END; / SHOW ERRORS;
動的な複数行問合せは、OPEN-FOR文、FETCH文およびCLOSE文を使用して実行できます。たとえば、次のプロシージャは、特定の所在地で特定の職種についているすべての社員をリストします。
CREATE OR REPLACE PROCEDURE list_employees (p_loc VARCHAR2, p_job VARCHAR2) IS TYPE cur_typ IS REF CURSOR; -- Define a cursor variable v_emp_cursor cur_typ; v_query_str VARCHAR2(1000); v_emp_name VARCHAR2(20); v_emp_num NUMBER; BEGIN -- Use concatenation to form the SELECT statement v_query_str := 'SELECT ename, empno FROM emp_' || p_loc || ' WHERE job = :g_job_title'; -- Open a cursor variable for the query OPEN v_emp_cursor FOR v_query_str USING p_job; -- Loop through each row to find employees who perform the specified job LOOP -- Fetch the employee name and ID into variables FETCH v_emp_cursor INTO v_emp_name, v_emp_num; EXIT WHEN v_emp_cursor%NOTFOUND; -- Process row here END LOOP; CLOSE v_emp_cursor; END; / SHOW ERRORS;
Oracle Databaseでは、PL/SQL内で動的SQLを使用するために、ネイティブ動的SQLおよびDBMS_SQLパッケージという2つの方法を提供しています。どちらの方法にもメリットとデメリットがあります。次の項では、この2つの方法のメリットを詳しく説明します。
ネイティブ動的SQLを使用すると、動的SQL文をPL/SQLコード内に直接入れることができます。このような動的な文は次のとおりです。
ほとんどのネイティブ動的SQL文を処理するには、EXECUTE IMMEDIATE文を使用します。複数行SELECT文を処理するには、OPEN-FOR文、FETCH文およびCLOSE文を使用します。
ネイティブ動的SQL文のかわりに、DBMS_SQLパッケージには動的SQL文を実行するためのPL/SQL APIが用意されています。たとえば、DBMS_SQLパッケージには、次の操作を実行するためのプロシージャが含まれています。
DBMS_SQLパッケージを使用するプログラムは、このパッケージをコールして動的SQL操作を実行します。
ネイティブ動的SQLには、DBMS_SQLパッケージと比べて次のようなメリットがあります。
ネイティブ動的SQLはSQLと統合されているため、現在PL/SQLコードで静的SQLを使用している方法と同じ方法で使用できます。ネイティブ動的SQLコードは、通常、DBMS_SQLパッケージを使用した同等のコードより簡潔で読みやすくなります。
DBMS_SQLパッケージを使用すると、数多くのプロシージャおよびファンクションを厳密な順序に従ってコールする必要があり、単純な操作にも多量のコードが必要になります。ネイティブ動的SQLを使用すると、それほど複雑になることはありません。
表8-3に、同じ操作の実行で、DBMS_SQLパッケージを使用した場合と、ネイティブ動的SQLを使用した場合の、必要となるコード量の違いを示します。
PL/SQLインタプリタにはネイティブ動的SQLのサポートが組み込まれているため、PL/SQLでのネイティブ動的SQLのパフォーマンスは、静的SQLのパフォーマンスと同等になります。ネイティブ動的SQLを使用するプログラムは、DBMS_SQLパッケージを使用するプログラムよりはるかに高速です。通常、ネイティブ動的SQL文は、DBMS_SQLコールを使用する同等の文より1.5〜3倍パフォーマンスが向上しますパフォーマンスの向上は、アプリケーションによっても異なります。
ネイティブ動的SQLでは、文の準備手順、バインド手順および実行手順を1つの操作の中にまとめます。これによって、データ・コピーおよびプロシージャ・コールによるオーバーヘッドが最小化され、パフォーマンスが向上します。
DBMS_SQLパッケージはプロシージャAPIに基づいているため、プロシージャ・コールが多くなり、データをコピーするオーバーヘッドが発生します。DBMS_SQLパッケージは、変数がバインドされるたびにPL/SQLバインド変数を自身の領域にコピーし、実行時に使用できるようにします。同様に、フェッチを実行するたびに、まずDBMS_SQLパッケージが管理する領域にデータがコピーされ、次に、フェッチされたデータが1列ずつ適切なPL/SQL変数にコピーされます。この結果、データのコピーによるオーバーヘッドがかなり多くなります。
ネイティブ動的SQLおよびDBMS_SQLパッケージのどちらの場合も、バインド変数を使用することでパフォーマンスを向上できます。これは、バインド変数の使用によって、Oracle Databaseが複数のSQL文に対して1つのカーソルを共有できるためです。
例8-11のネイティブ動的SQLコードでは、バインド変数のかわりにパラメータを使用してSQL文が作成されます。
CREATE OR REPLACE PROCEDURE del_dept (p_department_id departments.department_id%TYPE) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = ' || TO_CHAR(p_department_id); END; / SHOW ERRORS;
このプロシージャでは、p_department_idパラメータごとに新規カーソルが個別に作成されるため、リソースが競合してパフォーマンスが低下します。かわりに、例8-12に示すようにバインド変数を使用してSQL文を作成できます。
CREATE OR REPLACE PROCEDURE del_dept (p_department_id departments.department_id%TYPE) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = :1' USING p_department_id; END; / SHOW ERRORS;
例8-12では、1つのカーソルがバインド変数my_deptnoの異なる複数の値に再利用されているため、パフォーマンスおよびスケーラビリティが向上します。
ネイティブ動的SQLは、PL/SQLで静的SQLがサポートしているすべての型（ユーザー定義のオブジェクト、コレクション、REFなどのユーザー定義型を含む）をサポートしています。DBMS_SQLパッケージでは、ユーザー定義型はサポートされません。
ネイティブ動的SQLおよび静的SQLは、どちらもレコードへのフェッチをサポートしますが、DBMS_SQLパッケージはこれをサポートしません。ネイティブ動的SQLの場合は、問合せの結果の行をPL/SQLレコードに直接フェッチできます。例8-13では、問合せ結果の行がv_emp_rec変数にフェッチされます。
DECLARE TYPE EmpCurTyp IS REF CURSOR; v_emp_cursor EmpCurTyp; emp_record emp%ROWTYPE; v_stmt_str VARCHAR2(200); v_e_job emp.job%TYPE; BEGIN v_stmt_str := 'SELECT * FROM emp WHERE job = :1'; -- in a multi-row query OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER'; LOOP FETCH v_emp_cursor INTO emp_record; EXIT WHEN v_emp_cursor%NOTFOUND; END LOOP; CLOSE v_emp_cursor; -- in a single-row query EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT'; END; /
DBMS_SQLパッケージには、ネイティブ動的SQLと比べて次のようなメリットがあります。
DBMS_SQLパッケージはクライアント側プログラム内でサポートされていますが、ネイティブ動的SQLはサポートされていません。クライアント側プログラムからのDBMS_SQLパッケージへのすべてのコールは、PL/SQLリモート・プロシージャ・コール（RPC）に変換されます。これらのコールは、次のいずれかの操作が必要な場合に発生します。
ネイティブ動的SQLは、入力数または出力数が不明な文をサポートしません。DBMS_SQLパッケージには、この制約がありません。そのため、DBMS_SQLパッケージのDESCRIBE_COLUMNSプロシージャを使用すると、DBMS_SQLによってオープンおよび解析されるカーソルの列を記述できます。このプロシージャの機能は、SQL*PlusのDESCRIBEコマンドと同様です。ネイティブ動的SQLにはDESCRIBE機能はありません。
DBMS_SQLパッケージは、32KBを超える大規模なSQL文をサポートします。ネイティブ動的SQLはサポートしません。
DBMS_SQLパッケージのPARSEプロシージャは、SQL文を1回解析します。最初に解析した後、この文は異なるバインド引数を指定して複数回使用できます。
ネイティブ動的SQLでは、SQL文を使用するたびにその文を準備します。文の準備では、通常、解析、最適化および計画の生成が行われます。この準備によって、パフォーマンスはわずかに低下しますが、ネイティブ動的SQLによって、全体ではパフォーマンスは向上します。
次の例に、DBMS_SQLパッケージを使用した場合、およびネイティブ動的SQLを使用した場合の操作に必要なコードの違いを示します。次の操作について例を示します。
一般に、ネイティブ動的SQLコードの方が読みやすく簡潔なため、開発生産性が向上します。
次に、バインド変数1つ（:g_jobname）および選択列2つ（enameおよびsal）で構成される動的問合せ文を示します。
v_stmt_str := 'SELECT ename, sal FROM emp WHERE job = :g_jobname';
この例では、emp表のjob列の職種がSALESMANの社員を問い合せます。表8-2に、DBMS_SQLパッケージおよびネイティブ動的SQLを使用してこの問合せを行うコード例を示します。
次に、列が3つある表に対する動的INSERT文を示します。
v_stmt_str := 'INSERT INTO dept_new VALUES (:g_deptno, :g_dname, :g_loc)';
この例は、PL/SQL変数deptnumber、deptnameおよびlocationを列値とする新しい行を挿入します。表8-3に、DBMS_SQLパッケージおよびネイティブ動的SQLを使用してこのタスクを行うコード例を示します。
次に、部門の所在地を更新し、部門の名前を戻す動的UPDATE文を示します。
v_stmt_str := 'UPDATE dept_new SET loc = :g_newloc WHERE deptno = :g_deptno RETURNING dname INTO :g_dname';
表8-4に、DBMS_SQLパッケージおよびネイティブ動的SQLを使用してこの操作を行うコード例を示します。
SQLインジェクションとは、SQL文にクライアントから提供されるデータを使用するアプリケーションを悪用する手法です。この手法の目的は、制限付きデータを問い合せたり操作するためにデータベースに不正にアクセスすることです。この項では、PL/SQLにおけるSQLインジェクションの脆弱性と、SQLインジェクションの防止方法について説明します。
この項の内容は次のとおりです。
様々なSQLインジェクション手法がありますが、すべて1つの脆弱性を持っています。つまり、文字列入力が正常に検証されず、動的SQL文に連結されることです。ここでは、説明のためにSQLインジェクション攻撃を次のように分類します。
次の各項では、これらの手法について説明します。サンプル・データベースで例を試してみるために、例8-14のスクリプトを実行します。
CONNECT hr/hr SET SERVEROUTPUT ON SET LINESIZE 150 SET ECHO OFF DROP TABLE user_pwd; CREATE TABLE user_pwd( username VARCHAR2(100), password VARCHAR2(100) ); INSERT INTO user_pwd VALUES('whitehat', 'secret'); DROP TABLE delemp; CREATE TABLE delemp AS SELECT * FROM employees; COMMIT;
SQL変更では、アプリケーション開発者が意図していない方法で実行されるように、動的SQL文が故意に変更されます。通常、ユーザーは問合せのWHERE句を変更するか、UNION ALL句を挿入することで不正なデータを取得します。この手法の従来からの例は、WHERE句が常にTRUEになるようにしてパスワード認証をバイパスする方法です。
Webフォームにユーザー名とパスワードの入力を求めるプロンプトが表示されるとします。ユーザーが「発行」をクリックすると、フォームに入力されたユーザー名とパスワードを連結して動的SQL文を作成するPL/SQLストアド・プロシージャが起動されます。このプロシージャにより、ユーザー認証のための問合せが実行されます。
例8-15に、SQL*Plusでテストできる認証プロシージャを示します。ckpwdプロシージャは、ユーザー名とパスワードを連結した値を使用して、user_pwd表のSQL問合せを作成します。ユーザーが表に格納されているユーザー名とパスワードを入力すると、問合せの実行により1行が取得されてユーザーが認証されます。また、ckpwdプロシージャは連結された問合せも表示するため、どの問合せが実行されたかを確認できます。
CREATE OR REPLACE PROCEDURE ckpwd (p_user IN VARCHAR2, p_pass IN VARCHAR2) IS v_query VARCHAR2(100); v_output NUMBER; BEGIN v_query := q'{SELECT COUNT(*) FROM user_pwd }' || q'{WHERE username = '}' || p_user || q'{' AND password = '}' || p_pass || q'{'}'; DBMS_OUTPUT.PUT_LINE(CHR(10)||'Built the following query:'||CHR(10)||v_query); EXECUTE IMMEDIATE v_query INTO v_output; IF v_output = 1 THEN DBMS_OUTPUT.PUT_LINE(CHR(10)||p_user||' is authenticated'); ELSE DBMS_OUTPUT.PUT_LINE(CHR(10)||'access denied'); END IF; END; /
ユーザーwhitehatがWebフォームにパスワードsecretを入力するとします。この使用例は、例8-16に示すコードをSQL*Plusで起動し、whitehatを認証することでシミュレーションできます（サンプル出力が含まれています）。
BEGIN ckpwd ( p_user => q'{whitehat}', p_pass => q'{secret}' ); END; / Built the following query: SELECT COUNT(*) FROM user_pwd WHERE username = 'whitehat' AND password = 'secret' whitehat is authenticated
悪意のあるユーザーが、この連結を故意に使用し、Webベース・フォームにユーザー名xを入力し、パスワードとして例8-17に示すテキストを入力するとします。
x' OR 'x' = 'x
この使用例は、例8-18に示すコードをSQL*Plusで実行することでシミュレーションできます（サンプル出力が含まれています）。
BEGIN ckpwd ( p_user => q'{x}', p_pass => q'{x' OR 'x' = 'x}' ); END; / Built the following query: SELECT COUNT(*) FROM user_pwd WHERE username = 'x' AND password = 'x' OR 'x' = 'x' x is authenticated
例8-17のように巧妙に作成されたパスワードを使用すると、OR条件で常にTRUEが戻されるように連結SQL文を変更できます。そのため、user_pwd表の問合せは、どのようなユーザー名が入力されたかに関係なく常に成功します。
文のインジェクションが発生するのは、動的に生成されるSQL文に対してユーザーが1つ以上の新規SQL文を追加する場合です。無名PL/SQLブロックは、この手法に対して脆弱です。
Webフォームにユーザー名とパスワードの入力を求めるプロンプトが表示されるとします。ユーザーが「発行」をクリックすると、フォームに入力されたユーザー名とパスワードを連結して無名PL/SQLブロックにするPL/SQLストアド・プロシージャが起動されます。このプロシージャにより、ユーザー認証のための無名ブロックが実行されます。
例8-19に、SQL*Plusでテストできる認証プロシージャを示します。call_ckpwdプロシージャは、連結を使用して、例8-15のckpwdプロシージャを起動する無名ブロックを作成します。ユーザーがuser_pwd表に格納されているユーザー名とパスワードを入力すると、ブロックの実行により1行が取得されてユーザーが認証されます。また、call_ckpwdプロシージャは連結されたテキストも出力するため、どのブロックが実行されたかを確認できます。
CREATE OR REPLACE PROCEDURE call_ckpwd (p_user IN VARCHAR2, p_pass IN VARCHAR2) IS v_block VARCHAR2(100); BEGIN v_block := q'{BEGIN ckpwd( '}' || p_user || q'{' , '}' || p_pass || q'{' ); END; }'; DBMS_OUTPUT.PUT_LINE(CHR(10)|| 'Built the following anonymous block:'||CHR(10)||v_block); EXECUTE IMMEDIATE v_block; END; /
ユーザーwhitehatがWebベース・フォームにパスワードsecretを入力するとします。この使用例は、例8-20に示すcall_ckpwdプロシージャをSQL*Plusで起動することでシミュレーションできます（サンプル出力が含まれています）。
BEGIN call_ckpwd ( p_user => q'{whitehat}', p_pass => q'{secret}' ); END; / Built the following anonymous block: BEGIN ckpwd( 'whitehat' , 'secret' ); END; Built the following query: SELECT COUNT(*) FROM user_pwd WHERE username = 'whitehat' AND password = 'secret' whitehat is authenticated
whitehatに悪意があれば、例8-21に示す文字列をWebフォームにパスワードとして入力する可能性があります。
secret'); DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x
この手法は、例8-22に示すcall_ckpwdプロシージャをSQL*Plusで起動することでシミュレーションできます（サンプル出力が含まれています）。
BEGIN
call_ckpwd
(
p_user => q'{whitehat}',
p_pass => q'{secret'); DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x}'
);
END;
/
Built the following anonymous block:
BEGIN ckpwd( 'whitehat' , 'secret' ); DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x'); END;
Built the following query:
SELECT COUNT(*) FROM user_pwd WHERE username = 'whitehat' AND password = 'secret'
whitehat is authenticated
例8-21に示した偽のパスワードが入力されると、システムではwhitehatが認証され、インジェクトされたDELETE文が暗黙的に実行されます。delemp表を問い合せると、インジェクトされた文により表からすべての行が暗黙的に削除されたことがわかります。
SELECT * FROM delemp; no rows selected
PL/SQLアプリケーションに動的SQLを使用する場合は、入力テキストをチェックして、正確で予期した内容のみであることを確認する必要があります。必要に応じて次の方法を使用すると役立ちます。
「バインド変数を介したパフォーマンスの向上」では、バインド変数を使用して動的SQLのパフォーマンスを向上させる方法を説明しました。プレースホルダをバインドすると、パフォーマンスは向上しますが、PL/SQLコードがSQLインジェクション攻撃にさらされます。
例8-15に示したckpwdプロシージャは、バインド変数のかわりに連結を使用していました。例8-23に、このプロシージャを書き換えて連結のかわりにバインド変数を使用する方法を示します。
CREATE OR REPLACE PROCEDURE ckpwd_bind (p_user IN VARCHAR2, p_pass IN VARCHAR2) IS v_query VARCHAR2(100); v_output NUMBER; BEGIN v_query := q'{SELECT COUNT(*) FROM user_pwd WHERE username = :1 AND password = :2}'; DBMS_OUTPUT.PUT_LINE(CHR(10)||'Built the following query:'||CHR(10)||v_query); EXECUTE IMMEDIATE v_query INTO v_output USING p_user, p_pass; IF v_output = 1 THEN DBMS_OUTPUT.PUT_LINE(CHR(10)||p_user||' is authenticated'); ELSE DBMS_OUTPUT.PUT_LINE(CHR(10)||'access denied'); END IF; END; /
ユーザーが例8-17に示した偽のパスワードをckpwd_bindプロシージャに渡そうとしても、この方法ではユーザーの認証に失敗します。例8-24に示すブロックをSQL*Plusで実行すると、コードの改訂バージョンをテストできます（サンプル出力が含まれています）。
BEGIN ckpwd_bind ( p_user => q'{x}', p_pass => q'{x' OR 'x' = 'x}' ); END; / Built the following query: SELECT COUNT(*) FROM user_pwd WHERE username = :1 AND password = :2 access denied
これと同じバインド方法で、例8-19に示した脆弱なcall_ckpwdプロシージャを修正できます。バインド変数をコードに排他的に使用すると、SQL文の連結を回避することで、悪意のあるユーザーによる文の変更や追加インジェクションを防止できます。Oracleデータベースでは、バインド変数の値が排他的に使用され、その内容は解析されません。これは、PL/SQLプログラムにおけるSQLインジェクションを防止する上で最も有効な方法です。
プログラムでは、意図した内容であることを確認するために、常にユーザー入力を検証する必要があります。たとえば、ユーザーがDELETE文に対して部門番号を渡すと、departments表を問い合せることで、この部門番号の妥当性がチェックされます。同様に、ユーザーが削除対象の表の名前を入力すると、ALL_TABLESビューを問い合せることで、この表の存在が確認されます。
|
 Copyright © 2006 Oracle Corporation. All Rights Reserved. |
|