SQL Injection Attacks

This blog is gonna be about SQL injection attacks. I used to think almost everyone knows about this and there's no place where this might work. But recently (today, 19-04-2005) found a stupid site that blew up with SQL injection. So here's something on secure design with SQL.

SQL injection attack as the name says, basically involves injecting SQL snippet into some SQL statement. How to do it?

Well, if u r from VTU Comp. Science u might have done some Database application (or flicked one ) in 5th / 6th sem. Even otherwise, hope u have created/worked with some database application sometime. Most applications have a layer of so called SECURITY () which requires one to login with a userID and password to use the application. It's usual to have at least two categories of users as I've seen -the administrators and the other users. Security checks for the administrators are ususally more than for normal users.
How is the user authenticated? U get the userID into some textbox and get the password in a corresponding password box. Then u compare the (ID, password) pair against a database table with a statement that ususally goes like:
SELECT * FROM authenticated WHERE username =' value in username textbox' AND password = 'password textbox'

Usually usernames are unique, which means the above query returns either One record or None. So if the query successfully returns a record, the user is authenticated, otherwise not. Some people replace the '*' in the SQL to something like username or something else. i.e like:
SELECT credentials FROM authenticated WHERE username = 'value in username textbox' AND password = 'password textbox'
But this doesn't make any difference as long as you don't use the returned data for further authentication. But I've not seen many doing that. So all this was a brief intro to a typical authentication scenario.

Now I'll give u the example of a stupid SECURE page that was supposed to be opened by the ADMINs, but gave access to me even though I didn't know any userID for that.
Today I recieved a link from my friend which had a really good collection of Java e-Books for free download. The link was: http://dimpon.plus.ru/javabooks/ (Probably itz an illegal collection. But I'm not sure). I downloaded some books. Then I thought maybe the site has collection of other books too. But there were no links. So I just tried to explore around the site and landed in the admins section. (It was a very short exploration though). And as u might guess, it required me to know some stupid admin userID and of course the password, which I didn't have.

I thought why not try SQL injection? I never expected it to work anywhere (with the exception of the stupid DB applications submitted as mini projects ). But don't know which stupid did that app, it did work! I put in the login id as [ ' OR TRUE OR ' ] (excluding square brackets) and the same for the password. My expectation was to have the final SQL delivered to the server as:
SELECT FROM WHERE username=' ' OR TRUE OR ' ' AND password=' 'OR TRUE OR ' '
[i.e. the SQL in userID and password boxes is substituted in place of the placeholders in original SQL statements]

This is one of my favourite SQL injections. But it didn't work as expected for a lot of reasons. (Brush up ur SQL knwledge and figure out why ). But the stupid app puked out a much detailed info. It went to a php file (http://dimpon.plus.ru/admin/in.php) which eventually spat out an error message:
Couldn't execute SQL query!SELECT admin_id,is_su FROM admin WHERE login=' ' OR TRUE OR ' ' AND pass=' ' OR TRUE OR ' '

It's usual to have the application put out such detailed error messages when u r developing it (when u ususally run it in the debug mode), but never in a deployed application. Anyone can easily figure out how to proceed from here on. It's so detailed! Lesson to learn, never be verbose about the errors/exceptions ur application throws, at least not with the user.

So I just modified my input. My new login id was 'fool' (without quotes) and my password was:
' or login like '%
(with quotes)
Hence the final SQL constructed should be:
SELECT admin_id,is_su FROM admin WHERE login='fool' AND pass='' or login like '%'
which I know will return all the records. It seems the developer actually expected one login to return a lot of records. That's why he never made a check that only one record should be returned . He could have made my life a little more difficult by making that check, in which case I'd have to guess at least one valid username (thats quite easy too). But he never bothered to. I still don't understand what he did with the admin_id, is_su that he got from the query.

Okay, there was one thing that I felt bad about. The damn page was in some weird language (Couldn't figure out even after using Google Translator), with traces of english somewhere. So it was pretty much useless for me. The structure of the page gave a feeling as if it provided facility to add some users and add some content (wiki kind of thing?). So that's pretty much useless. The only thing I felt good was I cracked this without much efforts.

How to survive SQL injection?
Well, that's actually quite simple. Follow the well known rule. Never trust your user. Never trust user inputs. It can be anything stupid. So just validate user input everytime you get it and particularly before passing it to your SQL.

One solution to this would be checking user input for the existence of non alphanumeric characters. That's the simplest solution, provided you don't allow your user input to have special characters like spaces.

But what if you decide to allow your user to have spaces etc... in his password? Most of the popular sites provide this. (Yahoo!, Google etc...) One solution for this situation wouls be to hex encode the user input as is done with URLs. i.e. replacing spcial characters in the input by their hex value. A space gets replaced by and so on... This is very easy to do and is very effective.

But finally, the manthra to keep your application secure is to never trust the user inputs.