Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
AndreaGero opened this issue May 15, 2024 · 10 comments
Open

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

AndreaGero opened this issue May 15, 2024 · 10 comments

Comments

@AndreaGero
Copy link

AndreaGero commented May 15, 2024 •
edited
Loading

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
Copy link

sofianegargouri commented May 20, 2024 •
edited
Loading

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
Copy link

nadine-wunu commented May 21, 2024 •
edited
Loading

Facing this issue as well!

Package Version 5.8.1
PHP Version 8.3

Downgraded again to get it working

@GregoryGagua
Copy link

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
Copy link

bazylys commented May 30, 2024

+1

1 similar comment
@sbruni
Copy link

sbruni commented Jun 14, 2024

+1

@iamsubingyawali
Copy link

iamsubingyawali commented Jun 22, 2024 •
edited
Loading

Seems like it exists on version 6.0.1 as well

@AndreaGero
Copy link
Author

Seems like it exists on version 6.0.1 as well

Yes, I am still using 5.8 for now

@CheesyTech
Copy link

Same issue

@dwightwatson dwightwatson mentioned this issue Jul 6, 2024
Merged
@kurrata
Copy link

kurrata commented Jul 19, 2024

Can confirm that last version without this bug is 5.8.0

@Talpx1
Copy link
Contributor

Talpx1 commented Jul 30, 2024

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)

Talpx1 added a commit to Talpx1/laravel-query-builder that referenced this issue Sep 9, 2024
@Talpx1
Removed explicit escaping for pgsql driver in FilterPartial#maybeSpec…
0033fe1 
…ifyEscapeChar. Fixes spatie#941

Added mariadb driver in FilterPartial#maybeSpecifyEscapeChar phpdoc for param $driver. Also adjusted test in order to run with mariadb driver only if the installed version of illuminate/database dependency supports the driver.
@Talpx1 Talpx1 mentioned this issue Sep 9, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
10 participants
@kurrata @sofianegargouri @sbruni @GregoryGagua @iamsubingyawali @CheesyTech @AndreaGero @Talpx1 @bazylys @nadine-wunu