Union SQL Injection: How It Works and 6 Tips for Prevention

What Is Union SQL Injection?

SQL injection is a technique threat actors use to attack applications with a database connection. It occurs when actors send a crafted SQL query to add, extract, delete, or modify data from the database. Union SQL injection enables threat actors to extract sensitive information from a database. It lets threat actors extend the results returned by an original query. 

UNION SQL injection can result in the extraction of database content and can be used to perform command execution on the target server. However, threat actors can only use the UNION operator only if their malicious query has the same structure, including the number and data type of columns, as the original query.

In this article:

• Union SQL vs Error-Based SQL Injection
• How a Union SQL Injection Attack Works
  • How to Check Columns in Table Query
• Preventing SQL Injection
• SQL Injection Protection with Bright Security

Union SQL vs Error-Based SQL Injection

Here are the key differences between Union-based and error-based SQLi:

• Error-based SQLi – the attacker tries to get sensitive information about the database, its configuration, structure, or contents, via SQL error messages.
• Union-based SQLi – the attacker uses the UNION operator to combine a benign SQL statement with a malicious statement. The malicious statement must use the same columns and data types as the original statement. A vulnerable database processes the combined statement and executes the malicious code.

Related content: Read our guide to error-based SQL injection

How a Union SQL Injection Attack Works

A Union SQL injection uses the SQL keyword UNION to retrieve additional data beyond what was expected in the original query. For this attack to succeed, the application must return the result of database queries in its response. 

The UNION keyword allows database users to append additional SELECT queries to an original query, like this:

SELECT a, b FROM originaltable UNION SELECT e, f FROM othertable

This query returns one result set with two columns. The first column will contain values from column a in originaltable and also column e in othertable. The second column will contain values from column b in originaltable and also column f in othertable.

UNION can only work if each of the queries return the same number of columns, and the data types in each corresponding column are the same.

If an application is vulnerable to SQL injection, it typically allows the attacker to inject additional SQL code into a seemingly benign query. The attacker can use UNION to add SQL statements that retrieve data from sensitive tables in the database, bypassing authorization.

How to Check Columns in Table Query

There are two main conditions necessary for UNION queries to work:

• Each query must return an equal number of columns.
• Each column must contain data types that are compatible with each query.

There are two conditions necessary to enable SQL injection UNION attacks. The attacker generally has to figure out:

• The number of columns returned from each original query.
• Which of the columns returned from the original queries have a data type that can hold the injected query results. 

There are two ways to check the number of columns returned from the table query during an SQLi UNION attack. 

The “Order by” technique:

In the first technique, the attacker injects a sequence of “order by” clauses and increments the target column index to induce an error. 

If the attackers use a quoted string in the original query’s “where” clause as the injection point, the attackers might submit an “order by” sequence with each payload ascending in order. For example,’ ORDER BY 1-- followed by ‘ORDER BY 1--, etc., until there is an error. This payload sequence will modify the original query, ordering the results according to different result set columns. 

Attackers can specify the columns in “order by” clauses by their index—there is no need to know the columns’ names. Once the column index specified exceeds the actual number of columns in a result set, it will cause the database to return an error. For example, the error message might state that the “order by” position number does not match the number of items in the selected list.

In some cases, the application returns the database error in an HTTP response, while in other cases, it only returns a generic error or does not return any results. If the attackers can detect differences in the response from the application, they can infer the number of columns returned from the query.

The “Union select” technique:

In the second technique, the attacker submits a sequence of “union select” payloads to specify different numbers of null values. For example,’ UNION SELECT NULL-- followed by

‘UNION SELECT NULL, NULL--, etc. When the database identifies that the null and column numbers don’t match, it will return an error message. 

The error message might state that queries combined by a UNION operator must have corresponding numbers of expressions in the target lists. The application will not necessarily return an error message—it might simply return a generic error message or fail to return any results. 

If the number of nulls and the number of columns align, the database should return an additional row that contains the null values for each column in the result set. The HTTP response generated will depend on the application code. 

In some cases, the attacker can view additional content included in the response—for example, extra rows on the HTML table. In other cases, the null values can trigger different errors—for example, null pointer exceptions. Occasionally, the attacker will not be able to distinguish the response for the correct number of nulls from the response caused for a different number of nulls. Therefore, this technique is less effective for determining the number of columns.

6 Tips for Preventing SQL Injection

You can use the following techniques to prevent UNION SQL injection:

1. Disable errors—in most cases, the mechanism attackers use to view database results is through errors displayed by the application. Avoid showing SQL errors in application outputs, to avoid exposing system internals to attackers.
2. Use parameterized queries—never append user inputs as strings into a SQL query. Instead, construct a query in code and then add user inputs as parameters. This is the safest technique against all types of SQL injection attacks.
3. Limit input length—limiting the length of input fields can prevent UNION SQL injection attacks, because it will make it more difficult for the attacker to append strings to the query. For example, a name string can be limited to 20 characters.
4. Character allowlists—user inputs used in SQL statements should be limited to specific, safe characters, such as alphanumeric characters only. 
5. Character denylists—disallow common characters used in SQL Injection payloads such as the characters “<>/?*()&” and common SQL operations like SELECT and UPDATE. See our guide to SQL injection payloads.
6. Set up database auditing and deploy an Intrusion Detection/Prevention System (IDS/IPS) and set up database auditing—ensure that all SQL queries on the database are audited, and set up an IDS/IPS system that can immediately block obvious SQL injection attempts.

SQL Injection Protection with Bright Security

Bright Security’s Dynamic Application Security Testing (DAST) helps automate the detection and remediation of many vulnerabilities including SQLi, early in the development process, across web applications and APIs. 

By shifting DAST scans left, and integrating them into the SDLC, developers and application security professionals can detect vulnerabilities early, and remediate them before they appear in production. NeuraLegion completes scans in minutes and achieves zero false positives, by automatically validating every vulnerability. This allows developers to adopt the solution and use it throughout the development lifecycle. 

Subscribe to Bright newsletter!