e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第130回「実用WEBアプリ 文書管理システム(11)検索機能(その2 SELECT文の生成)」

2015.05.28

こんにちは。インストラクターの蓑島です。

検索機能の第2回目ですね。

前回は、検索条件入力フォームの外枠を作成しました。今回はそこに入力された検索条件を使って、SELECT文(動的SQL)を生成するところまでを実装します。そして次回でそのSELECT文を実行し、結果を画面に表示する機能を実装し、検索機能は完成です。

ではそのソースコードを解説しますが、その前にいつものように、文書やディレクトリの情報を格納するDOCS表の定義を掲載します。この表を検索するSELECT文を生成するわけです。表の定義も参照しながら生成されるSELECT文を確認してください。

SQL> SHOW USER
ユーザーは"SCOTT"です。

1
2
3
4
5
6
7
8
9
10
11
SQL> DESC DOCS
  名前                                      NULL ?    型
  ----------------------------------------- -------- ----------------------------
  ID                                        NOT NULL NUMBER           --番号(主キー)
  TITLE                                     NOT NULL VARCHAR2(300)    --タイトル
  KBN                                       NOT NULL NUMBER           --区分1:文書 2:ディレクトリ
  HONBUN                                             CLOB             --本文(文書のときのみセット)
  OYA_DIR                                            NUMBER           --親ディレクトリ
  SEQ                                                NUMBER           --ディレクトリ内の順序
  INSERT_DATE                               NOT NULL DATE             --登録日
  UPDATE_DATE                               NOT NULL DATE             --更新日

では早速、前回のプロシージャにSELECT文を作成する機能を実装したソースコードです。

(注意)以下のソースコードはブラウザ表示のために、山括弧(「<」と「>」)を全角にしています。 コピーして実行する方は、必ず、すべての山括弧(「<」と「>」)を半角に変換してください。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
CREATE OR REPLACE PROCEDURE DOC_FIND
( P_OYA_DIR   IN  VARCHAR2 DEFAULT NULL ,   -- 親ディレクトリ
   P_KEYWORD1  IN  VARCHAR2 DEFAULT NULL ,   -- キーワード1
   P_KEYWORD2  IN  VARCHAR2 DEFAULT NULL ,   -- キーワード2
   P_AND_OR    IN  VARCHAR2 DEFAULT 'AND' -- 「AND」または「OR」
   P_COL       IN  VARCHAR2 DEFAULT 'BOTH' , -- 「タイトル」または「タイトルと本文」
   P_BUTTON    IN  VARCHAR2 DEFAULT  NULL -- 送信ボタンをクリックしたかどうかの判定
IS
   V_OYA_DIR   NUMBER;
   V_ERRM      VARCHAR2(1000);
   V_KBN       DOCS.KBN%TYPE;
   V_SELECT_KBN_LIST  VARCHAR2(300);      -- 区分のリスト 「タイトル」または「タイトルと本文」
   V_AND_OR_LIST      VARCHAR2(300);      -- 「AND」、「OR」のリストお
   USERERROR   EXCEPTION;                 -- ★ チェックNG時のユーザー定義エラー
   V_SELECT_STR       VARCHAR2(32767);    -- ★  SELECT文を格納する変数
   V_KEYWORD1          VARCHAR2(32767);   -- ★ キーワードを格納する変数1  '%~%'の書式にする
   V_KEYWORD2          VARCHAR2(32767);   -- ★ キーワードを格納する変数2  '%~%'の書式にする
   V_WHERE_COL         VARCHAR2(100);     -- ★  SELECT句の列  「TILTE」または「TITLE || HONBUNN」
   V_WHERE_SUBDIR      VARCHAR2(32767);    --★   WHERE句
BEGIN
/**********************************/
/*  検索条件の入力フォームを生成  */
/**********************************/
   -- 検索対象区分のリストを生成
   V_SELECT_KBN_LIST := '<SELECT NAME="P_COL"><OPTION VALUE="TITLE">タイトルのみ</OPTION>' ||
                        '<OPTION VALUE="BOTH">タイトルと本文</OPTION></SELECT>' ;
   V_SELECT_KBN_LIST := REPLACE (V_SELECT_KBN_LIST, '"' || P_COL || '"' , '"' ||P_COL|| '" SELECTED' );
   -- AND_OR のリストを作成
   V_AND_OR_LIST := '<SELECT NAME="P_AND_OR"><OPTION>AND</OPTION><OPTION>OR</OPTION></SELECT>' ;
   V_AND_OR_LIST := REPLACE (V_AND_OR_LIST, '<OPTION>' ||P_AND_OR, '<OPTION SELECTED>' ||P_AND_OR);
   -- 検索条件の入力フォームを作成
   HTP.P( '<HTML>' );
   HTP.P( '<HEAD><TITLE>ディレクトリ・文書検索</TITLE></HEAD>' );
   HTP.P( '<BODY>' );
   HTP.P( '<H2>ディレクトリ・文書検索</H2>' );
   HTP.P( '<FORM  ACTION="doc_find" METHOD="POST">' );
   HTP.P( '<TABLE BORDER>' );
   HTP.P( '<TR><TD>親ディレクトリ(省略時:全ディレクトリ対象)</TD>' ||
             '<TD><INPUT TYPE="TEXT" NAME="P_OYA_DIR" VALUE="' ||P_OYA_DIR|| '"></TR>' );
   HTP.P( '<TR><TD>区分</TD><TD>' || V_SELECT_KBN_LIST || '</TD></TR>' );
   HTP.P( '<TR><TD>キーワード1</TD>' ||
         '<TD><INPUT TYPE="TEXT" NAME="P_KEYWORD1" VALUE="' || P_KEYWORD1 || '"></TD></TR>' );
   HTP.P( '<TR><TD>キーワード2</TD>' ||
         '<TD><INPUT TYPE="TEXT" NAME="P_KEYWORD2" VALUE="' || P_KEYWORD2 || '"></TD></TR>' );
   HTP.P( '<TR><TD>条件</TD><TD>' ||V_AND_OR_LIST || '</TD></TR>' );
   HTP.P( '</TABLE>' );
   HTP.P( '<INPUT TYPE="SUBMIT" NAME="P_BUTTON" VALUE="検索実行">' );
   HTP.P( '</FORM>' );
   IF P_BUTTON = '検索実行' THEN
   --    ★★★★ 処理の追加  (ここから)  ★★★★
    /******************************************/
    /*  チェックする                              */
    /******************************************/
     -- 区分
     IF P_COL NOT IN  ( 'BOTH' , 'TITLE' ) THEN
        V_ERRM := '・区分リストの値が不正です' ;
     END IF;
     IF P_AND_OR NOT IN ( 'AND' , 'OR' ) THEN
        V_ERRM := V_ERRM || '<BR>・条件リストの値が不正です' ;
     END IF;
     -- 親ディレクトリのチェック
     IF P_OYA_DIR IS NOT NULL  THEN
        BEGIN
        V_OYA_DIR := TO_NUMBER(P_OYA_DIR);
        SELECT KBN INTO V_KBN FROM DOCS WHERE ID = V_OYA_DIR;
        IF  V_KBN <> 2  THEN   --  ディレクトリでない
            V_ERRM := V_ERRM || '<BR>・指定された番号はディレクトリではありません' ;
            --RAISE  USERERROR;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
               V_ERRM := V_ERRM || '<BR>' || SQLERRM;
        END -- ネストブロック終わり
     END IF;
     -- 検索キーワードが二つともNULLでないことをチェックしつつ、キーワードの変数セット
     IF P_KEYWORD1 IS NULL AND P_KEYWORD2 IS NULL THEN
        V_ERRM := V_ERRM || '<BR>' || '・最低一つ検索キーワードを指定してください' ;
     ELSIF P_KEYWORD1 IS NOT NULL AND P_KEYWORD2 IS NULL THEN
        V_KEYWORD1 := '%' || UPPER (P_KEYWORD1) || '%' ;
     ELSIF P_KEYWORD1 IS NULL AND P_KEYWORD2 IS NOT NULL THEN
        V_KEYWORD1 := '%' || UPPER (P_KEYWORD2) || '%' ;
     ELSE
        V_KEYWORD1 := '%' || UPPER (P_KEYWORD1) || '%' ;
        V_KEYWORD2 := '%' || UPPER (P_KEYWORD2) || '%' ;
     END IF;
     -- 上記のチェックの結果、チェックに反することがあれば、エラーとする
     IF V_ERRM IS NOT NULL  THEN
        RAISE  USERERROR;
     END IF;
     /*************************************************/
     /*  SELECT 文を生成する                             */
     /*************************************************/
     V_SELECT_STR := 'SELECT ID, TITLE, KBN, INSERT_DATE, UPDATE_DATE  FROM DOCS WHERE ' ;
     -- 検索条件の列(TILTEなのか、TITTLEとHONBUNを連結したものか)
     IF P_COL = 'TITLE' THEN
        V_WHERE_COL := 'UPPER(TITLE)' ;
     ELSE
        V_WHERE_COL := 'UPPER(TITLE || HONBUN)' ;
     END IF;
    --キーワード1 を指定する
    V_SELECT_STR := V_SELECT_STR || '(' || V_WHERE_COL || ' LIKE :V_KEYWORD1 ' ;
    IF V_KEYWORD2 IS NOT NULL THEN
       V_SELECT_STR := V_SELECT_STR || ' ' || P_AND_OR || ' ' || V_WHERE_COL || ' LIKE :V_KEYWORD2 ' || ')' ;
    ELSE
       V_SELECT_STR := V_SELECT_STR || ')' ;
    END IF;
    -- 親ディレクトリの指定がある場合
    IF V_OYA_DIR IS NOT NULL THEN
        -- 「ID IN (1,5,6,・・・)」といった親ディレクトリ以下のIDを指定する条件を生成
        V_WHERE_SUBDIR := ' ID IN (' ;
          FOR REC IN ( SELECT ID FROM DOCS
                 START WITH ID = V_OYA_DIR
                 CONNECT BY PRIOR ID = OYA_DIR)  LOOP
                 V_WHERE_SUBDIR := V_WHERE_SUBDIR || TO_CHAR(REC.ID) || ',' ;
          END LOOP;
          -- 最後の余計の',' をとる
          V_WHERE_SUBDIR := SUBSTR(V_WHERE_SUBDIR,1,LENGTH(V_WHERE_SUBDIR)-1);
          -- 最後に')'を付ける
          V_WHERE_SUBDIR := V_WHERE_SUBDIR || ')' ;
          -- その条件を 生成するSELECT文の最後に付ける
          V_SELECT_STR := V_SELECT_STR || ' AND ' || V_WHERE_SUBDIR;
    END IF;
    V_SELECT_STR := V_SELECT_STR || ' ORDER BY UPDATE_DATE DESC' ;
    -- デバッグのため、生成したSELECT文を表示する
    HTP.P(V_SELECT_STR);
    HTP.P( '<BR>V_KEYWORD1=' || V_KEYWORD1);
    HTP.P( '<BR>V_KEYWORD2=' || V_KEYWORD2);
   --    ★★★★ 処理の追加ここまで追加  ★★★★
   ELSE
       --  初回、URLを指定して開いたときのメッセージ
       --  HTP.P('URLからこのプロシージャを指定して開いています');   ←コメントアウト
       HTP.P( '検索画面へようこそ。条件を指定して検索実行ボタンをクリックしてください' );
   END IF;
   -- HTMLページの最後
   HTP.P( '</BODY>' );
   HTP.P( '</HTML>' );
EXCEPTION
   WHEN OTHERS  THEN
       V_ERRM    := SQLERRM;
       HTP.P(V_ERRM);
END DOC_FIND;
/
 
プロシージャが作成されました。

では重要な部分を解説します。

今回、追加したコードには、「★」のコメントを付けてあります。例えば、15~19行目の宣言部の変数宣言の追加や、50~128行目のパラメータチェック処理と、その後のSELECT文生成処理にコメントがありますので、前回と違う部分が判断しやすいと思います。

今回の修正では、リクエストパラメータ(2~7行目)をもとに、検索処理のためのSELECT文を生成するわけです。そして、それらのパラメータを使った、追加処理が、50~128行目ですね。今回追加する処理は、検索実行ボタンがクリックされた場合(49行目のIF P_BUTTON = '検索実行' THEN)に実行される処理です。

ではこの追加した処理の部分に焦点を当てて解説します。

まず、一般にSELECT文を文字列連結で生成する(動的SQL文)処理の場合、場合によっては悪意のあるユーザなどによってSELECT文の構文を書き換えるような入力が可能な場合があります。これをSQLインジェクションといいます。今回のコードは、SQLインジェクションを防止するように意識して記述しました。

最初に、55行目のIF文ですが、P_COLパラメータの値が、'BOTH'か'TITLE'でなければエラーとしています。このパラメータは、画面上では区分リストの値であり、そのリストの値は、'BOTH'(タイトルと本文)、'TITLE'(タイトルのみ)のどれかの値(25~26行目参照)ですから、55行目の処理は不要ではないかと思われた方もいるかもしれません。確かにこの画面の検索実行ボタンをクリックする限り、P_COLの値は'BOTH'か'TITLE'のどちらかの値です。しかし悪意のあるユーザなどがSQLインジェクションを狙って不正な値でパラメータをURLで直接送信する可能性もあります。ですからここで、想定外の値であればエラーにするようにチェックします。

同様に、58行目のIF文も同じ意図です。ここでは、P_AND_ORパラメターの値が'AND'や'OR'でなければエラーにしますが、この値も画面上ではリストの値(29~30行目)ですから、普通であれば'AND'か'OR'以外の値は送信されてこないのですが、悪意のあるユーザなどからの想定外の値を防いでいるわけです。

次に、62~74行目ですが、これはP_OYA_DIRパラメータの値が正しい親ディレクトリであるかどうかのチェックです。チェックに反すればエラーにします。ですからP_OYA_DIRパラメータも想定外の値が送信された場合はエラーになります。

次に76~85行目のIF文ですが、検索キーワード(P_KEYWORD1, P_KEYWORD2)のチェックです。ここでは、少なくとも検索キーワードが一つ以上、セットされていないとエラーにします。そして、キーワードがセットされていれば、V_KEYWORDn (n=1,2)パラメータに、キーワードを大文に変換し前後に「%」を追加した文字列、つまり、「'%' || UPPER(P_KEYWORDn) || '%'」を代入していますが、これは後に、SELECT文のWHERE句の条件として、文字列の部分一致( LIKE '%~%')検索を行うための条件値として使います。そして後述しますが、その値自体は動的SQL文でバインド変数としますので、悪意のあるユーザがどのような値を送信してきても、その値は単に、検索条件の値としてしか使われることはありません。
したがって、SQLインジェクションを防止できます。また細かいことですが、検索キーワード1をセットせずに検索キーワード2をセットした場合でも、検索キーワードの変数1( P_KEYWORD1)に優先的にセットしますので、イレギュラーな順番で検索キーワードを指定してもプログラムのロジックには影響しません。

87行目で、もろもろのチェックの結果、一つでもエラーがあれば、ユーザ定義の例外を発生させます。それにより、138~140行目のエラー処理が行われ、エラーメッセージが表示されます。そのエラーメッセージはそれまでのすべてのチェックのNGのメッセージが追記されていますので、直近のNGのメッセージだけでなく、チェックされたすべてのNGのメッセージが表示されることになります。

チェックがOKであれば、90~123行目で、SELECT文の生成処理です。詳細には触れませんが、解説を要するところを説明します。

最初に、すこし分かりにくいのが、親ディレクトリの指定がある場合の処理(108~122行目)です。これは階層問い合わせ(111~114行目のSELECT文)で、親ディレクトリ以下のすべての子供の文書とサブディレクトリのIDを取り出し、比較演算子「IN」の括弧()内の条件値として使っているわけです。

次に、101行目にある「:V_KEYWORD1」や103行目にある「:V_KEYWORD2」という記述は先頭にコロン(:)がついていますが、これがバインド変数です。バインド変数の値がどのようなものであっても、それは動的SQLが処理する「値」としか認識されませんので、SQL文の意味を変えることはできず、SQLインジェクション攻撃を防止できます。

このようにして出来上がったSQL文とバインド変数の値を最後にデバッグのために、表示します(125~127行目)。

なお、補足ですが、129~132行目の処理はこのプロシージャが、検索実行ボタンのクリックではなく、初回などURLを指定して実行された場合のメッセージ表示です。前回(129回)で記述してメッセージ出力はコメントアウトして、「検索画面へようこそ。条件を指定して検索実行ボタンをクリックしてください」というメッセージに書き換えました。

では、実際にこのプロシージャをコールしてみましょう。初回はブラウザのURLから直接指定して実行するわけですね。

バックナンバー 第103回「WEBアプリ作成(1) (Oracle DBとPL/SQLだけで、即、WEBアプリ)」と同等の設定ができていれば、以下のURLです。

http://localhost:8080/dad/doc_find

ログインを求められたら、スキーマユーザ(私の場合は scott)でログインしてください。

開いた直後の画面です。検索条件の項目はNULL(値がない)であり、リストはデフォルト値です。初回など、URLで画面を開いたので、検索実行ボタンでこの画面が生成されたわけではなく、画面の下部に「検索画面へようこそ・・・」のメッセージが表示されています。

検索条件を指定して、検索実行ボタンをクリックすると、生成されたSELECT文が表示されます(次の画面)

このように、生成されたSELECT文が表示されました。SELECT文の中に、「:V_KEYWORD1」というバインド変数の記述があります。このバインド変数の値は「%太郎%」であることがわかります。つまり、指定したキーワードの前後に「%」がついています。これは、文字列の部分一致検索 LIKE '%~%'の検索条件として使われます。

生成されたSQLにバインド変数を当てはめて実際に実行してみました。ここでは、4番の親ディレクトリ以下のすべての階層の文書やディレクトリ「ID IN (4,5,7,・・・・)」に対して、タイトル(TITLE)や本文(HONBUN)に「太郎」という文字を含むディレクトリ(KBN=2)や、文書(KBN=1)を検索したわけです。登録日時(INSERT_DATE)と更新日時(UPDATE_DATE)も対象にしています。更新日時が最近の順番(ORDER BY UPDATE_DATE DESC)で表示します。その結果、私の環境では「○○太郎が登場する日本の昔話」というディレクトリと、「桃太郎」、「金太郎」という文書が検索されました。

また以下もご覧ください。

ご覧のように、キーワードを小文字で指定しても、バインド変数の値は大文字に変換されます。また比較される列もUPPER関数で大文字に変換されます。したがって、検索キーワードの大文字・小文字の違いは無視して検索されることがわかりますね。

如何ですか?正しく、SELECT文が生成されていることが確認わかりますね。

上記のコードはSQLインジェクションを防止することを心掛けて書きましたが以下の点に注意しました。

  • 選択リストの項目は、リスト値のどれからの値であることをチェックする。
    それに反する場合はエラーとする。
  • キーワードなど、不定の値でかつ、SQL文の処理する値(例 条件値、INSERT, UPDATEの列の値など)として使われるものはバインド変数とする。

それでは今回はここまでにします。

次回は生成されたSELECT文を画面に表示するのではなく、そのSELECT文を実際に実行して、その結果を画面に表示するように機能を追加します。それにより検索機能は完成です。

それでは次回、ご期待ください。

先頭へ戻る