Do UPDATE statements work with WHERE?

Question

I wrote some code for my login and registration system. In my registration system, I have included this code:

<?php 
    $sql3="UPDATE users SET 
            firstname='$firstname', lastname='$lastname',
            password='$password',bio='$bio',
            dob='$dob',gender='$gender' 
?>

But of course, this just updates all rows in the users table. But when I add:

<?php WHERE username=$username ?>

I will get an error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1.

Let me be clear, this is NOT an error question, I know what the problem is. I just want to know how to troubleshoot my code so that it doesnt update the whole table instead of just one users row. Thank you in advance.

Just put the `WHERE` statement at the end of the first query, — Mark, Sep 23 '17 at 16:50
Please add a block of code that makes sense as a section. This currently does not..... make any sense that is — RiggsFolly, Sep 23 '17 at 16:54
Try adding quotes around the value as its a text value like `WHERE username='$username'` — RiggsFolly, Sep 23 '17 at 16:55
Thank you soooo much RiggsFolly!!! Your answer was correct. Thanks again, God bless you! — Khadija Maria Musa, Sep 23 '17 at 16:59
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Sep 23 '17 at 17:21

unnamed · Accepted Answer · 2017-09-23T17:21:51.540

-1

Yes, they do work. The WHERE clause will actually help you to update specific row(s) otherwise, it will update all the rows of the table.

 UPDATE TABLE_NAME SET COLUMN_NAME=<VALUE> WHERE COLUMN_NAME=<VALUE>;

So, putting identifier column like username in WHERE clause will avoid updating all rows.

Something like using prepared statement

 UPDATE users 
 SET firstname=?, lastname=?,password=?,bio=?, dob=?, gender=? 
 where username=?

edited Sep 23 '17 at 17:21

answered Sep 23 '17 at 17:08

unnamed

74
10

Not really an answer to the question – RiggsFolly Sep 23 '17 at 17:15
@RiggsFolly I hope, now it is ? :D – unnamed Sep 23 '17 at 17:19
And of course you code sample is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Sep 23 '17 at 17:20
@RiggsFolly Correct I realized later. So, corrected :) – unnamed Sep 23 '17 at 17:23

Do UPDATE statements work with WHERE?

1 Answers1