SQL injection (SQLi)

From Embedded Lab Vienna for IoT & Security
Revision as of 22:00, 18 December 2021 by SBarakati (talk | contribs) (→‎Tautology)
Jump to navigation Jump to search

Summary

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

SQL injection

SQL injection, also known as SQLI, is one of the most prevalent and threatening types of attack. This type of security threat is defined as a web application vulnerability that allows an attacker to inject SQL commands into the program. The core reason for this is usually due to improper or inadequate coding methods. 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.

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 First-order injection attack a flaw in a vulnerable application allows an attacker to modify the running code immidiately by submitting a malicious input. 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:

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 attack's 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 will be 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 type of query, an unauthorized query is attached with the authorized query by using the UNION clause. 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 the database server's error messages to gather information about the database's structure. An attacker can sometimes enumerate an entire database using only error-based SQL injection. While errors are useful during the development phase of a web application, they should be deactivated on a live site or logged to a secure file.

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 database's responses. Because the attacker has no prior knowledge of the database or the replies that will be generated, this is considered a challenging SQLi assault. The Boolean-Based SQLi attack and the Time-Based SQLi attack are two 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

In the Timing attack, the attacker gathers information about the response time of the database. This technique is used by executing the if-then statement which results in the long-running query or time delay statement depending upon the logic injected in the database and if the injection is true then the “WAITFOR” keyword which is along with the branches delays the database response for a specific time. 

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=

Prevention Methods

Despite the fact that SQL has so many different sorts of attacks. Fortunately, prevention is very simple, as the majority of the solution comprises better handling of the user's data. Although certain treatments have drawbacks, they have shown to be helpful in defending. The following are some SQL attack defense strategies.

Prepared statement

Instead of using dynamic queries, which are vulnerable to SQL injection attacks, this solution involves defining all the SQL code first and then giving the arguments for it. Even if the attacker passes a SQL command in the user input for a parameterized query, he will not be able to do so in the query's content. The following is an example of this method: Assuming that passing "username" and "password" to a query is necessary, the Java code will be as follows: 

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

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. To prevent SQL injection, it is the developer's responsibility to avoid using dynamic queries inside a stored procedure.

Escaping User provided input

If dynamic queries cannot be avoided, escaping all user-supplied parameters is the best option. This methodology is evolved by escaping all user input, whether by URLs, the POST method, or any other sort of input, and then inserting these values into the query. This is done so that the DBMS can tell the difference between user input and the SQL 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 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's supposed to be numeric?
  • Data range: Is it a negative number if it's 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 will be automatically eliminated or cause 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.


References

Retrieved from "https://wiki.elvis.science/index.php?title=SQL_injection_(SQLi)&oldid=8321"