SQL injection (SQLi)

Summary

This Document explains SQL injection, how it can be performed, its impacts on a vulnerable application, different types of SQL injection attacks, and the prevention methods.

SQL injection

SQL injection vulnerabilities have been described as one of the most serious threats for Web applications. This type of security threat is defined as a vulnerability in Database Driven Web Applications that allows an attacker to inject SQL commands into a vulnerable program. A successful SQL injection exploit can read sensitive data from the database, modify database data, perform database administration operations such as shutting down the Database management system (DBMS), recovering 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, Generous privileges, Uncontrolled variable size, Error messages, Variable orphism, Unsensitized user input, Client-side control, and Multiple statements are some of the sources of SQL injection.

Impacts of SQLI

This vulnerability can affect the main aspects of security CIA triad named:

• Confidentiality,
• Integrity,
• Authentication, and
• Availability.

SQLi Mechanisms

Many different approaches can be taken for injecting malicious queries into a vulnerable application. The most common mechanisms are divided into two categories: First-Order injections and Second-Order injections.

First-Order injection

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. The First-Order injection is divided into 3 subcategories:

1. Injection via human input
2. Injection via cookies
3. Injection through server variables

Second-Order injections

Second-Order injections involve attackers planting malicious inputs into a system or database to cause an SQLi attack when that input is used later. It occurs when user-submitted values are kept in the database without being sanitized or escaped and subsequently used by another application functionality without being escaped or filtered.

SQLI Attacks

SQL injection attacks can happen in a variety of ways and in different contexts. The strategies can be used independently or jointly, depending on the attacker's goal. Some of these SQLi attacks are listed below:

https://doi.org/10.1145/3287324.3287490

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 "WHERE" 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 Email = ‘xx.com‘ OR 1 = 1 – the following code can be exploited:

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

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

In-band SQLi

The most common and easy to exploit SQL Injection attack is in-band SQL Injection. When an attacker is able to initiate the attack and collect the results using the same communication channel, this is known as in-band SQL injection. The two most common types of in-band SQL injection are error-based SQLi and Union-based SQLi.

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 [3]. 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.

Inferential SQLi

An Inferential 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 his 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.

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 web application and the database, out-of-band attacks involve retrieving data through alternative channels. This technique is particularly effective when the web server is unable to communicate directly with the system controlled by the attacker. 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 Methods

Despite the fact that SQL has so many different sorts of attacks, prevention is very simple, as the majority of the solution comprises better handling of the user data of the user. 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 involves defining all the SQL code first and then giving the arguments for it, and if the query does not generate any error, then it is sent to the database. 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. It uses the prepareStatement() function in Java, which requires a bind variable. Other languages, such as PHP, employ the bindParam function (). It also leverages bind variables with .NET functions like sqlCommand() and OleDbCommand()

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.

Escaping User provided input

All user-provided input should be escaped, whether by URLs, the POST method, or any other sort of input, and then these values can be inserted into the query. By doing this, the DBMS can separate the user's input from the query itself.

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 using whitelist validation, the following points should be considered:

• Known Value: Does the data consist of only well-known values? Can the data be tested to be correct?
• Data type: Is there a problem with the data type?
• Data size: Is the value numeric if it is supposed to be numeric?
• Data range: Is it a negative number if it is supposed to be a positive number?
• Data content: Does the data appear to be of the expected type? Is it only the expected character set for the expected data type? Regular expressions are a common way to implement 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.

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 also 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 because it only impacts one table or a set of tables rather than the entire database. In some circumstances, instead of allowing access to the entire table, a view for that piece of the table should be established.

SQL Server Firewalling

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.

References

• https://ieeexplore.ieee.org/document/9445347
• https://ieeexplore.ieee.org/document/8880301
• https://www.researchgate.net/publication/249773840_A_Classification_of_SQL_Injection_Attacks_and_Countermeasures
• https://www.w3.org/Protocols/rfc2616/rfc2616.html
• https://resources.infosecinstitute.com/topic/sql-injection-http-headers
• https://www.researchgate.net/publication/322735440_Detection_and_Prevention_of_SQL_Injection_Vulnerabilities_in_Web_Applications_A_Review
• http://www.gtisc.gatech.edu/bio aucsmith.html