【唐诗】《石鼓歌》 韓愈：張生手持石鼓文，勸我試作石鼓歌。少陵無人謫仙死，才薄將奈石鼓何。周綱陵遲四海沸，宣王憤起揮天戈。大開明堂受朝賀，

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

SQLSTATE[HY093] Error in Spatie\QueryBuilder When Request Parameters Contain “,”

在 Spatie\QueryBuilder ，请求参数中包含 "," 报错：SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens (SQL: select count(*) as aggregate from `table` where `table`.`shipping_type` = 2 and `shipping_at_gmt` >= 2024-05-21 08:54:14 and `shipping_at_gmt`

作者：

永夜

在

, , , , , , , , , , ,
Views: 43

1. In Spatie\QueryBuilder, when the request parameter contains “,”, an error occurs: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens (SQL: select count(*) as aggregate from `table` where `table`.`shipping_type` = 2 and `shipping_at_gmt` >= 2024-05-21 08:54:14 and `shipping_at_gmt` <= 2024-08-21 08:54:14 and `table`.`shipping_error_message` in (aaa)). See Figure 1

In Spatie\QueryBuilder, when the request parameter contains ",", an error occurs: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens (SQL: select count(*) as aggregate from `table` where `table`.`shipping_type` = 2 and `shipping_at_gmt` >= 2024-05-21 08:54:14 and `shipping_at_gmt` <= 2024-08-21 08:54:14 and `table`.`shipping_error_message` in (aaa)).
Figure 1

2. After changing the request parameter from “aaa,bbbb” to “aaa”, the SQL no longer throws an error. See Figure 2

After changing the request parameter from "aaa,bbbb" to "aaa", the SQL no longer throws an error
Figure 2

3. View the generated SQL

SQL

select
  count(*) as aggregate
from
  `table`
where
  `table`.`shipping_type` = 2
  and `shipping_at_gmt` >= '2024-05-21 09:00:21'
  and `shipping_at_gmt` <= '2024-08-21 09:00:21'
  and `table`.`shipping_error_message` in ('aaa')

4. Print the processed query and bound parameters to check whether all parameters match the placeholders in the SQL. Confirm that there are 3 placeholders ? and 4 bound parameters. The root cause is that “aaa,bbbb” was split into 2 bound parameters because it is comma-separated. See Figure 3

Print the processed query and bound parameters to check whether all parameters match the placeholders in the SQL. Confirm that there are 3 placeholders ? and 4 bound parameters. The root cause is that "aaa,bbbb" was split into 2 bound parameters because it is comma-separated.
Figure 3
PHP

        print_r($builder->toSql());
        print_r($builder->getBindings());
        exit;
Plaintext

select `table`.* from `table` where `shipping_at_gmt` >= ? and `shipping_at_gmt` <= ? and `table`.`shipping_error_message` in (?) order by `operated_at_gmt` descArray
(
    [0] => Illuminate\Support\Carbon Object
        (
            [endOfTime:protected] => 
            [startOfTime:protected] => 
            [constructedObjectId:protected] => 000000000000115d0000000000000000
            [localMonthsOverflow:protected] => 
            [localYearsOverflow:protected] => 
            [localStrictModeEnabled:protected] => 
            [localHumanDiffOptions:protected] => 
            [localToStringFormat:protected] => 
            [localSerializer:protected] => 
            [localMacros:protected] => 
            [localGenericMacros:protected] => 
            [localFormatFunction:protected] => 
            [localTranslator:protected] => 
            [dumpProperties:protected] => Array
                (
                    [0] => date
                    [1] => timezone_type
                    [2] => timezone
                )

            [dumpLocale:protected] => 
            [dumpDateProperties:protected] => 
            [date] => 2024-05-29 03:03:53.000000
            [timezone_type] => 3
            [timezone] => UTC
        )

    [1] => Illuminate\Support\Carbon Object
        (
            [endOfTime:protected] => 
            [startOfTime:protected] => 
            [constructedObjectId:protected] => 000000000000115e0000000000000000
            [localMonthsOverflow:protected] => 
            [localYearsOverflow:protected] => 
            [localStrictModeEnabled:protected] => 
            [localHumanDiffOptions:protected] => 
            [localToStringFormat:protected] => 
            [localSerializer:protected] => 
            [localMacros:protected] => 
            [localGenericMacros:protected] => 
            [localFormatFunction:protected] => 
            [localTranslator:protected] => 
            [dumpProperties:protected] => Array
                (
                    [0] => date
                    [1] => timezone_type
                    [2] => timezone
                )

            [dumpLocale:protected] => 
            [dumpDateProperties:protected] => 
            [date] => 2024-08-29 03:03:53.000000
            [timezone_type] => 3
            [timezone] => UTC
        )

    [2] => aaa
    [3] => bbbb
)

5. Decide to disable the automatic conversion of commas (,) to arrays in Spatie\QueryBuilder. Reference: Is it possible to disable a delimiter for an allowed filter?  Print the processed query and bound parameters to check whether all parameters match the placeholders in the SQL. Confirm that there are 3 placeholders ? and 3 bound parameters. This matches expectations. See Figure 4

Decide to disable the automatic conversion of commas (,) to arrays in Spatie\QueryBuilder. Reference: Is it possible to disable a delimiter for an allowed filter? https://github.com/spatie/laravel-query-builder/discussions/756 Print the processed query and bound parameters to check whether all parameters match the placeholders in the SQL. Confirm that there are 3 placeholders ? and 3 bound parameters. This matches expectations.
Figure 4
PHP

$allowedFilters[] = AllowedFilter::exact($field, null, true, false);
Plaintext

select `table`.* from `table` where `shipping_at_gmt` >= ? and `shipping_at_gmt` <= ? and `table`.`shipping_error_message` in (?) order by `operated_at_gmt` descArray
(
    [0] => Illuminate\Support\Carbon Object
        (
            [endOfTime:protected] => 
            [startOfTime:protected] => 
            [constructedObjectId:protected] => 000000000000115d0000000000000000
            [localMonthsOverflow:protected] => 
            [localYearsOverflow:protected] => 
            [localStrictModeEnabled:protected] => 
            [localHumanDiffOptions:protected] => 
            [localToStringFormat:protected] => 
            [localSerializer:protected] => 
            [localMacros:protected] => 
            [localGenericMacros:protected] => 
            [localFormatFunction:protected] => 
            [localTranslator:protected] => 
            [dumpProperties:protected] => Array
                (
                    [0] => date
                    [1] => timezone_type
                    [2] => timezone
                )

            [dumpLocale:protected] => 
            [dumpDateProperties:protected] => 
            [date] => 2024-05-29 05:54:56.000000
            [timezone_type] => 3
            [timezone] => UTC
        )

    [1] => Illuminate\Support\Carbon Object
        (
            [endOfTime:protected] => 
            [startOfTime:protected] => 
            [constructedObjectId:protected] => 000000000000115e0000000000000000
            [localMonthsOverflow:protected] => 
            [localYearsOverflow:protected] => 
            [localStrictModeEnabled:protected] => 
            [localHumanDiffOptions:protected] => 
            [localToStringFormat:protected] => 
            [localSerializer:protected] => 
            [localMacros:protected] => 
            [localGenericMacros:protected] => 
            [localFormatFunction:protected] => 
            [localTranslator:protected] => 
            [dumpProperties:protected] => Array
                (
                    [0] => date
                    [1] => timezone_type
                    [2] => timezone
                )

            [dumpLocale:protected] => 
            [dumpDateProperties:protected] => 
            [date] => 2024-08-29 05:54:56.000000
            [timezone_type] => 3
            [timezone] => UTC
        )

    [2] => aaa,bbbb
)

6. When the request parameter is “aaa,bbbb”, the SQL no longer throws an error, as expected. View the generated SQL. See Figure 5

When the request parameter is "aaa,bbbb", the SQL no longer throws an error, as expected. View the generated SQL
Figure 5
SQL

select
  count(*) as aggregate
from
  `table`
where
  `shipping_at_gmt` >= '2024-05-29 05:57:18'
  and `shipping_at_gmt` <= '2024-08-29 05:57:18'
  and `table`.`shipping_error_message` in ('aaa,bbbb')

关于作者 · 持续学习与技术实践

我是一名拥有 15 年以上开发经验的技术从业者，自 2013 年起持续运营个人技术博客，记录工作与个人项目中遇到的真实问题，以及解决问题过程中的探索、实践和思考。

除了技术写作，我也在持续开发和维护自己的独立项目，探索新技术和新工具在实际场景中的应用。我始终相信，没有不值得去解决的问题，也没有不值得去学习的技术。

如果这篇文章对你有所帮助，欢迎浏览博客中的其他文章，也欢迎交流技术经验与想法。

关于我  ·  GitHub  ·  邮件联系

更多文章