SQLi 101

SQL injection (SQLi) is a vulnerability where an attacker can manipulate a web application's database queries by injecting malicious SQL code through user inputs.

This can lead to unauthorized data access, data corruption, deletion of data, and potentially complete control over the database server.

Use prepared statements and parameterized queries, validate and sanitize user inputs, and employ ORM (Object-Relational Mapping) frameworks to abstract database operations.

Educated Guesses

If we know the web server, we might infer what database is in use.

Web Server	Database	Db-specific SQLi
IIS	MSSQL	For MSSQL-specific SQLi info see here.
Apache/Nginx	MySQL	For MySQL-specific SQLi info see here.

SQLi Types

Alex Olsen's Blind SQLi payload examples.

Figure 1: SQL Injection types (imaged adapted from here).

Detection

We can enumerate SQLi flaws via sending manual payloads, searching for dynamic SQL statements during a code review and/or fuzzing the request. The most common method for enumerating a SQLi flaw is to identify input fields that might be used in a database and submit characters that will break SQL queries. This might lead to verbose error messages or subtle differences in responses.

String Delimiters

Suppose we control an input field that asks for the parameter name. If our input is x7331 the query may look like this:

// normal query when the input is x7331
SELECT * FROM users WHERE name = 'x7331'

If our input is x7331' OR '1'='1 the query becomes:

// malicious query when the input is x7331' OR '1'='1
SELECT * FROM users WHERE name = 'x7331' OR '1'='1'

Payload	URL Encoded
'	%27
"	%22
#	%23
;	%3B
)	%29

We can also fuzz for pontially banned characters.

ffuf -k -u https://watch.streamio.htb/search.php -d 'q=FUZZ' -w /usr/share/seclists/Fuzzing/special-chars.txt -c -ic -ac -H 'Content-Type: application/x-www-form-urlencoded'

Closing Out Strings and Functions

There are various common functions used for string comparison, such as LOWER() and UPPER() to ensure consistency.

SELECT * FROM users WHERE LOWER(name) = LOWER('<userInput>');

In this case, the function should be closed out.

// injected payload
x7331') OR '1'='1

// resulting query
SELECT * FROM users WHERE LOWER(name) = LOWER('x7331') OR '1'='1')

We end up with a hanging ) at the end, thus, we will need to comment out whatever comes after our payload.

// injected payload
x7331') OR '1'='1'#

// resulting query
SELECT * FROM users WHERE LOWER(name) = LOWER('x7331') OR '1'='1'#)

Boundary Testing

Boundary Value Analysis is common software testing technique based on testing the boundary values of valid and invalid partitions. The behavior at the edge of the equivalence partition is more likely to be incorrect than the behavior within the partition, so boundaries are an area where testing is likely to yield defects.

Boundary testing within a SQLi context refers finding a parameter that takes a limited data set and submitting values that fall outside that set. One form of SQLi boundary testing is using the ORDER BY clause to enumerate the number of columns displayed in a SQL statement.

Fuzzing

Automated tools, such as ffuf or Burp's Intruder, can be used to fuzz the suspected parameters with SQLi payloads and then assess the application's responses. The results of automated tools must always be validated manually.

Prevention

Parameterized Queries

1. Separation of SQL and Data: When you use a parameterized query, the SQL statement and the data are sent to the database as separate entities. The SQL statement is pre-compiled by the database, and the placeholders (?, %s, :value, etc.) are used to specify where the parameters will be substituted.

2. Safe Binding: The database engine safely binds the user-provided input values to the placeholders. This means that the user input cannot alter the SQL command structure. Instead, it is strictly treated as data.

Here’s how an SQLi might happen with a normal query.

# Normal query with user input directly concatenated
user_id = "'; DROP TABLE users; --"
query = f"SELECT * FROM users WHERE id = '{user_id}'"

// resulting query
SELECT * FROM users WHERE id = ''; DROP TABLE users; --'

Here's how a parameterized query handles this.

# Parameterized query with user input
user_id = "'; DROP TABLE users; --"
query = "SELECT * FROM users WHERE id = %s"
cursor.execute(query, (user_id,))

// resulting query
SELECT * FROM users WHERE id = '''; DROP TABLE users; --'

Prepared Statements

Parameterized queries and prepared statements offer the same level of protection against SQLi. They both achieve the same goal of separating SQL logic from user input. However, prepared statements might be more explicitly linked to performance optimization through the process of compiling and reusing the SQL command.

# Prepared statement with user input
user_id = input("Enter the user ID: ")  # User enters: 1; DROP TABLE users;
query = "SELECT * FROM users WHERE id = %s"
cursor.execute(query, (user_id,))

// resulting query
SELECT * FROM users WHERE id = '1; DROP TABLE users;'