On Thu, Sep 15, 2011 at 00:56, Alex Nikitin <niksoft@xxxxxxxxx> wrote: > <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. > I agree with this point, except that MySQL does not parse any other (unicode) character as the single quote. > 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. > This has not been my experience. As for turning off the escape line, that is another argument for using the array that I demonstrated previously. > 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. > I just googled prepared statements in PHP and I see that they don't need to be pre-prepared in the database. I must have been conflating them with stored procedures. Thanks, I'll play around and possibly adopt the use of 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... > This is not good for searching the data afterwards. If I have a specific non-searchable field meant for code or such, then I do base64 encode. > 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 ;) > Change the access libraries for the devs? I think that you've gone a bit too far! -- 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
