When updating user email on Drupal, I got this error:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'P.', email='testing01@mysite.com', lastactivity='1167903564' WHERE username' at line 1 query: UPDATE user SET password='8866263b6f3e7f5686003548122b4817', passworddate='2007-01-04 04:39:24', salt=''P.', email='testing01@mysite.com', lastactivity='1167903564' WHERE username='Staff' in /data/web/cms.mysite.com/includes/database.mysql.inc on line 120.

Is this a quick fix in the syntax?

Comments

rszrama’s picture

rszrama commented

Hmm... Yes, it's a quick fix, I just don't see that it's wrong in the code. The problem here is that "P.' is incorrect. It should be 'P.' <-- note the quotes are the same, both single instead of a " and a '.

When I look in drupalvb.inc.php around line 185 I see that it's set correctly:

$setpass = "password='$passhash', passworddate='$passdate', salt='$salt', ";

Can you verify which version of the code you're using and what that line looks like in your file?

rszrama’s picture

rszrama commented

Hmm.. sorry, for some reason the second half of my follow up was cut off. : (

What I said was that in my code, file drupalvb.inc.php, I have the code properly written on around line 185 as:

$setpass = "password='$passhash', passworddate='$passdate', salt='$salt', ";

Can you verify what version you're using and what that line of code looks like for you?

GC_Chi’s picture

GC_Chi commented

Not sure exactly because I didn't install the module (I'll find out), but here's the whole thing from drupalvb.inc.php:

//Function to update an entry in the vB database user table
function _drupalvb_update_vb_user($username, $password, $email, $salt){
  //Set up the necessary variables
  $setpass = '';
  if($password != ''){
    $passhash = md5($password . $salt);
    $passdate = date('Y-m-d H:i:s', time());
    $setpass = "password='$passhash', passworddate='$passdate', salt='$salt', ";
  }
  $lastdate = time();
  
  //Set up the insertion query
  db_query("UPDATE user SET " . $setpass . "email='$email', lastactivity='$lastdate' "
         . "WHERE username='$username'");

  return;
}
rszrama’s picture

rszrama commented

The code looks fine there... I did notice that those are two ' not a " in your error. Somehow an extra ' got inserted... I wonder... I bet it's actually part of the salt. I think what needs to happen is actually a check to make sure that the salt doesn't include a ' or that it's at least escaped.

Thanks for bringing it to my attention!

rszrama’s picture

rszrama commented
Assigned: Unassigned » rszrama
Status: Active » Fixed

Ok... the deal is with users created by Drupal, I had it only selecting alphanumeric digits to create the password salt. The vB salt can sometimes contain problematic characters, like your single quote (') issue. This would've been avoided if I'd known how to properly use the database abstraction layer when I first coded Drupal vB. (This was one of my first modules. hehe)

So... I rewrote that part to use it correctly. It should escape the salt properly so that problem won't happen again. Thanks for bringing it to my attention! I'm going to rewrite other parts while I'm at it. ^_^

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)