MySQL error "right syntax to use near 'update"

Question

I am getting this error Unhandled Exception.

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'update, desc, indir, time) VALUES(?, ?, ?, ?, ?, ?, ?, 1392556810)' at line 1

whenever i submit the form. Here is source code

if(!is_admin())
{
header("Location: $config->url");
exit;
}
$cid = intval($_GET['id']);
if($_GET['act'] == 'add')
{
$db->bind(id,$cid);
$file = $db->row("SELECT * FROM `".PREFIX."category` WHERE `id`=:id");
if(!$file)
{
$file = new stdClass();
$file->path = "/files";
}
if(!is_dir("..".$file->path))
{
header("Location: $config->url");
exit;
}
if($_POST['save'])
{
$name=$_POST['name'];
$icon=$_POST['icon'];
if($db->count("SELECT COUNT(id) FROM `".PREFIX."category` WHERE `path` = '".$file->path."/".$name."'") == 0)
{
$path=''.$file->path.'/'.$name.'';
$desc=$_POST['desc'];
if(isset($_POST['new']))
$new=1;
else
$new=0;
if(isset($_POST['update']))
$update=1;
else
$update=0;
$time=time();
$insert=$db->query("INSERT INTO ".PREFIX."category(name, path, icon, new, update, desc, indir, time) VALUES(:name, :path, :icon, :new, :update, :desc, :id, $time)", array("name"=>"$name","icon"=>"$icon","new"=>"$new","update"=>"$update","path"=>"$path","id"=>"$cid","desc"=>"$desc"));
if($insert>0)
{
mkdir("..".$file->path."/".$name,0777);
header("Location: $config->url/cat/".$cid."/'".htmli(converturl($file->name)).".html");
}
}
echo 'already exixs';
}
else
echo '<form method="post" action="#">Name : <input type="text" name="name" /><br/>Description : <input type="text" name="desc" /><br/>Icon : <input type="text" name="icon" /><br/><input type="radio" name="new" value="1" /> New<br/><input type="radio" name="update" value="1" /> Update<br/><br/><input type="submit" name="save" value="Add" /></form>';
}

Both `UPDATE` and `DESC` are [MySQL reserved keywords](http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html). You need to quote them with backticks to use them as column or table identifiers. See also [this question](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) for quoting examples. — Michael Berkowski, Feb 16 '14 at 13:36

score 2 · Answer 1 · answered Feb 16 '14 at 13:37

This is your insert statement:

INSERT INTO ".PREFIX."category(name, path, icon, new, update, desc, indir, time)
    VALUES(:name, :path, :icon, :new, :update, :desc, :id, $time)

Several of the column names are reserved words in MySQL. You need to escape them:

INSERT INTO ".PREFIX."category(name, path, icon, `new`, `update`, `desc`, indir, `time`)
    VALUES(:name, :path, :icon, :new, :update, :desc, :id, $time);

The list of reserved words is here.

In general, you should avoid using these words as identifiers. They are already used in MySQL for other purposes. But, if you have to use them, then you need to escape them each time. (Note: I also escaped "time". You'll note at the end of the list of reserved words are a handful that are allowed, because they have been so commonly used. But time is the name of both a type and function, so it should also be reserved.)

score 0 · Accepted Answer · answered Feb 16 '14 at 13:39

Check your query here

$insert=$db->query("INSERT INTO 
".PREFIX."category(name, path, icon, new, update, desc

Here update/desc/new are all keyword/reserveword; so you will have to quote it like

$insert=$db->query("INSERT INTO 
".PREFIX."category(name, path, icon, `new`, `update`, `desc`

MySQL error "right syntax to use near 'update"

2 Answers2