SQL Injection

From Embedded Lab Vienna for IoT & Security
Revision as of 16:56, 5 February 2020 by Ikramer (talk | contribs)
Jump to navigation Jump to search

SQL Injection

SQL Injection is a vulnerability, which can be found in applications, especially web applications. According to a study conducted in 2019 by OWASP, SQL injections count to the most common way for attackers to fetch sensitive data from a database. SQL injections are simply possible if SQL queries in the application provide results based on user input, while the user input is processed directly in the SQL query.

Functionality

SQL injections are basically possible if SQL queries in the application deliver results based on user input. The user input is processed directly by the SQL interpreter without changes or validation. In this case, the database cannot distinguish between malicious or good entries.

An attack could look like this:

Case 1

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;


Case 2

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:

Tautologies

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.

Union Query

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. 

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

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

Prevention

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

Practice

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:

Installation:

Burp suite

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.

Proxy 4.png


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.

Demo2.png

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.

Demo3.png

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

Demo4.png

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


Demo5.png

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


Demo6.png

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


Demo7.png

References

Retrieved from "https://wiki.elvis.science/index.php?title=SQL_Injection&oldid=2896"