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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

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

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Escape it.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

Thanks I was using addslashes() and for some reason it wasn't working. Thanks!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

What does this say?

Code: Select all

echo "Magic Quotes GPC is turned " . (get_magic_quotes_gpc()) ? 'ON' : 'OFF';
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

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