Ways to get caught: SQL Injections

If you have taken CS2102, or wrote an application that uses a relational database to authenticate users, you might have come across code like this:

SELECT * FROM USERS WHERE USERNAME='[username]' AND PASSWORD='[password]'

..where [username] is usually a variable that holds the username the user inputed into you application and [password] is usually the variable that holds the password the user inputed into your application. There may be string concatenation operators that make this possible in your favorite web application development language.

Assuming that the user uses the inputs directly in the SQL query without manipulation there are ways to break into such a system. And SQL Injection is the way. Here is how you do it.

Let us substitute the following input into the above query:

• [username] is ' OR 'x'='x
• [password] is ' OR 'x'='x
  

SELECT * FROM USERS WHERE USERNAME='' OR 'x'='x' AND PASSWORD='' OR 'x'='x'

As you can see this query will return some user from the database and the remaining application logic will authenticate you as that user.

This is a well known technique therefore it is very unusual to find web applications that have this vulnerability. However my polytechnic hired me after I showed them this vulnerability in one of their intranet web applications. So keep your eyes open, and report all such holes to NUS and be rewarded for your effort.