SQL injection (SQLi)

From Embedded Lab Vienna for IoT & Security
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. When a SQL statement is transmitted to the database as part of a parameter's value without being sanitized or escaped. An unauthenticated user who does not know the password can transmit a constructed input that looks something like this. A login form in a website 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 site 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();

Escaping User provided input

If dynamic queries cannot be avoided, escaping all user-supplied parameters is the best option. Then the developer should identify the all input sources to define the parameter that needs escaping, follow database-specific escaping procedures, and use standard defining libraries instead of the custom escaping methods.

Whitelisting/Blacklisting

The term "whitelist" refers to permitting only those words/characters in an input field that the developer has established. For example, most usernames do not contain special characters, hence special characters in the username field should be limited, and it is not going to be accepted. Any additional submission or input will be ignored.

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.

Data type validation

After completing the procedures for the parameterized query and escaping, the developer must ensure that the input data type is correctly validated. The developer must specify if the input data type is a string, numeric, or any other kind, and if the user's input data is erroneous, it will be easily rejected.

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=8302"