- 1 SQL Injection
- 2 Functionality
- 3 Types of SQL queries
- 4 Prevention
- 5 Practice
- 6 References
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.
Types of SQL queries
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. These are for example:
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.
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.
SQL Blind SQL Injection
Error messages provide the attacker with information on how he can continue his attack against the database. So it is usually a tool for attackers. With Blind SQl Injection, the attacker assumes that the error messages from the database are deactivated and still tries to executes the SQL injection.
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.
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:
Procedure Stored procedure attacks
The basic idea behind stored procedure attacks is to execute a stored database procedure. The attacker determines the database type and uses this knowledge to find out which procedures exist. The attacker first determines the database type and uses this knowledge to determine which stored procedures may exist. There appear to be several threats against which the database is vulnerable, such as the escalation of permissions, SQL injection buffer overflow and the gathering of extended information, since these are based on stored procedures. The following is a stored procedure that returns a description of its products using the buyer's first name.
CREATE PROCEDURE getDescription @vname VARCHAR(50) AS EXEC('SELECT description FROM products WHERE name = +@vname+ ') RETURN
Dabei wird der Vorname von dem/der KäuferIn eingegeben. Der Angreifer gibt jedoch statt dem Namen eine wahre Bedingung.
AND 'a'='b' UNION SELECT password FROM members WHERE username='admin
Beim Ausführen der Bedingung ergibt sich die folgende Prozedur:
CREATE PROCEDURE getDescription @vname VARCHAR(50) AS EXEC('SELECT description FROM products WHERE name ='z' AND 'a'='b' UNION SELECT password FROM members WHERE username='admin) RETURN
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
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