Re: SQL Injection myths under DBI

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.

Comment on Re: SQL Injection myths under DBI

Replies are listed 'Best First'.
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
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? ++ I love that! This quote should be added to the next edition of the Camel book!	[reply]