概要
みなさんは下記のエラーに悩まされたことはありませんか？
SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders
こちらは、PDOで使用しているプレースホルダーの上限数を超えるプレースホルダーを使用しようとした場合に発生するエラーです。
具体的には、65,535 件以上のプレースホルダーを使用しようとすると発生します。
みなさんも大量の WHERE INで経験があるのではないでしょうか。
この記事では、whereIntegerInRaw()を用いて、該当のエラーを回避しつつ、より高速にWHERE IN句を使用する方法を記載します。
該当のPDOエラーについて
下記のテストケースの通り、Eloquentを使用して、エラーを再現してみました。
Eloquentの水面下で動くPDOにおいては、65535より大きい数のプレースホルダーを使用すると、PDOExceptionが発生します。
<?php
namespace Tests\Feature;
use App\Models\User;
use PDOException;
use Tests\TestCase;
class PdoTest extends TestCase
{
private static array $arrayAsPdoLimit = [];
private static array $arrayOverPdoLimit = [];
public static function setUpBeforeClass(): void
{
for ($i=1; $i<=65535; $i++) {
self::$arrayAsPdoLimit[] = 1;
}
self::$arrayOverPdoLimit = array_merge(self::$arrayAsPdoLimit, [1]);
}
public function test_PDOのバインディングが65535であれば実行可能(): void
{
$query = User::whereIn('id', self::$arrayAsPdoLimit);
$this->assertCount(65535, $query->getBindings());
$this->assertEmpty($query->get(), 'factoryなどで作成したレコードが存在しないので空で返る');
}
public function test_PDOのバインディングが65536を超えるとPDO例外(): void
{
$query = User::whereIn('id', self::$arrayOverPdoLimit);
$this->assertCount(65536, $query->getBindings());
$this->expectException(PDOException::class);
$this->expectExceptionMessageMatches('/SQLSTATE\[HY000\]: General error: 1390 Prepared statement contains too many placeholders/');
$query->get();
}
Laravelにおける解決方法
Laravelを使用しており、intの値で WHERE IN を使用したい場合は、whereIntegerInRaw() 、または、 whereIntegerNotInRaw() を使用しましょう。
If you are adding a large array of integer bindings to your query, the whereIntegerInRaw or whereIntegerNotInRaw methods may be used to greatly reduce your memory usage.
ちなみに、intのWHERE INにおいて、大幅にメモリを削減できることが本来の使い方です。
なぜプレースホルダー問題が、whereIntegerInRaw() で解決するのか？
ソースコードを見に行きましょう。
/**
* Add a "where in raw" clause for integer values to the query.
*
* @param string $column
* @param \Illuminate\Contracts\Support\Arrayable|array $values
* @param string $boolean
* @param bool $not
* @return $this
*/
public function whereIntegerInRaw($column, $values, $boolean = 'and', $not = false)
{
$type = $not ? 'NotInRaw' : 'InRaw';
if ($values instanceof Arrayable) {
$values = $values->toArray();
}
$values = Arr::flatten($values);
foreach ($values as &$value) {
$value = (int) $value;
}
$this->wheres[] = compact('type', 'column', 'values', 'boolean');
return $this;
}
Arrayable をarray化して、要素の$valueをintキャストして Whereに突っ込んでいるのがわかりますね。
そう、プレースホルダーをそもそも使用してないんです。
よって、掲題のPDOエラーは発生しません。
ちなみに、whereIntegerInRaw() はLaravel 10.x 以前からあるメソッドなので、手元のLaravelが古い方も安心して使えます。
実際にやってみた
「intキャストしているとはいえ、プレースホルダーのは脆弱性的にどうなの？」と思った方もいらっしゃるのではないでしょうか？
やってみましょう。
public function test_whereIntegerInRawであればPDO例外はでない(): void
{
$query = User::whereIntegerInRaw('id', self::$arrayOverPdoLimit);
$this->assertEmpty($query->getBindings());
$this->assertEmpty($query->get(), 'factoryで作成したデータが存在しないので空配列が返る');
}
public function test_ついでにwhereIntegerInRawはintキャストされることを確認(): void
{
$query = User::whereIntegerInRaw('id', [
'1); DROP TABLE users;',
'DROP DATABASE *;',
'',
]);
$this->assertMatchesRegularExpression(
'/select \* from `users` where `id` in \(([0-1],* *)*\)/',
// https://laravel-news.com/laravel-10-15-0
$query->toRawSql()
);
$this->assertMatchesRegularExpression(
'/in \(1\, 0\, 0\)/',
$query->toRawSql(),
'PHPのintキャストの結果を適当に見ておく...'
);
}
ご覧ください。
'1); DROP TABLE users;' のような殺意高めなstringも、intキャストしているので、無害化されていますね。
また、掲題のPDOエラーも起きておりません。
# sail test --profile
PASS Tests\Feature\PdoTest
✓ p d oのバインディングが65535であれば実行可能 0.32s
✓ p d oのバインディングが65536を超えると p d o例外 6.51s
✓ where integer in rawであれば p d o例外はでない 0.05s
✓ ついでに、where integer in rawはintキャストされることを確認 0.01s
Tests: 4 passed (9 assertions)
Duration: 6.94s
Top 10 slowest tests:
Tests\Feature\PdoTest > p d oのバインディングが65536を超えると p d o例外 6.51s
Tests\Feature\PdoTest > p d oのバインディングが65535であれば実行可能 0.32s
Tests\Feature\PdoTest > where integer in rawであれば p d o例外はでない 0.05s
Tests\Feature\PdoTest > ついでに、where integer in rawはintキャストされることを確認 0.01s
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
whereIn() では0.32s、whereIntegerInRaw() では 0.05sと、クエリの発行時点で、処理時間が1/6以下になっていますね。
ちなみに、今回のテストケースにおいては、レコードは用意していないので、実際にデータベースからデータを取ってくる際は、どちらのケースもコレクション処理などで更に時間がかかることが予想されます。
余談：SQLドライバによって WHERE IN に使える数が異なる
下記の方の記事のように、使用しているSQLドライバによって、使用できる数が異なってきます。
PDO以外にも、詰まりポイントがあるので注意しましょう。
まとめ
whereIntegerInRaw() の特徴は...
- 早い
- PDOのプレースホルダーの上限数を気にしなくていい
- intキャストがあるので、あまり脆弱性を気にしなくていい
そもそも、 WHERE IN ('dog', 'cat') のようなstringを使うケースはまれで、
皆さん何かしらのintの値を用いてWHERE IN することが、実際のユースケースとして多いのではないでしょうか。
whereIntegerInRaw() を使い倒しましょう！