Escaping single quote in PHP when inserting into MySQL

You should do something like this to help you debug

$sql = "insert into blah values ('$myVar')";
echo $sql;

You will probably find that the single quote is escaped with a backslash in the working query. This might have been done automatically by PHP via the magic_quotes_gpc setting, or maybe you did it yourself in some other part of the code (addslashes and stripslashes might be functions to look for).

See Magic Quotes

You should be escaping each of these strings (in both snippets) with mysql_real_escape_string().

http://us3.php.net/mysql-real-escape-string

The reason your two queries are behaving differently is likely because you have magic_quotes_gpc turned on (which you should know is a bad idea). This means that strings gathered from $_GET, $_POST and $_COOKIES are escaped for you (i.e., "O'Brien" -> "O\'Brien").

Once you store the data, and subsequently retrieve it again, the string you get back from the database will not be automatically escaped for you. You'll get back "O'Brien". So, you will need to pass it through mysql_real_escape_string().

For anyone finding this solution in 2015 and moving forward...

The mysql_real_escape_string() function is deprecated as of PHP 5.5.0.

See: php.net

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

mysqli_real_escape_string()

PDO::quote()