[PHP]SQLのIN節に配列をセットする

PHPでSQLのIN節で配列を指定したい時に詰まった事メモ。

NG : IN節に配列でパラメータを渡す

$id = 1;
$num = [1,2,3,4,5,...];

$param = array(
    "id" => $id,
    "num" => $num,
);

$pdo = new PDO();
$sql = "SELECT * FROM hoge_table WHERE id=:id AND num IN (:num)"; // :numでエラーが起きる
$stmt = $pdo->prepare($sql);
$stmt->execute($param);

これだとこういうエラーが出る。

Notice: Array to string conversion in 

プレースホルダーに配列は使えないらしい。

OK : IN節にパラメータを一個一個指定する

一旦プレースホルダを一個一個指定して確認してみる。問題はなさそう。

$param = array(
    "id" => $id,
    "num1" => $num1,
    "num2" => $num2,
    "num3" => $num3,
);

$pdo = new PDO();
$sql = "SELECT * FROM hoge_table WHERE id=:id AND num IN (:num1, :num2, :num3);"; //一個一個指定する
$stmt = $pdo->prepare($sql);
$stmt->execute($param);

でもこれだと配列の個数が可変の時に困る。

なので修正。

NG : IN節にカンマ区切りの文字列のパラメータとして渡す

$param = array(
    "id" => $id,
    "num" => implode(",",$num), // numをパラメータとして渡す
);

$pdo = new PDO();
$sql = "SELECT * FROM hoge_table WHERE id=:id AND num IN (:num)";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);

これだとなぜかINに当てはまる最初の行だけ取得する。

（天の声：(:num)のカンマ区切りが数値型にパースされちゃうとか、そうゆう感じじゃないかな・・・）
（ありえそう・・・）

なので修正。

NG : カンマ区切りの文字列としてSQL文に入れ込む

$param = array(
    "id" => $id,
);

$pdo = new PDO();
$sql = "SELECT * FROM hoge_table WHERE id=:id AND num IN (".implode(",",$num).");"; //numをSQL文に入れ込んでしまう
$stmt = $pdo->prepare($sql);
$stmt->execute($param);

結果的にはうまくいく。
だけどパラメータをSQLに直接入れてるので、ユーザが入力できる値だったりするとSQLインジェクションの恐れがある。

OK : 疑問符のプレースホルダ使ってがんばる

以下のように解決できるみたい。詳しくは記事先へ。
PDOのwhereでIN句を使う - Qiita

リファレンスを確認

PDOじゃないけどmysqliに気になる記述があった。
PHP: mysqli_stmt::prepare - Manual

Turns out you can't directly use a prepared statement for a query that has a placeholder in an IN() clause.
There are ways around that (such as constructing a string that consists of n question marks separated by commas, then using that set of placeholders in the IN() clause), but you can't just say IN (?).
This is a MySQL restriction rather than a PHP restriction, but it's not really documented in the MySQL docs either, so I figured it was worth mentioning here.

「IN()節の中にプレースホルダを持つクエリに対して、準備された文を直接使用することはできないことがわかりました。
これを回避する方法はありますが (例えば、カンマで区切られた n 個のクエスチョンマークからなる文字列を作成し、そのプレースホルダのセットを IN() 節で使用するなど)、単に IN (?) と言うことはできません。
これはPHPの制限というよりはMySQLの制限ですが、MySQLのドキュメントにも書かれていないので、ここで言及する価値があると思いました。」

SQLの仕様らしい。

おしまい。