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]

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

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

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]

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]