mysql_real_escape_string quote problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

mysql_real_escape_string quote problem

Post by Sindarin »

So I am doing a small research about SQL injection and I found that mysql_real_escape_string() helps a lot for it, but there's an issue with it.
What if the text that the user inputs is like,
This was a really "nice" party. Get it?
In that case that will display:

This was a really \"nice\" party. Get it?
which looks wrong. Isn't there a way to allow the user using single and double quotes in his input but protecting from injection at the same time?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: mysql_real_escape_string quote problem

Post by andyhoneycutt »

I may be incorrect, but I figured those went into the database as literals, so they would be input as " and '.

-Andy
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: mysql_real_escape_string quote problem

Post by Sindarin »

I may be incorrect, but I figured those went into the database as literals, so they would be input as " and '.
what? how come they display with the backslash in them if this is true?

I also wonder. Can't sql injection be prevented with,

$mystr = str_replace("'","",$mystr); since sql uses single quotes for variables instead of doing the whole mysql_real_escape_string thing?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: mysql_real_escape_string quote problem

Post by andyhoneycutt »

ah, true. for some reason i thought they inserted as literals. lovely.

when i use your method on this text: blah " blah " 'blah' jane said,
this is the output: blah " blah " lah jane said

so the only problem i see right off doing it your way would be if the server has the ever LOVELY :( magic-quotes turned on.

-Andy
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: mysql_real_escape_string quote problem

Post by Sindarin »

get_magic_quotes_gpc() returned 1 so I guess they're on.

Well I have found a fun way to do this with str_replace. One good way was with html characters like ' for apostrophe, but once more IE disappointed me, so I have to replace ' with apostrophes myself just for IE's sake, oh well...

There are no sql queries below that point so I think it's safe enough.
Post Reply