Mysql_real_escape_string () vs addslashes () vs addcslashes ()

Addslashes (): Don't call it a comebackstill number one for escaping SQL statements I 've seen a lot of people talking about mysql_real_escape_string () vs addslashes () vs addcslashes (). there seems to be a lot of real confusion about what these functions do (even with the php.net manual around), especially when it comes to character sets. I feel that some people are beingScaredInto using some escaping methods with which they are not very familiar. so, I 've decided to lay it all out in a few charts so there is no confusion about what each function does and how each can help protect against SQL injection attacks. The test As = addslashes () MREs = mysql_real_escape_string () ACS = addcslashes () // called with "\\\ 000 \ n \ R' \" \ 032% _" Feature As MREs ACS Escapes quote, double quote, and backslash Yes Yes Yes Escapes like modifiers: underscore, percent No No Yes Escapes with single quotes instead of backslash No Yes * 1 No Character-set aware No Yes * 2 No Prevents multi-byte attacks No Yes * 3 No Escape 7 strings 5000 times Secs 0.031 Secs 0.047 Secs 0.039 Escape 1 binary image 200 times Secs 0.514 Secs 1.091 Secs 0.552 * 1 MREs can escape with single quotes instead of backslashes if you set no_escape_backslash during your SQL session. Since this is strictly a MySQL function, its use is dubious. * 2 Although MREsCanBe Character Set aware, it requires that you explicitly set the character set of the string which needs to be escaped, a feature that was not available until PHP 5.2.3. * 3 Although MREs stops the intended SQL injection, it can result in database errors, which, if unexpected cocould leak more information to the attacker. it also requires you to properly set your character sets, a feature that was not available until PHP 5.2.3. As you can see, none of these functionsFullyProtect Against "Multi-Byte" injection attacks. I qualify this with "fully" because if you have mismatched character sets, MREs can present your attacker with an unexpected error message, or fail to work at all. Speed As far as speed goes, you can see that MREs can be up to twice as slow as or ACS. This is because the data to be escaped is sent toLibmysqlclientLibrary. While this has some benefits when dealing with outdated character sets, it presents a number of problems. Testability When grouping Ming unit tests (you write unit tests, don't you ?), If you are using MREs to escape your data, you are saddled with being required to have a MySQL server running. Hopefully the same version as the live server. too. In the eyes of a unit testing 'purist', requiring a running database might disqualify your test from being 'true' unit tests. I say this half-jokingly. but, it does raise the question of how isolated you can make your code and cocould stand in the way of making mock objects for your database layer. Multi byte breakdown Multi-byte character sets is where the entire SQL escaping debate really takes off. there is so much confusion about how to properly escape text for exotic (or you cocould call them "outdated") character sets. in the above comparison there is a line that reads "character-set aware", and only MREs has a "yes" for that column. if we're assuming that "character-set aware" is a good thing, this begs Question, "When do you need to be aware of your character set? "Answer, all the time. But, for SQL escaping, it turns out that you need to be Character Set aware only some of the time. In order to execute a multi-byte SQL injection the database tables must be created with a special character set and/or collation during table Creation Time,OrThe mysql client library must be already ucted to use a special character set during the connection. having a different table collation/character set other than the current character set of the SQL client (libmysqlclient for PHP) will result in a collation error even if the SQL is escaped properly by MREs. The client character set was set with mysql_set_charset () Client Character Set Table Character Set MREs vulnerable As vulnerable Utf8 Utf8 No No GBK Utf8 No (W/errors) Yes Utf8 GBK No No GBK GBK No Yes As you can see from this table, if your database is constructed only to deal with BGK (or sjis, or other vulnerable charset) And you only deal with that character set at connection time, then youNeedMREs to properly escape your data. Here is where PHP specific issues come to light. A lot of people have read that they shocould send the SQL commands "set names" or "set Character Set" to MySQL in order to change the running connection's character sets. while this is true, it doesn' t tell the whole story for PHP. PHP uses the libmysqlclient library to pass commands to MySQL either over a network or through a UNIX socket. this MySQL library has some utilities for clients wanting to use MySQL, one of these features is the function "mysql_real_escape_string ". unfortunately, for libmysqlclient, it cannot parse commands sent to MySQL, so it is blissfully unaware of any "set names" commands you might have sent to the server. As you can see in the graph above, any MySQLCommandsPass right through libmysqlclient, but the PHP functionMysql_real_escape_string ()Is actually part of the libmysqlclient C code and does not need to round trip to the server. (it does need to round trip "out" of the Zend memory space, which is why you see the slowness on large binary objects .) the "New-ish" PHP functionMysql_set_charset ()Is exactly what you need to instruct libmysqlclient to be aware of any desired character set. This shocould be usedIn conjunctionWith any "set names" commands so that the library and the server are in sync. Conclusion AsIs the most basic function. It has its limitations, but if you are aware of the character sets flying around you it shocould not pose any significant danger. SinceACSOr str_replace must be used for like-type queries, ACS shoshould be sufficient over MREs for all but the most unusual cases. if ACS cocould take an optional parameter to use a single quote or a backslash it wocould be the best SQL escaping tool, hands down, for any backend database. The moral of this story is, go ahead and keep usingAddslashes (). It's fast, and fast... and it works with UTF-8. If you have a SQL library, which knows when you're adding like or grant clses and parameters, useAddCSlashes ()Instead. What else do you need to know? People who say that you "shocould" be doing something this way or that (parameterized queries) shocould understand that * knowing * your data and being confident about a solution is more important than just reaching for the most comfy looking security blanket without understanding what's going on. remember, all data looks like binary data to a computer, it's only how you interpret it with charsets, plugins, protocols, etc. which makes data really data.