|Think about Loose Coupling|
SQL Injection myths under DBI?by Andre_br (Pilgrim)
|on Apr 12, 2005 at 00:26 UTC||Need Help??|
update: please read the entire thread. Some ideas of this first post were in fact wrong, e.g the #2 assumption. The #1 really applies at this moment with the conditions I tested (see below), and, about the #2, as far as I could test, backslashing the backslashes makes it apply too. But this is just a speculation wich is being discussed in this thread.
Iīve been reading all the material I could get about SQL injection and I would like to share with you some controverse points Iīve found. Fortunately, it seems the question is much more simple than it is assumed through all the tutorials Iīve found. Of course I may be making things easier here by ignorance, but after all Iīve tested it really seems I may have a point.
My point is that there are many 'query injections' that simply cannot happen if youīre using the DBI module. As far as I was able to teorically and pratically investigate they are:
SQL Injection myth # 1 - evil extra query
From SQL Injection Attacks by Example, wrotte by Steve Friedls for non-DBI specific treatment of the question, you can see many examples, like: (in 3 lines: query, malicious filling and resulting query)
I was so scared with this possibility that I decided to test - with a test database, of course, I called it screwme - by simply doing this:
The result is: nothing happens! As you can read from DBI manpage, the do() and prepare() methods happen to assume a single statement (prepare: "Prepares a single statement for later execution by the database engine and returns a reference to a statement handle object." do: "Prepare and execute a single statement."), thus, our steemed cracker will read the following message (if you were outputing the errors to him, what youīd better not):
Note: an update. Iīm using mysql as DB and itīs right that we canīt rely, like jZed precisely pointed, that they wonīt implement multiple queries at the prepare() and do() methods, but this second query will still be impossible based on the point I explore next.
Query Injection myth # 2 - treating single quotes wonīt be enough
From many articles Iīve read, you were warned that erasing or escaping the single quote wasnīt enough, that you had a whole set of evil characters/expressions to worry about, like *, semicolons, /*, */, // etc. They say it you delete or escape the single quote, these still can harm you seriouslly. Can they?Those were my findings. Please confirm or refute them, my friends.I trust only thy wisdom to judge this matter.
Lets see an example. Iīve set up a .cgi to test what would be an attempt to list username and password of some userīs session id (in case this was badly coded right into the query string, and not in the cookie, as you can see the guys from http://www.secondspin.com did.). We can imagine this query is done to output an already filled form for the user to update his personal info, including changing his username and password.
Our fellow Mister-SQL-Injector would then type this in the form:
(5 or whatever, doesnīt matter as it wonīt match, no one is this crazy to put an incremental session id!)
The result is that he has managed to do a query like this (spaces to make it easy to visualize the magic heīs done):
Heīs just succeeded in selecting all the records of you table users and, the more important, even if you used encripted session id's with tens of characters (like our friends at secondspin). To see username and password of all the database - as the fetchrow_array() of this screen probably isnīt in a loop, because the developer didnīt expect many rows - he has just to use another trick, to see username and password one by one. We could try putting a LIMIT clause at the end like:
...but the problem is that we have to provide a pair for the closing single quote at sid='' and LIMIT does not take quotes, as it is a numeric argument mysql understands directly. The approach could, then, be our friend using his imagination and solving the 'problem ' by typing this:
In this case he would get the login info for the last user starting with b. Interessting, isnīt it? But how to avoid this? Do I have to be "PARANOID", as everyone says, and distrust about almost anything but letters, digits and spaces? (Oh, you too my fellow interrogation point!) Well, I think not, and this is my point in this second item - and please correct me if Iīm wrong in my assumptions.
Letīs take a look again at the syntax in wich we tell our DBI module the variables we want him to interpolate: (from our example)
The more important part , with spaces for we to see clearly:
The solution all the tutorials suggest is: use placeholders:
That is using the ? in the query, instead of '$input' and placing the variable in the execute method, where it cannot be used the evil way it can in the prepare() method. (this solution will cost you a hell of a work if you like the simplicity of the do() method and have used it all over your code).
But what I could conclude, after a lot of thinking and, most of all, after testing all the supposedly harmfull examples in my little script, is that the only problem is the single quote. Once you escape, or delete it, Perl and the DBI module will think that is the string for whatever language their strange human masters speak(I speak portuguese, so sorry for the english mistakes Iīve certainly made!).
So, when I say sid='$input - note that I havenīt closed the single quote yet -, Perl and DBI are asking each other, at each new character: "Hey, Perly, new character, is it an SQL command or is it just the value of this field going on?". Perl then tells DBI gently: "No, itīs the value, we havenīt seen any single quote yet, silly."
In other words, untill they donīt see a single quote, wich is the signal we used to open the variable in the example (I always do like that, and have always seen this syntax around), they just assume itīs the string. The danger about other characters is if the single quote has already came. Then, weīre screwd with many many characters, in fact. But, as long as no one can fill our queries but in the values spaces - and that is what we manage when we delete or escape the single quotes coming from the inputs - we can chill out.
So, if you delete or escape (wich I prefer, in respect to our friends at O'reilly) the single quote, you will get the same result than with the placeholders: mysql (or the database youīre using) looking for a sid with the following content (wich wonīt match never!).
5\' or 1=1 HAVING username<\'c
Perl and DBI: "What the fuck! They went mad. Ok, nevermind, run the query, DBI.". The funny thing is that our steemed negleted cracker wonīt even see an error message (it it's set, and stuff, like we talked). This was a valid value, and the search resulted in just nothing. And, for nothing, our scripts always know what to do: a gentle message to the user. (or to the cracker! better not to curse him, despite all the work weīre having.)
To escape, it is so simple as:
Cheers from Brazil.
Edit by tye: Add READMORE tags