union based sql injection

PortSwigger- Examining the Database using Union-based SQL Injection

Posted on 10 May 202426 May 2024 by CyberiumX

Hello folks,
This blog will be based on Examining the Database using Union-based SQL Injection vulnerabilities. We will understand how we can get the database version and information related to tables and columns present in the database. We will explore the same on the PortSwigger platform.
We have discussed the basics of Union-based SQL injection in our previous blog which you can explore. Let’s start and begin the penetration testing process.

Lab-1 SQL injection attack, querying the database type and version on Oracle

We can identify the database version based on different DBMS by exploiting Union-based SQL injection. We can refer to the SQL injection cheat sheet where under the “Database version” section, we have the useful syntax for querying the database version. In this scenario, we are exploring the same for Oracle databases.
We can read the lab description and click “Access the lab”. This lab can be performed without Burp Suite as well.
We must find a vulnerable parameter to exploit SQL injection attacks. On the home page, we will find different categories of products. Let’s click on any one of the categories to view 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.
We know that this is an Oracle database so there is a mandatory condition that is required to fulfil. In Oracle databases, it is compulsory to provide the “FROM” keyword with a valid table name. Now, let’s create a payload to determine the number of columns. We can start with a single null keyword and then increase it one by one as follows:

‘ UNION SELECT null from dual––
‘ UNION SELECT null,null from dual––

We will find that the second query gives you a valid response which means that there are two columns. Now let’s find out the data type of each column using the following statements:

‘ UNION SELECT ‘a’,null from dual––
‘ UNION SELECT null,’b’ from dual––

Both statements provided a non-error value which confirms that these columns have varchar data type.
Finally, we will use the following query to get the version detail of the Oracle database:

‘ UNION SELECT banner,’a’ from v$version––

Hence, we successfully got the version details related to the Oracle database using the Union attack.

Lab-2 SQL injection attack, querying the database type and version on MySQL and Microsoft

In this scenario, we will identify the database version of MySQL and Microsoft databases using Union-based SQL injection. Let’s explore it.
We can read the lab description and click “Access the lab”. This lab can be performed without Burp Suite as well.
We must find a vulnerable parameter to exploit an SQL injection attack. On the home page, we will find different categories of products. Let’s click on any one of the categories to view 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.
To test the MySQL database, we need to make sure that we provide a space after adding the “––” symbol for comments. If we try to add a space at the end of the query on your browser, we will find that it will not accept the space. So, we have to URL encode the space and then mention it at the end of the malicious SQL query. Now, let’s create a payload to determine the number of columns. We can start with a single null keyword and then increase it one by one as follows:

‘ UNION SELECT null––%20
‘ UNION SELECT null,null––%20

We will find that the second query gives you a valid response which means that there are two columns. We are not required to find the data type of each column as we are targeting the MySQL database which has an exception. It doesn’t matter what data type is assigned to columns.
Finally, we will use the following query to get the version detail of the Oracle database:

‘ UNION SELECT @@version,null––%20

We will find the version of the MySQL database. Hence the lab is solved.

Lab-3 SQL injection attack, listing the database contents on non-Oracle databases

Using Union-based SQL injection, we can list the contents of the database like information related to tables and their columns so that further using this information, we can access all data stored in the database. In this scenario, we will target non-Oracle databases such as MySQL, Microsoft SQL, and PostgreSQL. Let’s explore it.
We can read the lab description and click “Access the lab”. This lab can be performed without Burp Suite as well.
We must find a vulnerable parameter to exploit an SQL injection attack. On the home page, we will find different categories of products. Let’s click on any one of the categories to view 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.
Now, let’s create a payload to determine the number of columns. We can start with a single null keyword and then increase it one by one as follows:

‘ UNION SELECT null––
‘ UNION SELECT null,null––

We will find that the second query gives you a valid response which means that there are two columns. 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, let’s get the information related to tables. When we target non-Oracle databases, we can send queries to information_schema.tables for getting table names and information_schema.columns for getting column names. The following statements will help us:

‘ UNION SELECT table_name,null from information_schema.tables––
‘ UNION SELECT column_name,null from information_schema.columns where table_name=’users_csgkjw’––

The first statement will provide all the table names from which we have to target the users table. Then we can use the second statement to get the column names present in the users table.

Now we know everything i.e. table name and its column name using which we can create the following statement to obtain the password of the administrator user:

‘ UNION SELECT username_getuem,password_frlhfy from users_csgkjw––

We successfully got the password for all users present in the users table.

To solve the lab, we have to click on the “My account” button and log in as administrator user.

Lab-4 SQL injection attack, listing the database contents on Oracle

In this scenario, we will get the information about table and column names for Oracle databases. Let’s begin the process.
We must find a vulnerable parameter to exploit an SQL injection attack. On the home page, we will find different categories of products. Let’s click on any one of the categories to view 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.
We know that this is an Oracle database so there is a mandatory condition that is required to fulfil. In Oracle databases, it is compulsory to provide the “FROM” keyword with a valid table name. Now, let’s create a payload to determine the number of columns. We can start with a single null keyword and then increase it one by one as follows:

‘ UNION SELECT null from dual––
‘ UNION SELECT null,null from dual––

We will find that the second query gives you a valid response which means that there are two columns. Now let’s find out the data type of each column using the following statements:

‘ UNION SELECT ‘a’,null from dual––
‘ UNION SELECT null,’b’ from dual––

Both statements provided a non-error value which confirms that these columns have varchar data type.
Now, let’s get the information related to tables. When we target the Oracle database, we can send a query towards all_tables to get table name and all_tab_columns to get column name. The following statements will help us:

‘ union select null,table_name from all_tables––
‘ union select null,column_name from all_tab_columns where table_name=’USERS_XCXCGQ’––

The first statement will provide all the table names from which we have to target the users table. Then we can use the second statement to get the column names present in the users table.

Now we know everything i.e. table name and its column name using which we can create the following statement to obtain the password of the administrator user:

‘ union select USERNAME_NIKHHJ,PASSWORD_TLYIPP from USERS_XCXCGQ––

We successfully got the password for all users present in the users table.

To solve the lab, we have to click on the “My account” button and log in as administrator user.

In this blog, we understood the concepts of exploring Union-based SQL injection to exploit the database. In the upcoming blogs, we will cover the concepts related to Blind SQL Injection vulnerability.
You can check out our other web application penetration testing blogs on our website.

Happy Pentesting!
Team CyberiumX

PortSwigger- Union-Based SQL Injection

Posted on 2 May 202426 May 2024 by CyberiumX

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