SQL injection UNION attack

Photo by Émile Perron on Unsplash

What is union select?

When an application is vulnerable to SQL injection and the results of the query are returned within the application’s responses, the UNION keyword can be used to retrieve data from other tables within the database. This results in an SQL injection UNION attack.

Example union attack

The UNION keyword lets you execute one or more additional SELECT queries and append the results to the original query. For example:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2

This SQL query will return a single result set with two columns, containing values from columns a and b in table1 and columns c and d in table2.

For a UNION query to work, two key requirements must be met:

• The individual queries must return the same number of columns.
• The data types in each column must be compatible with the individual queries.

To carry out an SQL injection UNION attack, you need to ensure that your attack meets these two requirements. This generally involves figuring out:

• How many columns are being returned from the original query?
• Which columns returned from the original query are of a suitable data type to hold the results from the injected query?

How to check column in table query

When performing an SQL injection UNION attack, there are two effective methods to determine how many columns are being returned from the original query.

The first method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs. For example, assuming the injection point is a quoted string within the WHERE clause of the original query, you would submit:

' ORDER BY 1 -- 
' ORDER BY 2 --
' ORDER BY 3 --

This series of payloads modify the original query to order the results by different columns in the result set. The column in an ORDER BY clause can be specified by its index, so you don’t need to know the names of any columns. When the specified column index exceeds the number of actual columns in the result set, the database returns an error, such as:

The ORDER BY position number 3 is out of range of the number of items in the select list.

Finding columns with a useful data type

The reason for performing an SQL injection UNION attack is to be able to retrieve the results from an injected query. Generally, the interesting data that you want to retrieve will be in string form, so you need to find one or more columns in the original query results whose data type is, or is compatible with, string data.

Having already determined the number of required columns, you can probe each column to test whether it can hold string data by submitting a series of UNION SELECT payloads that place a string value into each column in turn. For example, if the query returns four columns, you would submit:

' UNION SELECT ‘a’,NULL,NULL,NULL --
' UNION SELECT NULL,’a’,NULL,NULL -- 
' UNION SELECT NULL,NULL,’a’,NULL --
' UNION SELECT NULL,NULL,NULL,’a’ --

If the data type of a column is not compatible with string data, the injected query will cause a database error, such as:

Conversion failed when converting the varchar value ‘a’ to data type int.

If an error does not occur, and the application’s response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.

Variable/function on MySQL

Extract information

To extract the data table from the current database

1' and 1=2 union select 1,group_concat(table_name),3,4 from information_schema.tables where table_schema = database() -- -

To extract column name from table name we are select

1' and 1=2 union select 1,group_concat(column_name),3,4 from information_schema.columns where table_schema = database() and table_name ='user'-- -

Finally extract sensitive data from table user

1' and 1=2 union select 1,group_concat(username,0x3a,password),3,4 from user-- -