Hello folks,
In this blog, we will discuss one of the most critical vulnerabilities which is SQL Injection. We will understand different types of SQL Injection and how we can identify and exploit them using various methods. This blog will explain Union-based SQL injection using PortSwigger platform where you will find the best resources for web application-related vulnerabilities. Let’s start and understand the concepts of SQL injection vulnerability.
SQL Injection is a critical web app vulnerability that allows an attacker to alter the SQL queries and inject malicious payload using a vulnerable parameter/header, after which the attacker gets the required data stored in the database. There are various types of SQL injection techniques and in this blog, we are going to explore Union-based SQL injection. You can access it from PortSwigger’s website.
Union SQL injection involves an attacker to get the contents of other tables stored in the database with the default SQL queries. For example:
SELECT Column_A,Column_B FROM Table_1 UNION SELECT Column_C,Column_D FROM Table_2
In the above example, we can retrieve data from Table_2 with the results of Table_1. To perform a union-based SQL injection attack, the following conditions should be met:
1. The number of columns should be the same in both SELECT statements.
2. The data types of each column should match in exact order for both SELECT statements.
Note: There is an exception for the second condition. This doesn’t matter in the case of the MySQL database.
As we can see from the above example, the number of columns in the left side statement is 2 which is equal to the right side statement and the data type of Column_A and Column_B should match with Column_C and Column_D respectively. If Column_A is integer then Column_C needs to be integer.
After getting an overview of Union-based SQL injection, let’s begin the penetration testing process and understand how we can identify and exploit the same.
Lab-1 SQL injection UNION attack, determining the number of columns returned by the query
In this scenario, we will understand how we can identify the number of columns used in the previous SQL query sent by the server to the database so that we can perform a union-based SQL injection attack. We can use “null” characters to replace each column so that when we get a non-error response, we can count the number of null keywords to get the number of columns. Let’s understand the same in this lab.
We can read the lab description and click ‘Access the lab’. This lab can be performed without Burp Suite as well.
To perform an SQL injection attack, we have to find a parameter. On the home page, we will find different categories of items. Let’s click on any one of them to get some parameters. We can now see that we have a GET parameter on the URL named category where we can try to provide our malicious SQL query.
Let’s now create a payload to determine the number of columns. We can start with a single null value and then increase it one by one as follows:
‘ UNION SELECT null––
‘ UNION SELECT null,null––
‘ UNION SELECT null,null,null––
In the above payloads, we have used –– symbol which represents comments in SQL Language. When we use the above two payloads, we will find “Internal Server Error” but as we use the third one, we will find a non-error value which confirms that there are 3 columns available in the previous SQL query. Hence the lab is solved.
Lab-2 SQL injection UNION attack, finding a column containing text
After determining the number of columns, we have to confirm the data types of each column for which we have to replace each null character with either a character or a number. Let’s see the same in practice.
We can read the lab description and click ‘Access the lab’. This lab can be performed without Burp Suite as well.
To perform an SQL injection attack, we have to find a parameter. On the home page, we will find different categories of items. Let’s click on any one of them to get some parameters. We can now see that we have a GET parameter on the URL named category where we can try to provide our malicious SQL query.
Let’s now create a payload to determine the number of columns. We can start with a single null value and then increase it one by one as follows:
‘ UNION SELECT null––
‘ UNION SELECT null,null––
‘ UNION SELECT null,null,null––
In the above two payloads, we will find “Internal Server Error” but as we use the third one, we will find a non-error value which confirms that there are 3 columns available in the previous SQL query.
Now let’s find out the data type of each column using the following statements:
‘ UNION SELECT ‘a’,null,null––
‘ UNION SELECT null,’b’,null––
‘ UNION SELECT null,null,’c’––
Using the first and last payload, we got “Internal Server Error” which means that these columns are not varchar, hence they are integer and the second statement provided a non-error value which confirms that the second column is a varchar.
Now, to solve the lab we have to print the provided character value for which we have to use the following statement:
‘ UNION SELECT null,'<Provided_Value>’,null––
We will find that the provided value is printed on the web page, hence the lab is solved.
Lab-3 SQL injection UNION attack, retrieving data from other tables
After identifying the number of columns and their data types, we can finally move toward extracting sensitive data from the database. In this scenario, we will see how we can get juicy content from the database using a UNION-based SQL Injection attack.
We can read the lab description where we are provided with table names, column names, and usernames. Now we can click ‘Access the lab’ to start exploring. This lab can be performed without Burp Suite as well.
To perform an SQL injection attack, we have to find a parameter. On the home page, we will find different categories of items. Let’s click on any one of them to get some parameters. We can now see that we have a GET parameter on the URL named category where we can try to provide our malicious SQL query.
Let’s now create a payload to determine the number of columns. We can start with a single null value and then increase it one by one as follows:
‘ UNION SELECT null––
‘ UNION SELECT null,null––
In the above two payloads, we will find that the first one will provide “Internal Server Error” but the second one will provide a non-error value which confirms that are 2 columns available in the previous SQL query.
Now let’s find out the data type of each column using the following statements:
‘ UNION SELECT ‘a’,null––
‘ UNION SELECT null,’b’––
Both statements provided a non-error value which confirms that these columns have varchar data type.
Now, to solve the lab we have to get the values of the “username” and “password” columns from the “users” table for which we have to use the following statement:
‘ UNION SELECT username,password FROM users––
This statement will fetch the usernames and passwords for different users present in the “users” table.
We require the password of the “administrator” user to solve the lab. Let’s copy the password and click on the “My account” button. It will redirect us to the login page where we can provide a username as administrator and its password. We will find that we are successfully logged in as administrator user. Hence the lab is solved.
Lab-4 SQL injection UNION attack, retrieving multiple values in a single column
In the previous lab, we had two columns with varchar data type, but there are other cases. We might have a situation where we have only one column with the varchar data type. In this scenario, we will explore how we can fetch multiple columns of data using a single column of a specific data type.
We can read the lab description and click ‘Access the lab’. This lab can be performed without Burp Suite as well.
To perform an SQL injection attack, we have to find a parameter. On the home page, we will find different categories of items. Let’s click on any one of them to get some parameters. We can now see that we have a GET parameter on the URL named category where we can try to provide our malicious SQL query.
Let’s now create a payload to determine the number of columns. We can start with a single null value and then increase it one by one as follows:
‘ UNION SELECT null––
‘ UNION SELECT null,null––
In the above two payloads, we will find that the first one will provide “Internal Server Error” but the second one will provide a non-error value which confirms that there are 2 columns available in the previous SQL query.
Now let’s find out the data type of each column using the following statements:
‘ UNION SELECT ‘a’,null––
‘ UNION SELECT null,’b’––
After using the above statements, we will identify that the data type of the first column is integer and of the second column is varchar. This time we only have a single column with varchar data type. So we require concatenation characters to get the data of two or more columns using a single column of specific data type. We will use the following SQL query to achieve the same:
‘ UNION SELECT null,username || ‘–’ || password from users––
Here, double pipe is a concatenation character used in Oracle and PostgreSQL databases. You can check out the SQL Injection Cheat Sheet provided by PortSwigger where you can explore other concatenation characters used for different databases.
We will find the usernames and passwords of all users separated by a “–” symbol. Now, we require the password of the “administrator” user to solve the lab. Let’s copy the password and click on the “My account” button. It will redirect us to the login page where we can provide a username as administrator and paste its password. We will find that we are successfully logged in as administrator user. Hence the lab is solved.
In this blog, we learned how to identify Union-based SQL injection vulnerability. In the upcoming blog, we will explore methods using which we can retrieve the table names and column names using Union.
You can check out our other web application penetration testing blogs on our website.
Happy Pentesting!
Team CyberiumX