Finding and Fixing SQL Injection

Published: 19 October 2020

Introduction

SQL Injection is an old vulnerability; first published on Christmas Day 1998 in Phrack Magazine 54. The issue occurs where user supplied input is insecurely concatenated into an SQL query. It generally allows an attacker to perform any of the operations that the database user can execute – such as extracting, changing, or deleting database contents. Rarely, where the database user is highly privileged, this can allow for command execution through features such as the MSSQL xp_cmdshell system stored procedure.

Exploiting the issue manually is often trivial, but there are freely available public exploitation tools available – such as SQLmap.

How to Fix SQL Injection

As the issue described is user input insecurely concatenated into a query, the remediation for SQL injection is fairly simple: don’t build queries through string concatenation.

Instead it’s more secure to use “Prepared Statements”, often called “Parameterized Queries”. All modern languages support this type of query either directly or through a framework; here we will supply a PHP example to show the difference.

An insecure example would be:

<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
/* Insecure (Non-prepared) example */
$query = "SELECT id FROM tblMembers WHERE username = " . $_GET["id"];
if (!($res = $mysqli->query($query))) {
echo "ID query failed: " . $mysqli->error;
}
var_dump($res->fetch_all());
$mysqli->close();
?>

In the above example, the query is generated through string concatenation, therefore it would be possible to alter the logic of the query to perform malicious actions such as extracting additional database content.

A parameterized example:

<?php 
$mysqli = new mysqli("localhost", "user", "password", "world");

$stmt = $mysqli->stmt_init();
$stmt->prepare("SELECT id FROM tblMembers WHERE username = ?");
$stmt->bind_param("s", $_GET["user"]);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM)) {
foreach ($row as $r) {
echo "$r\n";
}
}
$stmt->close();
$mysqli->close();
?>

How to Detect SQL Injection

SQL Injection can be found simply through code review, where a query is built through concatenation and that concatenation includes data that originates as user input. However, it can also be found through dynamic analysis. SQL Injection isn’t restricted to one input type, such as a GET request parameter, it could just as easily occur through a POST request, or from a user modifiable configuration. This issue is per-parameter, so each parameter must be tested in turn to ensure no vulnerabilities exist.

Error-based Injection

Where raw database error messages are shown by the target application, then SQL injection can be simple to find by simply supplying input likely to cause an error. Most commonly this can be quotation marks such as:

'
"
`

Generally these characters will cause an “unbalanced” number of quote marks, which will throw an error (these errors differ depending on the back-end database in use), such as:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''' at line 1

An error is caused by appending a quote mark to the end of the expected input.

The raw error shown on the screen, indicating the page is vulnerable to SQL injection.

Boolean-Based Injection

Where raw database error messages are not returned, either due to being replaced by generic error messages or no error at all, detection and exploitation is still possible. A simple way is through Boolean queries which are intended to cause a visible change in the application response.

For example a query which causes a list of products to be displays could be modified through a Boolean query to return no products, thereby showing injection is possible. This is often possible with queries such as:

' AND 1=1
' AND 1=2
or
' AND @@version=@@version
' AND @@version='foobar'

The two examples above are designed such that if the application returns output such as: “showing 117 products” for the first input and “showing 0 products” for the second input, this is an indication of injection.

The expected input is shown to return 1 row.

When appending AND 1=1 this does not alter the query output and therefore 1 row is also returned.

However when AND 1=2 is appended this causes the output to be false, so no rows are returned – this therefore indicates the input is likely vulnerable.

The preceding quote mark should match the type used by the developer; if this is not known then attempting each type and checking the output differences would allow this to be determined. If the input is an integer, then a preceding quote mark will not be required. If the injection point is within a sub query then closing brackets may be required.

Arithmetic Evaluation

An alternative check is to use arithmetic evaluation, this is useful to find inputs that are vulnerable but protected by Web application Firewalls – which commonly block keywords such as “AND” or “OR” by default.

The intention is to supply an input, and then the equivalent input as an arithmetic equation – if the output is the same then it is likely vulnerable to SQL injection.

In the following example 1 is a valid input, but 2 is not a valid input – however when we supply 2-1 we are given output, due to the fact that SQL processes the 2-1 and treats it as equivalent to entering 1.

The input of 1 is accepted, showing 1 rows returned.

The input 2 is not accepted, showing 0 rows returned.

However 2-1 is processed and returns the equivalent of input 1. Indicating an injection vulnerability here (potentially SQL injection).

Time-Based Injection

It’s also possible to find SQL injection vulnerabilities through causing database delays. The syntax differs depending on the database type, such as:

MySQL: sleep(5)
MSSQL: WAITFOR DELAY '0:0:5'
PostgreSQL: pg_sleep(5)

When the sleep(5) payload is supplied; the server response is delayed by at least five seconds, as shown by the “Waiting for” message displayed.

SQL Injection Exploitation

There are several methods for exploiting SQL Injection vulnerabilties depending on the context of the injection point, any potential filters and Web Application Firewalls (WAF) in place.

These methods are generally broken down into: