PHP + MySQL Error

Question

Hello, here's the code:

if ($_POST) {$content = stripslashes($_POST['content']);
$by = $_SESSION['exp_user']['username'];
$dt = date("F j, Y, g:i a"); 
mysql_query("UPDATE tbl_intmsg SET time = ".$dt.", by = ".$by." AND content = ".$content."") or die(mysql_error());

For which I recieve error:

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 '26, 2009, 2:28 pm, by = shamil.nunhuck AND content = Test message! ' at line 1

Any ideas?

Answer 1

Ah scrap that, here's the answer: $db = mysql_query("UPDATE tbl_intmsg SET time = '".$dt."', by = '".$by."', content = '".$content."'");

Answer 2

put SQL quotes around your string variables:

"UPDATE tbl_intmsg SET time = \"".$dt."\", by = \"".$by."\" AND content = \"".$content."\"

Answer 3

You might consider sanitizing your input with the intended function as well:

mysql_real_escape_string()

See: http://uk3.php.net/manual/en/function.mysql-real-escape-string.php

Answer 4

Yeah your SQL looks like this when it is being read by the MySQL server:

UPDATE tbl_intmsg SET time = 26, 2009, 2:28 pm, by = shamil.nunhuck 
    AND content = Test message!

How is MySQL to figure out what is what in that statement?

Imagine, for instance, that you set your $content to '0 WHERE dummy = true'. Then your SQL would look like this:

UPDATE tbl_intmsg SET time = 26, 2009, 2:28 pm, by = shamil.nunhuck 
    AND content = 0 WHERE dummy = true

Do you see the problem? How is SQL to figure out what is your data, and what is SQL keywords?

The answer is: quotes!

Your SQL should look like this:

UPDATE tbl_intmsg SET time = '26, 2009, 2:28 pm', by = 'shamil.nunhuck' 
    AND content = 'Test message!'

Your PHP SQL-query string could look like this:

$q="UPDATE tbl_intmsg SET time = '$dt', by = '$by' AND content = '$content'";

(Note that PHP parses double-quote strings for variables and replaces them with their value. Also note that you should use the above mentioned function

mysql_real_escape_string($var)

to sanitize your data before putting it into MySQL. Here's the link that mechler couldn't post: php.net/mysql-real-escape-string)

Cheers!

/0