SQL Injection is a vulnerability, which can be found in applications, especially web applications. According to the List of OWASP Top 10 Web Application Security Risks Injections are still on top in 2020. SQL Injections - which are a specific type of Injections - are the most common way for attackers to either fetch sensitive data from databases or harm a system in its dependability or even causing non-availablity of the same. Such vulnerabilities become possible when input data is concatenated to SQL queries in conjuction with the absence of data sanitation.
This vulnerability can only arise when there is a lack of data sanitation. This implies the existence of data input interfaces, whether an input field or a HTTP header field. However when SQL is used in code at application layer it is often concatenated with input data from presentation layer. The combination of unsanitized input and SQL code concatenation with data input is very bad, because this leads to perfect SQL Injections. Input interfaces can be distinguished between:
- Userinput can be every field in the User Interface (UI) where a user can input data.
- Cookies can be altered, because they reside on client-side which enables the user to inject malicious code into it.
- HTTP Header are also a type of input which can contain malicious SQL code.
All these entities contain data which is created on the client-side and also can be altered on the same. This is why the first step when any of this data arrives on the server is sanitation and validation. The latter can be partly done already on client-side. Thus, defining HTML attributes, which force user to use specific type or validating with JS, which can be used to apply regular expressions (Regex) on inputs, is essential.
Direct attack method
When input data, therefore malicious SQL code, is directly concatenated to the SQL code in the application tier it is called Direct attack method. This way an authentication of a web-page can be bypassed.
Indirect attack method
In contrast to direct attack the indirect attack is when the SQL statement is terminated and arbitrary statements are attached to it. The end is then marked as a comment with using SQL specific commenting syntax, a double dash --.
Either way the malicious code is executed at the time it is received by the database.
An attack could look like this:
In the business logic of the application, a query is used to retrieve the data of an user based on username and password. The username and password are filled in by the user via a web form. The input goes directly into the query.
SELECT * FROM Users WHERE name ='userName' and password='password';
Suppose the attacker gives admin as the username and ' or '1' = '1' as a password. That results in the following query that will be evaluated by the database:
SELECT * FROM Users WHERE name ='admin' and password= or '1'='1';
The above SQL query is valid and returns all tuples from the Users relation. By adding the OR condition '1' = '1', the WHERE clause is always evaluated as true. The query then corresponds to:
SELECT * FROM Users;
Furthermore, several SQL statements can be executed simultaneously on many database servers by separating them with semicolons. The attacker can thus insert additional commands, for example, to delete a relation with a call.
The attacker enters the following in the password field:
' or '1'='1'; DROP TABLE Users; SELECT * FROM info WHERE '1' = '1
This results in the following queries:
SELECT * FROM Users WHERE name ='admin' and password=' 'or '1'='1'; DROP TABLE Users; SELECT * FROM info WHERE '1' = '1'
When the database server processes these two queries, it first returns all data records from the Users relation and then deletes or removes the entire Users table.
Categories of SQL attacks
There are a number of SQL injection attacks that occur in different situations. Depending on the target of the attacker, the techniques can be used separately as well as together. First lets take a look at the different categories of SQL attacks:
In-Band SQL Injection
Most attacks fall under in-band SQL injections. In-band means that the attacker can carry out both attacks and retrieve information via a single communication channel. This means that the results are returned on the same medium as the attack itself was executed.
Known examples for this category would be:
The goal of the Tautologies attack is to find injectable parameters in the application to bypass authentication and initially extract data. The basic idea here is to always set the where clause in the SQL statement to true so that the condition is ignored. The most common tautology is or 1 = 1. By concatenating the operator or and 1 = 1, another condition is set that is always true, so that the result of the entire condition is true. An example of this can be found in the previous chapter.
Error based SQL Injection get their information from error messages. This means that an SQL statement is manipulated in such a way that an error message is thrown by the server. This error message can then be evaluated and thus reveal the type of database and the vulnerability to SQL injections.
In the case of a UNION query attack, the keyword 'UNION' is inserted in the input in order to retrieve data from other relations in the database. UNION enables the execution of several independent SQL queries in addition to the main instruction. The results of the further queries are appended to the results of the legitimate SQL query.
As a prerequisite, the attacker must have information about the database in order to extract data using UNION. These include e.g. Information about table names and their respective attributes, with which the further queries can be created and linked to the original statement. Furthermore, the individual queries must return the same number of columns and the data types of the attributes must be compatible between the individual queries. The attacker can use the ORDER BY clause to derive the number of columns from the results. The clause is added to the statement and the column index is increased until an error message occurs. The columns in a relation can be specified in the ORDER BY clause by an index, so you do not need to know the column names.
Example of a UNION query attack:
SELECT accounts FROM Users WHERE username=username and password=password
The attacker extends the legitimate instruction by entering the following in the password field:
' 'UNION SELECT cardNo FROM Credit Cards WHERE acctNo=123456 -- ',
This results in the following query:
SELECT accounts FROM users WHERE login=' ' UNION SELECT cardNo from CreditCards where acctNo=123456
The first statement returns zero. However, the second query is performed and returns the card number from the account number 123456.
Blind SQL Injection
Blind SQL injections may not result in an immediate response from the server. The injected commands are processed by the server and do not throw an error message, only the behavior of the server afterwards allows conclusions to be drawn about the execution and results of these, this leads to their nickname "Blind SQL Injections". The big disadvantage of these attack methods is the time required and the reliability.
Two types of Blind SQL Injections exist:
Boolean based means that boolean queries are used to get different answers from the server. Depending on the answer, you know that there is a certain UserId or a certain user has administrator rights.
For example it is assumed that a website retrieves its user data from the database using a UserId. The ID is transferred via the URL: To check whether an SQL injection is possible, the following condition (and 1 = 2) is added.
http://newspaper.com/items.php?id=2 and 1=2
The actual SQL query that is executed is:
SELECT title, description, body FROM items WHERE ID = 2 and 1=2
In order for this assertion to be confirmed, the attacker builds a condition that returns true. This would be, for example, and 1 = 1, since 1 = 1 is always correct:
http://newspaper.com/items.php?id=2 and 1=1
In time-based attacks, the attacker inserts a query after the actual request, e.g. whether the system username is admin. If yes, the server should wait 15 seconds before returning the answer. This shows that both boolean based and time based attacks are very good at revealing sensitive information about the server without sending any sensitive data around, because in the end the server responds with a normal user query, only the response times change.
Out-of-Band SQL Injection
Out-of-band SQL injections use a different information channel than the actual attack vector, hence their name Out-of-band SQL injections. However, these are rarely used nowadays because special functions must be activated on the database server. For example, it must be possible to set up a DNS query or an HTTP request within the SQL query. A well-known example of out-of-band SQL injections would be a DNS query to a server controlled by the attacker, in which case the information is packaged as a URL and sent to the attacker as a DNS query.
The countermeasures for SQL injection, apart from the type of attack, are mainly the same. The root cause of SQL injections is the lack of input validation. Therefore, the simple solution to address these vulnerabilities is to use appropriate defensive coding. If you are working with user input that is initially used in SQL queries, it should be passed to the query via parameters and validated using defined regular expressions, also known as input whitelisting.
- Check the input type by parameterizing
- Positive pattern comparison
- Filter input data
- Avoid error messages
- Least privilege
- Cloudflare or other third party services
Damn Vulnerable Web App (DVWA)
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is damn vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, help web developers better understand the processes of securing web applications and aid teachers/students to teach/learn web application security in a class room environment.
To Install the DvWA project follow the following page:
Burp Suite is a tool for performing penetration testing of web applications. With the Burp Suite, administrators can intercept and manipulate HTTP / HTTPS traffic to web applications before it is sent to the server. This enables security gaps in web applications to be discovered quickly and effectively.
To start intercepting web traffic, you have to set the proxy of the browser to 127.0.0.1:8080. The proxy listener is by default configured on 127.0.0.1:8080 in burp suite.
In the DVWA project move to "SQL Injection" and type in the following input as User ID:
1' or 1=1
This will deliver all users stored in the database as the condition is always true.
Afterwards when switching to burp suite tab "Proxy" -> History you will see that the request has been captured. Within the request the "PHPSESSID" will be included as part of the cookie.
With this sessionid it is possible to connect to database and get further information with the help of a third tool called "sqlmap". To get the databases within DBMS system use the following command:
sqlmap -u "http://localhost/DVWA/vulnerabilities/sqli/?id=1&Submit=Submit" "--cookie=security=<e.g. medium>; PHPSESSID=<SessionID>" --dbs
To retrieve the tables of a specific database for example dvwa database. Replace the "--dbs" option with the "--tables" and "-D" option and define the database.
sqlmap -u "http://localhost/DVWA/vulnerabilities/sqli/?id=1&Submit=Submit" "--cookie=security=low; PHPSESSID=t664gnfopfo6kem1nkon3a6kvb" --tables -D dvwa
Furthermore, it is possible to see the database schema from database "dvwa" as well by executing the following command:
sqlmap -u "http://localhost/DVWA/vulnerabilities/sqli/?id=1&Submit=Submit" "--cookie=security=low; PHPSESSID=t664gnfopfo6kem1nkon3a6kvb" --columns -D dvwa -T users
At the end to get to the sensitive data the attacker will execute the command:
sqlmap -u "http://localhost/DVWA/vulnerabilities/sqli/?id=1&Submit=Submit" "--cookie=security=low; PHPSESSID=t664gnfopfo6kem1nkon3a6kvb" --dump -D dvwa -T users