The topic of the difference between addslashes() and mysql_escape_string() has come up a few times as of late, and I found myself actually having to look up the answer. I knew (as I hope you do) that the mysql_ version was better (mysql_real_escape_string() being better still), but I couldn't remember exactly why. In my research I found a bunch of miscellaneous blog posts that simply agreed with my understanding that mysql_escape_string() > addslashes(). Finally I found a post at NyPHP (of which I am a member) which described it in a bit more detail, listing some of the meta-characters that MySQL understands, but it didn't go into detail on exactly which characters were escaped. A chart, indicating my findings comparing addslashes to mysql_escape_string (all three sources of information are shown). I will consider the source and my test definitive (results after the jump).
    addslashes() mysql_escape_string()
Ascii Name testing testing source
0 Null      
8 Backspace      
9 Tab      
10 \n      
13 \r      
26 Substitute      
34 "      
39 '      
92 \      

So in conclusion: Use The Source! and you should use mysql_escape_string() over addslashes() to escape three additional characters, newline, carriage return, and substitute character.
Notes:
My test consisted of iterating through the ascii character chart, and comparing chr($i) to mysql_escape_string(chr($i)) then reporting if they didn't match (as would happen when something is escaped. Reading the source was looking at escape_string_for_mysql() in charset.c (part of mysql 5.0.45), and mysql_sub_escape_string() in libmysql.c shipped with the PHP 4 branch. The NyPHP document indicates that mysql_escape_string() and mysql_real_escape_string() all escape those same characters, it may be true that the real version escapes more, I intend to test more in the future.

Note^2
You shouldn't actually be using mysql_escape_string() you should be using mysql_real_escape_string() which takes the character set into account.

Comments »

No Trackbacks
Character encoding matters:

http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string
#1 Chris Shiflett (Homepage) on 2007-09-23 04:52 (Reply)

mysql_real_escape_string() is what every body should use i don't see reason why people should use others
#2 blackman (Homepage) on 2007-11-21 05:17 (Reply)

i get an error when i use the mysql_escape_string function in my perl script to import data from an xml data file into a mysql database.
i use this function to get rid of the error i get whenever i come across an apostrophe in the xml data file.

Undefined subroutine &main::mysql_escape_string

please help.
#3 sunz on 2007-12-03 20:56 (Reply)

I've been looking for this - thanks!
#4 Kamil (Homepage) on 2008-03-01 23:24 (Reply)


Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
 

Hi, I’m Paul Reinheimer, a developer working on the web.

I co-founded WonderProxy which provides access to over 200 proxies around the world to enable testing of geoip sensitive applications. We've since expanded to offer more granular tooling through Where's it Up

My hobbies are cycling, photography, travel, and engaging Allison Moore in intelligent discourse. I frequently write about PHP and other related technologies.

Search