SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

[AndreaGero]

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 .

[sofianegargouri]

Same issue here !

HTTP Request:

curl --location --globoff 'http://localhost:8000/transactions?filter[type]=AO,OE' \
--header 'Accept: application/json'

HTTP Response:

```json { "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" } ] } ```

[nadine-wunu]

Facing this issue as well!

Package Version 5.8.1 PHP Version 8.3

Downgraded again to get it working

[GregoryGagua]

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.

[bazylys]

+1

[sbruni]

+1

[iamsubingyawali]

Seems like it exists on version 6.0.1 as well

[AndreaGero]

Seems like it exists on version 6.0.1 as well

Yes, I am still using 5.8 for now

[cheeeasy2501]

Same issue

[kurrata]

Can confirm that last version without this bug is 5.8.0

[Talpx1]

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)