MySQL update syntax error

Question

I'm using php to run a basic update on a database but I'm getting SQL syntax errors and I can't for the life of me see what's wrong. The field in the db is called orders, it is set to int(8).I have tried changing it to smallint and varchar, have increased but no joy. I have also tried tinkering with the ' and " in the query. The code is as follows...

    $query = "UPDATE gigs SET order='$order' WHERE gig_id='$id'";
    if ($r = mysql_query($query) ) {
        echo  "<p>UPDATED:" . $row['year'] ." - " . $row['month'] . " - " .  $row['day'] . " = $order</p>";
    } else {
        echo "<p>" . mysql_error() . "<br />$q</p>";
    }

$order is year month and day values combined into one integer value, e.g. 12 apr 2013 = 20130412.

here are the error messages I get

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 'order='20040802' WHERE gig_id='10'' at line 1 UPDATE gigs SET order='20040802' WHERE gig_id='10'

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 'order='20040804' WHERE gig_id='11'' at line 1 UPDATE gigs SET order='20040804' WHERE gig_id='11'

possible duplicate of [How can I write SQL for a table that shares the same name as a protected keyword in MySql?](http://stackoverflow.com/questions/10706920/how-can-i-write-sql-for-a-table-that-shares-the-same-name-as-a-protected-keyword) — Jocelyn, May 12 '13 at 01:24

score 5 · Accepted Answer · edited May 23 '17 at 11:49

5

ORDER is a reserved keyword. In order for the statement not to throw an exception, you need to escape the column with backtick,

UPDATE gigs SET `order` = '$order' WHERE gig_id = '$id'

MySQL Reserved Keywords List

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 11:49

Community

1
1

answered Apr 12 '13 at 17:49

John Woo

258,903
69
498
492

2

You took the words right out of my mouth. – Samuel Cook Apr 12 '13 at 17:49
1

Also probably `gid_id` is numeric - so `WHERE gig_id = $id` is probably required. – Ed Heal Apr 12 '13 at 17:51
@EdHeal I agree with you but I'm not sure about the data type so I leave it as is. – John Woo Apr 12 '13 at 17:52
1

@JW - Just added it as a comment to flag it as another possible problem. – Ed Heal Apr 12 '13 at 17:56
Thanks guys, that's great, all updated now :-) – Shane Apr 12 '13 at 17:58

score 1 · Answer 2 · answered Apr 12 '13 at 18:03

@EdHeal If it's numeric MySQL doesn't care if it's quoted or not. If I know it's numeric I won't quote it, but I've created dynamic queries many times in which case I just quote everything. Of course an even better option would be to use PDO and bound parameters ;)

MySQL update syntax error

2 Answers2