0

There is an SQL query I am writing which executes fine when written as below, but it updates all rows.

mysqli_query($connection, 'UPDATE users SET hashed_pass = ' . mysql_escape($newpassword).'');

However, as soon as I attempt to add some logic to it by adding a WHERE statement, I don't get any errors but it is not updating the password for the intended user. I want the statement to update, in this case, user 569 (which is what $user_id is equal to).

mysqli_query($connection, 'UPDATE users SET hashed_pass = ' . mysql_escape($newpassword).' WHERE user_id ='.mysql_escape($user_id).'');

The mysql_escape function just strips slashes and escapes the variable so I know that is not the cause. I also made sure that the table does in fact have the user_id column and the variable $user_id is 569, which matches the user I want to update. After I run the command I don't get any errors and I have an echo statement under the query to confirm that it ran.

Any ideas what I am doing wrong?

Update: As everyone suggested I, converted my code to a prepared statement and it is now working.

$stmt = mysqli_prepare($connection,  "UPDATE users SET hashed_pass = ? WHERE user_id = ?");                             
mysqli_stmt_bind_param($stmt, "si", $newpassword, $user_id);
mysqli_stmt_execute($stmt);
Karim Aljandali
  • 138
  • 9
  • 2
    "what I am doing wrong". That's easy. You are munging query strings with literal values when you should be using parameters. – Gordon Linoff Feb 08 '19 at 15:22
  • what happens if you alter the code to `mysqli_query($connection, "UPDATE users SET hashed_pass = '" . mysql_escape($newpassword)."' WHERE user_id ='".mysql_escape($user_id)."'");` Also, note that concatenation like this is frowned upon for security purposes. – TheMouseMaster Feb 08 '19 at 15:26
  • 1
    You're mixing APIs. You can't use mysql_* functions with mysqli. Skip the escape string and use [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). – aynber Feb 08 '19 at 15:29
  • Thanks for all of your replies, I tried your solution @TheMouseMaster and unfortunately it didn't work, I will revise the code to be a prepared statement as Gordon and aynber suggested. – Karim Aljandali Feb 08 '19 at 15:37

1 Answers1

1

Use prepared statements would be the first step. PHP mysqli_prepare

$stmt = mysqli_prepare($connection, "UPDATE users SET hashed_pass = ?")

mysqli_stmt_bind_param($stmt, "s", $citynewpassword);

mysqli_stmt_execute($stmt);
Jason K
  • 1,406
  • 1
  • 12
  • 15