Writing secure web applications - Part II
Tags : Sql injection, Application Security, application access
SQL Injection
Well we are back, offering you the second dosage of our three part series on writing secure web applications. This time we (To-Do Master and I) will take you through SQL Injection. I hope the early-morning chants prescribed the last time around have made your developers more aware of the dangers of trusting client side input.
Part 1 is available here. If you do not wish to go through it (because you always knew client side input could not be trusted, duh), all you need is an introduction to To-Do Master and we have that right here.
The To-Do Master feature set:1. It’s a system that lets you maintain a To-Do list. So users can add/edit/delete To-Do items (belonging to the user, we wouldn’t want Mary to edit Lucy’s To-Do list). 2. If a user has administrator access then he/she can add/edit/delete users. 3. The users need to login to access the system. 4. The users can update their passwords. |
We will use a database with two tables:
|
Before we delve into the nefarious world of SQL Injection, there is an afterthought to the last article that I would like to share with you. Client side input is not just what the web application receives from a HTML form. It is any information that a client can send to your web application.
This means you cannot trust access control based on passing some information in the URL. This example will illustrate my point: Mary logs in to To-Do Master and we show her the following list:
| To-Do Item | Deadline | |
|
1 |
Buy TV |
Tomorrow |
|
2 |
Pay Phone Bill |
24th January 2006 |
|
3 |
Call Mom |
Yesterday |
If you hover over the first link – “Buy TV”, you will see that the target is:
http://www.todomaster.com/edititem?id=2
The “edititem” page allows the user to edit the item. It cannot trust that the id belongs to the user who logged in. If we do not check for the user’s access rights on this page then Mary can simply change the hyperlink to http://www.todomaster.com/edititem?id=12 and chance upon Lucy’s To-Do list. We would never want that. This seemingly simple problem exists in a number of web applications and you only need to look up Google to find out several stories of banks revealing customer information, schools revealing results and people accessing their colleagues’ email.
Another implication of this fact is that you cannot control access by hiding links. For example: If Tom is a To-Do Master administrator and Joe is not, you cannot prevent Joe from accessing administrator pages by hiding the links from him. You need to put some access control logic on the administrator pages otherwise Joe can always type out the URL to the administrator pages after logging in.
OK, done with the afterthought. I think it’s time that you gather your developers in the meditation room and repeat “I don’t trust client input” for five minutes as before. Then we can move on to SQL Injection.
SQL Injection
SQL Injection is the process of manipulating input such that you fool the application to make changes to the database it is accessing. Well, that’s the simplified version. Given the right environment, you can use SQL Injection to drop databases or even shut down servers.
Let us start with the To-Do Master again. For example: Joe, as discussed earlier, does not have administrator access. He is smart and he wants to add/edit/delete users. He already tried accessing the pages directly but the application is smart enough to deny him access. If you look at the database, the user table has a field called isAdmin. It is set to “no” for Joe. He goes to the update password page. The page asks him for his old and new passwords. Joe enters the correct old password and updates the new password using something like:
update user set password=’prettysecret123’ where login=’joe’
Works fine and looks decent. Now Joe tries to change the password to joe’snewpassword. The query now becomes:
update user set password=’joe’snewpassword’ where login=’joe’
The database throws an error because it thinks the password text is joe and the rest is invalid SQL. The application error-handling informs Joe that there was a system error trying to update the password.
Joe immediately tries to change the password to prettysecret123’, isAdmin=’yes. The query now becomes:
update user set password=’prettysecret123’, isAdmin=’yes’ where login=’joe’
Well, that grants Joe administrator access. Another query which is usually vulnerable to SQL Injection is the one checking for authenticating users. It usually looks like:
select login from user where login=’joe’ and password=’prettysecret123’
The user might try with a password ‘ OR ‘1’=’1. This makes the query:
select login from user where login=’joe’ and password=’’ OR ‘1’=’1’
That will give Joe access to any user account.
Depending on what database you are using, there are several things SQL Injection can accomplish. For example, a semicolon is used to separate queries in some databases. You could specify a password ‘;delete from user where ‘1’=’1 and the query would be:
select login from user where login=’joe’ and password=’’;delete from user where ‘1’=’1’
You could go a step further if you could comment out part of a query. If the database uses two dashes to comment you could use ‘’;drop database todomaster;– as the login and the query becomes:
select login from user where login= ‘’;drop database todomaster;–‘ and password=’password’;
The part of the query after the two dashes is ignored.
Some databases will allow you to run a command shell inside the database. I can see you imagining the infinite possibilities that are presented if a vulnerable application connects to that database.
It might look like SQL Injection needs you to know the database schema. That is true if you want to manipulate the system (for example giving yourself admin access) but manipulating the queries for update password or those for authenticating users can be accomplished after a few minutes of hit and trial. Then you can get around to deleting users, dropping databases or simply getting access to the system. The best way to check such vulnerability is to use special characters in your input and see if the system throws an error. What is a more special character than a single quote? Come on, try it now.
Prevention of SQL Injection, some might argue, is part of input validation. That is partly true because sometimes you need to accept special characters. For example, the description of a To-Do item might contain single quotes. Get a present for Joe’s birthday is a perfectly valid To-Do item.
The moral of that long-winded story is that the application needs to check and, if necessary, modify the user input that goes into the query. The best way to do this is to use parameterized queries. That means you do not concatenate the user input to the queries but pass them as parameters to the query. That forces you to specify the type of the parameter too. Libraries for doing that are available for Microsoft as well as JAVA Technologies. For languages like PHP you need to do two things:
1. Parse all your text input using functions like mysql_escape_string
2. Make sure any numeric input is indeed numeric (you can use functions like settype)
Let us elaborate on the second point above. Developers usually parse all text input but concatenate numeric input as it arrives. With loosely-typed languages like PHP user can specify text input for numeric parameters. For example: The “edititem” URL looks like http://www.todomaster.com/edititem?id=2. If the application is only parsing text input, a malicious user could change the URL to: http://www.todomaster.com/edititem?id=2;drop database todomaster and well we are back to anyone and everyone dropping our database. Always make sure that any input you assume to be numeric is in fact numeric.
OK, now that we have made To-Do Master a bit smarter, let us see how it responds to Joe’s attempts at elevating his status to Administrator.
Joe tried to change the password to prettysecret123’, isAdmin=’yes. The query became:
update user set password=’prettysecret123’, isAdmin=’yes’ where login=’joe’
Well, that was a long time ago, we are not stupid anymore. We parse and modify the input. A mysql_escape_string would change the password Joe entered to prettysecret123’’, isAdmin=’’yes. This is because if you want to pass a single quote to a MySQL query you need to pass two single quotes. So the query becomes:
update user set password=’prettysecret123’’, isAdmin=’’yes’ where login=’joe’
And this one actually sets Joe’s password to what he wanted: prettysecret123’, isAdmin=’yes.
Good luck remembering that, Joe.
The To-Do Master database and application were tailor-made to illustrate the vagaries of SQL Injection and several examples described above might not be applicable to your application. But if you are running queries by concatenating unparsed user input, the above examples can be modified to exploit the vulnerability. We are trying to focus on the concept and not the implementation.
Well, we have defeated Joe’s intentions of tampering with our database and made To-Do Master a more slick, smart, savvy and secure application (I like the sound of that: To-Do Master, the slick, smart, savvy and secure way to manage the things to do).
Until next time then, keep up the chants.
Well, if you think the writer of this article deserves a compliment or two (or if you think he should be abused, vilified and never allowed to write a single line of code again) then don’t just sit there. Voice your opinion. Direct your comments, suggestions, corrections (especially), anecdotes, taunts, abuses, compliments and remarks to Tarun.
