Tuesday, May 19, 2009

Web Application Security & SQL-Injection

SQL Injection is a technique to inject SQL query or command as an input to the application through web pages. The intent is to make the application run some malicious SQL queries.

Below are some of the threats from SQL Injection->

Unauthorized Access:
Suppose a developer takes the username and password as inputs from the web page and executes the following query to authenticate/authorize a user.
sqlQuery = “SELECT * from users WHERE username=’”+strUsrName+”’ AND password= ‘”+strPassword+”’”;
If the code is determining the validity of the user by checling if any record returned from the above statement, below is a catch.

A malicious user can pass “blah’ or 1=1” for password field and the code above will give the user access to the application. How? The SQL query above will now read ->
SELECT username from users WHERE username=’does nor matter’ AND
password= ‘blah’ OR 1=1

Because of the "OR 1=1" condition, the query will return all the records in the users table.

Adding a new User:

Now, in the same example pass “blah’; INSERT into users (“username”,password”,”email”) values (“JohnDoe”,”hackedit”,johnDoe@fakeemail.com)” as an input to the password field.
The SQL above will read ->
SELECT * from users WHERE username=’does nor matter’ AND
password= ‘blah’; INSERT into users (“username”,password”,”email”) values (“JohnDoe”,”hackedit”,
johnDoe@fakeemail.com)
The login might fail, but will create a new user account and the hacker can use it to access your application going forward.

Changing DB Schema:
If the DB is not read-only, we have a more serious threat.
Now, in the same example pass “blah’; drop table users;” as an input to the password field.
The SQL above will read ->
SELECT * from users WHERE username=’does nor matter’ AND
password= ‘blah’; drop table users;
And that’s it, the user table will be dropped and nobody can access the application.

Preventing SQL-Injection:

SQL Injection can be prevented by using parametrized SQLs.
The above code can be written as below and can be protected from SQL-Injection.

sqlQuery = “SELECT username from users WHERE username=? AND password= ?”
PreparedStatement pStmt = con.prepareStatement(sqlQuery);
pStmt.setString(1,strUsrName);
pStmt.setString(1,strPassword);
ResultSet rs= pStmt.executeQuery()

Variables passed as arguments to PreparedStatement are escaped by JDBC drive.

1 comment:

  1. That's a very nice post. I just wanted to add an alternative and expensive [very very very] solution. DataPower X150. =] =] .
    Awesome Blog, I'll Follow.

    ReplyDelete