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:

{
    "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

[CheesyTech]

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)

[AlexVanderbist]

Hey, thanks for everyone's patience. We'll continue this discussion in the bugfix PR #968.