Security : SQL injection

Context

SQL injection (SQLi) refers to a security vulnerability in web applications that allows an attacker to modify the queries sent to the database. This technique enables the attacker to retrieve data that they wouldn't normally have access to. Typically, this data belongs to other users or is information that the application has access to. Additionally, the attacker can also modify or delete this data, causing permanent changes to the content or behavior of the application.

By successfully carrying out an SQL injection attack, the attacker can gain access to sensitive data such as passwords, credit card information, or users' personal data. Many data breaches reported in the media in recent years are the result of SQL injection attacks, causing reputational damage and regulatory fines. In some cases, an attacker can even obtain a persistent backdoor in an organization's systems, leading to a long-term compromise that can go unnoticed for an extended period.

There are a wide variety of SQL injection vulnerabilities, attacks, and techniques that occur in different situations. Here are some common examples of SQL injection:

Retrieval of hidden data: By modifying an SQL query, the attacker can retrieve additional results.
Subversion of application logic: By modifying a query, the attacker can interfere with the application logic.
UNION attacks: The attacker can retrieve data from different database tables.
Database reconnaissance: The attacker can extract information about the version and structure of the database.
Blind SQL injection: The results of a query that the attacker controls are not returned in the application's responses.

Retrieval of hidden data

Let's take the example of a purchasing application that displays a list of products categorized by type. If the user clicks on the "Gifts" category, their browser will request the corresponding URL:

 https://website.com/products?category=Sport

This triggers an SQL query at the application level to retrieve information about the corresponding products in the database:

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

The application uses the "released = 1" restriction to hide products that are not yet released. Unreleased products likely have the value "released = 0".

However, the application has no defense against SQL injection attacks, which allows an attacker to launch an attack such as:

 https://website.com/products?category=Sport'--

The key element here is that the sequence "--" (double dash) is used in SQL to indicate a comment, which means that everything that follows is interpreted as a comment and ignored. Thus, by including "--" in the query, the rest of the query is ignored, including the condition "AND released = 1". This has the effect of removing this condition from the query, so that all products, including unreleased products, are displayed.

Subversion of application logic

Let's take the example of an application that allows users to log in using their username and password. To check the credentials, the application executes the following SQL query:

 SELECT * FROM users WHERE username = 'scadra' AND password = 'P@ssw0rd'

If the query returns information about a user, the login is considered successful. Otherwise, it is denied.

However, an attacker can bypass this password check by using the SQL comment sequence "--" to remove the password check condition in the WHERE clause of the query. Thus, by submitting the username "administrator'--" and an empty password, the following query is executed:

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

This query returns the user information for the username "administrator", allowing the attacker to successfully log in as that user without needing to provide a valid password.

Extraction of data from other database tables.

In cases where the results of an SQL query are returned in the application's responses, an attacker can exploit an SQL injection vulnerability to extract data from other tables in the database. This technique uses the UNION keyword, which allows an additional SELECT query to be executed and the results added to the original query.

For example:

 SELECT name, description FROM products WHERE category = 'Sport'

then an attacker can submit the input:

 ' UNION SELECT username, password FROM users--

Using this SQL injection technique using UNION, the application can return all the stored usernames and passwords in one table in the database, as well as the names and descriptions of products from another table.

Examination of the database

After identifying an SQL injection vulnerability, it is often useful to obtain information about the database itself. This information can then be exploited later.

It is possible to retrieve details about the version of the database using a specific query. The way to achieve this varies depending on the type of database, which can be inferred from the techniques that work. For example, for Oracle, one can execute the following query:

 SELECT * FROM $version

It is also possible to determine which tables exist in the database and what columns they contain. For example, on most databases, one can execute the following query to list the tables:

 SELECT * FROM information_schema.tables

Blind SQL injection vulnerabilities

Many SQL injection vulnerabilities are called blind because the application does not return the results of the SQL query or database errors in its responses. Although blind vulnerabilities are more difficult to exploit than non-blind vulnerabilities, they can still be exploited to access unauthorized data.

Depending on the nature of the vulnerability and the database involved, the following techniques can be used to exploit blind SQL injection vulnerabilities:

Modifying the query logic to trigger a detectable difference in the application's response based on the truth of a single condition. This may involve injecting a new condition into Boolean logic or conditionally triggering an error such as division by zero.
Conditionally triggering a delay in query processing, allowing the truth of the condition to be inferred based on the time it takes the application to respond.
Using out-of-band attack (OAST) techniques to trigger network interaction outside the application. This technique is very powerful and can work in situations where other techniques are not applicable. It is often possible to directly extract data through the out-of-band channel, for example by placing the data in a DNS lookup for a domain that you control.

ent les données via le canal hors bande, par exemple en plaçant les données dans une recherche DNS pour un domaine que vous contrôlez.

How to detect SQL injection vulnerabilities?

To detect SQL injection vulnerabilities, a manual method involves performing a set of systematic tests on each entry point of the application. This may include the following steps:

Checking for single quote characters (') and looking for errors or other anomalies.
Checking for SQL-specific syntax that evaluates the base (original) value of the entry point and a different value, and looking for systematic differences in the resulting application responses.
Checking for boolean conditions such as OR 1=1 and OR 1=2, and looking for differences in application responses.
Checking for payloads designed to trigger delays when executed in an SQL query, and looking for differences in the time it takes to respond.
Submitting OAST payloads designed to trigger out-of-band network interaction when executed in an SQL query, and monitoring any resulting interaction.

SQL injection in different parts of the query

Most SQL injection vulnerabilities occur in the WHERE clause of a SELECT query. However, it is important to note that SQL injection vulnerabilities can occur anywhere in the query, as well as in different types of queries. The other most common places where SQL injection occurs are:

In UPDATE statements, in the updated values or WHERE clause.
In INSERT statements, in the inserted values.
In SELECT statements, in the table or column name.
In SELECT statements, in the ORDER BY clause.

SQL injection in different contexts

SQL injection attacks can be carried out through any input that can be controlled and processed as an SQL query by the application. For example, some web applications accept inputs in JSON or XML format and use them to query the database. Attackers can thus find alternative ways to obfuscate attacks that are otherwise blocked due to WAF and other defense mechanisms. Weak implementations of these filters often look for common SQL injection keywords in the query, which can be bypassed by simply encoding or escaping characters in the forbidden keywords. For example, in an XML-based SQL injection, the attacker can use an XML escape sequence to encode the S character in the SELECT query, allowing the malicious query to be executed without detection.

Attackers can also use other data formats, such as CSV or YAML, to attempt to exploit SQL injection vulnerabilities. Therefore, it is essential for developers to implement security controls for all user inputs, regardless of their format, to reduce the risks of SQL injection.

 <stockCheck>
    <productId>
        1
    </productId>
    <storeId>
        34 &#x53;ELECT * FROM information_schema.tables
    </storeId>
</stockCheck>

This will be decoded on the server side before being passed to the SQL interpreter.

Second Order SQL Injection

First-order SQL injection occurs when the application insecurely integrates user input into an SQL query during HTTP request processing. However, in second-order SQL injection (or stored SQL injection), the application stores user input for future use. This is done by placing the input into a database, but there is no vulnerability at the time of data storage. Later, when the application retrieves the stored data and insecurely integrates it into an SQL query during the processing of a different HTTP request, second-order SQL injection occurs.

Developers are often aware of SQL injection vulnerabilities and securely manage the initial placement of data into the database. However, they wrongly assume that stored data is safe when processed later, as it was previously stored securely. In reality, stored data can be malicious and lead to second-order SQL injection, which can be used by attackers to access sensitive information or compromise the system.

Second-order SQL injection is often more difficult to detect and prevent than first-order SQL injection, as the application may store data for a long time before using it later. In addition, second-order SQL injection vulnerabilities can be difficult to detect, as they often require a combination of multiple user inputs to trigger exploitation. Therefore, it is important to implement appropriate security practices to detect and prevent second-order SQL injection, such as strict validation of input data and the implementation of monitoring mechanisms to detect suspicious activities.

How to Prevent

Here is a rephrased statement: The majority of SQL injection vulnerabilities can be avoided by opting for parameterized queries (or prepared statements) instead of concatenating strings into the query.

When user input is directly concatenated into the query, as in the following example, this creates a potential vulnerability for SQL injection:

 String query = "SELECT * FROM products WHERE category = '"+ input + "'";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

It is possible to easily modify this code to prevent user input from disrupting the structure of the query.

 PreparedStatement statement = connection.prepareStatement("SELECT * FROM products WHERE category = ?");
statement.setString(1, input);
ResultSet resultSet = statement.executeQuery();

Parameterized queries are useful in situations where unreliable data is included in a query, such as the WHERE clause or values in an INSERT or UPDATE statement. However, they cannot be used to handle unreliable input in other parts of the query, such as table or column names, or the ORDER BY clause. For these cases, other approaches must be adopted, such as whitelisting allowed input values or using different logic to achieve the desired behavior.

It is important to note that to effectively prevent SQL injection, the string used in the query must be a hardcoded constant and should not include variable data from any source. It is risky to decide on a case-by-case basis whether data is reliable or not and continue to use string concatenation for data considered safe. Errors can easily occur when evaluating the origin of the data or modifying other code that circumvents assumptions about tainted data.