Oracleにおけるバインド変数の利用

この記事は「LIFULL Advent Calender2017その2」の11日目の記事です。

バインド変数とは

• SQL文の中で用いる変数のことです。
• これを用いることで、動的にSQL文を作成することができます

バインド変数を使うメリット

バインド変数を用いるメリットとして、次の二つのことが挙げられます。

• SQLインジェクション対策になる
• パフォーマンスの効率化が見込まれる

この2点について、以下に簡単にまとめてみました。

SQLインジェクションに対する対策

（例）文字列リテラルに対する攻撃

sample.sql

$sql = "SELECT * FROM USER_TABLE WHERE USERID=$id AND PASSWORD=$password";

というクエリに対し、$passwordに「' OR 'A' = 'A」という入力を行うと

injection.sql

$sql = "SELECT * FROM USER_TABLE WHERE USERID=$id AND PASSWORD='' OR 'A' = 'A' ";

となり、実装者の意図せずにWHERE句が常に成立してしまいます。よくないですね。

（例）数値リテラルに対する攻撃

sample.sql

$sql = "SELECT * FROM USER_TABLE WHERE USERID=$id";

というクエリに対し、$idに「0;DELETE FROM USER_TABLE」という入力を行うと

injection.sql

$sql = "SELECT * FROM USER_TABLE WHERE USERID=0;DELETE FROM USER_TABLE";

となり、実装者の意図せずにテーブルの情報が全て削除されてしまいます。よくないですね。

バインド変数（プレースホルダ）による対策

上記の例のように、SQLインジェクションの原因は、パラメータに与えられた値がリテラルの外にはみ出た状態となり、SQL文が変更されることです。

プレースホルダを利用すると、先ほど示したSQL文は以下のように記述できます。

bind1.sql

＄ｓｑｌ = "SELECT * FROM USER_TABLE WHERE USERID=:user_id AND PASSWORD=:password";

bind2.sql

＄ｓｑｌ = "SELECT * FROM USER_TABLE WHERE USERID=? AND PASSWORD=?";

コロン（:）から始まる項目やクエスチョンマーク(?)をプレースホルダと呼び、プレースホルダに値を割り当てることをバインドと呼びます。
※プレースホルダについて、OracleのSQLやPL/SQLではコロン（:）、他の言語ではクエスチョンマーク(?)等を使用します。

プレースホルダが安全な理由

では、なぜプレースホルダを用いると安全なのでしょうか。

ここでは、静的プレースホルダの流れを用いて説明したいと思います。
静的プレースホルダでは、値のバインドをDB側で行う特徴があり、大まかな流れとしては以下の通りです。

1. バインド変数値と、SQLがそれぞれ別々にDBへ発行される。
2. バインド変数値と、SQLがそれぞれ別々にコンパイルされる。
3. 2で処理されたバインド変数の値とSQLがマージされて実行される。

このように、プレースホルダの状態でSQL文がコンパイルされるため、後からSQL文が意図せずに変更されることはありえません。安全ですね。

バインド変数を用いたパフォーマンスの改善

例えば、以下のようにバインド変数部以外が同じSQLを複数回実行するような場合、
バインド変数を利用することで解析処理のフェーズがスキップされ、SQL処理のパフォーマンスが改善されます。

not_use_bind.sql

＄ｓｑｌ1 = "SELECT * FROM MASTER_TABLE WHERE USERID='aaa' AND PASSWORD='pass'";
＄ｓｑｌ2 = "SELECT * FROM MASTER_TABLE WHERE USERID='bbb' AND PASSWORD='hoge'";
＄ｓｑｌ3 = "SELECT * FROM MASTER_TABLE WHERE USERID='ccc' AND PASSWORD='fuga'";

use_bind.sql

＄ｓｑｌ = "SELECT * FROM MASTER_TABLE WHERE USERID=:user_id AND PASSWORD=:password";

この理由として、Oracleでは以下の3ステップで発行されたSQLの処理を行われることが関係しています。

1. SQL文の構文解析 : SQL文の構文を検証し、最適化された実行計画を立てる
2. 変数のバインド : プレースホルダーに対する実際の値を指定
3. 実行 : 1で選択された実行計画と、2で格納された実際の値を使用してクエリの実行

このステップから分かるように、SQL文がDBに発行される度に解析の判定が行なわれることから、
バインド変数を用いることで実行計画の選択にかかる処理時間が短縮され、パフォーマンスの改善に繋がるということでした。（※必ずしも処理が早くなるということではないです。）

実装例

では、実際の実装例を見てみましょう。
ここでは、バインド変数を利用し、1000人以上の対象会員に対してUSER_TABLEにおける更新日を一斉に更新する場合を想定します。
OracleではIN句が1000件までしか指定できないので、OR句を利用して対応しています。

sample.php

// USER_TABLEの更新日を更新するsql文
// $user_idは更新対象の会員ID一覧の配列とします。
$sql  = 'UPDATE ';
$sql .=    'USER_TABLE';
$sql .= 'SET ';
$sql .=    'MODIFY_DATE = SYSDATE ';
$sql .= 'WHERE ';
// WHERE要素以降のsql文の設定 バインド変数を利用
$bind_key    = array();
$bindstr_arr = array();
$bind_arr    = array();
// バインド変数の作成準備
$bind_key = array_map( // 引数の配列の各要素にcallback関数を適用する
                function($key) {return 'uid' . $key;},
                array_keys($user_id) // 配列のkey全てを返す
            );
// sql構文連結用のバインド変数の作成
$bindstr_arr = array_map(
                    function($key) {return ':' . $key;}, 
                    $bind_key
                );
// sql実行用のバインド変数を作成
$bind_arr = array_combine( // 引数の配列をkeyとvalueとして配列を生成する
                $bind_key,
                $user_id
            );
// IN句が1000件以上になる場合を考慮した対応
$sql_in_id = implode(
           array_map( // 配列の各要素にcallback関数を適用
               function($bindstr_chunk) {return '('. implode($bindstr_chunk, ',') . ')';},
               array_chunk($bindstr_arr, 1000) // 配列を要素数に分割
           ),
           ' OR id IN ' // OR句で分割して連結
       );
// sqlの連結
$sql .= '( id IN '.$sql_in_id.' )';

何をやっているか

最終的なゴールは、以下のような形にすることです。

$sql = 'UPDATE USER_TABLE SET modify_date = SYSDATE WHERE ( id IN (:uid0, :uid01, :uid2, ・・・, :uid999) OR id IN (:uid1000, :uid1001, :uid1002, ・・・, :uid1999) )'

この形にするため、以下のステップで実装を行います。

1. WHERE句までのSQLを作成しておく。
2. バインド変数を作成するため、識別番号（uid0, ・・・・, uid1999）を作成。（$bind_key）
3. 各番号の先頭にコロン（:）をつける。（$bindstr_arr）
4. 識別番号とユーザーIDを紐づける配列の作成（$bind_arr）
5. 1000区切りで分割しimplodeを行い、SQLを作成する（$sql_in_id）
6. SQLの連結を行い、完成

おわり

Oracleにおけるバインド変数の利用について、簡単にまとめさせて頂きました。
最後まで読んでくださり、ありがとうございました。