[Laravel] whereIntegerInRaw()で、PDOのプレースホルダー問題と向き合う

# 概要

みなさんは下記のエラーに悩まされたことはありませんか？

```bash
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
<?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()` を使用しましょう。

https://laravel.com/docs/10.x/queries#additional-where-clauses

> 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()` で解決するのか？

ソースコードを見に行きましょう。

https://github.com/laravel/framework/blob/10.x/src/Illuminate/Database/Query/Builder.php#L1144-L1170

```php
    /**
     * 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キャストしているとはいえ、プレースホルダーのは脆弱性的にどうなの？」と思った方もいらっしゃるのではないでしょうか？

やってみましょう。

```php
    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エラーも起きておりません。

```bash
# 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以外にも、詰まりポイントがあるので注意しましょう。

https://qiita.com/tondemonai7/items/93bf71714f7f828c0164


# まとめ

`whereIntegerInRaw()` の特徴は...

- 早い
- PDOのプレースホルダーの上限数を気にしなくていい
- intキャストがあるので、あまり脆弱性を気にしなくていい

そもそも、 `WHERE IN ('dog', 'cat')` のようなstringを使うケースはまれで、
皆さん何かしらのintの値を用いて`WHERE IN` することが、実際のユースケースとして多いのではないでしょうか。

`whereIntegerInRaw()` を使い倒しましょう！