0

I am running a mysql query in my php strict where I have to insert the current date in a column, but when running, I am confronted with this error:

Error updating record: 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 '22:42:06 WHERE RequestorID = 1 AND RequesteeID = 5 AND StoryID = 16' at line 1

Here is the part of my code that is causing this issue:

$usrID = 5;
//The user that accepts
$rqstorID = 1;
//This requestor
$stryID = 16;
//The story ID
date_default_timezone_set('Asia/Beirut');
$date = date('Y-m-d H:i:s');
$sql1 = "UPDATE clearance SET ClearanceDate= $date WHERE RequestorID = $rqstorID AND RequesteeID = $usrID AND StoryID = $stryID";
//The mysql query causing the error

I can't seem to find the cause of this error as I checked the column names thoroughly and just for clarification ClearanceDate is of type datetime. Thanks in advance.

EDIT: Thank you for the helpful input from you all and for directing me to the similar question. I just put the $date variable in single quotes and it worked like a charm.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
plank223
  • 29
  • 6
  • ClearanceDate = ".$date." WHERE – clearshot66 Mar 30 '17 at 19:51
  • @clearshot66 - that shouldn't matter, as he is using double-quotes, rather than single-quotes. With double-quotes, PHP will automatically evaluate any variables contained within the string. – Obsidian Age Mar 30 '17 at 19:52
  • Ever heard about [SQL injection](https://en.wikipedia.org/wiki/SQL_injection)? – Alon Eitan Mar 30 '17 at 19:53
  • Queries still require quoted values if they are strings @ObsidianAge – Jay Blanchard Mar 30 '17 at 19:53
  • [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Mar 30 '17 at 19:54
  • Quick and dirty fix is to enclose `$date` in apostrophes. Proper fix is to use prepared statements. – Jirka Hrazdil Mar 30 '17 at 19:54
  • If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Mar 30 '17 at 19:55
  • 1
    As I stated in my answer getting downvoted... – clearshot66 Mar 30 '17 at 19:55
  • @AlonEitan Before you start freaking out "SQL injection" you have to ask yourself what is the **source** of the parameters used in the SQL statement . if the case is like the question only `$date` variable come from this statement `date('Y-m-d H:i:s');` you can concatenate it's value to the SQL statement safely, unless the php core function `date()` has been tampered with – Accountant م Mar 30 '17 at 20:05
  • @Accountantم If the user is executing queries the way they do in this example I can assume that it's the same in other places. I never run queries like the this - Not even if I can control the source. It's just not a good practice to begin with. (Side note - I doubt that it's always `$usrID = 5;` and `$rqstorID = 1;` - It look like a simplified example of their real code) – Alon Eitan Mar 30 '17 at 21:58
  • Oh, and they could just update rhe field to `NOW()` if you think about it ;) – Alon Eitan Mar 30 '17 at 22:09

0 Answers0