SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally allows an attacker to view data that they are not normally able to retrieve. This might include data belonging to other users, or any other data that the application itself is able to access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application's content or behavior.
In some situations, an attacker can escalate a SQL injection attack to compromise the underlying server or other back-end infrastructure, or perform a denial-of-service attack.
Types of SQLi
1. In-band SQLi
1.1 Error-based SQLi
1.2 Union-based SQLi
2. Inferential (Blind) SQLi
2.1 Boolean SQLi
2.2 Time-based SQLi
3. Out-of-band SQLi
1. In-band SQLi: The attacker uses the same channel of communication to launch their attacks and to gather their results. In-band SQLi’s simplicity and efficiency make it one of the most common types of SQLi attack. There are two sub-variations of this method:
1.1 Error-based SQLi: Error-based SQL injection occurs when an attacker deliberately injects malicious SQL code into a web application's input fields, which is then executed by the application's database server. If the application is not properly handling and validating user input, the injected SQL code may result in errors being generated by the database server. These error messages are then returned to the attacker by the application, revealing sensitive information about the database structure, data, or other internal details.
The error messages generated by the database server can provide valuable information to the attacker, such as column names, table names, and even the actual data stored in the database. By carefully crafting SQL injection payloads, an attacker can trigger different types of errors and analyze the error messages to gain insights into the underlying database structure and data. This information can be used to further exploit the vulnerability and potentially gain unauthorized access to the database or perform other malicious actions.
Example: Consider a web application that allows users to search for products by name and the application constructs an SQL query like this:
SELECT * FROM products WHERE name = '<user_input>';
If the application does not properly validate and sanitize the user input, an attacker could inject malicious SQL code like this:
' OR 1=1; --'
The resulting SQL query would become:
SELECT * FROM products WHERE name = '' OR 1=1; --'';
This would cause the database server to generate an error because of the extra quotes and the commented-out portion (--) at the end. The error message may then be returned by the application to the attacker, revealing information about the database structure or data, which can be used for further attacks.
Prevention: To prevent error-based SQL injection, it is important to properly validate and sanitize all user input that is used in SQL queries, and use prepared statements or parameterized queries instead of dynamically constructing SQL queries with user input. Additionally, limiting the amount of detailed error information exposed to users can also help mitigate the risk of error-based SQL injection attacks.
1.2 Union-based SQLi: Union-based SQL injection is a type of SQL injection attack where an attacker injects malicious SQL code into a web application's input fields to manipulate the query's results using the SQL UNION operator. The UNION operator is used in SQL to combine the results of two or more SELECT statements into a single result set.
The basic idea behind a union-based SQL injection is to inject a SELECT statement that retrieves additional data from another table or source, and then combine it with the original query's results using the UNION operator. This allows the attacker to retrieve data from other tables, even if the original query was not designed to fetch that data.
Example: Consider a web application that constructs an SQL query like this:
SELECT id, name, age FROM users WHERE id = '<user_input>';
If the application does not properly validate and sanitize the user input, an attacker could inject malicious SQL code like this:
' UNION SELECT cc_number, cc_expiry, cc_cvv FROM credit_cards; --
The resulting SQL query would become:
SELECT id, name, age FROM users WHERE id = '' UNION SELECT cc_number, cc_expiry, cc_cvv FROM credit_cards; --';
The attacker's injected SELECT statement retrieves credit card information from the credit_cards table, and the UNION operator combines the results of the original query with the injected query. The -- at the end is a comment to ensure that the rest of the original query is ignored and does not cause any syntax errors.
The combined result set of the UNION operation is then returned by the application to the attacker, potentially revealing sensitive information about credit card data that was not intended to be exposed.
Prevention: To prevent union-based SQL injection, it is important to properly validate and sanitize all user input that is used in SQL queries, and use prepared statements or parameterized queries instead of dynamically constructing SQL queries with user input. Additionally, access control measures should be implemented to limit the permissions and privileges of the database user used by the application, and minimize the amount of sensitive information that can be retrieved in case of a successful SQL injection attack.
2. Inferential (Blind) SQLi: The attacker sends data payloads to the server and observes the response and behavior of the server to learn more about its structure. This method is called blind SQLi because the data is not transferred from the website database to the attacker, thus the attacker cannot see information about the attack in-band.
Blind SQL injections rely on the response and behavioral patterns of the server so they are typically slower to execute but may be just as harmful. Blind SQL injections can be classified as follows:
2.1 Boolean: Boolean-based SQL injection is a type of SQL injection attack where an attacker exploits a web application vulnerability to infer information about the underlying database by manipulating the application's behavior based on boolean (true/false) conditions. This type of SQL injection is based on the concept of injecting SQL queries that return boolean-based responses, and then analyzing the application's response to determine whether a particular condition is true or false.
The attacker typically injects malicious SQL code into the application's input fields, such as search forms or login forms, in order to manipulate the behavior of the underlying SQL query. The injected SQL code is crafted in a way that allows the attacker to infer information about the database, such as the presence of certain data, the number of rows in a table, or the structure of the database.
Example: Consider a web application that constructs an SQL query like this:
SELECT * FROM users WHERE username = '<user_input>' AND password = '<password>';
If the application does not properly validate and sanitize the user input, an attacker could inject malicious SQL code like this:
' OR 'a'='a
The resulting SQL query would become:
SELECT * FROM users WHERE username = '' OR 'a'='a' AND password = '';
The injected condition 'a'='a' is always true, and the injected OR statement bypasses the original username and password check. If the application responds differently when the condition is true or false, the attacker can infer information about the database based on the application's response. For example, the attacker could keep injecting different conditions and observe whether the application responds with an error or displays different behavior, indicating the presence or absence of certain data.
Boolean-based SQL injection attacks can be more challenging and time-consuming than other types of SQL injection, as the attacker needs to carefully craft boolean conditions and analyze the application's response to infer information about the database. However, it can be effective in cases where other types of SQL injection may be detected or prevented by security measures such as input validation or prepared statements.
Prevention: To prevent boolean-based SQL injection, it is important to properly validate and sanitize all user input that is used in SQL queries, and use prepared statements or parameterized queries instead of dynamically constructing SQL queries with user input. Additionally, access control measures should be implemented to limit the permissions and privileges of the database user used by the application, and minimize the amount of sensitive information that can be inferred from boolean-based SQL injection attacks.
2.2 Time-based SQLi: Time-based SQL injection is a type of SQL injection attack where an attacker exploits a vulnerability in a web application to infer information about the underlying database by manipulating the application's response time. This type of SQL injection is based on the concept of injecting SQL queries that contain time delays, and then analyzing the application's response time to determine whether a particular condition is true or false.
The attacker typically injects malicious SQL code into the application's input fields, such as search forms or login forms, in order to manipulate the behavior of the underlying SQL query. The injected SQL code is crafted in a way that introduces time delays into the query execution, allowing the attacker to infer information about the database based on the application's response time.
Example: Consider a web application that constructs an SQL query like this:
SELECT * FROM users WHERE username = '<user_input>' AND password = '<password>';
If the application does not properly validate and sanitize the user input, an attacker could inject malicious SQL code like this:
' OR SLEEP(5)--
The resulting SQL query would become:
SELECT * FROM users WHERE username = '' OR SLEEP(5)--' AND password = '';
The injected SLEEP(5) function introduces a time delay of 5 seconds in the query execution. If the application takes longer to respond when the condition is true (i.e., the sleep function is executed), the attacker can infer that the injected condition is true and that the underlying database contains certain data. By injecting different time delays and analyzing the application's response time, the attacker can gradually infer information about the database, such as the presence of certain data, the number of rows in a table, or the structure of the database.
Time-based SQL injection attacks can be stealthy and difficult to detect, as they do not generate error messages or unusual behavior in the application's response. However, they can be slow and resource-intensive, as the attacker needs to introduce time delays into the query execution and wait for the application's response time to determine the outcome of the injected condition.
Prevention: To prevent time-based SQL injection, it is important to properly validate and sanitize all user input that is used in SQL queries, and use prepared statements or parameterized queries instead of dynamically constructing SQL queries with user input. Additionally, access control measures should be implemented to limit the permissions and privileges of the database user used by the application, and minimize the amount of sensitive information that can be inferred from time-based SQL injection attacks. Monitoring and analyzing the application's response time for unusual delays can also help in detecting and mitigating time-based SQL injection attacks.
3. Out-of-band SQLi: Out-of-band SQL injection, also known as Out-of-Band (OOB) SQLi, is a type of SQL injection attack where an attacker exploits a web application vulnerability to extract information from a database using a different channel or communication method than the one used by the application's response. This type of SQL injection is based on the concept of injecting SQL queries that trigger external communication or data transfer, allowing the attacker to extract information from the database through a separate channel.
Out-of-band SQL injection attacks typically involve injecting malicious SQL code into the application's input fields, such as search forms, comment fields, or other user input fields. The injected SQL code is crafted in a way that triggers communication with an external server or service controlled by the attacker, allowing them to extract information from the database.
Example: Consider a web application that constructs an SQL query like this:
SELECT * FROM users WHERE username = '<user_input>' AND password = '<password>';
If the application does not properly validate and sanitize the user input, an attacker could inject malicious SQL code like this:
The injected UNION SELECT statement is used to append a new SELECT statement to the original query, which retrieves the username and password columns from the users table. The INTO OUTFILE clause is used to specify an external file or URL controlled by the attacker where the retrieved data should be written.
When the application executes the injected SQL code, it triggers an external communication to the attacker's server or service, effectively transferring the extracted data from the database to the attacker through a separate channel. The attacker can then analyze the data captured on their server to obtain sensitive information from the database, such as usernames, passwords, or other confidential data.
Out-of-band SQL injection attacks can be stealthy and difficult to detect, as they do not rely on the application's response to extract information from the database. Instead, they involve external communication that may not be visible in the application's logs or monitoring systems. However, they may require additional setup and coordination by the attacker to establish the communication channel and capture the extracted data.
Prevention: To prevent out-of-band SQL injection, it is important to properly validate and sanitize all user input that is used in SQL queries, and use prepared statements or parameterized queries instead of dynamically constructing SQL queries with user input. Additionally, access control measures should be implemented to limit the permissions and privileges of the database user used by the application, and minimize the amount of sensitive information that can be extracted from the database through out-of-band channels. Monitoring and analyzing external communication from the application to detect and block unauthorized data transfers can also help in detecting and mitigating out-of-band SQL injection attacks.