Update password problem

Updating a password in the administration panel causes the following SQL error:

Error: 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 'where user_id = '5'' at line 1
Error No: 1064
insert into user set password = 'ba352acae7a4b3532fb4f9ca26f79bbd' where user_id = '5'

This is caused by the following line in admin/plugin/user.php (50, 51, ...):

      if (@$_POST['password']) {
      $sql = "insert into user set password = '?' where user_id = '?'";
        $database->query($database->safe_query($sql, md5($_POST['password']), $_GET['user_id']));
      }

The generated SQL query should be:

$sql = "UPDATE user SET password = '?' WHERE user_id='?'";

And if you use prepared statements for the MySQL database, it is not necessary to use ' ' around the question marks.

I certainly do propose a clearer SQL syntax and a transparent seperation of SQL code and PHP code.

Best Regards,

Anders (iteration)
aj AT itersys DOT dk

I have Just fixed this problem on the dev server.

Please explain more about:

I certainly do propose a clearer SQL syntax and a transparent seperation of SQL code and PHP code.

I thought I had done this with whats in the code already.

maybe he's suggesting SELECT something FROM rather than select something from. Also the code is all on one line, perhaps break it across multiple lines. And as for the seperation comment... who knows, maybe he means using a sql class which dynamically builds the statement, ie $database->query("*","table","WHERE id=null").

The idea of using less code is better. For example with out queries I do the following

dbInsert(TABLE_NAME,$sql);
dbUpdate(TABLE_NAME,$sql,"field = '".$value."'");
dbDelete(TABLE_NAME,"field = '".$value."'");
dbSelect($sql_query,$optional); wjhere is $optional is not false it gets row rather then rows

The $sql is an arrary with the fields being the key and the values being the values. this allows the same array created for an insert as an update ie.

if($id){
dbUpdate(TABLE_NAME,$sql,"field = '".$id."'");
}else{
$id = dbInsert(TABLE_NAME,$sql);
}

Just my comments.. Saves so much time and I never have any sql errors as it's pretty much automated.