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.