where bind error An SQL error occurred: SQLSTATE[HY093]: Invalid parameter number: number of bound

lucianoluna

Hello, this is causing me the error "An SQL error occurred: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

->where(function ($q) {
    $_SESSION['myparam'] = 'Mr';

    $q->where('users.title', ':title', '=');
    $q->bind(':title', $_SESSION['myparam']);   
})  

if I do not bind the parameter but write the where line with a constant string everything is working fine.

I'm using php 7.4.27 this is the debug data:

{
    "fieldErrors": [],
    "error": "An SQL error occurred: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens",
    "data": [],
    "ipOpts": [],
    "cancelled": [],
    "debug": [
        {
            "query": "SELECT  `users`.`id` as 'users.id', `users`.`first_name` as 'users.first_name', `users`.`last_name` as 'users.last_name', `users`.`site` as 'users.site', `sites`.`name` as 'sites.name', `user_dept`.`dept_id` as 'user_dept.dept_id', `dept`.`name` as 'dept.name', `users`.`created` as 'users.created', `users`.`updated` as 'users.updated' FROM  `users` LEFT JOIN `sites` ON `sites`.`id` = `users`.`site`  LEFT JOIN `user_dept` ON `users`.`id` = `user_dept`.`user_id`  LEFT JOIN `dept` ON `user_dept`.`dept_id` = `dept`.`id` WHERE `users`.`title` = :where_0 ",
            "bindings": [
                {
                    "name": ":where_0",
                    "value": ":title",
                    "type": null
                },
                {
                    "name": ":title",
                    "value": "Mr",
                    "type": null
                }
            ]
        }
    ]
}

Thanks

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

$q->where('users.title', ':title', '=');

This is an automatic binding. You want:

$q->where('users.title', ':title', '=', false);

to disable that automatic binding.

Allan

lucianoluna

Now the error is different, I think could be a bug somewhere in the bind function ...

{
    "fieldErrors": [],
    "error": "An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''Mr'' in 'where clause'",
    "data": [],
    "ipOpts": [],
    "cancelled": [],
    "debug": [
        {
            "query": "SELECT  `users`.`id` as 'users.id', `users`.`first_name` as 'users.first_name', `users`.`last_name` as 'users.last_name', `users`.`site` as 'users.site', `sites`.`name` as 'sites.name', `user_dept`.`dept_id` as 'user_dept.dept_id', `dept`.`name` as 'dept.name', `users`.`created` as 'users.created', `users`.`updated` as 'users.updated' FROM  `users` LEFT JOIN `sites` ON `sites`.`id` = `users`.`site`  LEFT JOIN `user_dept` ON `users`.`id` = `user_dept`.`user_id`  LEFT JOIN `dept` ON `user_dept`.`dept_id` = `dept`.`id` WHERE `users`.`title` = `:title` ",
            "bindings": [
                {
                    "name": ":title",
                    "value": "Mr",
                    "type": null
                }
            ]
        }
    ]
}

allan

Thinking about it, the simplest form to get you going is going to be:

    $q->where('users.title', $_SESSION['myparam']);

That will correctly bind the value to protect against injection attacks.

That should get you going. However, I agree, I'll look into the bindings there and resolve the underlying issue.

Allan

lucianoluna

Thanks Allan