laravel-query-builder
laravel-query-builder copied to clipboard
Published
20 hours ago •
spatie
spatie
SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
With the version 5.8.1 there is an issue with multiple partial filters, the query builder throw an error:
SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
select count(*) as aggregate from "users" where LOWER("users"."name") LIKE %vella% ESCAPE '\' and LOWER("users"."email") LIKE %rea% ESCAPE '\'
The query seems ok, I've tested with Laravel 11.7.0 and Postgres 16 and also on Postgres 14 .
Same issue here !
HTTP Request:
curl --location --globoff 'http://localhost:8000/transactions?filter[type]=AO,OE' \
--header 'Accept: application/json'
HTTP Response:
{
"message": "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (Connection: pgsql, SQL: select count(*) as aggregate from \"banking_transactions\" where \"banking_transactions\".\"company_id\" = 9c0e501e-b3db-4496-92e0-09a25715b2cd and \"banking_transactions\".\"company_id\" is not null and (LOWER(\"banking_transactions\".\"type\") LIKE %ao% ESCAPE '\\' or LOWER(\"banking_transactions\".\"type\") LIKE %oe% ESCAPE '\\'))",
"exception": "Illuminate\\Database\\QueryException",
"file": "/vendor/laravel/framework/src/Illuminate/Database/Connection.php",
"line": 813,
"trace": [
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Connection.php",
"line": 767,
"function": "runQueryCallback",
"class": "Illuminate\\Database\\Connection",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Connection.php",
"line": 398,
"function": "run",
"class": "Illuminate\\Database\\Connection",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php",
"line": 2993,
"function": "select",
"class": "Illuminate\\Database\\Connection",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php",
"line": 2978,
"function": "runSelect",
"class": "Illuminate\\Database\\Query\\Builder",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php",
"line": 3566,
"function": "Illuminate\\Database\\Query\\{closure}",
"class": "Illuminate\\Database\\Query\\Builder",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php",
"line": 2977,
"function": "onceWithColumns",
"class": "Illuminate\\Database\\Query\\Builder",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php",
"line": 3172,
"function": "get",
"class": "Illuminate\\Database\\Query\\Builder",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php",
"line": 3131,
"function": "runPaginationCountQuery",
"class": "Illuminate\\Database\\Query\\Builder",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php",
"line": 967,
"function": "getCountForPagination",
"class": "Illuminate\\Database\\Query\\Builder",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Support/Traits/ForwardsCalls.php",
"line": 23,
"function": "paginate",
"class": "Illuminate\\Database\\Eloquent\\Builder",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Support/Traits/ForwardsCalls.php",
"line": 52,
"function": "forwardCallTo",
"class": "Illuminate\\Database\\Eloquent\\Relations\\Relation",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php",
"line": 517,
"function": "forwardDecoratedCallTo",
"class": "Illuminate\\Database\\Eloquent\\Relations\\Relation",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Support/Traits/ForwardsCalls.php",
"line": 23,
"function": "__call",
"class": "Illuminate\\Database\\Eloquent\\Relations\\Relation",
"type": "->"
},
{
"file": "/vendor/spatie/laravel-query-builder/src/QueryBuilder.php",
"line": 105,
"function": "forwardCallTo",
"class": "Spatie\\QueryBuilder\\QueryBuilder",
"type": "->"
},
{
"file": "/app/Traits/PaginatesCollection.php",
"line": 38,
"function": "__call",
"class": "Spatie\\QueryBuilder\\QueryBuilder",
"type": "->"
},
{
"file": "/app/Http/Controllers/TransactionController.php",
"line": 49,
"function": "paginateCollection",
"class": "App\\Http\\Controllers\\Controller",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Controller.php",
"line": 54,
"function": "index",
"class": "App\\Http\\Controllers\\V1\\TransactionController",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php",
"line": 43,
"function": "callAction",
"class": "Illuminate\\Routing\\Controller",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Route.php",
"line": 260,
"function": "dispatch",
"class": "Illuminate\\Routing\\ControllerDispatcher",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Route.php",
"line": 206,
"function": "runController",
"class": "Illuminate\\Routing\\Route",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Router.php",
"line": 806,
"function": "run",
"class": "Illuminate\\Routing\\Route",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 144,
"function": "Illuminate\\Routing\\{closure}",
"class": "Illuminate\\Routing\\Router",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php",
"line": 50,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Routing\\Middleware\\SubstituteBindings",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php",
"line": 161,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php",
"line": 127,
"function": "handleRequest",
"class": "Illuminate\\Routing\\Middleware\\ThrottleRequests",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php",
"line": 89,
"function": "handleRequestUsingNamedLimiter",
"class": "Illuminate\\Routing\\Middleware\\ThrottleRequests",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Routing\\Middleware\\ThrottleRequests",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php",
"line": 64,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Auth\\Middleware\\Authenticate",
"type": "->"
},
{
"file": "/vendor/laravel/sanctum/src/Http/Middleware/EnsureFrontendRequestsAreStateful.php",
"line": 25,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 144,
"function": "Laravel\\Sanctum\\Http\\Middleware\\{closure}",
"class": "Laravel\\Sanctum\\Http\\Middleware\\EnsureFrontendRequestsAreStateful",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 119,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/sanctum/src/Http/Middleware/EnsureFrontendRequestsAreStateful.php",
"line": 24,
"function": "then",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Laravel\\Sanctum\\Http\\Middleware\\EnsureFrontendRequestsAreStateful",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 119,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Router.php",
"line": 805,
"function": "then",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Router.php",
"line": 784,
"function": "runRouteWithinStack",
"class": "Illuminate\\Routing\\Router",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Router.php",
"line": 748,
"function": "runRoute",
"class": "Illuminate\\Routing\\Router",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Routing/Router.php",
"line": 737,
"function": "dispatchToRoute",
"class": "Illuminate\\Routing\\Router",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php",
"line": 200,
"function": "dispatch",
"class": "Illuminate\\Routing\\Router",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 144,
"function": "Illuminate\\Foundation\\Http\\{closure}",
"class": "Illuminate\\Foundation\\Http\\Kernel",
"type": "->"
},
{
"file": "/vendor/livewire/livewire/src/Features/SupportDisablingBackButtonCache/DisableBackButtonCacheMiddleware.php",
"line": 19,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Livewire\\Features\\SupportDisablingBackButtonCache\\DisableBackButtonCacheMiddleware",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php",
"line": 21,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ConvertEmptyStringsToNull.php",
"line": 31,
"function": "handle",
"class": "Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Foundation\\Http\\Middleware\\ConvertEmptyStringsToNull",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php",
"line": 21,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php",
"line": 51,
"function": "handle",
"class": "Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Foundation\\Http\\Middleware\\TrimStrings",
"type": "->"
},
{
"file": "/vendor/illuminatech/multipart-middleware/src/MultipartFormDataParser.php",
"line": 131,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminatech\\MultipartMiddleware\\MultipartFormDataParser",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Http/Middleware/ValidatePostSize.php",
"line": 27,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Http\\Middleware\\ValidatePostSize",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php",
"line": 110,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Http/Middleware/HandleCors.php",
"line": 62,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Http\\Middleware\\HandleCors",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Http/Middleware/TrustProxies.php",
"line": 57,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 183,
"function": "handle",
"class": "Illuminate\\Http\\Middleware\\TrustProxies",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php",
"line": 119,
"function": "Illuminate\\Pipeline\\{closure}",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php",
"line": 175,
"function": "then",
"class": "Illuminate\\Pipeline\\Pipeline",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php",
"line": 144,
"function": "sendRequestThroughRouter",
"class": "Illuminate\\Foundation\\Http\\Kernel",
"type": "->"
},
{
"file": "/public/index.php",
"line": 51,
"function": "handle",
"class": "Illuminate\\Foundation\\Http\\Kernel",
"type": "->"
},
{
"file": "/vendor/laravel/framework/src/Illuminate/Foundation/resources/server.php",
"line": 16,
"function": "require_once"
}
]
}
Facing this issue as well!
Package Version 5.8.1 PHP Version 8.3
Downgraded again to get it working
The issue is introduced in version 5.8.1. The source of issue is in aravel-query-builder/src/Filters /FiltersPartial.php file function maybeSpecifyEscapeChar
Removing the append of the ESCAPE override resolves the problem on PSQL. As far as it overrides the default escape character using the same default value it seems unclear the reason of why this function was added originally.
Seems like it exists on version 6.0.1 as well
Yes, I am still using 5.8 for now
Creator of the mentioned PR that causes the bug.
@GregoryGagua - To answer your doubt, the maybeSpecifyEscapeChar was added because an unexpected behavior happened while using the package with sqlite. Adding the explicit ESCAPE solves the issue. You can find more info and the testing I did in the dedicated PR #927.
I'm currently trying to debug and fix the issue. The easy solution would be to avoid adding the explicit ESCAPE while using the pgsql driver. According to my testing, it should both fix the 'Invalid parameter number' error and not re-introduce the bug fixed in the original PR, since pgsql was not affected by the bug itself (but supports explicit ESCAPE, so that's why it was included in the drivers to "escape").
If I won't be able to find a better/more complete solution, I will open a new PR proposing the fix I just described, so at least you all could update the dependency if merged.
I'll update this thread in case of any news, thanks to everybody! :)
(special thanks to @dwightwatson for mentioning the PR and bringing this issue to my attention)
Hey, thanks for everyone's patience. We'll continue this discussion in the bugfix PR #968.
Hi all, I've merged and tagged the proposed fix in v6.2.1 of the package. This is a new major release since the last bug-free version of this package (v5.8.0). I'd recommend upgrading to v6 as the upgrade path is very minimal. If you really require this fix on v5 of the package, feel free to send a PR with the same fix forked from the v5 branch of the package. Thanks!