In spatie\QueryBuilder , the request parameter contains “,” error: sqlstate[HY093]: Invalid parameter number: number of bound variables not match number of tokens
1. In Spatie\QueryBuilder , the request parameter contains “,” error: sqlstate[HY093]: Invalid parameter number: number of bound variables 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)). as shown in Figure 1
![在 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` <= 2024-08-21 08:54:14 and `table`.`shipping_error_message` in (aaa))。](https://www.shuijingwanwq.com/wp-content/uploads/2024/09/1.png)
Figure 1
Figure 2
3. View the generated 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 out the processed query and binding parameters, and check whether all parameters match the placeholders in SQL. Confirm placeholders ? There are 3, and there are 4 binding parameters. The root cause is “aaa, bbbb” because it is a comma interval, it is split into 2 binding parameters. as shown in Figure 3
Figure 3
print_r($builder->toSql());
print_r($builder->getBindings());
exit;
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 behavior of automatically converting comma (,) into an array in Spatie\QueryBuilder. Reference:Is it possible to disable a delimiter for an allowed filter? Print out the processed query and binding parameters, and check if all the parameters match placeholders in SQL. Confirm placeholders ? There are 3, and there are 3 binding parameters. in line with expectations. as shown in Figure 4
Figure 4
$allowedFilters[] = AllowedFilter::exact($field, null, true, false);
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”, sql will no longer report an error, which is in line with expectations. View the generated SQL. as shown in Figure 5
Figure 5
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')
