PHPで動的なSQLでプリペアドステートメントな一例(本題は、IN句でどうやってプリペアるか)

端的には「PHPで、動的にSQL文を組む必要があるときにどうやってプリペアドステートメントで組んでいくか」の一例と、それに合わせて「INをうまいことプリペアドステートメントで使いたい」時の一例を書いてみます。
いやなんか知られてるような知られてないような微妙なナレッジだったので「まぁ書いておけばいいや」的な、雑な発想ですｗ

毎度のお話な気もするのですが。
「ここクラックできるんぢゃね？」とかあったらコメント等にて突っ込みをいただければ、速やかに修正を入れますノ

さて「PHPで、動的にSQL文を組む必要があるとき」って例えばどんな？ってのがあるのですが。
割と端的には「(複数の入力項目がある)検索系のフォーム」。
「なにか入力されていたらその項目を検索用に使う」「なにも入力されてなかったらその項目は検索用に使わない」ってのは、多分、業務的には「ちょいちょいあって」かつ「ある程度、動的に対応する必要があるんじゃないかなぁ」と思うです。
噛み砕くと「WHERE句あたりを動的にする必要があるよねぇ」的な。

一方では「動的にSQLを生成せざるを得ない明確な要件」があって。
他方で「動的なSQL生成はSQL=Injection的に危険」というお話があって。
その間を少しパテのように埋めてみましょう、ってのが、この記事の主題でございます。

さて前提として、例えば以下の３項目があると仮定しましょう。項目は超絶適当です*1ｗ
・name：名前
・age：年齢
・email：メアド
あと、やっぱり前提として、面倒なんで「検索formの名前も同じ」と仮定します。
検索は、おいちゃん的には「ブックマークできた方が楽だよねぇ」ってんでGETメソッドでやり取りする事が多いので、メソッドはGET前提。
＆ PHP7ね。null合体演算子が楽だからｗ。PHP5な方々は、適当に読み替えてちょ。

さて。上述をシンプルに実装するんであれば、まぁ、だいたいこんな感じのコードになります。

// WHERE句用作業領域
$where_wk = [];

// SQLのベース
$sql = 'SELECT * FROM 検索対象テーブル';

// WHERE句の積み込み
foreach($_GET as $k => $v) {
    if ('' !== $v) {
        $where_wk[] = "{$k} = '{$v}'";
    }
}
if ([] !== $where_wk) {
    $sql .= ' WHERE ' . implode(' AND ', $where_wk);
}

// SQL文の〆
$sql .= ';';

………さてこれで「OK」とか本気で思っている場合は以下検閲削除*2。
上述のコードは「どの角度からどうみてもどんなレベルからも明らかに絶対に完全にダメ」でございます。

とりあえず最低限
・$_GETに与えられたkeyとか当たり前に使ってる
・SQL文組み立てるのに「変数をダイレクトに入れている」
とか、昨今の事情的にはアウト感満載でございます。

んでは。
その辺を踏まえたうえで「たとえばこうやったら？」の一例を、簡単にコードで書いてみましょう。
一端あらかじめ、$dbhに「PDOの接続済インスタンス」が入ってるものとします。

// WHERE句用作業領域
$where_wk = [];
// ターゲット用のホワイトリスト
$target = ['name', 'age', 'email'];
// プレースホルダに充てるデータ用の作業配列
$placeholder_data = [];

// SQLのベース
$sql = 'SELECT * FROM 検索対象テーブル ';

// WHERE句の積み込み
foreach($target as $k) {
    if ('' !== ($_GET[$k] ?? '')) {
        // 「WHERE句の欠片」を配列にため込む
        $where_wk[] = "{$k} = :{$k}";
        // プレースホルダに当て込む値を保持しておく
        $placeholder_data[":{$k}"] = $_GET[$k];
    }
}
// 「WHERE句の欠片」からWHERE句を生成する
if ([] !== $where_wk) {
    $sql .= ' WHERE ' . implode(' AND ', $where_wk);
}

// SQL文の〆
$sql .= ';';

// 文の準備
$pre = $dbh->prepare($sql);
// プレースホルダに値を当て込む
foreach($placeholder_data as $k => $v) {
    $pre->bindValue($k, $v);
}

幾分おおざっぱですが、大体こんなところでしょうか。
個人的にはbindValueの第三引数は明示するほうが割と好みなので。$placeholder_dataにデータを入れるあたりで適宜、型を指定して入れつつ

// プレースホルダに値を当て込む
foreach($placeholder_data as $k => $v) {
    if ( (is_int($v))||(is_float($v)) ) {
        $type = PDO::PARAM_INT;
    } else {
        $type = PDO::PARAM_STR;
    }
    $pre->bindValue($k, $v, $type);
}

とかって書く方が好みですが、まぁこの辺はお好みで。
「わざわざ$placeholder_dataとかはさまなくてもいいじゃん」とか一瞬思いがちなのですが
・SQLが組み立て終わらないとprepareが発行できない
ので、少し手間を踏んでます。

こんな風にすると、とりあえず
・「SQLを動的に組み立てる」ところで、外部入力を直接使う箇所はない(自分で用意したホワイトリストだけを使ってる)
ので。「自分に悪意がある or 思いっきり不注意」以外では、あまりSQL-Injectionが入り込む余地はないのではないかなぁ、と。
プリペアドが名前付きなのは「そのほうが解りやすくて好みだから」です。

さて。
ここを少し応用して「IN句がある場合」を書いてみましょう。
ちなみに、斜めにざっくりとググるといくつかで「エスケープしたうえで文字列ダイレクト連結」とか見かけたりしますが、個人的にはあまりお好まない感じかなぁ、と。
重ねますと「値が数値である前提」ではあるものの「エスケープすらせずに文字列ダイレクト連結」とかってのも以前に拝見をしましたが、まぁ正直「お作法的にどうなのよ？」とか思ったりしてみたものでございます。っつかおいちゃん的には「激しくお好まない」実装です。
せっかく「プリペアド前提」で書いてるんだから、それくらいは貫いてみたいものでございます。

「おいちゃんならこう書くかなぁ」的サンプルは、以下の通り。
多分「動的な検索＋IN句」は少ないとは思うのですが、その辺はあえて「混ぜられるよ〜」ってニュアンス込みで、混ぜてみます。
「IN句を構成する」ために必要なデータはあらかじめ「どこかでゲトってる」前提。「副問い合わせ」だと、参考にならんでしょ？ｗ

// WHERE句用作業領域
$where_wk = [];
// ターゲット用のホワイトリスト
$target = ['name', 'age', 'email'];
// プレースホルダに充てるデータ用の作業配列
$placeholder_data = [];
// IN句で使いたいデータ
$in_array = [1, 2, 3, 4, 5]; // XXX 本当は上の方の処理でデータが入ってるはず、前提

// SQLのベース
$sql = 'SELECT * FROM 検索対象テーブル ';

// WHERE句の積み込み
foreach($target as $k) {
    if ('' !== ($_GET[$k] ?? '')) {
        // 「WHERE句の欠片」を配列にため込む
        $where_wk[] = "{$k} = :{$k}";
        // プレースホルダに当て込む値を保持しておく
        $placeholder_data[":{$k}"] = $_GET[$k];
    }
}

// IN句の積み込み
$in_wk = [];
// $in_arrayの整形
$in_array = array_values($in_array);
//
foreach($in_array as $num => $in_datum) {
    // 先にプレースホルダ名を作っておく
    $key = ":hoge_{$num}";
    // プレースホルダ名を作業領域に積んでいく
    $in_wk[] = $key;
    // プレースホルダに当て込む値を保持しておく
    $placeholder_data[$key] = $in_datum;
}
// IN句の組み立て、或いは「WHERE句の欠片」への積み込み
$where_wk[] = 'hoge IN (' . implode(', ', $in_wk) . ')';

// 「WHERE句の欠片」からWHERE句を生成する
if ([] !== $where_wk) {
    $sql .= ' WHERE ' . implode(' AND ', $where_wk);
}

// SQL文の〆
$sql .= ';';

// 文の準備と値の当て込み
$pre = $dbh->prepare($sql);
// プレースホルダに値を当て込む
foreach($placeholder_data as $k => $v) {
    $pre->bindValue($k, $v);
}

変数名とか超雑だけど、大体、イメージとしてはこんな感じ。
これだと「外部由来の変数は埋め込んでない」ので安全性が担保しやすいし。?じゃなくて:nameにする事で「順番が〜」とかあんまり考えずにどかどか組み立てられるし、万が一のデバッグも、?よりはしやすいんじゃなかろうか、と思うですだす。
「IN句の積み込み」〜「IN句の組み立て」までは一連の流れなので。「複数のINがある」ケースとかは、この一連をloopさせるくらいで片付くと思う。

ちな。
もちろんこの辺の論調で、カラム名について「いくら"自前で用意したホワイトリスト"でも、念のためにエスケープをするべきだ！！」って話が出る可能性はあって、そこについてはとりあえず肯定も否定もしないかなぁ、位の感触。
もちろん、原理原則的に「より安全」なのは特段に否定をしないので、その辺が否定しない理由。
ただまぁ「自分で用意するカラム名」で「引っかかりかねないような命名」って、普通あんまりしないので「そこまでやらんでもいいんじゃないかなぁ」って、今の所は思ってる……多分「何度か連打とかでやらかされたら」この辺はすぐに手のひらを返すんだけどｗ
なので。実装時に「いや俺は気になるからカラム名をエスケープするのだ！！」って諸氏を止める気は全くありませんので、その辺はご自由に。
おいちゃんは今までの経験と自分の手癖的に「そーゆーネーミングにはしない」前提で、面倒なんでエスケープは省いてます。多分「ヤバい可能性」が想起されたら、速攻でエスケープ処理入れるんだろうなぁｗ

以上。
おいちゃん的には大分と以前にたどり着いて「割と普通に書いている」処理なのですが。
なんか見聞きしていると「必ずしも一般的とは限らんのかしらん？？」と思ったので、幾分メモ書き的ニュアンス込みで、メモり。

誰かの参考にでもなれば幸いでございます。

追伸
IN句のあたり、おおざっぱに関数化するんなら、例えばこんなん。
おいちゃんにしては極めて珍しく参照渡しがありますが。

//
function make_in($col_name, $in_array, &$placeholder_data) {
    // IN句の積み込み
    $in_wk = [];
    // $in_arrayの整形
    $in_array = array_values($in_array);
    //
    foreach($in_array as $num => $in_datum) {
        // 先にプレースホルダ名を作っておく：カラム名＋'_'＋番号
        $key = ":{$col_name}_{$num}";
        // プレースホルダ名を作業領域に積んでいく
        $in_wk[] = $key;
        // プレースホルダに当て込む値を保持しておく
        $placeholder_data[$key] = $in_datum;
    }
    // IN句の組み立て、或いは「WHERE句の欠片」をreturn
    return "{$col_name} IN (" . implode(', ', $in_wk) . ')';
}

これを前提に

// WHERE句の積み込み
foreach($target as $k) {
    if ('' !== ($_GET[$k] ?? '')) {
        $where_wk[] = "{$k} = :{$k}";
        $placeholder_data[":{$k}"] = $_GET[$k];
    }
}
// IN句の積み込み
$where_wk[] = make_in('hoge', $in_array, $placeholder_data);
$where_wk[] = make_in('foo', [10,20], $placeholder_data); // 即席でこさえてみたダミーデータ

//
if ([] !== $where_wk) {
    $sql .= ' WHERE ' . implode(' AND ', $where_wk);
}

こんな感じで使えば、複数出てきた時に楽ちんだったりします。

2017-11-04 11:10頃追記
徳丸さんから

$in_array = ['1) ; DELETE FROM foo -- ' => 1, 1 => 2];
だとやばくね？

という意図の(もっと口調としては丁寧な)指摘を受けました。
確かに$in_arrayは「内部でSQLで作る想定なのでkeyについては数値前提」だったのですが、何か間違って「外部由来」にしたら危ないなぁ、というのに気付きました。
色々と手はあるかなぁと思うのですが、基本的に$in_arrayは「いわゆる(C++的な意味で)vector」のみを想定しているので。
使う前に、身もふたもなく

$in_array = array_values($in_array);

を差し込んで「問答無用でおかしなkeyを排除する」方向でいったいよいかなぁ、と思ったので、その方向で修正を入れてみます。

ちなみにコードは修正済。
修正前は、以下のコードでした。

//
function make_in($col_name, $in_array, &$placeholder_data) {
    // IN句の積み込み
    $in_wk = [];
    foreach($in_array as $num => $in_datum) {
        // 先にプレースホルダ名を作っておく：カラム名＋'_'＋番号
        $key = ":{$col_name}_{$num}";
        // プレースホルダ名を作業領域に積んでいく
        $in_wk[] = $key;
        // プレースホルダに当て込む値を保持しておく
        $placeholder_data[$key] = $in_datum;
    }
    // IN句の組み立て、或いは「WHERE句の欠片」をreturn
    return "{$col_name} IN (" . implode(', ', $in_wk) . ')';
}

*1:英字のスペルが短いからとかいう真実に対しては黙秘します

*2:ヤバい台詞が山盛りで記述されたのであわてて消してみましたまる

がるの健忘録

エンジニアでゲーマーで講師で占い師なおいちゃんのブログです。

PHPで動的なSQLでプリペアドステートメントな一例(本題は、IN句でどうやってプリペアるか)