Getting SQL ' invalid input syntax for integer' error on registration

I believe the problem is the quotes around the argument for IN(). Maybe MySQL will strip the quotes, but PostgreSQL won't. Attached is a patch that works in PostgreSQL.

Is there any problem with using the above patch?

Which issue does this duplicate(or, to restate, which issue is still left open that this problem is being handled on)?

I'm opening this back up because:

1. The patches I'm supplying are for 6.x, and #427638: Error checking for send-to-role is for 7.x
2. The technique I'm using is only available for 6.x. I'm not yet sure what the equivalent is for 7.x
3. While I suspect this may fix some other send-to-role problems, I'm not sure it will fix all of the problems covered in #427638: Error checking for send-to-role. I do suspect, though, that this may be needed as a prerequisite for fixing those other problems.

The problem, as I originally reported it, is that building the parameters for the IN function is done incorrectly:

$roles = implode(',', variable_get('user_register_notify_roles', array())
if (!empty($roles)) {
  $result = db_query("SELECT mail FROM {users} AS u INNER JOIN {users_roles} AS r ON u.uid = r.uid WHERE r.rid IN('%s')", $roles);
  while ($mail = db_fetch_object($result)) {
      $emails[] = $mail->mail;
  }
}

IN expects a variable number of parameters. This code builds a string of the parameters in $node, and passes them as a %s placeholder. Since the %s is surrounded by single quotes, it is actually seen as a single, string parameter. PostgreSqL objects to this because r.rid is an integer and therefore it is expecting integer parameters. MySQL apparently does not complain, but I suspect it isn't doing what you expect (we're a PostgreSQL shop and don't deal with MySQL much). I suspect that MySQL compares r.rid to the string, the comparison fails, and no users are selected.

My original solution was to strip the quotes from around the %s. This works, but leaves a potential SQL Injection vulnerability. The correct way to do this is to leave $roles as an array and use db_placeholders:

$roles =  variable_get('user_register_notify_roles', array();
if (!empty($roles)) {
  $result = db_query("SELECT mail FROM {users} AS u 
                                  INNER JOIN {users_roles} AS r ON u.uid = r.uid 
                                  WHERE r.rid IN(" . db_placeholders($roles, 'int') . ")", $roles);
  while ($mail = db_fetch_object($result)) {
    $emails[] = $mail->mail;
  }
}

This works in D6, but db_placeholders has been dropped in D7. I haven't yet dug into what the equivalent is for D7.

Attached are two patches, one for 6.x-1.11 (in case anyone wants to apply this to an installed 6.x-1.11), and one for 6.x-1.x-dev(which is slightly changed from 1.11).

'WHERE r.rid IN(:role)' is valid syntax for Drupal 7, not Drupal 6, and this patch is for the Drupal 6 version (the equivalent Drupal 7 patch is in #1228914: Notify based on role not working (patch attached) and has been accepted). The db_placeholder function is how this is done in Drupal 6.

Looks like this was committed while I was typing my last message.

Running Matthew Bonner's change does not work. On my test site, using the current git version (which includes that change) I get this error:

warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near ":" LINE 1: ... users_roles AS r ON u.uid = r.uid WHERE r.rid IN(:role) and... ^ in /home/testsite/public_html/includes/database.pgsql.inc on line 138.
user warning: query: SELECT mail FROM users AS u INNER JOIN users_roles AS r ON u.uid = r.uid WHERE r.rid IN(:role) and u.status = '1' in /home/testsite/public_html/sites/all/modules/user_register_notify/user_register_notify.module on line 67.
Unable to send e-mail. Please contact the site administrator if the problem persists.

The 'WHERE r.rid IN(:role)' type of placeholder syntax was introduced in D7, and does not work in D6. As you can see above, the ":role" text is not replaced, and the SQL server rejects it as invalid syntax. For D6, the db_placeholders function is the proper way to handle this.

Attached is a patch against the repository 6.x-1.x version which will correct this to use db_placeholders. Note that it is also necessary to remove the implode call from the creation of $roles, as db_query is expecting an array of roles, not a comma-separated string of roles.