SQL Injection myths under DBI?

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.

Dear Monks,

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)

SELECT email, passwd, login_id, full_name FROM members WHERE email = '
+$email'; 
x'; UPDATE members SET email = 'steve@unixwiz.net' WHERE email = 'bob@
+example.com
SELECT email, passwd, login_id, full_name FROM members WHERE email = '
+x'; UPDATE members SET email = 'steve@unixwiz.net' WHERE email = 'bob
+@example.com';
[download]

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:

my $sth = $dbh->do ("select * from users; drop database screwme;" );
[download]

and this:

my $sth = $dbh->prepare ("select * from users; drop database screwme;"
+ );
$sth->execute();
[download]

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):

DBD::mysql::db do failed: You have an error in your SQL syntax. Check 
+the manual that corresponds to your MySQL server version for the righ
+t syntax to use near '; drop database screwme' at line 1...
[download]

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?

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.


# ...load the DBI and connect to your database first

my $input = "3"; # we´ll type our hacks right here inside, to save the
+ work of doing a form. Makes no difference to what we´re testing.

my $sth = $dbh->prepare ("select username, password from users where s
+id='$input';" );
                            
my $affected = $sth->execute();
   
print $affected;
   
while ( my @row = $sth->fetchrow_array() ) { print join("-",@row) , "<
+br>"; } # instead of the form, we´re lazy, aren´t we? That´s good!
[download]

Our fellow Mister-SQL-Injector would then type this in the form:

5' or 1='1
[download]

(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):

select username, password from users where sid='   5' or 1='1  ';
[download]

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:

5' or 1=1 LIMIT 1,'1
[download]

...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:

5' or 1=1 HAVING username<'c
[download]

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)

my $sth = $dbh->prepare ("select username, password from users where s
+id='$input';" );
[download]

The more important part , with spaces for we to see clearly:

sid= ' $input '
[download]

The solution all the tutorials suggest is: use placeholders:

my $sth = $dbh->prepare ("select username, password from users where s
+id='?';" );
                            
my $affected = $sth->execute ( $input );
[download]

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:

$input =~ /'/\\'/g;
[download]

Those were my findings. Please confirm or refute them, my friends.I trust only thy wisdom to judge this matter.

Cheers from Brazil.

André

Edit by tye: Add READMORE tags

Comment on SQL Injection myths under DBI? Select or Download Code

Replies are listed 'Best First'.
Re: SQL Injection myths under DBI by perrin (Chancellor) on Apr 12, 2005 at 03:11 UTC
You just went to an awful lot of trouble to avoid using bind variables. Why bother? Bind variables are easy and improve performance under many databases. Just use them.	[reply]
Re^2: SQL Injection myths under DBI by Thilosophy (Curate) on Apr 12, 2005 at 03:18 UTC
++ Perrin. Let me just say that again: Bind variables are easy and improve performance under many databases. Just use them.	[reply]
Re: Discussion - SQL Injection under DBI by jZed (Prior) on Apr 12, 2005 at 00:40 UTC
You didn't mention what DBD and DBMS you are using. The fact that you can't get some of those tricks to work with your combination of DBD/DBMS doesn't mean it's safe for others. Some simple tests and "thinking about" it are a good way to start, but they're not sufficient to "debunk" what you call myths. Regardless of what you may think, placeholders are strongly recommended for security. While DBI currently has no native support for multiple queries, that support may be coming and it's possible for DBDs to implement it whether or not DBI does.	[reply]
Re: SQL Injection myths under DBI by tilly (Archbishop) on Apr 12, 2005 at 04:59 UTC
I consider this abysmally stupid advice and am moderately depressed that people have actually voted you up for it. First of all you decided that you'd only worry about a '. But the substiution that you provide is beatable by having \'. Now you substitute it into \\', which is read as \ followed by an unescaped '. Oops. Just to pick another random item you overlooked, what about a null byte? If the driver interfaces with a C library, many will terminate what they think is the string when they see a null byte. So someone can break out of a string, insert code, and terminate the SQL statement prematurely. Not a good idea. In any case when it comes to security it is not enough to hope that things will work out OK. You have to be paranoid. Just because you can't see the exploit doesn't mean that it isn't there. Pre-emptively remove the possibility of being exploited and maybe that will be enough. Then a new class of exploits get discovered and you can get all worried again.	[reply]
Re^2: SQL Injection myths under DBI by itub (Priest) on Apr 12, 2005 at 13:59 UTC
I upvoted him even if I don't entirely agree because I think this is a good debate to be had. I tried some of this stuff myself a few months ago, and was surprised that the typical sample SQL injection attacks you see advertised didn't work (on my database and dbd) because it didn't execute multiple statements. What may be different is that, even after noticing that I decided to keep using placeholders, for the same reasons others have cited in this thread. In other words, I think it's good that the OP tried to figure out how things work, but I think one should be wary of extrapolating this too far and reach the conclusion that SQL injection is "impossible".	[reply]
Re^3: SQL Injection myths under DBI by tilly (Archbishop) on Apr 12, 2005 at 19:07 UTC
While I agree that it is good to try these things for yourself, I objected to his very wrong conclusion.	[reply]
Re^2: SQL Injection myths under DBI by Andre_br (Pilgrim) on Apr 12, 2005 at 05:45 UTC
My dear tilly, Please take a look at my last post, regarding the point you mentioned (that was already mentioned 2 times by other friends of ours). Regarding null-byte, please explain how can I receive this through CGI. Regarding the judgement you´ve made, "abysmally stupid advice", please, my friend, this is not the place to be this rude. This is not behaviour for a monk! André	[reply]
Re^3: SQL Injection myths under DBI by tilly (Archbishop) on Apr 12, 2005 at 18:44 UTC
It has already been pointed out that null bytes can be passed through CGI. This is an idea that has been successfully applied to attacking CGI scripts that make shell commands. There are other tricks to use as well. For instance rather than try to get multiple SQL statements in there, you can put subqueries in. Like this: `\' or exists (delete from tblusers) or --` [download] with -- attempting to use a more traditional comment to hide the closing '. It is likely that you'd need to try several variants of this to get something that would work, and it is possible that your database is immune. That doesn't mean that depending on that immunity is OK though, because you may continue to think that you are immune when you switch to a different database. In IIS with common configuration mistakes, for instance, a subquery like this can launch a remote shell giving you direct access to the database machine. Furthermore a later rev of your current database may add features that are currently missing - and you are no longer immune. As for the perceived rudeness, I see two good options for you. The first is to regard me as a friend who cares enough to tell you honestly when you are making a terrible mistake. The second is to regard me as a not-friend who is warning other people that he might care about about how awful your advice is because he doesn't want to see them get hurt. I'm willing to make either option become true. In any case I didn't say that you are abysmally stupid, just that your advice was. And it really was, you are approaching security from exactly the wrong direction. Rather than say, "I couldn't figure out how to break this, I must be OK" you need to say, "I can guarantee that this is correct." Because even if you can't figure out the trick needed to make the attack work, that is no guarantee that some attacker out there who does this all of the time (rather than just takes a day or 2 to try to get it to work) won't know some trick that you didn't think of to get this to work. Open source provides a good demonstration. There is no shortage of cases where good, experienced programmers have looked at a programming mistake in open source code and proclaimed, "OK, this is bad but I don't think it is exploitable" only to find that shortly afterwards someone has figured out how to exploit it and the exploit is in the wild. In short, if you catch yourself thinking, "Despite knowing that this is supposed to be bad, I think that this is OK because I don't see how an attacker would attack it" you're on the wrong path. You are opening yourself up to a game of wits that you might lose and is at best a draw for you. Why play that game? If you can easily guarantee safety, there is every reason to do so and no reason not to.	[reply] [d/l]
Re^3: SQL Injection myths under DBI by Joost (Canon) on Apr 12, 2005 at 14:15 UTC
Regarding null-byte, please explain how can I receive this through CGI. Easy: `scipt.cgi?name=admin'%00` [download] Regarding the judgement you´ve made, "abysmally stupid advice", please, my friend, this is not the place to be this rude. This is not behaviour for a monk! Well, I think it is stupid advice :-) I'm not trying to be rude, and I'm not saying that you are stupid. You're quite right in questioning something that you see as cargo-cult programming. It's just that you are advocating ignoring something that is potentially dangerous without knowing the full scale of the problem. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re^3: SQL Injection myths under DBI by Jaap (Curate) on Apr 12, 2005 at 09:46 UTC
Insults aside, tilly makes a good point here. You conclude that ONLY the ' is a problem because you could not find a problem with any other character/sequence. Now if anyone else finds a problem with any other character you are screwed. Generally speaking, it is better to ALLOW characters that you know are safe (whitelisting) than to DISALLOW characters that you know are unsafe.	[reply]
Re: SQL Injection myths under DBI by dbwiz (Curate) on Apr 12, 2005 at 07:32 UTC
I second tilly's opinion that what you are saying is plainly wrong, and here are a few points that you shoudl consider: `my $sth = $dbh->prepare ("select username, password from users where sid='?';" )` [download] That is simply wrong. Placeholders must not be quoted. A placeholder means "leave it to the database driver to deal with any quoting business." 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 What's wrong with using the do() method with placeholders? `$dbh->do("INSERT INTO table (id, name) values (?, ?)", undef, $id, $na +me);` [download] 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 That depends on the database you are using, and you should not be concerned about this matter, if you care to use the `$dbh->quote` method, which you should go and read in the docs. The bottom line: I am afraid you don't know what you are talking about. Please read the docs more carefully.	[reply] [d/l] [select]
Re: SQL Injection myths under DBI by ikegami (Patriarch) on Apr 12, 2005 at 01:09 UTC
As far I can tell, you tested using only one DBD. You didn't even say which. Since everything you've covered is DBD-specific, what applies to the DBD you used doesn't necessarily apply to another. Why not use bindings or the provided quoting function instead of trying to figure out what else might work. You're only opening yourself to SQL injection attacks if something change in the database or in your configuration.	[reply]
Re: SQL Injection myths under DBI by Eimi Metamorphoumai (Deacon) on Apr 12, 2005 at 02:18 UTC
What about backslash? `$input = "\\'";` [download] Now your code is going to insert a backslash in front of the ', but my backslash will eat it up, leaving my ' out there to do its dirty work. Can that actually be exploited? I honestly haven't taken the time to work out how, but the fact that it took me all of 30 seconds to find something you overlooked leads me to suspect there might be other problems. Is escaping just backslashes and single quotes enough? Maybe. My big question is, why not just use the quote function the SQL library provides? It's way, way more likely to have caught anything you, personally, have overlooked, and done it in a nice and portable, easy to use way.	[reply] [d/l]
Re: SQL Injection myths under DBI by jhourcle (Prior) on Apr 12, 2005 at 03:30 UTC
I agree -- sometimes, people are more paranoid than they need to be. They quote rules that might be be years or even decades out of date. They get to be like religious customs, where it might have once served a purpose, but no one knows why they do it any more. (eg, scavengers ate rotten food, and might carry disease ... but pork doesn't have nearly the same problems these days). I know that I've seen the semicolon not allowed bit for quite a few years, but I don't know if it was a function of DBI, or DBD::Oracle (which I know was when I noticed the issue ... about 4-5 years ago (~ DBI 1.14?)). I would still argue for testing for tainted data, and using bind variables, even with the knowledge that these particular items aren't a problem, as it's just a good practice. It's possible that the underlying modules might change (I hope by accident, and not intentionally allowing semicolons and other such bad characters), but it's better for when you're not in an environment with such hand holding. For instance, a true story from my college days -- I worked helpdesk for the general university computer labs. We ran a stock Solaris 2.5.1 system. The engineering school ran Solaris, but they had added some 'convenience' tweaks, such as aliasing 'rm' to 'rm -i'. This resulted in lots of people typing 'rm *' and expecting prompting for which files they wanted to remove... but well, when they got to our system, there'd be no prompting, and they'd delete everything. So, the moral of the story is -- don't depend on someone else letting you be sloppy. It doesn't take that much effort to check what you're passing in, and in most cases, the impact if something did go wrong, even if it's a low overall risk, just isn't worth saving a few keystrokes for.	[reply]
Re^2: SQL Injection myths under DBI by Andre_br (Pilgrim) on Apr 12, 2005 at 05:34 UTC
Hello folks, Thanks for the comments. In fact, this reveals much more like a matter of faith than reason. I agree that if there is a solution that is consensus, maybe it´s not necessary to explore alternatives that may pay their price for the heresy. But, for we to continue our challenge of investigating if such a simple approach can wipe away the Sql injection fear, let me go on with the polemic. Please don´t take me bad, this can be a challenge for us. Even if is to prove that in fact, the consensus is right - but then we´ll at least tried to be heretic! As Aimi pointed, what if we had this: `$input = "\\'"; # these two backslashes will print a single backslash +in the SQL, but, as I will add one backslash before the single quote, + the result there will be just the same as you can see here.` [download] The move Aimi suggested is: a backslashed backslash is printed literally in the sql and cancels the backslash my code uses to cancel the backslashed single quote. Wow, ´this is becoming philosophy! In fact, cancels. But I´ve been thinking and, no problem. Here´s the messy solution the backslash trick will put our cracker into. Remember our query: `select username, password from users where sid='$input';` [download] ...wich was cracked by entering this: `5' or 1='1` [download] Now, he´d enter: `5\\' or 1=\\'1` [download] ...resulting in:(extra spaces to see clearly) `...where sid=' 5\\' or 1=\\'1 ';` [download] Can you see the problem? The first semicolon (in the variable being interpolated), ok, he´s out there, as the backslash before him lost it´s canceling power, canceled by the backslash before her. The second single quote is out there too, free by the same method. But, and here´s the point, the second pair of backslash sql has screwed completely any chance of doing any valid sql statement! With the first pair the mess is not a problem, as our "5" or whatever is a value that won´t match anyway. But with the `or 1='1` we really needed silence of mess! And now there is a `\\` in the middle of the attempt to compose alternative permissive code! As we have a `';` waiting to be matched at the end of the query statement (see after $input), I can´t see any way of delivering any clean syntax that has a `\\` in the middle. Any query you may think would have do end as `something='` or `something<'` or `something>'` or `something like '` or any operator, but the end must be a value wich delimitation cleanly started with a single quote. And, about that, we now know, this single quote can be there, but not without his happy new companion `\\`. Please, find any hack for this if you can. André	[reply] [d/l] [select]
Re^3: SQL Injection myths under DBI by Joost (Canon) on Apr 12, 2005 at 13:04 UTC
Prematurely breaking a select can be just as bad as inserting additional statements; consider this: `my ($id) = $dbh->selectrow_array("SELECT id FROM users WHERE NAME='$na +me' AND PASSWORD='$password'");` [download] If I somehow manage to break the SQL statement in $name, my password will not be checked! Without a good quoting mechanism, you could break that with `$name = "admin\'\0";` [download] or even `$name = "admin';";` [download] Or whatever kind of escapes your specific DBD/database combination will allow. This is the main reason for using $dbh->quote() and placeholders - the quoting mechanism can be different for different databases, and they are a little more complex than you imagine. In effect, all you're doing is trying to reinvent the $dbh->quote() method. Why reinvent the wheel when there already is one that's been especially made for your type of car, has been checked and double-checked, and is already safely attached to your car? "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l] [select]
Re^4: SQL Injection myths under DBI by itub (Priest) on Apr 12, 2005 at 14:02 UTC
Re: SQL Injection myths under DBI by adrianh (Chancellor) on Apr 12, 2005 at 14:06 UTC
--. I'm in the "really bad advice please ignore" camp. Sorry :-) For example: As you can read from DBI manpage, the do() and prepare() methods happen to assume a single statement Unless you're using a driver that does allow multiple statements. Like DBD::Sybase.	[reply]
Re: SQL Injection myths under DBI by runrig (Abbot) on Apr 12, 2005 at 05:55 UTC
Even if an attacker can't drop your database or corrupt your data, I'd rather they not be able to do a resource sucking cartesian join on tables in my database (mysql doesn't allow subqueries, so you may be safe from that). Update: I, um, meant old versions of MySQL...yeah, that's it :-) (Ok, so I need to update my own mental database :)	[reply]
Re^2: SQL Injection myths under DBI by dragonchild (Archbishop) on Apr 12, 2005 at 12:44 UTC
(mysql doesn't allow subqueries, so you may be safe from that). Oh, really? My wife's blog	[reply]
Re: SQL Injection myths under DBI by mpeppler (Vicar) on Apr 12, 2005 at 18:40 UTC
I think this is the first time that I've downvoted something - but in this case I couldn't ignore it. Use placeholders - it's not difficult to do, it avoids various quoting issues, it helps with security, and it usually gives you better performance. Michael	[reply]
Re: SQL Injection myths under DBI by cbrandtbuffalo (Deacon) on Apr 12, 2005 at 16:01 UTC
Class::DBI provides some degree of protection from SQL injection. Even more than you've listed, Class::DBI is a nice layer that does things right to prevent you from doing bad things. For example, I believe it binds all variables for you. But in general, I still don't think you can just disregard the threat. Rather, you should be aware of it and know how the modules you use do and do not protect you.	[reply]
Re: SQL Injection myths under DBI by etcshadow (Priest) on Apr 12, 2005 at 23:51 UTC
All I have to say is: give me a site you've built, and let's see how long it lasts. Seriously. If I don't actually have control of your site within minutes, I can at least knock it off the net. `------------ :Wq Not an editor command: Wq` [download]	[reply] [d/l]
Re^2: SQL Injection myths under DBI? by Andre_br (Pilgrim) on Apr 16, 2005 at 00:58 UTC
Hello folks, Hey, I think one thing may have made me a little misunderstood on my intentions: I´ve posted the topic originally in the 'questions' section, not in the meditation. But it was moved by the administrator monks to this section, where it appeared as I was 'meditating', when in fact I was just 'asking'. So perhaps the right title now for this 'meditation' would include a question mark after the "SQL Injection myths under DBI". I will update it as that to make it sound less like I am definately giving some advice on tearing apart all the advices in CGI security we learn all the way from the Lhama book. I really wasn´t. So, I think, as this can have caused justified hot reactions of some of you. Sorry. Hey, tilly, no problem, man. Having said that, in fact null-byte really screws my idiot-solution (I assume!) of just escaping the single quote. I tested it and doesn´t matter which mess remains at the right side of the query, after the query injection - the null-byte really makes DBI unaware of aesthetics! It executes everything ´till the %00. Detail: if you want to thest this, don´t put the semicolon in the input, finish your evil query (on yourself, for test, we´re the good guys!) with the null-byte, not with semi-colon, otherwise it won´t work and you will end up like me, saying sql injection is not a problem when it really may be. Nevertheless, even with this technique, I couldn´t crack myself if the scaping of backslashes was included in the filtering of the input. I´ll go on searching the hacker manuals to see if I can find any way of overcome this, but it really seems as a paradox. If I can´t get a free-single quote to be inside the query, I can´t make the text I manage to input more than a strange string to be not-matched in the query. If you guys know of something, please post it up. Cheers André	[reply]
Re^3: SQL Injection myths under DBI? by jamroll (Beadle) on Mar 13, 2017 at 12:16 UTC
could we not just delete the single quote(s) and other "bad" characters? `$input =~ s/\'\|\"\|\@//g;` [download]	[reply] [d/l]
Re^4: SQL Injection myths under DBI? by afoken (Chancellor) on Mar 13, 2017 at 18:26 UTC