2023年10月25日水曜日

SQL問合せを返すファンクション本体によりSELECT文を生成する際の注意点

Oracle APEXのレポートやチャートのコンポーネントのソースの位置がローカル・データベースの場合は、以下の３種類のタイプをソースのタイプとして選択できます。
  1. 表/ビュー
  2. SQL問合せ
  3. SQL問合せを返すファンクション本体
これらのタイプで指定されたソースが、実際にはどのようにデータベースで実行されるか確認し、SQL問合せを返すファンクション本体でSELECT文を作成する際の注意点を紹介します。

今回説明する注意点は以下になります。

SQL問合せを返すファンクション本体でSELECT文を生成する際にページ・アイテムの値をSELECT文に含めると、ソースとして実行されるSELECT文にはリテラルが含まれることになります。データベースにとっては効率の悪いSQLですし、リテラルが文字列の場合はSQLインジェクションの脆弱性を含む可能性もあります。

以下のように表EMPをソースとしたクラシック・レポートを作成し、選択リストで指定したDEPTNOでフィルタをするようなページを作成します。


ソースのタイプとして表/ビューを選択している場合は、ソースの設定は以下のようになります。

表名にEMP、WHERE句にdeptno = :P1_DEPTNO、送信するページ・アイテムとしてP1_DEPTNOを設定します。


デバッグを有効にし、上記の設定で実際にデータベースで実行されるSELECT文を確認します。


動的アクションでリージョンがリフレッシュする際にデータベースへの問合せが実行されているため、パス情報はajax pluginになります。


Generated Component SQL Query:に続いて、実際に実行されたSELECT文が表示されています。


読みやすくなるよう、整形します。
select
    i.*
from
    (
        select
            "EMPNO",
            "ENAME",
            "JOB",
            "MGR",
            "HIREDATE",
            "SAL",
            "COMM",
            "DEPTNO"
        from
            (
                select
                    /*+ qb_name(apex$inner) */
                    d."EMPNO",
                    d."ENAME",
                    d."JOB",
                    d."MGR",
                    d."HIREDATE",
                    d."SAL",
                    d."COMM",
                    d."DEPTNO"
                from
                    (
                        select
                            x.*
                        from
                            "EMP" x
                        where
                            (
                                deptno = :P1_DEPTNO
                            )
                    ) d
            ) i
    ) i
where
    1 = 1
order by
    "EMPNO" asc nulls last
最も内側の副問合せがソースの指定に対応しています。ページ・アイテムP1_DEPTNOがバインド変数として扱われていることが確認できます。
                        select
                            x.*
                        from
                            "EMP" x
                        where
                            (
                                deptno = :P1_DEPTNO
                            )
ソースのタイプをSQL問合せに変更し、同じ動作になるようにSQL問合せを記述します。
select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP
 where deptno = :P2_DEPTNO

デバッグを有効にし、実行されるSQLを確認します。


先ほどと同様に整形します。最も内側の副問合せを確認すると、ページ・アイテムP2_DEPTNOがバインド変数として扱われていることが確認できます。
select
    i.*
from
    (
        select
            "EMPNO",
            "ENAME",
            "JOB",
            "MGR",
            "HIREDATE",
            "SAL",
            "COMM",
            "DEPTNO"
        from
            (
                select
                    /*+ qb_name(apex$inner) */
                    d."EMPNO",
                    d."ENAME",
                    d."JOB",
                    d."MGR",
                    d."HIREDATE",
                    d."SAL",
                    d."COMM",
                    d."DEPTNO"
                from
                    (
                        select
                            EMPNO,
                            ENAME,
                            JOB,
                            MGR,
                            HIREDATE,
                            SAL,
                            COMM,
                            DEPTNO
                        from
                            EMP
                        where
                            deptno = :P2_DEPTNO
                    ) d
            ) i
    ) i
where
    1 = 1
order by
    "EMPNO" asc nulls last
ソースのタイプをSQL問合せを返すファンクション本体に変更し、SQL問合せを戻すPL/SQLファンクション本体として以下を記述します。

SELECT文を生成するファンクションgenerate_sqlを定義し、その引数p_deptnoとしてDEPTNOに当たる数値を受け取ります。

declare
/*
* SQL問合せを生成するファンクション
*/
function generate_sql(
p_deptno number
)
return clob
is
l_sql clob;
begin
l_sql := q'~
select EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
from EMP where
~';
if p_deptno is not null then
l_sql := l_sql || 'deptno = ' || p_deptno;
else
/* 静的なSELECT文と同じ結果が得られるように指定する。 */
l_sql := l_sql || 'deptno = null';
end if;
return l_sql;
end;
begin
return generate_sql(p_deptno => :P3_DEPTNO);
end;
view raw generate-sql-1.sql hosted with ❤ by GitHub

実際に実行されるSQLを確認します。


同様に整形します。最も内側の副問合せを確認するとdeptno = 20となっており、部門を選択しているページ・アイテムの値がリテラルで渡されていることが確認できます。
select
    i.*
from
    (
        select
            "EMPNO",
            "ENAME",
            "JOB",
            "MGR",
            "HIREDATE",
            "SAL",
            "COMM",
            "DEPTNO"
        from
            (
                select
                    /*+ qb_name(apex$inner) */
                    d."EMPNO",
                    d."ENAME",
                    d."JOB",
                    d."MGR",
                    d."HIREDATE",
                    d."SAL",
                    d."COMM",
                    d."DEPTNO"
                from
                    (
                        select
                            EMPNO,
                            ENAME,
                            JOB,
                            MGR,
                            HIREDATE,
                            SAL,
                            COMM,
                            DEPTNO
                        from
                            EMP
                        where
                            deptno = 20
                    ) d
            ) i
    ) i
where
    1 = 1
order by
    "EMPNO" asc nulls last
SELECT文を作る際にp_deptnoとして渡された数値を使っているため、生成されたSELECT文にリテラルが含まれています。

引数の型が文字列の場合は、DBMS_ASSERTパッケージを使うなど、SQLインジェクションへの対応も検討が必要になります。

ページ・アイテムは、SQL問合せを戻すPL/SQLファンクション本体に記述したファンクション内でも値は割り当てられますが、生成されたSELECT文にページ・アイテム名が含まれていれば、それにも値は割り当てられます。

そのため、SQL問合せを戻すPL/SQLファンクション本体は以下のように記述できます。

declare
/*
* SQL問合せを生成するファンクション
*/
function generate_sql(
p_deptno varchar2
)
return clob
is
l_sql clob;
begin
l_sql := q'~
select EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
from EMP where
~';
/* DEPTNOの代わりにDEPTNOのページ・アイテム名を含める。 */
l_sql := l_sql || 'deptno = :' || p_deptno;
return l_sql;
end;
begin
return generate_sql(p_deptno => 'P4_DEPTNO');
end;
view raw generate-sql-2.sql hosted with ❤ by GitHub


実際に実行されるSQLを確認します。


同様に整形します。最も内側の副問合せを確認するとdeptno = :P4_DEPTNOとなっており、バインド変数が使われていることが分かります。
select
    i.*
from
    (
        select
            "EMPNO",
            "ENAME",
            "JOB",
            "MGR",
            "HIREDATE",
            "SAL",
            "COMM",
            "DEPTNO"
        from
            (
                select
                    /*+ qb_name(apex$inner) */
                    d."EMPNO",
                    d."ENAME",
                    d."JOB",
                    d."MGR",
                    d."HIREDATE",
                    d."SAL",
                    d."COMM",
                    d."DEPTNO"
                from
                    (
                        select
                            EMPNO,
                            ENAME,
                            JOB,
                            MGR,
                            HIREDATE,
                            SAL,
                            COMM,
                            DEPTNO
                        from
                            EMP
                        where
                            deptno = :P4_DEPTNO
                    ) d
            ) i
    ) i
where
    1 = 1
order by
    "EMPNO" asc nulls last
ソースのタイプとしてSQL問合せを返すファンクション本体を選択した場合に、安全で効率のよいSELECT文を生成する注意点の紹介は以上になります。

今回の作業で使用したAPEXアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/note-on-function-body-returning-sql.zip

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完
投稿者 時刻:
ラベル: