Mysql + php with special characters like '(Apostrophe) and " (Quotation mark)

Question

I have been struggling with a small problem for a while. It's been there for years but it's just been an irritating problem and not a serious one, and I have just worked around it. But now I want to find out if anyone can help me. I have done some google'ing but no success.

If I do a form post from a html textarea in a php file like this:

<form action="http://action.com" method="post">
<textarea name="text"><a href="http://google.com">google's site</a></textarea>
</form>

and of course there is a submit button and so on.

The value is the problem: <a href="http://google.com">google's site</a> The value of the textarea have both "(Quotation mark) and '(Apostrophe).

To save this in a mysql_database I do this:

$result = mysql_query("INSERT INTO `table` (`row1`) VALUES ('".$_POST['text']."') ") or die(mysql_error());

And now I get the mysql 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 's site'' at line 1

Answer 1

Your sql string will be:

INSERT INTO `table` (`row1`) VALUES ('google's site')

Which is not a valid statement. As Nanne wrote, escape the string at least with mysql_real_escape_string : http://php.net/manual/en/function.mysql-real-escape-string.php

And read about sql injection http://en.wikipedia.org/wiki/SQL_injection

Think a bit: if someone posts this: $_POST['text'] with value: ');delete from table;....

Your can say good bye to your data :)

Always filter/escape input!

EDIT: As of PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. Please use mysqli extension and mysqli::escape_string function instead