Union Clause

#sql #databases

Another type of SQL injection is injecting entire SQL queries executed along with the original query. This section will demonstrate this by using the MySQL Union clause to do SQL Union Injection.

Union

Before we start learning about Union Injection, we should first learn more about the SQL Union clause. The Union clause is used to combine results from multiple SELECT statements. This means that through a UNION injection, we will be able to SELECT and dump data from all across the DBMS, from multiple tables and databases. Let us try using the UNION operator in a sample database. First, let us see the content of the ports table:

mysql> SELECT * FROM ports;

+----------+-----------+
| code     | city      |
+----------+-----------+
| CN SHA   | Shanghai  |
| SG SIN   | Singapore |
| ZZ-21    | Shenzhen  |
+----------+-----------+
3 rows in set (0.00 sec)

Next, let us see the output of the ships tables:

mysql> SELECT * FROM ships;

+----------+-----------+
| Ship     | city      |
+----------+-----------+
| Morrison | New York  |
+----------+-----------+
1 rows in set (0.00 sec)

Now, let us try to use UNION to combine both results:

mysql> SELECT * FROM ports UNION SELECT * FROM ships;

+----------+-----------+
| code     | city      |
+----------+-----------+
| CN SHA   | Shanghai  |
| SG SIN   | Singapore |
| Morrison | New York  |
| ZZ-21    | Shenzhen  |
+----------+-----------+
4 rows in set (0.00 sec)

As we can see, UNION combined the output of both SELECT statements into one, so entries from the ports table and the ships table were combined into a single output with four rows. As we can see, some of the rows belong to the ports table while others belong to the ships table.

Note: The data types of the selected columns on all positions should be the same.

Even Columns

A UNION statement can only operate on SELECT statements with an equal number of columns. For example, if we attempt to UNION two queries that have results with a different number of columns, we get the following error:

mysql> SELECT city FROM ports UNION SELECT * FROM ships;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

The above query results in an error, as the first SELECT returns one column and the second SELECT returns two. Once we have two queries that return the same number of columns, we can use the UNION operator to extract data from other tables and databases.

For example, if the query is:

SELECT * FROM products WHERE product_id = 'user_input'

We can inject a UNION query into the input, such that rows from another table are returned:

SELECT * from products where product_id = '1' UNION SELECT username, password from passwords-- '

The above query would return username and password entries from the passwords table, assuming the products table has two columns.

Un-even Columns

We will find out that the original query will usually not have the same number of columns as the SQL query we want to execute, so we will have to work around that. For example, suppose we only had one column. In that case, we want to SELECT, we can put junk data for the remaining required columns so that the total number of columns we are UNIONing with remains the same as the original query.

For example, we can use any string as our junk data, and the query will return the string as its output for that column. If we UNION with the string "junk", the SELECT query would be SELECT "junk" from passwords, which will always return junk. We can also use numbers. For example, the query SELECT 1 from passwords will always return 1 as the output.

Note: When filling other columns with junk data, we must ensure that the data type matches the columns data type, otherwise the query will return an error. For the sake of simplicity, we will use numbers as our junk data, which will also become handy for tracking our payloads positions, as we will discuss later.

Tip: For advanced SQL injection, we may want to simply use 'NULL' to fill other columns, as 'NULL' fits all data types.

The products table has two columns in the above example, so we have to UNION with two columns. If we only wanted to get one column 'e.g. username', we have to do username, 2, such that we have the same number of columns:

SELECT * from products where product_id = '1' UNION SELECT username, 2 from passwords

If we had more columns in the table of the original query, we have to add more numbers to create the remaining required columns. For example, if the original query used SELECT on a table with four columns, our UNION injection would be:

UNION SELECT username, 2, 3, 4 from passwords-- '

This query would return:

mysql> SELECT * from products where product_id UNION SELECT username, 2, 3, 4 from passwords-- '

+-----------+-----------+-----------+-----------+
| product_1 | product_2 | product_3 | product_4 |
+-----------+-----------+-----------+-----------+
|   admin   |    2      |    3      |    4      |
+-----------+-----------+-----------+-----------+

As we can see, our wanted output of the 'UNION SELECT username from passwords' query is found at the first column of the second row, while the numbers filled the remaining columns.

Exercise

TARGET: 94.237.52.235:39976
Authenticate to target with username "root" and password "password"

Challenge 1

Connect to the above MySQL server with the 'mysql' tool, and find the number of records returned when doing a 'Union' of all records in the 'employees' table and all records in the 'departments' table.

First login to the mysql database server:

──(macc㉿kaliLab)-[~]
└─$ mysql -u root -h 94.237.52.235 -P 39976 --skip-ssl -p

Output:

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.7.3-MariaDB-1:10.7.3+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

List the databases on the server using show databases:

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.110 sec)

Lets go ahead and use this database:

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

List the tables found on the employees database:

MariaDB [employees]> SHOW tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.109 sec)

First we simply try to make a Union out of the employees and departments tables, this will fail if the contain different number of columns:

MariaDB [employees]> SELECT * FROM employees UNION SELECT * FROM departments;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

Lets check the number of columns in the employees table using describe:

MariaDB [employees]> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.114 sec)

Lets now check the number of columns in the departments table using describe:

MariaDB [employees]> describe departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no   | char(4)     | NO   | PRI | NULL    |       |
| dept_name | varchar(40) | NO   | UNI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.117 sec)

Taking into account the 4 junk fields we need to add to departments, lets construct the new query:

SELECT * FROM employees UNION SELECT dept_no, dept_name, 3, 4, 5, 6 FROM departments;

The result looks as follows:

MariaDB [employees]> SELECT * FROM employees UNION SELECT dept_no, dept_name, 3, 4, 5, 6 FROM departments;
+--------+--------------------+--------------+-----------------+--------+------------+
| emp_no | birth_date         | first_name   | last_name       | gender | hire_date  |
+--------+--------------------+--------------+-----------------+--------+------------+
| 10001  | 1953-09-02         | Georgi       | Facello         | M      | 1986-06-26 |
| 10002  | 1952-12-03         | Vivian       | Billawala       | F      | 1986-12-11 |
| 10003  | 1959-06-16         | Temple       | Lukaszewicz     | M      | 1992-07-04 |
| 10004  | 1956-11-06         | Masanao      | Rahimi          | M      | 1986-12-16 |
| 10005  | 1962-12-11         | Sanjay       | Danlos          | M      | 1985-08-01 |
...
| d001   | Marketing          | 3            | 4               | 5      | 6          |
| d004   | Production         | 3            | 4               | 5      | 6          |
| d006   | Quality Management | 3            | 4               | 5      | 6          |
| d008   | Research           | 3            | 4               | 5      | 6          |
| d007   | Sales              | 3            | 4               | 5      | 6          |
+--------+--------------------+--------------+-----------------+--------+------------+
663 rows in set (0.261 sec)

flag: 663