<rant from="tired of constantly having to explain it, developer"> MySQL real escape string doesn't work, it's a bad solution to the problem that has been with the internets since the very beginning, and if people program like they are taught to by books, doesn't look like it's going away any time soon. The problem of course is that various programming languages don't know how to talk to other languages, and we as devs see no better way to do this then concatenate strings. Basically this is the core reason why XSS and SQL injection is rampant on the interwebs. Escaping only seems like it's a good idea to you, but if you analyze what it does and compare it to today's technology, you quickly realize how wrong of a concept it actually is. Escaping looks for certain characters, and if found escapes them in some form. The problem here is that rather then say defining all safe characters, it defines what the developers believe to be bad characters, and the affect that you get is not dissimilar to creating a firewall rule set where the bottom rule is accept all, as long as my character doesn't match what they thought was a bad character, it is allowed. This was fine in the days of ASCII, but the tubes are hardly ASCII anymore, with Unicode, UTF-16, i have 1,112,064 code points, they are not even called characters anymore, because they really aren't. And if you are familiar with best-fit mapping, you would know that there are now dozens of characters that can represent any single symbol in ASCII, meaning that using the above type of blocking mechanisms is silly and technically insecure. Another problem with it is the fact that security-wise this again is a bad solution from another perspective. A programmer comes in, and starts debugging code, the first thing they always seem to do is to turn off the security and comment out the escape line, and you know what happens, the bug gets found and fixed completely else-where, but the security never gets re-enabled. This is called failing open, and it again goes with the concept above where the escape in itself fails open as well. So if you look into the problem at the core, what you have are two types of code, code that you know is good, and crap data that you have to somehow make safe. So you know how you do it in the same language? Right, you assign that data to a storage container called a variable, and the interpreter knows that this data here, i execute, and that data there i use as data and don't execute. Well what happens when you add another language into the mix? Well language a passes known good code that it string concatenates to bad code, and what you get as a result is the second language parser thinking "hey, all of this stuff is good code, let me execute it!"... This is why a stringent delimiter between known good and not good data needs to be portrayed to the second language. How do we do it with SQL? There are a few ways, one of the more common ones is to use a prepared statement, this clearly separates the code from the data for the SQL interpreter on the other side. This works really well, with one HUGE down-side, it can be a REAL pain in the butt to use, the more complex your query gets, the more pain in the butt it is to use prepared statements. Another way, and this works for mostly any language is to use an in-common function that jumbles the known-bad data on one end, and unjumbles it as data on the other. For example base64. It works extremely well, you take any data on the PHP side, base 64 encode it, and send it to SQL or JS or whatever. you can string concatenate the b64'd data, because you know what b64'd data looks like? Yep, data, its not JS, it's not SQL, bunch of garbled junk. You can then use b64decode on that data, and by the design of the function the result will be just that, data. So with this you keep the code/data separation even with string concatenation... Base 64 performs really well, and is well worth the few extra cycles for the above-mentioned guaranteed code/data separation barrier, it's easy to implement. More importantly, this by default fails closed. You would have to disable at least 4 security points and change 2 queries to disable this (and if you are using a stored procedure this is even harder), and that's beyond what you want to do during troubleshooting usually, and if you disable one point, your application fails to work all together and it fails closed. More over you can make this completely transparent to your devs by changing your data access libraries (for SQL, or Ajax functions for JS for example). They can pass in crap data, and the first thing your data access library does before doing anything else is it encodes the data into a bunch of gibberish... And when they pull the data back, your library gets the data and unencodes it. the devs don't have to worry about SQL injection, you don't have to worry about their competence, you win ;) </rant> sources: Dan Kaminsky - HOPE keynote - http://dankaminsky.com/interpolique/ Mike Samuel - Secure string interpolation in JS- http://google-caja.googlecode.com/svn/changes/mikesamuel/string-interpolation-29-Jan-2008/trunk/src/js/com/google/caja/interp/index.html -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray On Wed, Sep 14, 2011 at 5:02 PM, Dotan Cohen <dotancohen@xxxxxxxxx> wrote: > On Wed, Sep 14, 2011 at 16:02, Eric Butera <eric.butera@xxxxxxxxx> wrote: >> Just out of curiosity, where are these ids coming from? Doing a raw >> implode on them like that is a sql injection vuln. >> > > They are in an array. I do of course is_int() them first, plus some > other sanitation including mysql_real_escape_string(). > > -- > Dotan Cohen > > http://gibberish.co.il > http://what-is-what.com > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
