SQL Injection: Difference between revisions

From Elvis Wiki
No edit summary
 
(29 intermediate revisions by 7 users not shown)
Line 1: Line 1:
== SQL Injection ==  
== SQL Injection ==


SQL Injection is a vulnerability, which can be found in applications, especially web applications. According to a study conducted in 2019 by OWASP, SQL injections count to the most common way for attackers to fetch sensitive data from a database. SQL injections are simply possible if SQL queries in the application provide results based on user input, while the user input is processed directly in the SQL query.
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 ==
== Functionality ==


SQL injections are basically possible if SQL queries in the application deliver results based on user input. The user input is processed directly by the SQL interpreter without changes or validation. In this case, the database cannot distinguish between malicious or good entries.
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 ==
 
[[File:SQLi Example.png|600px|thumb|right|Functional Principle of the Example]]
[[File:SQLi_WebIF_Example.png|400px|thumb|right|Web Interface 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.
 
<code>
SELECT * FROM Users WHERE user ='userName' AND password='password';
</code>
 
Suppose the attacker gives  <code>myuser' --</code> as the user and leaves the password empty. That results in the following query that will be evaluated by the database:


An attack could look like this:
<code>
SELECT * FROM Users WHERE user ='myuser' --' AND password='';
</code>


=== Step 1 ===
The <code>--</code> starts a comment in SQL, ignoring everything after it. The query then corresponds to:
In the business logic of the application, a query is used to retrieve the data of an 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 name ='userName' and password='password';
<code>
=== Step 2 ===
  SELECT * FROM Users = 'myuser';
Suppose the attacker gives admin as the username and ' or '1' = '1' as a password. That results in the following query that will be evaluated by the database:
  </code>
  SELECT * FROM Users WHERE name ='admin' and password='' or '1'='1';


=== Step 3 ===
The SQL Query has successfully been manipulated by an injection.
The above SQL query is valid and returns all tuples from the Users relation. By adding the OR condition '1' = '1', the WHERE clause is always evaluated as true. The query then corresponds to:
SELECT * FROM Users;


== Categories of SQLi Attacks ==


=== Step 4 ===
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:
Furthermore, several SQL statements can be executed simultaneously on many database servers by separating them with semicolons. The attacker can thus insert additional commands, for example, to delete a relation with a call.


The attacker enters the following in the password field:
=== In-Band SQL Injection ===
' or '1'='1'; DROP TABLE Users; SELECT * FROM info WHERE '1' = '1


This results in the following queries:
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.
SELECT * FROM Users WHERE name ='admin' and password=' 'or '1'='1'; DROP TABLE Users; SELECT * FROM info WHERE '1' = '1'


When the database server processes these two queries, it first returns all data records from the Users relation and then deletes or removes the entire Users table.
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.


== Types of SQL queries ==
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:


There are a number of SQL injection attacks that occur in different situations.
<code>
Depending on the target of the attacker, the techniques can be used separately as well as together.
SELECT * FROM users WHERE email = $_POST[’email’] AND password = md5($_POST[‘password’]);
These are for example:
</code>


=== Tautologies ===
The values of the <code>$_POST[]</code> 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 <code>xy@yahoo.com' OR 1 = 1 --</code> into the email field the following code can be exploited:
The goal of the Tautologies attack is to find injectable parameters in the application to bypass authentication and initially extract data.
The basic idea here is to always set the where clause in the SQL statement to true so that the condition is ignored. The most common tautology is or 1 = 1. By concatenating the operator or and 1 = 1, another condition is set that is always true, so that the result of the entire condition is true. An example of this can be found in the previous chapter.


=== Union Query ===
<code>
SELECT * FROM users WHERE email = 'xy@yahoo.com' OR 1 = 1 --' AND password = md5('123');
</code>


In the case of a UNION query attack, the keyword 'UNION' is inserted in the input in order to retrieve data from other relations in the database. UNION enables the execution of several independent SQL queries in addition to the main instruction. The results of the further queries are appended to the results of the legitimate SQL query.
* The string quotation is completed with a single quote at the end of xy@yahoo.com.
* <code>OR 1 = 1</code> is a condition that is always true.
* <code>-- AND</code> .... is a SQL comment that removes the password portion from the equation.


As a prerequisite, the attacker must have information about the database in order to extract data using UNION. These include e.g. Information about table names and their respective attributes, with which the further queries can be created and linked to the original statement. Furthermore, the individual queries must return the same number of columns and the data types of the attributes must be compatible between the individual queries.
==== Union-Based SQLi ====
The attacker can use the ORDER BY clause to derive the number of columns from the results. The clause is added to the statement and the column index is increased until an error message occurs. The columns in a relation can be specified in the ORDER BY clause by an index, so you do not need to know the column names.


Example of a UNION query attack:
In this form of attack, the attacker uses the <code>UNION</code> 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.
SELECT accounts FROM Users WHERE username=''username'' and password=''password''


The attacker extends the legitimate instruction by entering the following in the password field:
An example of a Union query:


  ' 'UNION SELECT cardNo FROM Credit Cards WHERE acctNo=123456 -- ',
  <code>
SELECT * FROM Accounts WHERE user=’’ UNION SELECT *FROM Students—‘AND pass=’’AND eid=
</code>


This results in the following query:
* 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.


SELECT accounts FROM users WHERE login=' ' UNION SELECT cardNo from CreditCards
==== Error-based SQLi ====
where acctNo=123456


The first statement returns zero. However, the second query is performed and returns the card number from the account number 123456.
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.


=== SQL Blind SQL Injection ===
=== Blind SQLi ===


Error messages provide the attacker with information on how he can continue his attack against the database. So it is usually a tool for attackers. With Blind SQl Injection, the attacker assumes that the error messages from the database are deactivated and still tries to executes the SQL injection.
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.
It is assumed that a website retrieves its user data from the database using a UserId. The ID is transferred via the URL:
To check whether an SQL injection is possible, the following condition (and 1 = 2) is added.


http://newspaper.com/items.php?id=2 and 1=2
==== Boolean-based SQLi ====


The actual SQL query that is executed is:
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.


SELECT title, description, body FROM items WHERE ID = 2 and 1=2
An example of Boolean-based SQli:


In order for this assertion to be confirmed, the attacker builds a condition that returns true. This would be, for example, and 1 = 1, since 1 = 1 is always correct:
<code>
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
</code>


http://newspaper.com/items.php?id=2 and 1=1
* 1=0 False
* 1=1 True


=== Procedure Stored procedure attacks ===
==== Time-based SQLi ====


The basic idea behind stored procedure attacks is to execute a stored database procedure. The attacker determines the database type and uses this knowledge to find out which procedures exist. The attacker first determines the database type and uses this knowledge to determine which stored procedures may exist. There appear to be several threats against which the database is vulnerable, such as the escalation of permissions, SQL injection buffer overflow and the gathering of extended information, since these are based on stored procedures. The following is a stored procedure that returns a description of its products using the buyer's first name.
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 <code>WAITFOR</code> 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.


CREATE PROCEDURE getDescription
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.
@vname VARCHAR(50)
AS
EXEC('SELECT description FROM products WHERE name =
'''+@vname+ '''')
RETURN


Dabei wird der Vorname von dem/der KäuferIn eingegeben. Der Angreifer gibt jedoch statt dem Namen eine wahre Bedingung.
An example:


AND 'a'='b' UNION SELECT password FROM members WHERE
<code>
username='admin
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=
</code>


Beim Ausführen der Bedingung ergibt sich die folgende Prozedur:
=== Out-of-band SQLi ===


CREATE PROCEDURE getDescription
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.
@vname VARCHAR(50)
AS
EXEC('SELECT description FROM products WHERE name ='z' AND
'a'='b' UNION
SELECT password FROM members WHERE username='admin'')
RETURN


== Prevention ==
== Prevention ==
The countermeasures for SQL injection, apart from the type of attack, are mainly the same. The root cause of SQL injections is the lack of input validation. Therefore, the simple solution to address these vulnerabilities is to use appropriate defensive coding. If you are working with user input that is initially used in SQL queries, it should be passed to the query via parameters and validated using defined regular expressions, also known as input whitelisting.


* Check the input type by parameterizing
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.
* Positive pattern comparison
 
* Filter input data
=== Prepared statement ===
* Avoid error messages
 
* Least privilege
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 (<code>?</code>) in the query's input field, then uses <code>setString()</code> (in the case of Java programming language) or other methods in other languages to provide the parameter as user input.
 
<code>
PreparedStatement ps=conn.prepareStatement ("SELECT * FROM users_data WHERE username=? AND password=?");
ps.setString(1, username);
ps.setString(2, password);
resultset = ps.executeQuery();
</code>
 
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 ==


== Practice ==
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.


=== Burp suite ===
An example is the Detection with the CNN-BiLSTM Algorithm. Their Detection process looks like this:
Burp Suite is a tool for performing penetration testing of web applications. With the Burp Suite, administrators can intercept and manipulate HTTP / HTTPS traffic to web applications before it is sent to the server. This enables security gaps in web applications to be discovered quickly and effectively.


=== Damn Vulnerable Web App (DVWA) ===
[[File:SQLi CNN-BiLSTM.png|500px|SQLi_CNN-BiLSTM]]
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is damn vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, help web developers better understand the processes of securing web applications and aid teachers/students to teach/learn web application security in a class room environment.


== References ==
== References ==


* https://ieeexplore.ieee.org/document/9445347
* https://ieeexplore.ieee.org/document/8880301
* https://ieeexplore.ieee.org/document/10481914
* https://ieeexplore.ieee.org/document/9410675
* https://ieeexplore.ieee.org/document/10250505
* https://ieeexplore.ieee.org/document/6400112
* https://ieeexplore.ieee.org/document/6400112
* https://ieeexplore.ieee.org/abstract/document/6396096
* 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/bioaucsmith.html
* https://pdfs.semanticscholar.org/81a5/02b52485e52713ccab6d260f15871c2acdcb.pdf/
* https://pdfs.semanticscholar.org/81a5/02b52485e52713ccab6d260f15871c2acdcb.pdf/
* https://ieeexplore.ieee.org/abstract/document/6396096
* http://web.archive.org/web/20210302071805/https://www.dbcybertech.com/pdf/sql-injection-detection-web-environment.pdf
* https://www.dbcybertech.com/pdf/sql-injection-detection-web-environment.pdf
* https://www.cisecurity.org/wp-content/uploads/2017/05/SQL-Injection-White-Paper2.pdf
* https://www.cisecurity.org/wp-content/uploads/2017/05/SQL-Injection-White-Paper2.pdf
* https://info.sucuri.net/hubfs/images/owasp-ebook-2019/sucuri-ebook-OWASP-top-10.pdf
* https://info.sucuri.net/hubfs/images/owasp-ebook-2019/sucuri-ebook-OWASP-top-10.pdf
* http://web.archive.org/web/20200125164747/http://www.dvwa.co.uk/
* https://owasp.org/www-project-top-ten/


[[Category:Documentation]]
[[Category:Pentesting]]

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:

  1. User input: Can be every field in the User Interface (UI) where a user can input data.
  2. Cookies: Can be altered, because they reside on client-side which enables the user to inject malicious code into it.
  3. 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

Functional Principle of the Example
Web Interface 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:

SQLi_CNN-BiLSTM

References

Retrieved from "https://elvis.hcw.ac.at/wiki/index.php?title=SQL_Injection&oldid=17563"