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).
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.
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
mysql_real_escape_string() is what every body should use i don't see reason why people should use others
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.
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.
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string