Page 1 of 1

MySQL Select with a single quote in a string...

Posted: Sat Jun 23, 2007 9:51 pm
by tecktalkcm0391
I have this....

Code: Select all

$string = "Chris'";
$result = mysql_query("SELECT * FROM `data` WHERE `value` = '".$string."'");
because the string contains a single quote, its not working. How can I fix it to get Chris' from the database?

Posted: Sat Jun 23, 2007 9:53 pm
by Benjamin

Posted: Sat Jun 23, 2007 9:58 pm
by superdezign
Escape it.

Posted: Sat Jun 23, 2007 10:01 pm
by tecktalkcm0391
Thanks I was using addslashes() and for some reason it wasn't working. Thanks!

Posted: Sat Jun 23, 2007 10:07 pm
by Benjamin
What does this say?

Code: Select all

echo "Magic Quotes GPC is turned " . (get_magic_quotes_gpc()) ? 'ON' : 'OFF';

Posted: Sat Jun 23, 2007 10:15 pm
by superdezign
astions wrote:What does this say?

Code: Select all

echo "Magic Quotes GPC is turned " . (get_magic_quotes_gpc()) ? 'ON' : 'OFF';
If they were on, then it would have gone into the database from just one apostrophe.

Posted: Sat Jun 23, 2007 10:35 pm
by Benjamin
Ya think?

Code: Select all

SELECT * FROM `data` WHERE `value` = 'Chris\\''
MySQL wrote: #1064 - 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 ''Chris\\'' at line 1

Posted: Sat Jun 23, 2007 11:25 pm
by John Cartwright
You should always pass your data either through mysql_real_escape_string(), or intval() -- or equivalent integeter functions -- to avoid SQL injection

Posted: Sat Jun 23, 2007 11:38 pm
by superdezign
astions wrote:Ya think?

Code: Select all

SELECT * FROM `data` WHERE `value` = 'Chris\\''
MySQL wrote: #1064 - 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 ''Chris\\'' at line 1
I was referring to when he didn't use addslashes (in the example). Magic quotes exists as a method of preventing SQL injections for those who don't know how to do it themselves.

Posted: Sun Jun 24, 2007 12:06 am
by Benjamin
superdezign wrote:I was referring to when he didn't use addslashes (in the example). Magic quotes exists as a method of preventing SQL injections for those who don't know how to do it themselves.
He was using addslashes() per his comment about already escaping the variable. This combined with the fact that get_magic_quotes_gpc() is most likely turned on, resulted in the variable being escaped twice, effectively unescaping the single quote, which caused the query to fail.