SQL error when adding a third user in GRANT table

Created on 16 September 2016, about 9 years ago
Updated 25 September 2025, 11 days ago

NodeAccess use db_query for everything, but it can fails when SQL contains a IN / NOT IN clause.
Here's the current code performing a user search :

    // Perform search.
    if ($form_values['keys']) {
      // @todo rewrite
      $params = array();
      $sql = "SELECT uid, name FROM {users} WHERE name LIKE :name";
      $name = preg_replace('!\*+!', '%', $form_values['keys']);
      $params[':name'] = $name;
      $users = '';
      if (isset($form_values['uid']) && is_array($form_values['uid'])) {
        $sql .= ' AND uid NOT IN (:uid)';
        $users = implode(',', array_keys($form_values['uid']));
        $params[':uid'] = $users;
      }

      $result = db_query($sql, $params);
      foreach ($result as $account) {

        $form_values['uid'][$account->uid] = array(
          'name' => $account->name,
          'keep' => 0,
        );
      }
    }

If there're already 2 users in the GRANT table (uid 1, 2), this is the final query:

SELECT uid, name FROM {users} WHERE name LIKE 'xxx' 
AND uid NOT IN ('1, 2')

This will make the query to fail, and prevents you to add a third user.
Instead, we need to use the db_select function, to get a well-formed NOT IN clause:

    // Perform search.
    if ($form_values['keys']) {
      $name = preg_replace('!\*+!', '%', $form_values['keys']);
      if (isset($form_values['uid']) && is_array($form_values['uid'])) {
        $result = db_select('users', 'u')
            ->fields('u', array('uid', 'name'))
            ->condition('name', $name, 'LIKE')
            ->condition('uid', array_keys($form_values['uid']), 'NOT IN')
            ->execute();
      } else {
        $result = db_select('users', 'u')
            ->fields('u', array('uid', 'name'))
            ->condition('name', $name, 'LIKE')
            ->execute();
      }

      foreach ($result as $account) {
        $form_values['uid'][$account->uid] = array(
          'name' => $account->name,
          'keep' => 0,
        );
      }
    }
🐛 Bug report
Status

Postponed: needs info

Version

1.0

Component

Code

Created by

🇫🇷France zessx

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.

No activities found.

Production build 0.71.5 2024