Strange (?) results of mysql_real_escape_string

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
klubbit
Forum Newbie
Posts: 3
Joined: Sat Oct 31, 2009 12:31 pm

Strange (?) results of mysql_real_escape_string

Post by klubbit »

I'm curious if this is what mysql_real_escape_string is supposed to do. It seems to escape backslashes correctly, but it escapes an apostrophe by adding a second apostrophe before it, and it doesn't escape double quotes at all. e.g., if I enter "\x00, \n, \r, \, ', " and \x1a" into my form, the database stores "\\x00, \\n, \\r, \\, '', " and \\x1a".

Also, am I correct in assuming that an escaped character only counts as one character towards the max? For example, a varchar(5) field can store \'\'\'\'\' (10 characters), in which case a line break would count as 2 characters towards the max.

Thanks.
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Re: Strange (?) results of mysql_real_escape_string

Post by Skara »

php.net wrote:mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.
Escaping a character means adding a backslash. It shouldn't be adding second quotes.

As for the second question, \' is a single character. I've actually never tried that out, but I can't imagine it filling up two spaces.
klubbit
Forum Newbie
Posts: 3
Joined: Sat Oct 31, 2009 12:31 pm

Re: Strange (?) results of mysql_real_escape_string

Post by klubbit »

Yeah, that's what I thought it was supposed to do. I did some more testing and found out that mysql_real_escape_string is functioning correctly. It's MySQL that changes \' to '' and \" to " -- even if I run a query through phpMyAdmin. I assume it's still safe from injections though since \" is what gets sent to MySQL. I also assume this is the standard behavior for MySQL since I'm using WAMP with default settings. If this is the case, it seems one would be susceptible to a previously attempted SQL injection if they run a query that pulls the injection from the database, then use that value in a second SQL query without escaping it. Right? :?
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Re: Strange (?) results of mysql_real_escape_string

Post by Skara »

klubbit wrote:It's MySQL that changes \' to '' and \" to " -- even if I run a query through phpMyAdmin.
That doesn't sound right to me. The second one sounds great, remember that \" will always display as " (since that is, in fact, what it is). \" is generated by \\\".
Therefore, \' should appear as ', not ''. That seems really awkward to me.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Strange (?) results of mysql_real_escape_string

Post by John Cartwright »

Sounds like you have register globals and should be disabled (check the manual for why).
klubbit
Forum Newbie
Posts: 3
Joined: Sat Oct 31, 2009 12:31 pm

Re: Strange (?) results of mysql_real_escape_string

Post by klubbit »

Skara wrote:
klubbit wrote:It's MySQL that changes \' to '' and \" to " -- even if I run a query through phpMyAdmin.
That doesn't sound right to me. The second one sounds great, remember that \" will always display as " (since that is, in fact, what it is). \" is generated by \\\".
Therefore, \' should appear as ', not ''. That seems really awkward to me.
Hmm, I think maybe all this confusion is due to me misunderstanding what's stored in the database. I thought the database literally stored \", \', \r, \n, etc., but it sounds like it stores each one as a single character (", ',
) [that's a line break ;)]. This is what I saw when I looked at the database in phpMyAdmin, but I thought the default view was just that way for readability. When you export the database it shows everything as plain text using SQL commands, which is where is was seeing ", '', \r, and \n. Two single quotes is an escaped single quote when the SQL command is surrounded by single quotes. Same thing if you replace "single quotes" with "double quotes" in that sentence. See http://dev.mysql.com/doc/refman/5.0/en/ ... yntax.html

So it's also true that an escaped character only counts as one character towards the max, with a line break counting as 2 (\r\n). I did some testing to verify this.

Also, I have register_globals off, thanks.
Post Reply