UNION-Based SQL Injection

UNION-based SQL injection appends a second SELECT statement to the original query using the SQL UNION operator. The database executes both SELECT statements and combines their results. The application receives the combined result set and renders data from the injected query alongside, or instead of, data from the original query.

This technique requires the application to return query results in the response. It does not work if the application discards the result set or only uses it for a boolean check.

Requirements for UNION to Work

SQL's UNION operator requires that both SELECT statements return the same number of columns and that the data types of corresponding columns are compatible. Violating either requirement causes a database error.

Before injecting useful data, two things must be determined:

Column count: the number of columns in the original SELECT. This is found by incrementally appending ORDER BY clauses with increasing column numbers until an error occurs. ORDER BY 1 is valid for any query with at least one column; ORDER BY 5 errors if the query returns fewer than 5 columns. The last valid number is the column count. An alternative is to inject UNION SELECT NULL,NULL,NULL with increasing NULLs until no error occurs; NULL is compatible with any data type.

Renderable columns: not all columns in the result set are displayed in the response. The application may render only some fields. To find which column positions are rendered, substitute a recognizable string literal for each NULL in turn:

UNION SELECT 'marker1','marker2',NULL--

The position where the marker appears in the rendered output is the position where injected data will be visible.

Extracting Data

Once the column count and a renderable position are known, the injected SELECT retrieves data from any table the database user has SELECT privilege on:

UNION SELECT username, password, NULL FROM users--

If the application renders only one column, concatenation operators combine multiple fields into that column. MySQL uses CONCAT(username,':',password); PostgreSQL and SQL Server use username||':'||password.

Reading the Schema

The standard starting point for database enumeration is information_schema, present in MySQL, PostgreSQL, SQL Server, and SQLite. It contains the table and column definitions for all databases the current user can access:

UNION SELECT table_name, NULL, NULL FROM information_schema.tables--
UNION SELECT column_name, NULL, NULL FROM information_schema.columns WHERE table_name='users'--

Oracle does not implement information_schema. The equivalent views are ALL_TABLES and ALL_COLUMNS.

Neutralizing the Original Query

The original query may return rows that appear alongside the injected results, creating noise. To return only the injected data, the original query's result set can be suppressed by adding a condition that matches no rows: WHERE 1=0 or WHERE id=0 before the UNION. This works when the injection point is in a WHERE clause that can be extended.