SQL Error when using apostrophes

Question

I'm getting the following error whenever I try to post something with an apostrophe in it:

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...

For example when I'm trying to post/using INSERT something like "I'm working hard".It's getting me an error. If I write "I am working hard" everything is fiine.

The code is:

 $sql="INSERT INTO tb_table (`postcontent`, `userid`, `posttime`) VALUES ('".$_POST[content]."', '".$user_id."', '".time()."')";

Any ideas how to fix it?

Answer 1

The quotes in the value being inserted are closing the quotes that surround the value in the INSERT statement because you're using dynamically generated SQL.

Best solution is to use parameterised SQL which will prevent this problem and be safer (guard against SQL injection). Otherwise, you need to parse/make safe the supplied values - e.g. escape all single quotes so they don't break the statement.

Answer 2

That's because you are using apostrophes to show MySQL where each value for the field starts and ends. If you put an apostrophe in the middle of the string, suddenly the database thinks that you're trying to put in four values in three table fields, or some such thing.

It looks like you're using PHP to insert data in the database so I'll give you a couple of examples of dealing with this with the means that PHP provides.

A quick way to fix it to use mysql_real_escape_string():

$sql="INSERT INTO tb_table (`postcontent`, `userid`, `posttime`) 
         VALUES ('" . mysql_real_escape_string($_POST['content']) . "',
                 '" . mysql_real_escape_string($user_id) . "', 
                 ".time().")";

A better approach would be to use prepared statements:

$db = // your mysqli() connection

$sql="INSERT INTO tb_table (`postcontent`, `userid`, `posttime`) 
             VALUES (?, ?, ?)";

if ($stmt = $db->prepare($sql)) 
{
   $stmt->bind_param("ssi", $_POST['content'], $user_id, time());
   $stmt->execute();           
   $stmt->close();
}

P.S. You don't need single quotes around time() - this is a number, it's safe to insert as is.

Answer 3

If this is in any way accessible to the public, take it down NOW, then go and read up on SQL injection attacks. The best fix is to use parameterised queries, but you must use some kind of escaping mechanism, because the quotes in your text are being interpreted as part of the SQL command, not as part of the text.

Consider what would happen if you submitted the comment:

', 'No-one', time()); DROP TABLE tb_table; //

Your SQL:

 $sql="INSERT INTO tb_table (`postcontent`, `userid`, `posttime`) VALUES ('".$_POST[content]."', '".$user_id."', '".time()."')"

Then expands to the string:

 INSERT INTO tb_table (`postcontent`, `userid`, `posttime`) VALUES ('', 'No-one', now()); DROP TABLE tb_table; //', 'user', 'time')"