addslashes() vs mysql_real_escape_string()...the final debate

[aamonkey]

addslashes() and mysql_real_escape_string() are an equal solution to cleansing data before it's inserted in a database.

There, I've said it.

I've done quite a bit of web searching to prove this point to myself, but I'm very curious about why people swear by mysql_real_escape_string(). The only things people seem to say about mysql_real_escape_string() is that it's safer...and somehow they always fail to mention why it's safer.

Character encoding.....ok--I don't quite grasp it all. Just show me any kind of sql injection that gets by addslashes() but not mysql_real_escape_string().

[triexa]

aw crap this bugs me. haha i remember seeing an example of just that, which is why i switched to the *escape_string()

and now, i can't remember where I saw it and I can't seem to find it

[MrOrange]

Well, mysql_real_escape_string doesn't protect against sql injections more than addslashes, but that's not the reason you use it. addslashes() was from the developers of PHP whereas mysql_real_escape_string uses the underlying MySQL C++ API (i.e. from the developers of MySQL). mysql_real_escape_string escapes EOF chars, quotes, backslashes, carriage returns, nulls, and line feeds. There is also the charset aspect.

However, it is a common thought among a lot of PHP programmers (beginning and even more advanced) that SQL injections are the only thing to guard against with sanitizing user input using it in a query. That, actually, is incorrect. If you only rely on *_escape_string and addslashes because you are only thinking about injections, you leave yourself vulnerable to attacks from users.

MySQL has some good tips to PHP programmers in their documentation that is, sadly, no where to be found in PHP's documentation (that I know of, as I've read almost, if not all, and PHP's mysql documentation).

http://dev.mysql.com/tech-resources/...curity-ch3.pdf . It's a nice read, especially if you like reading articles about PHP programming (*guilty*). Scroll down to page 78 where they talk about LIKE attacks.

If you aren't one for reading, they use the following illustration of something that neither mysql_real_escape_string or addslashes protects against:

$sub = mysql_real_escape_string("%something"); // still %something

mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'"); 

And recommends the following:

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");

// $sub == \%something\_

mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'"); 

Also, read the section above the section on LIKE: No Means of Escape.

Hope that helps some,
Jonathan

[aamonkey]

Thanks for the response, MrOrange,
a couple thoughts:

Originally Posted by MrOrange

However, it is a common thought among a lot of PHP programmers (beginning and even more advanced) that SQL injections are the only thing to guard against with sanitizing user input using it in a query.

I don't mean to suggest that addslashes() or mysql_real_escape_string() are all you should use to sanitize input. I'm strictly interested in the claim that mysql_real_escape_string() is safer.

Originally Posted by MrOrange

addslashes() was from the developers of PHP whereas mysql_real_escape_string uses the underlying MySQL C++ API (i.e. from the developers of MySQL). mysql_real_escape_string escapes EOF chars, quotes, backslashes, carriage returns, nulls, and line feeds. There is also the charset aspect.

So how does that translate into mysql_real_escape_string() being safer than addslashes() ? Why would it matter if mysql or php developers wrote a function? And maybe I'm just being dense, but I still don't understand how escaping these extra characters makes a query any safer....

Any thoughts?

and thanks for the link--there's some very interesting material in there

[MrOrange]

I guess I didn't quite articulate my thought in my original post.

addslashes() and mysql_real_escape_string() are both as effective at preventing most SQL injections -- although, not completely effective as one would have to count the % and _ in LIKE clauses as injections, which neither function protects against. They both protect to the same degree. The mysql_real_escape_string function does not protect against SQL injections anymore than addslashes().

I still don't understand how escaping these extra characters makes a query any safer

You don't use mysql_real_escape_string for additional security. You use it to escape special characters correctly. Illustration:

$string = "\$variable has a value of $variable."; 

The reason is like the above. You don't escape the first variable for security reasons, you escape it so that it will display and format correctly.

addslashes escapes: ', ", \, and NUL
mysql_real_escape_string escapes, in addition: \x00, \n, \r, and \x1a.

Why would it matter if mysql or php developers wrote a function?

It was just an interesting note. mysql_real_escape_string was designed by the MySQL people for the purposes of inserting data into a database. It utilizes MySQL's library function for escaping certain characters.

I'm strictly interested in the claim that mysql_real_escape_string() is safer.

So, no, the are both equally as safe (or unsafe) at preventing injections. However, someone could make the argument that mysql_real_escape_string is safer at protecting the integrity of data as the PHP manual reads that "If binary data is to be inserted, this function must be used." Of course, it's just a side point.

I hope that clears up any confusion.

[aamonkey]

Originally Posted by MrOrange

The mysql_real_escape_string function does not protect against SQL injections anymore than addslashes()

If I had read your initial post more closely I would have realized that is what your were saying. This is the same conclusion that I have reached, yet there seems to be a 'myth' circulating in forums that mysql_real_escape_string is somehow magically safer....Thanks again for the response (and for the confirmation)

[shiflett]

Originally Posted by aamonkey

addslashes() and mysql_real_escape_string() are an equal solution to cleansing data before it's inserted in a database.

Wrong.

Note: I assume you mean escaping, not cleansing. Cleansing is an informal alias of filtering.

The important issue is that mysql_real_escape_string() is more appropriate for escaping data used in a MySQL query. Although I've explained this several times here, it hasn't helped. I decided to blog about it in a bit more detail and provide a specific example (e.g., code) that demonstrates an SQL injection attack that is immune to addslashes():

http://shiflett.org/archive/184

Please link to this anytime someone tries to debate this again. :-)

[mudkicker]

shiftlett,

Thanks for your blog post. I read it 5 mins ago.

So, you say that mysql_real_escape_string is more efficient by SQL injections?

...and is it still good to use addslashes()?

just to clear my mind

thank you

[shiflett]

I've never benchmarked addslashes() and mysql_real_escape_string() to see which is fastest. The point is that addslashes() can be insufficient for protecting against SQL injection when you're using MySQL.

As Andi mentions in the comments, using bound parameters still offers the strongest protection against SQL injection, but I wanted to focus on this particular debate.

Hope that helps. :-)

[aamonkey]

Hey Chris,
thanks for the information--really interesting post.
My question is: Is the exploit you mentioned only an issue if you're using the GBK character set in mysql? That was my understanding, but just wanted to clarify.
Thanks,
aamonkey

[shiflett]

It's an issue for many multi-byte character sets, specifically those with a valid character ending in 0x5c (a backslash in ISO-8859-1), because addslashes() can be tricked into adding that byte, creating a valid multi-byte character instead of escaping the single quote.

UTF-8 is immune to this attack, because all of its multi-byte characters specifically avoid bytes with a value less than 128 - this is because the first two bits are 10 in every byte except the first.

Hope that helps.

[tylerfeetcore]

Question about the level of escaping w/magic_quotes_gpc turned on.

Does anyone know if if magic_quotes_gpc = 1 accomplishes the same escaping as addslashes(), or is it more encompassing like mysql_real_escape_string() ?

Been curious about this one for a while but could not get the answer through net search or anything.

[clamcrusher]

Originally Posted by tylerfeetcore

Question about the level of escaping w/magic_quotes_gpc turned on.

Does anyone know if if magic_quotes_gpc = 1 accomplishes the same escaping as addslashes(), or is it more encompassing like mysql_real_escape_string() ?

Been curious about this one for a while but could not get the answer through net search or anything.

http://www.php.net/magic_quotes

[Tiffany Hua]

Originally Posted by clamcrusher

http://www.php.net/magic_quotes

I have totally read the content on this page but still feel confused about this. Further, it seems that "addslashes" is better?

[triexa]

Originally Posted by Tiffany Hua

I have totally read the content on this page but still feel confused about this. Further, it seems that "addslashes" is better?

I didn't read the articles, but I searched google for "mysql_real_escape_string vs addslashes" and got some good results.

the title says it all
another title says it all
...and more...

Did you happen to search google before (or even after) posting your question? I can only assume you didn't because then your question would have been answered?

[Grumps]

strip_tags() is the best stripper for html related tags.

[Tiffany Hua]

Originally Posted by triexa

I didn't read the articles, but I searched google for "mysql_real_escape_string vs addslashes" and got some good results.

the title says it all
another title says it all
...and more...

Did you happen to search google before (or even after) posting your question? I can only assume you didn't because then your question would have been answered?

I didn't search after posting, but now I got it.

[bangkok_knight]

I would recommend using the escape_string() much simpler.

[kyberfabrikken]

Originally Posted by bangkok_knight

I would recommend using the escape_string() much simpler.

There is no such function.

[felgall]

Originally Posted by kyberfabrikken

There is no such function.

It is a reference to mysql_real_escape_string() and the equivalent for other databases.

[Sasha Smaili]

thanks for the tip!

[jetmuzer]

felgall absolutly right, it's just reference

[barcajunior]

I think mysql_real_escape_string() is safe enough.

[Donniep]

Thanks for the link MrOrange that is a great read and I wish I had read it years ago.

[butlimous]

Thanks for the EXTREMELY useful link! I have been searching for it for a long time