Introduction to SQL Injection attacks
This article is a part of our program, #re:educate where we empowering cybersecurity students and beginners to share their understanding about anything related to offensive security. For more info, refer to this link RE:HACK - #re:educate
Author: Ahmed Amru a.k.a Ahmd
University: UOW Malaysia KDU
The purpose of this article is to give a brief introduction to SQL injection and the three main types of attacks that can be launched using it. SQL Injection however can sometimes lead to gaining a reverse shell and thus compromise a system completely. Therefore, SQL Injection attacks can be devastating, not only to the database but also to the entire IT infrastructure.
SQL databases
SQL databases have been the standard databases used across industries for decades. Although new database implementations like NoSQL have emerged, SQL databases still dominate the world of databases. Some examples of SQL databases include : MariaDB, MySQL & PostgreSQL.
Data retrieval and storage on SQL databases
As with any database, its primary purpose is to store data securely and retrieve it when needed with utmost efficiency. “Structured Query Language” (SQL) language is used to write different commands or statements, and these are executed against the database to either retrieve or store data.
For example, let’s say we have a database table called rehack_articles. The SQL query used to store and retrieve data will look similar to this :
SQL query to store data :
SQL query to retrieve data :
SQL injection
Preface :
The target in this example is an application that allows users to search for certain fruits in the inventory. Such an application can be found in warehouses whereby employees can search for items in stock. This application is intentionally designed in a way that is vulnerable to SQL injection.
Note : This demo application can grabbed from here : https://github.com/amhmdr/sqli-basics-rehack
Goals :
The following are the goals that we want to achieve during the SQL Injection attacks.
Bypass login
Dump data from other tables into users view
Delete data from tables
Demonstration :
1. Bypassing the login
Now let’s get right into SQL injection. You have already seen some basic examples of SQL queries that can be used to store and retrieve data. In order to explain the concept of SQL injection, imagine an application authentication scenario. Where the user utilises the login portal by entering the username and password.
In the back-end, these values are compared with the data from the SQL database to see if they match. Users can log in if their credentials match. Otherwise, they are either asked to retype the password or sign up. Let’s review this scenario in more depth.
The back-end of the application has this code :
The comments on the code explain what is happening:
At first, the user input is used to create a SQL statement to run a query. The
unameandpwordvariables are used to store the username and password the user has input.A SQL statement is set by appending the variables to the statement. The statement is then run.
The
Ifstatement checks whether any row is returned by theSELECTstatement, if it does the home page is shown.
So what makes this vulnerable to SQL injection? As observed from the above steps, there is no validation done to the user input at any stage. Validation means, cleaning up user input received from the front-end before sending it to the back-end. An example of a popular tool used for sanitising user input is DOMpurify. This tool works to remove ‘dirty HTML’ which could be used in the lead up to another type of attack called Cross-site Scripting attacks.
Likewise, sanitising had to be done here to remove the malicious SQL code. The user should not be entering any SQL query statements in the input. Thus, the application’s creator could have written code to prevent users from entering such statements. However, that is not the case here. Instead, the user input is appended to the query, as shown in the above screenshot.
SQL injection is an attack that takes advantage of this fact and others to inject and execute SQL queries for malicious purposes. Because there is no sanitisation of user input, an attacker could inject SQL queries into the back-end to achieve this. The attacker can now use this security lapse to bypass authentication. This is demonstrated further below.
Under a normal circumstance of a user logging in the interface will show similar to the following:
If we attach a debugging point on Visual Studio we can see the SQL query that gets executed:
However in the picture below, we see that the attacker appended ‘-- to the username field.
(Note that the ‘ character before the comment sequence -- is used to close off the first ‘, and complete the SQL statement.)
Due to the injection by the attacker, the SQL statement now gets interpreted as:
As you can see, the injected SQL statement will cause whatever comes after the username to be commented out by the --, which in our case happens to be - the very important password.
Therefore, the modified SQL statement is now querying for only the username. As long as the username exists, the SELECT statement will succeed and the next screen will appear, allowing someone to log in using just the username because the password is no longer checked.
In the previous example, we assumed the attacker already knew the username (admin). What if the username is unknown? SQL Injection can still be carried out using the following payload : test' OR 1=1--
The resulting SQL statement will be :
What is happening here is that we have appended an OR statement into the query. An OR statement will always return true as long as one condition is true. The username is given as a test which does not exist (false). However, the no where clause, 1=1 , is always true, hence resulting in an overall true statement and allowing the user to login.
Both payloads that were discussed above would take the user to this screen :
This is the home screen that shows the fruits in stock. In the next step we will perform SQL Injection in this screen to retrieve data from the other tables.
2. Dump data from other tables
We have just seen how SQL injection can be used to bypass application logic and let an attacker into the application without knowing the password. Likewise, SQL injection also opens up the door for an attacker to retrieve data that they are not authorised to access.
In the home screen of our target has a feature to search for fruits. The code that handles the searching is shown below:
So if someone searches for “Apple”, the interface will be like this :
By placing a breakpoint, we are able to view the resulting SQL query which is:
In this example, we will assume that we know the name of another table in the database, the users table. So how do we use this knowledge to dump the users table into the application’s view which is actually only supposed to show fruits?
To do this we will perform a technique in SQL Injection attack that is called, union injection. UNION is a term in SQL which can be combined with SELECT to combine results from multiple SELECT statements. An example syntax for this is :
SELECT * from table1 UNION SELECT * from table2
In order for a UNION clause to work, the resulting data from both tables must be off the same type and also have the same count. However, since this is a basic demonstration of SQL Injection we will assume that the fruits table and users table both have the same datatype and count.
With this information, we can now insert this malicious SQL payload such as:
mango%' UNION SELECT * FROM users --
Note : the percentage % character used here is the LIKE symbol in SQL and is commonly used to wrap search terms, therefore we have wrapped the word using% and ended with ‘.
When the user clicks search, the malicious SQL query formed is:
Once this code is executed the users table containing the username admin and password password is displayed in the home screen :
We can use this same knowledge to dump data from another table called shipping. Once again, we assume that this table has the same data type and count as the other two tables.
Therefore, the payload will be:
mango%' UNION SELECT * FROM users UNION SELECT * FROM shipping--
The result will be :
As can be seen above, the contents of the users and shipping tables have been dumped.
3. Delete data from tables
Similarly, we can use SQL Injection to delete records of an entire table. For demonstration purpose, we will delete the entire fruits table so that when a user login to the system they will not be able to see any records.
To perform this attack we will use the semicolon ; character. The ; character denotes the end of one SQL statement, and will allow us to execute another SQL statement after it. An example is shown below:
SELECT * FROM table1; TRUNCATE TABLE table2
From this information we can use the following payload :
mango%’; TRUNCATE TABLE fruits; --
NOTE: The DROP command does not work during the testing. This was probably due to security limitations, however, TRUNCATE seems to work.
Once the search button is clicked the table will be wiped, and the next time user logs in there will be no fruits shown :
Thank you