Contrib.social

Array placeholder error in where condition of update query "Invalid parameter number: parameter was not defined"

Open on Drupal.org →
Created on 22 February 2023, over 1 year ago
Updated 27 March 2024, 3 months ago

Problem/Motivation

After update from 9.2 to 9.5 an error appeared in application:
"SQLSTATE[HY093]: Invalid parameter number: parameter was not defined."

Steps to reproduce

- Install Drupal, set name "admin" for the user.
- Run the code (I do it with drush)

$query = \Drupal::database()->update("users_field_data");
// Its not important what to update.
$query->expression("name", "LOWER(name)");
// The error related to where condition. I know that it could be done with ::condition(), its just an example.
$query->where("name IN (:names[])", [":names[]" => ["admin", "user_1"]]);
// Updated count.
dump($query->execute());

- The next error happens:

In ExceptionHandler.php line 53:
                                                                                                                          
  SQLSTATE[HY093]: Invalid parameter number: parameter was not defined: UPDATE "users_field_data" SET "name"=LOWER(name)  
  WHERE (name IN (:names"")); Array                                                                                       
  (                                                                                                                       
      [:names[]] => Array                                                                                                 
          (                                                                                                               
              [0] => admin                                                                                                
              [1] => user_1                                                                                               
          )                                                                                                               
                                                                                                                          
  )                                                                                                                       
                                                                                                                          

In StatementWrapper.php line 145:
                                                                        
  SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Notes

I didn't found changes breaked it, but looks like arguments aren't expanded in this case before query.

Other cases

a) If use arguments without "[]", there is no error "Placeholders must have a trailing [] if they are to be expanded with an array of values.", the query executes, but does nothing (0 affected strings dumped, no changes in DB)

$query->where("name IN (:names)", [":names" => ["admin", "user_1"]]);

b) Select query works, other classes haven't been tested.

$query = \Drupal::database()->select("users_field_data", "u")
  ->fields("u", ["uid"]);
$query->where("name IN (:names[])", [":names[]" => ["admin", "user_1"]]);
dump($query->execute()->fetchAll());

array:1 [
  0 => {
    "uid": "1"
  }
]
🐛 Bug report
Status

Active

Version

11.0 🔥

Component
Database  →

Last updated about 14 hours ago

  • Maintained by
  • 🇳🇱Netherlands @daffie
Created by

🇧🇾Belarus dewalt

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Issue created by @dewalt
  • Comment 4 months ago →
    🇮🇳India himanshu_jhaloya Mandsaur

    It seems like you're encountering an issue with parameter expansion in your where condition when using an array placeholder (:names[]) in your update query. This might be due to a change in behavior between Drupal 9.2 and 9.5. To resolve this, you can try a different approach to handle the array of values in the where condition.

    One possible solution is to use the condition() method instead of directly specifying the where condition. Here's how you can modify your code to use condition():

    $query = \Drupal::database()->update("users_field_data");
    $query->expression("name", "LOWER(name)");

    // Use condition() to specify the WHERE clause with an array of names.
    $query->condition('name', ['admin', 'user_1'], 'IN');

    // Execute the query.
    $result = $query->execute();

    This way, you explicitly specify the IN condition and provide the array of names directly to the condition() method. This should avoid the issue you're encountering with parameter expansion.

  • Comment 3 months ago →
    🇧🇾Belarus dewalt

    Thanks @himanshu_jhaloya, but "::condition()" methods doesn't support MySQL expressions, simple query is provided as example only. For a cases when expressions are used in conditions looks like only "::where()" method works, e.g. "WHERE BINARY value = IN (:arg[])" or for binary (case-sensitive) columns "WHERE LOWER(value) IN (:args[])", and many other possible expressions.

contrib.social Blog FAQ Discussions
Production build 0.69.0 2024