- Updated at
- Reading time
SQL Injection attacks are still a threat to current web applications, despite their long history. In this article, we discuss the most common SQL Injection attack techniques with concrete examples from DVWA (Damn Vulnerable Web Application).
1. What is SQL Injection?
SQL Injection is a technique that allows an adversary to insert arbitrary SQL commands in the queries that a web application makes to its database. It can work on vulnerable web pages and apps that use a backend database like MySQL, Oracle, and MSSQL.
A successful attack can lead to unauthorized access to sensitive information in the database or to modifying entries (add/delete/update), depending on the type of the affected database. It also may be possible to use SQL Injection to bypass authentication and authorization in the application, shut down, or even delete the entire database.
2. How do SQL Injection attacks work?
We will see some concrete examples of multiple techniques that can be used to exploit SQL Injection vulnerabilities in web applications.
The target application in our case will be Damn Vulnerable Web Application (DVWA), which contains multiple types of vulnerabilities (SQLi, XSS, LFI, etc) and it is an excellent testbed for learning web security.
The types of SQL Injection attacks that we’ll discuss are:
2.a. Error-based SQL Injection
One of the most common types of SQL Injection vulnerabilities, it is also quite easy to determine. It relies on feeding unexpected commands or invalid input, typically through a user interface, to cause the database server to reply with an error that may contain details about the target: structure, version, operating system, and even to return full query results.
In the example below, the webpage allows fetching the first and last name of the user for a given ID. By submitting
5 as input for the User ID, the application returns user details from the database.
The SQL query used by the application is:
SELECT firstname, lastname FROM users WHERE user_id = '$id';
The server accepts the input from the user and returns the associated values, indicating that an attacker can use malicious input to modify the backend query. Typing
5', the backend responds with an error due to the single quote:
The input from the user modifies the backend query, which becomes:
SELECT firstname, lastname FROM users WHERE user_id = '5''; (note the extra quote here)
Doing the same queries directly on the database server (just for testing purposes), the same results are visible:
Exploiting error-based SQL Injection relies on the fact that the injected SQL query will output the results into the error message returned by the database. For instance, by injecting the following payload into the User ID field:
0' AND (SELECT 0 FROM (SELECT count(), CONCAT((SELECT @@version), 0x23, FLOOR(RAND(0)2)) AS x FROM information_schema.columns GROUP BY x) y) - - '
will cause the application to return the following SQL error message (containing the value of the @@version variable):
Error: Duplicate entry '10.1.36-MariaDB#0' for key 'group_key'
The error is generated because GROUP BY requires unique group keys, which is intentionally not-unique to return the value of
SELECT @@version in the error message.
2.b. UNION-based SQL Injection
The UNION operator extends the results returned by the original query, enabling users to run two or more statements if they have the same structure as the original one. We chose SELECT in our example; for the exploit to work, the following conditions are required:
Each SELECT statement within UNION has the same number of columns
The columns must also have similar data types
The columns in each SELECT statement are in the same order
SELECT firstname, lastname FROM users UNION SELECT username, password FROM login;
Here, first_name and last_name are the names of the columns in the table users, and username and password are the names of the columns in the table login.
Running a UNION operator with statements referring to different numbers of columns results in an error message, as with the following payload:
User ID: 1’ UNION SELECT 1;- -
However, the query is successful when it has the correct number of columns:
1' UNION SELECT 1,2;- -
Trying it in the database renders the same output; an incorrect number shows an error and the right values complete the query successfully:
An attacker can test multiple variants until they hit the right one. Next, they can use this method to elicit information about the database version number with the help of the @@version command:
UNION SELECT 1,@@version;- -
Similarly, the command current_user() can extract the user type under whose privileges the database is running.
UNION SELECT 1,current_user();- -
Further exploiting the vulnerability, we can obtain the name of the tables in the current database along with the same details for the columns in the table that contain information.
To extract the list of tables, we can use:
1' UNION SELECT 1,tablename FROM informationschema.tables;- -
To get the column names, we can use:
1' UNION SELECT 1,columnname FROM informationschema.columns;- -
Using these two queries, we have extracted the table name users and column names userid, first_name, last_name, user, avatar, last_login, password, and failed_login. Now using the query below we can get the usernames and passwords of application users from the database:
1' UNION SELECT 1,concat(user,':',password) FROM users;- -
Most likely, the password is not stored in plain text but in hashed form (MD5 in our case). However, an attacker can try cracking it using rainbow tables, which match plain text strings with their hash representation.
2.c. Blind SQL Injection
This type of injection attack does not show any error message, hence “blind” in its name. It is more difficult to exploit as it returns information when the application is given SQL payloads that return a true or false response from the server. By observing the response, an attacker can extract sensitive information.
There are two types of blind SQL Injection: boolean-based and time-based.
Boolean-based Blind SQL Injection
In this type of attack, a Boolean query causes the application to give a different response for a valid or invalid result in the database. It works by enumerating the characters from the text that needs to be extracted (ex. database name, table name, column name, etc) one by one.
Using the same vulnerable application as before, instead of receiving user details for the provided User ID, the reply shows if the ID is present in the database or not.
As you can see in the image above, we get the message “User ID exists in the database” for values 1-5, while an ID value larger than 5 gets “User ID is MISSING from the database”.
We can try a Boolean-based payload to check if the application is vulnerable. Injecting the payload
1' and 1=1;- - results in a true condition because
1 is a valid ID and the
'1=1' is a TRUE statement. So the result returned informs that the ID is present in the database.
Alternatively, feeding the payload
1' and 1=2;- - results in a false condition because
1 is a valid user ID and
1=2 is false; thus, we’re informed that the user ID does not exist in the database.
The scenario above indicates that a blind SQL Injection attack is possible. Moving forward with identifying the number of columns, we use the following payload:
1' and 1=1 UNION SELECT 1;- -
The query fails because there are two columns in the table. But when adjusted correctly, the condition becomes true and the message validates the query.
1' and 1=1 UNION SELECT 1,2;- -
The same method can be used to discover the version of the database. We get the first number of the database version with:
1' and substring(@@version,1,1)=1;- -
The reply is positive because ‘1’ is a valid entry in the database and it is also the first character/number of database version (@@version,1,1). For the second character, we use the following command:
1' and substring(@@version,2,1)=1;- -
Since the second character of the database version is not 1, there’s a negative result. Asking for a ‘zero’ as the second character in the database version, the message is positive (version number is “10”).
1' and substring(@@version,2,1)=0;- -
The next step is to learn the name of the database, which starts by determining the length of the name and then enumerating the characters in the correct order until the right string is hit.
We use the following payloads to determine how long is the name:
1’ and length(database())=1;-- 1’ and length(database())=2;- - 1’ and length(database())=3;- - 1’ and length(database())=4;- -
In our case, we received errors for the first three attempts and hit the right value on the fourth. This means that the name of the database is four characters long.
To enumerate the characters in the name of the database, we use these payloads:
1' and substring(database(),1,1)='a';- - 1' and substring(database(),1,1)='b';- - 1' and substring(database(),1,1)='c';- -
None of the commands were correct because’ is the first letter in the name.
Moving towards identifying the second character, we use the command
1' and substring(database(),2,1)='v';- -
And for the third, we run:
1' and substring(database(),3,1)='w';- -
While the fourth is discovered using:
1' and substring(database(),4,1)='a';- -
In the end, the name of the database is “dvwa.”
Time-based Blind SQL Injection
This type of blind SQL Injection relies on waiting for a specific period before a vulnerable application responds to an attacker’s queries tailored with a time delay value. The success of the attack is based on the time taken by the application to deliver the response. To check the time-based blind SQL Injection we use this command:
1' AND sleep(10);- -
Because we forced a delayed response of 10 seconds, the reply comes when this period expires.
With the confirmation of the vulnerability, we can proceed to extract the database version number. We used a command that forces a reply after two seconds:
1' and if((select+@@version) like "10%",sleep(2),null);- -+
If the response comes in two seconds, it means that the version starts with “10.” The “like” string operator we used in the query is designed to make a character-by-character comparison.
2.d. Out-of-band SQL Injection
With this type of SQL Injection, the application shows the same response regardless of the user input and the database error. To retrieve the output, a different transport channel like HTTP requests or DNS resolution is used; note that the attacker needs to control said HTTP or DNS server.
Exfiltrating information about an MYSQL database, an attacker can use these queries:
The two commands above concatenate the output of version() or database() commands into the DNS resolutions query for the domain “hacker.com”.
The image below shows how the version and name of the database have been added to the DNS info for the malicious domain. The attacker that controls the server can read the information from the log files.
3. Mitigating SQL Injection
At the root of it, SQL Injection has two main causes:
Failure to validate input before constructing the query
User input is included in building dynamic queries
To mitigate the problem, developers can enforce input validation and resort to prepared statements in combination with other protection methods.
3.a. Validating user-supplied input
It is possible in two ways: whitelisting and blacklisting characters that are accepted or denied in the user input fields.
Creating a list of approved characters is an efficient method to defend against SQL Injection attacks. Once the whitelist is ready, the application should disallow all requests containing characters outside it.
Blacklisting is not a recommended way to protect against SQL Injection because it is highly prone to failure. It works as long as the developer can make sure that the user input fields accept no special characters, other than what’s required. The result should be escaping all characters that may prove harmful.
3.b. Prepared Statements
This can force queries at the front-end to be handled as the content of the parameter, not part of the SQL query itself. This means there is no way an attacker can modify the backend SQL query by inserting malicious input at the front-end of the application.
Here is a prepared statement example in Java:
String uid = request.getParameter("userid"); String query = "SELECT first_name, last_name FROM users WHERE user_id = ? "; PreparedStatement pstmt = connection.prepareStatement( query ); pstmt.setString( 1, uid); ResultSet results = pstmt.executeQuery( );
3.c. The principle of least privilege
Prevents the application database user from running queries that require elevated privileges. The result is a lower impact of the SQL Injection attack. For example, an account that only has read access to the database cannot be used to alter stored information if the application is compromised.
3.d. Additional layers of security
Solutions like a Web Application Firewall (WAF) can help as an extra measure of protection against SQL Injection attacks. WAFs inspect traffic at the application level and can determine whether it’s bad or not. Maintenance is required as signatures need to be updated, otherwise, attackers can find a way to bypass the WAF.
Learn about these common SQL Injection attacks
SQL Injection is one of the most common and dangerous vulnerabilities. A small mistake in the process of validating the user input may cost victims the entire database. Several open-source tools exist that help make an attacker’s job easier by getting them shell access or helping dump the database.
Developers can avoid this security risk by following secure coding guidelines for writing SQL queries in the application and by adopting the best practices.
You can read more about SQL Injection in these resources from OWASP: