SQL Injection: Difference between revisions

Latest revision as of 17:46, 18 December 2024

SQL Injection

SQL Injection (SQLi) is a critical vulnerability often present in applications, particularly web applications. It is widely regarded as one of the most severe threats to the security of web platforms. According to the OWASP Top 10 Web Application Security Risks, injection vulnerabilities remained the top threat in 2021. A successful SQL injection exploit can potentially read sensitive data from the database, modify database data, perform database administration operations such as shutting down the Database Management System (DBMS), recover the content of a given file on the DBMS file system, and in some cases, issue commands to the operating system. The core reason for this is usually due to improper or inadequate coding methods. Invalid input, excessive privileges, uncontrolled variable sizes, detailed error messages, unsanitized user input and multiple statements are some of the sources of SQL injection vulnerabilities.

Impacts of SQL Injections

SQL Injections can affect the main aspects of the CIA triad of security, namely:

Confidentiality
Integrity
Authentication
Availability

Functionality

This vulnerability can only arise when there is a lack of data sanitation. This implies the existence of data input interfaces, whether an input field or an HTTP header field. However, when SQL is used in code at the application layer, it is often concatenated with input data from the presentation layer.

Input interfaces can be categorized as:

User input: Can be every field in the User Interface (UI) where a user can input data.
Cookies: Can be altered, because they reside on client-side which enables the user to inject malicious code into it.
HTTP Header: Also a type of input which can contain malicious SQL code.

All these entities contain data created on the client-side, which can also be altered there.

First-Order injection vs. Second-Order injections

In a First-Order injection attack, a flaw in a vulnerable application allows an attacker to modify the running code immediately by submitting a malicious input.

Second-Order injections involve attackers planting malicious inputs into a system or database, which are exploited when that input is used later. It occurs when user-submitted values are kept in the database without being sanitized and subsequently used by another application functionality.

Example

In the business logic of the application, a query is used to retrieve the data of a user based on username and password. The username and password are filled in by the user via a web form. The input goes directly into the query.


SELECT * FROM Users WHERE user ='userName' AND password='password';

Suppose the attacker gives myuser' -- as the user and leaves the password empty. That results in the following query that will be evaluated by the database:


SELECT * FROM Users WHERE user ='myuser' --' AND password=;

The -- starts a comment in SQL, ignoring everything after it. The query then corresponds to:


SELECT * FROM Users = 'myuser';

The SQL Query has successfully been manipulated by an injection.

Categories of SQLi Attacks

There are a number of SQL injection attacks that occur in different situations. Depending on the target of the attacker, the techniques can be used separately as well as together. Some of these attacks are listed below:

In-Band SQL Injection

Most attacks fall under in-band SQL injections. In-band means that the attacker can carry out both attacks and retrieve information via a single communication channel. This means that the results are returned on the same medium as the attack itself was executed.

Known examples for this category would be:

Tautology

In a Tautology SQL injection attack, the attacker tries to utilize a conditional query statement to evaluate it as always true. Furthermore, the attacker uses the "OR" clause to insert and effectively change a condition into a tautology, which is always true. Bypassing authentication, discovering injectable parameters, and extracting data are all examples of this attacking intent.

An example of this attack is a login form in a website that accepts the user-provided email address, and password, then submits them directly to the backend. The following code is executed against the database:


SELECT * FROM users WHERE email = $_POST[’email’] AND password = md5($_POST[‘password’]);

The values of the $_POST[] array are used straight in the above code without being sanitized and the MD5 algorithm is used to encrypt the password. When an attacker enters xy@yahoo.com' OR 1 = 1 -- into the email field the following code can be exploited:


SELECT * FROM users WHERE email = 'xy@yahoo.com' OR 1 = 1 --' AND password = md5('123');

The string quotation is completed with a single quote at the end of xy@yahoo.com.
OR 1 = 1 is a condition that is always true.
-- AND .... is a SQL comment that removes the password portion from the equation.

Union-Based SQLi

In this form of attack, the attacker uses the UNION operator to return records from another table. As a result of this attack, the database produces a dataset that is a union of results of the original query and the injected query. Bypassing authentication and obtaining data are the goals of this attack.

An example of a Union query:


SELECT * FROM Accounts WHERE user=’’ UNION SELECT *FROM Students—‘AND pass=’’AND eid=

The result of the first query in the example given above is null and the second one returns all the data in the Students table so the union of these two queries is the student table.

Error-based SQLi

Error-based SQLi is an in-band SQL injection technique that uses error messages of the database server to gather information about the structure of the database. An attacker can sometimes enumerate an entire database using only error-based SQL injection. syntax, type conversion, or logical error. While errors are useful during the development phase of a web application, they should be hidden on production or logged to a secure file, therefore no vulnerable/injectable parameters can be revealed to an attacker.

Blind SQLi

An Blind SQLi attack is a technique in which the attacker asks a database a series of questions and then extracts the replies. Following that, the attacker decides on their next line of action based on the responses of the database. Because the attacker has no prior knowledge of the database or the replies that are generated, this is considered a challenging SQLi assault. Identifying injectable parameters, extracting data, and determining database schema are all part of the attacking intent. The Boolean-Based SQLi attack and the Time-Based SQLi attack are two most common types of blind SQLi attacks.

Boolean-based SQLi

In this technique, the information must be inferred from the behavior of the page by asking the server true/false questions. If the injected statement evaluates to true, the page continues to function normally. If the statement evaluates to false, although there is no descriptive error message, the page differs significantly from the normally-functioning page.

An example of Boolean-based SQli:


SELECT accounts FROM users WHERE login = ‘user’ and 1=0 -- ‘ AND password=‘ ‘ AND pin = 0
SELECT accounts FROM users WHERE login = ‘user’ and 1=1 -- ‘ AND password=‘ ‘ AND pin = 0

1=0 False
1=1 True

Time-based SQLi

The Timing attack allows an attacker to gather information from the response time of the database by executing an injected query in the form of an if/then statement and the WAITFOR keyword. This causes a delay along with the branches in the database response for a specific amount of time. The attacker can then determine which branch was chosen in their injection by analyzing the increase or decrease in database response time as well as the answer to the injected question.

In the example the attacker tries to find the first character of the first table by comparing its ASCII value with X. If there is a 9-second delay in the response time, they realize that the answer to this question is true. So by continuing the process the name of the first table can be discovered.

An example:

SELECT * FROM Accounts WHERE user=’user1’ AND ASCII (SUBSTRING((SELECT TOP 1 name FROM sysobjects),1,1))>X WAITFOR DELAY ‘000:00:09’- -‘AND PASS=’‘ AND eid=

Out-of-band SQLi

Unlike traditional SQL injections, which rely on the standard communication channels between the attacker and the targets database, out-of-band attacks involve retrieving data through alternative channels. Out-of-band SQL injection typically uses DNS requests, HTTP requests, or other network protocols to transmit data to the attacker's machine. Such is the case with Microsoft SQL Server’s xp_dirtree command, which can be used to make DNS requests to a server an attacker controls; as well as Oracle Database’s UTL_HTTP package, which can be used to send HTTP requests from SQL and PL/SQL to a server an attacker controls.

Prevention

Despite the variety of SQL injection attacks, prevention is straightforward and primarily involves better handling of user data. Although certain treatments have drawbacks, they have shown to be helpful in defending. The following are some SQL attack defense strategies.

Prepared statement

The prepared statement was created to make SQL more efficient, but it is also provided a better security mechanism. This technique defines all SQL code beforehand and only adds arguments later. Because user-provided parameters are not sent directly to the database, even if the attacker passes a SQL command in the user input, it will not be included in the final SQL statement at runtime. This function inserts a question mark (?) in the query's input field, then uses setString() (in the case of Java programming language) or other methods in other languages to provide the parameter as user input.


PreparedStatement ps=conn.prepareStatement ("SELECT * FROM users_data WHERE username=? AND password=?");
ps.setString(1, username);
ps.setString(2, password);
resultset = ps.executeQuery();

A prepared statement can take many different forms and be written in a variety of languages.

Stored procedure

Stored procedures prevent SQL injection in the same way as prepared statements do, but the difference is that stored procedures are defined and stored in the database itself by a programmer and after that, they are called by the application. Store procedure sits between application and database, therefore the user is not able to directly read or write from the database. A stored procedure is not always protected from SQL injection, if the developer uses a dynamic query inside it. It is the developer's responsibility to avoid using dynamic queries inside a stored procedure to prevent SQL injection.

Whitelisting/Blacklisting

Whitelisting is the practice of only accepting information that is known to be good. Before accepting the input for further processing, it should be assured that it complies with the expected known values, type, length or size, numeric range, or other format criteria.

When implementing whitelist validation, consider the following:

Known Values: Does the input match a predefined set of safe, well-known values? Can its correctness be verified?
Data Type: Is the input of the correct data type? For example, is it a number where expected?
Data Size: Does the input conform to the expected length or size?
Data Range: If a numeric input is required, is it within the acceptable range (e.g., positive vs. negative numbers)?
Data Content: Does the input align with the expected character set or format? For instance, is it restricted to alphanumeric characters? Regular expressions are commonly used to enforce such content validation.

The inverse of whitelisting is blacklisting, which refers to not allowing characters or words that have been defined in a developer's blacklist. Any input that is listed in the blacklist is automatically eliminated or causes an error. Blacklisting is generally considered to be less safe than whitelisting as it only blocks known harmful inputs and may overlook new or unexpected attack vectors.

Use the principle of least privilege

The idea of least privilege both avoids SQL injection attacks and mitigates their impact if they do occur. Instead of granting users access to the entire database, this strategy allows them to simply access the tables they require. They should be given only the privileges they require, such as read-only, write-only, or read and write, depending on their needs. The concept of least privilege is a cornerstone of security, and it also applies to SQL injections. As a result, the impact of SQL injection is reduced.

Web Application Firewalls (WAFs)

Implementing a firewall can block potential attacks before they reach the web application. The firewall should filter out entries with binary data, escape sequences, and comment characters. Multiple layers of validation can be added and unvalidated user input can be concatenated.

Detection

Standard SQL injection Detection Software are Fuzzing tools, dynamic analysis tools and Web Application Firewalls. Nowadays (2024) Machine Learning (ML) and Deep Learning (DL) Detection are coming in handy for IT Security. With ML and DL the Security keeps up to date and less likley expires, when new SQL injection attacks are evolving.

An example is the Detection with the CNN-BiLSTM Algorithm. Their Detection process looks like this:

References