Security Testing: SQL Injection

SQL injection is a type of security vulnerability that can allow an attacker to access or modify data stored in a database. The basic idea behind SQL injection is to exploit a web application’s failure to properly sanitize user-supplied input, which allows the attacker to inject SQL code into the database query.

There are several techniques that attackers may use to carry out SQL injection attacks:

Union-based SQL injection

Union-based SQL injection is a common technique used by attackers to extract data from a database. It involves adding a “union select” statement to the end of an existing SQL query in order to retrieve additional data from a different table in the same database.

Here’s an example of a union-based SQL injection attack:

Suppose we have a web application that takes a user ID as input and returns the corresponding username:

SELECT username FROM users WHERE id = 'input';

An attacker may attempt to inject SQL code into this query in order to retrieve additional data from another table in the same database. Here’s what the modified query might look like:

SELECT username FROM users WHERE id = '1' UNION SELECT password FROM passwords;

In this example, the attacker has added a “union select” statement to the end of the query, which retrieves data from the “passwords” table in addition to the “users” table. If the input provided by the user is not properly sanitized, the attacker can submit a specially crafted input that includes the injected SQL code.

If the attack is successful, the application will return the password from the “passwords” table along with the username from the “users” table. This can be a serious security vulnerability, as it allows an attacker to access sensitive data from the database.

To prevent this type of attack, it is important to properly sanitize user input and use prepared statements or parameterized queries instead of building SQL statements dynamically with user input.

Error-based SQL injection

Error-based SQL injection is a type of SQL injection attack that exploits errors in the SQL code to extract information from the database. Here’s an example of how an error-based SQL injection attack might be carried out:

Suppose we have a web application that takes a username and password as input and checks if the credentials are valid:

SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';

An attacker may attempt to inject SQL code into this query in order to extract information from the database. Here’s what the modified query might look like:

SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password' OR 1=1/0;

In this example, the attacker has added an “OR 1=1/0” statement to the end of the query. This will cause a “divide by zero” error, which will generate an error message that includes information about the structure of the database.

If the attack is successful, the application will display an error message that includes information about the database structure, such as the names of tables or columns. The attacker can use this information to carry out further attacks, such as a union-based SQL injection attack to extract data from the database.

To prevent this type of attack, it is important to properly sanitize user input and use prepared statements or parameterized queries instead of building SQL statements dynamically with user input. Additionally, error messages should be carefully crafted to avoid revealing sensitive information about the database structure.

Blind SQL injection

Blind SQL injection is a type of SQL injection attack where the application does not provide any feedback to the attacker. Instead of retrieving data directly from the database, the attacker uses techniques such as time delays or conditional queries to extract information from the database.

Here’s an example of how a blind SQL injection attack might be carried out:

Suppose we have a web application that takes a user ID as input and returns the corresponding username:

SELECT username FROM users WHERE id = 'input';

An attacker may attempt to inject SQL code into this query in order to extract information from the database. However, since the application does not provide any feedback to the attacker, the attacker must use a technique called “boolean-based blind SQL injection” to extract information.

Here’s what the modified query might look like:

SELECT username FROM users WHERE id = '1' AND substring((SELECT password FROM passwords LIMIT 1), 1, 1) = 'a';

In this example, the attacker has added a “substring” statement to the query, which extracts the first character of the password from the “passwords” table. The attacker uses a conditional statement to check if the first character is equal to ‘a’. If it is, the application will return the username for user ID 1. If not, the application will return an empty result set.

The attacker can repeat this process for each character of the password, using a combination of conditional statements and time delays to extract the entire password.

Blind SQL injection attacks can be difficult to detect and prevent, as they do not rely on the application providing any feedback to the attacker. To prevent blind SQL injection attacks, it is important to properly sanitize user input and use prepared statements or parameterized queries instead of building SQL statements dynamically with user input. Additionally, it is important to monitor the application for unusual behavior or traffic patterns that may indicate a blind SQL injection attack in progress.

Boolean-based SQL injection

Boolean-based SQL injection is a type of SQL injection attack where the attacker uses conditional statements to extract information from the database. Here’s an example of how a boolean-based SQL injection attack might be carried out:

Suppose we have a web application that takes a user ID as input and returns the corresponding username:

SELECT username FROM users WHERE id = 'input';

An attacker may attempt to inject SQL code into this query in order to extract information from the database. Here’s what the modified query might look like:

SELECT username FROM users WHERE id = 1 AND (SELECT ASCII(SUBSTRING((SELECT password FROM users WHERE id = 1),1,1))) = 97;

In this example, the attacker has added a nested query that retrieves the password for user ID 1 and extracts the first character of the password using the “SUBSTRING” and “ASCII” functions. The attacker uses a conditional statement to check if the ASCII value of the first character is equal to 97, which is the ASCII value for the letter ‘a’. If it is, the application will return the username for user ID 1. If not, the application will return an empty result set.

The attacker can repeat this process for each character of the password, using a combination of conditional statements and brute force techniques to extract the entire password.

Boolean-based SQL injection attacks can be difficult to detect and prevent, as they do not rely on obvious error messages or other feedback from the application. To prevent boolean-based SQL injection attacks, it is important to properly sanitize user input and use prepared statements or parameterized queries instead of building SQL statements dynamically with user input. Additionally, it is important to monitor the application for unusual behavior or traffic patterns that may indicate a boolean-based SQL injection attack in progress.

Out-of-band SQL injection

Out-of-band SQL injection is a type of SQL injection attack where the attacker uses a separate channel to extract information from the database. Instead of relying on the application to return data directly, the attacker uses techniques such as DNS requests or HTTP requests to extract information.

Here’s an example of how an out-of-band SQL injection attack might be carried out using DNS requests:

Suppose we have a web application that takes a user ID as input and returns the corresponding username:

SELECT username FROM users WHERE id = 'input';

An attacker may attempt to inject SQL code into this query in order to extract information from the database. Here’s what the modified query might look like:

SELECT username FROM users WHERE id = '1' OR (SELECT LOAD_FILE(CONCAT('/etc/passwd'))) IS NOT NULL;

In this example, the attacker has added a nested query that reads the contents of the “/etc/passwd” file using the “LOAD_FILE” function. The attacker uses a conditional statement to check if the query returns a non-null value, which indicates that the file has been successfully read.

The attacker can then use a DNS request to extract the contents of the file. Here’s what the DNS request might look like:

attacker-controlled-domain.com'; SELECT LOAD_FILE('/etc/passwd'); #

In this example, the attacker has added a DNS subdomain to the input value, which triggers the execution of the modified SQL query. The resulting SQL query will read the contents of the /etc/passwd file and return it to the attacker-controlled domain through the DNS request.

Out-of-band SQL injection attacks can be difficult to detect and prevent, as they do not rely on the application providing any feedback to the attacker. To prevent out-of-band SQL injection attacks, it is important to properly sanitize user input and use prepared statements or parameterized queries instead of building SQL statements dynamically with user input. Additionally, it is important to monitor the application for unusual behavior or traffic patterns that may indicate an out-of-band SQL injection attack in progress.

Second-order SQL injection

Second-order SQL injection, also known as stored procedure injection, is a type of SQL injection attack where the attacker injects malicious code into a stored procedure or function that is later executed by the database.

Here’s an example of how a second-order SQL injection attack might be carried out:

Suppose we have a web application that allows users to submit feedback that is stored in a database:

INSERT INTO feedback (username, comment) VALUES ('user-input-username', 'user-input-comment');

The application uses a stored procedure to process the feedback and send a confirmation email to the user:

CREATE PROCEDURE send_feedback_email @username VARCHAR(50), @comment VARCHAR(500)
AS
BEGIN
   DECLARE @subject VARCHAR(100);
   DECLARE @body VARCHAR(500);
   
   SELECT @subject = 'Feedback Received';
   SELECT @body = 'Dear ' + @username + ', we have received your feedback: ' + @comment;
   
   EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Default',
      @recipients = @username,
      @subject = @subject,
      @body = @body;
END

An attacker may attempt to inject SQL code into the feedback comment in order to modify the stored procedure and send the email to a different address. Here’s what the modified feedback might look like:

user-input-username', 'user-input-comment'); UPDATE feedback SET comment = 'dummy comment'; --

In this example, the attacker has injected a SQL statement that updates the feedback table with a dummy comment and comments out the rest of the original SQL statement. This injection does not cause any direct harm, but the dummy comment will be processed by the stored procedure and executed at a later time.

The attacker can then submit a second feedback with a modified comment that injects malicious code into the stored procedure. Here’s what the modified feedback might look like:

user-input-username', '''); DROP TABLE users; --'

In this example, the attacker has injected a SQL statement that closes the comment, drops the users table, and comments out the rest of the original SQL statement. The stored procedure will execute this modified SQL statement, causing the users table to be dropped.

Second-order SQL injection attacks can be difficult to detect and prevent, as the malicious code is injected into the database and executed at a later time. To prevent second-order SQL injection attacks, it is important to properly sanitize user input and use prepared statements or parameterized queries instead of building SQL statements dynamically with user input. Additionally, it is important to review the code and permissions of all stored procedures and functions to ensure that they do not allow for SQL injection vulnerabilities.

— — — — — — Happy Testing — — — —