MySQL Error: You have an error in your SQL syntax

Question

I get this error message from mySQL:

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 'key,time) 
VALUES ('FreeTest','86400')' at line 2

Here is the code:

if ((isset($_POST['key'])) && (isset($_POST['days']))) {

  $key = mysql_escape_string($_POST['key']);
  $days = mysql_escape_string($_POST['days'] * 86400);

  $add = "INSERT INTO licence
  (key,time)
  VALUES
  ('$key','$days')";

  $addkey = mysql_query($add);
}

You can't use mysql keyword `key` as a bare column name for a mysql table. You could escape them with back tics (aka Grave accents), but that is extremely tacky. Just don't use these words: https://dev.mysql.com/doc/refman/4.1/en/reserved-words.html — Eric Leschinski, Apr 17 '13 at 16:48

score 6 · Accepted Answer · edited May 23 '17 at 10:32

The column named KEY, which is one of the column names, happens to be a reserved keyword, you need to escape with backticks so you won't get syntax error, TIME is also a reserved keyowrd but mysql permits it to be used without backticks.

INSERT INTO licence (`key`,time) VALUES ('$key','$days')

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?

MySQL Error: You have an error in your SQL syntax

1 Answers1