MySQL Update from PHP form

Question

As a novice MySQL user I tried to insert, but I just read on the MySQL documentation that you can only insert on blank rows. My UPDATE statement needs work though, and I'm not sure that I have the syntax correct.

$query3 = "UPDATE `offices` SET `scash`="$total" WHERE `officename`="$office"";

offices is the table name. scash is the row to be updated. $total is a variable pulled from a post. $office is a variable pulled from the same database. I only want to set scash to total where the officename is $office.

Parse error: syntax error, unexpected T_VARIABLE is the error I'm getting.

Please learn how to use [proper SQL escaping](http://bobby-tables.com/php) before you hurt yourself. This query is **extremely** dangerous. — tadman, Oct 22 '12 at 17:42

score 2 · Accepted Answer · edited May 23 '17 at 10:24

2

$query3 = "UPDATE `offices` SET `scash`='$total' WHERE `officename`='$office'";

Replace the double quotes with normal quotes in the string since double quotes are string delimiters and can't be used in the string.

And as Marc B mentioned your code might be vurnerable for SQL injections. See this post how you can avoid that.

edited May 23 '17 at 10:24

Community

1
1

answered Oct 22 '12 at 17:26

juergen d

201,996
37
293
362

4

and for the average php novice, better mention something about [SQL injection](http://bobby-tables.com) – Marc B Oct 22 '12 at 17:28
I really appreciate your efforts. I don't know what I'd do without you guys. – smada Oct 22 '12 at 17:42
The best way to say thanks is to learn PDO or `mysqli` so you don't fall into this trap again in the future. – tadman Oct 22 '12 at 17:44
I'm on my way to learning mysqli, unfortunately, I'm under a time crunch right now, so this is the route I have to take. I can update my code as of next week. – smada Oct 22 '12 at 17:59

score 1 · Answer 2 · answered Oct 22 '12 at 17:27

1

You are going wrong at quotes

$query3 = "UPDATE `offices` SET `scash`="$total" WHERE `officename`='$office'";

Also always use LIMIT 1 if you want to update just a single row...

And sanitize your inputs before updating your row, atleast use mysqli_real_escape_string()

answered Oct 22 '12 at 17:27

Mr. Alien

153,751
34
298
278

Using `LIMIT 1` on an `UPDATE` is bad advice. Your `WHERE` clause should be more specific if you're having limit issues. – tadman Oct 22 '12 at 17:42
Yes, there is harm. It makes no sense, for one, and secondly leads to a false sense of security. Randomly updating one row is crazy. – tadman Oct 22 '12 at 17:44
1

The problem with `LIMIT 1` is you don't get to say *what* row. MySQL will just pick one randomly for you, which leads to unpredictable behavior. – tadman Oct 22 '12 at 17:49
Additionally, if you're using `mysqli` and somehow involve `mysqli_real_escape_string` in your code you're doing it wrong. Please use placeholders for all data escaping. – tadman Oct 22 '12 at 17:50
The `WHERE` clause doesn't have a limit, the `UPDATE` does. It's generally wrong to do this. You really don't have a leg to stand on here. – tadman Oct 22 '12 at 17:52

score 1 · Answer 3 · answered Oct 22 '12 at 17:48

1

if you still want to use double quotes inside double quotes escape it..

your query can be modified as follows..

$query3 = "UPDATE `offices` SET `scash`=\"$total\" WHERE `officename`=\"$office\"";

answered Oct 22 '12 at 17:48

Sony Mathew

2,929
2
22
29

MySQL Update from PHP form

3 Answers3