Introduction to SQL Injection

SQL Injection is another type of security attack that can do serious damage to your application.  It's important to find SQL Injection vulnerabilities before a malicious user does.

In SQL Injection, a malicious user sends in a SQL query through a form field which interacts with the database in an unexpected way.  Here are four different things that a malicious user might do with SQL Injection:

• drop a table
• change the records of another user
• return records that the user shouldn't have access to
• log in without appropriate credentials

To understand how a SQL Injection attack is crafted, let's take a look at an example.  Let's say we have a form in our application with a username field.  When the username field is populated with a name such as 'testerguy' and submitted to the server, the following SQL query is run:

SELECT * from users where username = 'testerguy'

If this username exists in the database, results for the users table are returned to the application.  

A malicious user will try to trick the database by 

1. making it think that the entry has terminated, by passing in testerguy' 
2. adding an additional clause, such as OR 1=1
3. adding a terminating statement such as ; to make sure no other SQL statement will be run

In the above example, what the user would add to the username field would be:

testerguy' OR 1=1;

And what the database will execute is:

SELECT * from users where username = 'testerguy' OR 1=1;'

Take a moment to think about the 1=1 clause.  1=1 is always true, so the database interprets this as selecting everything in the table!  So this select statement is asking for all values for all the users in the table. 

Let's see some SQL Injection in action, using the OWASP Juice Shop.  Click the login button in the top left corner of the page.  We are going to use SQL injection to log in without valid credentials.

We'll make the assumption that when the login request happens, a request like this goes to the database:

SELECT * from users where username = 'testerguy' AND password = 'mysecretpass'

If the request returns results, then it's assumed that the user is valid, and the user is logged in.

What we will want to do is try to terminate the statement so that all usernames will be returned, and so that the password isn't looked at at all. 

So we will send in:

1. any username at all, such as foo
2. a single quote ' to make it look like our entry has terminated
3. the clause OR 1=1 to make the database return every username in the table
4. a terminating string of -- to make the database ignore everything after our request

Taken together, the string we will add to the username field is:

foo' OR 1=1--

You may notice that the submit button is not enabled yet.  This is because we haven't added a password.  The UI expects both a username and a password in order to submit the login.  You can add any text at all into the password field, because we are ensuring that it will be ignored. Let's add bar.

Now when you submit the login request, this is what will be executed on the database:

SELECT * from users where username = 'foo' OR 1=1--' AND password = 'bar'

The first part of the request is returning all users, because 1=1 is always true.  And the second part of the request will be ignored, because in SQL everything after the dashes is commented out.  So when the code sees that all users have been returned, it logs us in!

If you hover over the person icon at the top left of the screen, you will see that you have actually been logged in as the admin!  The admin's email address was the first address in the database, so this is the credential that was used.  Because you are logged in as the admin, you now have elevated privileges on the website that you would not have as a regular user.

Obviously, this sort of scenario is one that you would want to avoid in your application!  Next week we'll take a look at some other SQL Injection patterns we can use to test for this vulnerability.