Introduction

In this section I'll cover several topics:

• What is SQL injection (SQLi)?
• What is the impact of a successful SQL injection attack?
• How to detect SQL injection vulnerabilities
• SQL injection in different parts of the query
• SQL injection examples
• Second-order SQL injection
• Examining the database
• SQL injection in different contexts
• How to prevent SQL injection

What is SQL injection (SQLi)?

Web applications often receive input from users and use it to compose a database query that provides results that are sent back to a user. For example, consider the search function on an e-commerce site. If a user enters orange tiger pillow into the search box, the web server needs to know what products in the catalog might match this search term. It might send a request to the backend database server that looks something like this:

SELECT ItemName, ItemDescription, ItemPrice

FROM Products

WHERE ItemName LIKE '%orange%' AND

ItemName LIKE '%tiger%' AND

ItemName LIKE '%pillow%'

This command retrieves a list of item that can be included in the results returned to the end user. In a SQL injection attack, the attacker might send a very unusual-looking request to the web server, perhaps searching for

orange tiger pillow'; SELECT CustomerName, CreditCardNumber FROM Orders; --

If the web server simply passes this request along to the database server, it would do this (with a little reformatting for ease of viewing):

SELECT ItemName, ItemDescription, ItemPrice

FROM Products

WHERE ItemName LIKE '%orange%' AND

ItemName LIKE '%tiger%' AND

ItemName LIKE '%pillow%';

SELECT CustomerName, CreditCardNumber

FROM Orders;

--%'

This command, if successful would run two different SQL queries (separated by the semicolon). The first would retrieve the product information, and the second would retrieve a listing of customer names and credit card numbers.

In the basic SQL injection attack we just described, the attacker is able to provide input to the web application and then monitor the output of that application to see the result. Though that is the ideal situation for an attacker, many web applications with SQL injection flaws do not provide the attacker with a means to directly view the results of the attack. However, that does not mean that the attack is impossible; it simply makes it more difficult. Attackers use a technique called blind SQL injection to conduct an attack even when they don't have the ability to view the results directly.

What is the impact of a successful SQL injection attack?

An effective SQL injection attack can lead to unauthorized entry into a system's sensitive data, like passwords, credit card information, or personal user details. Numerous notable data breaches in recent times have occurred due to such attacks, causing harm to a company's reputation and resulting in financial penalties from regulators. Additionally, the severity of these attacks is such that they may allow the attacker to establish a hidden access point in the organization's systems, enabling a prolonged compromise that can remain undetected for a considerable time.

How to detect SQL injection vulnerabilities

Burp Suite's web vulnerability scanner is a highly effective tool for swiftly and reliably identifying the majority of SQL injection vulnerabilities.

To detect SQL injection manually, a systematic series of tests can be employed on each entry point within the application. This process typically involves:

• Entering the single quote character ' and observing if any errors or anomalies occur.
• Submitting SQL-specific syntax that evaluates to the original value of the entry point and a different value, then observing the application's responses for systematic differences.
• Using Boolean conditions like OR 1=1 and OR 1=2, and comparing the application's responses for any variations.
• Sending payloads designed to trigger time delays when executed within a SQL query and observing differences in response times.
• Submitting OAST payloads meant to trigger out-of-band network interactions when executed within a SQL query and monitoring for any resulting interactions.

SQL injection in different parts of the query

The majority of SQL injection vulnerabilities are commonly found in the WHERE clause of a SELECT query, and experienced testers are well aware of this type of vulnerability.

However, it's essential to note that SQL injection can potentially occur at various locations within the query and across different query types. The most common alternative locations where SQL injection vulnerabilities may arise are:

• In UPDATE statements, either within the updated values or the WHERE clause.
• In INSERT statements, specifically within the inserted values.
• In SELECT statements, where injection can happen within the table or column name.
• In SELECT statements, within the ORDER BY clause, presenting another potential avenue for exploitation.

SQL injection examples

SQL injection vulnerabilities, attacks, and techniques come in diverse forms, each manifesting in various situations. Several typical examples of SQL injection include:

• Retrieving hidden data: This involves tweaking a SQL query to obtain extra results that were not originally intended to be shown.
• Subverting application logic: In this case, attackers modify a query to disrupt the normal functioning of the application's logic.
• UNION attacks: Attackers leverage this technique to fetch data from different database tables, combining results to their advantage.
• Blind SQL injection: Here, the attacker controls a query, but the application's responses do not directly reveal the results, making it more challenging to exploit but still potentially dangerous.

Second-order SQL injection

In the case of first-order SQL injection, the application directly takes user input from an HTTP request and includes it in a SQL query without proper safety measures. This unsafe incorporation of user input into the query leads to the vulnerability.

On the other hand, second-order SQL injection (also known as stored SQL injection) occurs when the application initially takes user input from an HTTP request and safely stores it in a database for future use. The vulnerability arises later, during the processing of a different HTTP request, when the application retrieves the stored data from the database and incorporates it into a SQL query without adequate safeguards.

Second-order SQL injection is often found in situations where developers are aware of SQL injection vulnerabilities and take precautions when initially storing the data in the database. Consequently, when processing the data later on, they mistakenly consider it safe because it was previously stored securely. Unfortunately, this assumption leads to the mishandling of the data, creating the unsafe condition.

Examining the database

Certain fundamental aspects of the SQL language are consistently implemented in a similar manner across well-known database platforms. Consequently, various methods used to identify and exploit SQL injection vulnerabilities tend to function identically on different types of databases.

Despite this similarity, there are significant distinctions between common databases, resulting in certain detection and exploitation techniques operating differently on various platforms. These differences encompass aspects such as:

• Syntax for string concatenation.
• Handling of comments within SQL queries.
• Execution of batched (or stacked) queries.
• Usage of platform-specific APIs.
• Format and content of error messages generated by the databases.

These variations necessitate tailored approaches when dealing with SQL injection vulnerabilities across different database types.

SQL injection in different contexts

It's essential to understand that SQL injection attacks can be carried out using any input that the application processes as a SQL query and is under your control. For instance, certain websites take input in JSON or XML format, which is then used to query the database.

These diverse input formats can sometimes offer alternative ways to execute attacks that might be otherwise blocked by Web Application Firewalls (WAFs) and other defense mechanisms. Weak implementations of security measures often focus on identifying common SQL injection keywords within the incoming request. As a result, you might be able to circumvent these filters by simply encoding or escaping characters in the restricted keywords, thus evading detection.

How to prevent SQL injection

Using parameterized queries (or prepared statements) instead of string concatenation within the query can effectively prevent most SQL injection vulnerabilities.

Parameterized queries are suitable for scenarios where untrusted input is utilized as data within the query, including situations involving the WHERE clause, as well as values in INSERT or UPDATE statements. However, they cannot handle untrusted input in other parts of the query, such as table or column names, or the ORDER BY clause. In such cases, different approaches like white-listing permitted input values or employing alternative logic will be necessary to ensure secure application functionality.

To ensure the efficacy of a parameterized query in preventing SQL injection, the string used in the query must always be a hard-coded constant and should never contain any variable data from any source. It is essential to avoid making decisions on a case-by-case basis about whether certain data is trusted or not, leading to the continued use of string concatenation within the query for what may seem like safe cases. Such an approach can be error-prone, as it is easy to make mistakes about the origin of data or for changes in other parts of the code to undermine assumptions about data integrity. To maintain robust security, consistent use of parameterized queries with hardcoded values is the recommended practice.