The Basics: SQL Injections

Photo by John Barkiple on Unsplash

Heads up! The demo app might only work in Chromium browsers, and possibly Safari.

This demo is very basic and makes a lot of assumptions for the SQL server and implementation. This is intentional to keep things as simple as possible, as it's meant to help anyone without prior knowledge in this subject understand fundamentally what a SQL injection is and how they work.

What is a SQL injection

It is a type of attack which targets the SQL statements of an application. They are pieces of code which alters the SQL statement's behaviour to do something other than what was intended.

According to OWASP's top 10 of 2017, Injections (like SQL injections) is the top security risk for web applications, and with good reason. The results of a SQL injection can vary wildly, for example you could download the whole SQL database, allow you to login with any user of your choice or potentially allow you to exploit a vulnerability with the SQL server itself (ei. RCE).

In this demo we will focus on just altering the SQL statement to allow us to fetch more data from the database and login without knowing the password.

Demo App

The demo app runs entirely in your browser so whatever you do in this app only affects you. So feel free to experiment and mess around with it as you please.

Before you continue, I recommend you open the app so you can follow along:

Open in a new tab or open in a popup.

Before we get into to the actual injections, lets familiarise ourself a bit with the demo app.

Try type a username and password into the form, anything will do. This should return [] (empty array) in the "Query results". What this means is that no users were returned from the database matching the password and username you entered.
Now try enter "markeliasen" as the username and "123456" as the password. This should return the details for that account:

[
   {
      "id": 1,
      "username": "markeliasen",
      "password": "7c4a8d09ca3762af61e59520943dc26494f8941b"
   }
]

Probing

You can often test forms and other inputs if they are vulnerable to injections before you actually begin to write any code. One way we can do this is by typing a single " (double quote) into the username input field.
The " character is the string delimiter (see in the SQL Query output, the values are wrapped in "<value>"), so it could reveal if the application is not sanitising the input if it throws an error, since the " would make the SQL statement invalid in this application (this method is not definitive).

Try it now, type a single " into the username field and see what happens.

It threw an error! This is often a clear indicator that an application is vulnerable (an application could be vulnerable without throwing an error, see Blind SQL Injections).

Now if you look closely at the executed SQL query, you should be able to see the following: username = """

Do you see the extra " in there? Because we used a " as input, we are closing the initial value comparison, it leaves the last " hanging at the end. This turns the query into an invalid statement which makes the server throw an error.

Exploiting the vulnerability

Now that we know the server is vulnerable to our injections, lets craft an injection which returns all the user records in the database: " OR 1=1 /*

I will explain in detail what this injection does in a moment, but for now type the above highlighted code into the username field in the demo app.

If you look at the query results, you can see it returned all of the users in the database!

So, why did that happen? I'll try to break down the injection that we used into parts:

Part 1 | "  OR 1 == 1 /*
First we close the original value comparison. Because we want all the users in the database to be returned, we don't want to restrict it to a specific username. So we leave it empty by closing the value comparison.

Part 2 | "  OR  1 == 1 /*
Instead of selecting all users by username, we can instead make our own comparison to test users against. We can do this by adding an OR statement. This will alter the statement to read something like this (psudo): if the "username" value is equal to "" (empty string) OR ?

Part 3 | " OR  1 == 1  /*
Since we want to return all users, we need to specify something which will always be true. We can do this by simply checking is 1 is 1. The statement will now read (psudo): if the "username" value is equal to "" (empty string) OR 1 is equal to 1

Because its an OR statement, only one OR the other of the conditions needs to be true, and since 1 will always be equal to 1 the statement will always be fulfilled.

Part 4 | " OR 1 == 1  /*
We then add the opening tag for a multiline comment. Opening this tag and never closing it will make the parser think whatever else comes after this tag is just a part of a comment and should not be included in the query.

Result
The final injection will make the SQL server interpret the whole statement something like this:

SELECT
    *
FROM
    users
WHERE
    username = ""
OR
    1 = 1

Login without a password

Taking what we have learned above, its time for you to try and craft a sql injection which allows you to login to the account "markeliasen" without knowing the password.

There are several ways you can accomplish the same result in SQL, so if you find another way to produce the same result that is absolutely fine.

The statement we need to craft will need to do the following:

1. Inject the username we want to login with.
2. Ignore the password statement.

I want you to try it out yourself. Don't worry about breaking anything in the demo, it is all local within your browser, and you can always refresh the demo app page.

If you can produce the following Query result, you have succeeded:

[
   {
      "id": 1,
      "username": "markeliasen",
      "password": "7c4a8d09ca3762af61e59520943dc26494f8941b"
   }
]

If you just want to see the answer, click here

Protecting Your Applications

There is really only one answer to the question "how do I prevent SQL Injections?", and it is to use parameterised queries and sanitise all user input before using it. Even if you use a NoSQL database, you are still vulnerable to injections without proper sanitisation.

So in short: Type check and sanitising anything from the user if you intend to use it anywhere. Even values from the HTTP headers of a request can be spoofed (like IP address), so don't trust anything and sanitise everything!

Conclusion

What we went through in this post is very very basic and only the absolutely tip of the iceberg. If you would like to know more about SQL injections, check out OWASP or look up more advanced tutorials and guides on websites like Pluralsight, Udemy and LinkedIn Learning.

Knowing how SQL injections work will make you much more capable at preventing them. Hopefully you found this guide useful and relatively easy to follow. If you have questions or feedback make sure you post it in the comments section below.

Please be responsible. Regardless of whether you are new to security or not, if you happen to find a vulnerability in a website or application, please follow the responsible disclosure model.