> 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/data-extraction/trick-0461.md).

# SQL Injection Data Aggregation via group\_concat()

***

When facing SQL injection scenarios where the application only displays results from a single row, even with a `UNION` query, aggregate functions can be used to concatenate data from multiple rows into that single output row.

For example, in SQLite or MySQL, the `group_concat()` function (or `GROUP_CONCAT()`) can merge values from a specified column across multiple rows into a single string. The basic syntax is `GROUP_CONCAT(column_name)`. This function is highly flexible and can include optional clauses such as `DISTINCT` to return only unique values, `ORDER BY` to sort the values before concatenation, and `SEPARATOR` to specify a string to be used between concatenated values instead of the default comma (`,`). A more complete syntax is `GROUP_CONCAT( [DISTINCT] column_name [ORDER BY clause] [SEPARATOR separator_string] )`. For instance, `GROUP_CONCAT(DISTINCT column_name ORDER BY column_name SEPARATOR ';')` would return unique values sorted and separated by a semicolon.

SQL Server uses `FOR XML PATH` for a similar purpose. A common pattern involves selecting the column and using `FOR XML PATH('')` to concatenate the rows into a single XML string, then using the `STUFF` function to remove the leading separator (like a comma). The structure looks like `STUFF(( SELECT ',' + column FROM table FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')`. Here, `FOR XML PATH('')` concatenates the values, `.value('.', 'NVARCHAR(MAX)')` extracts the text content, and `STUFF(..., 1, 1, '')` removes the first character (the leading comma).

The payload structure would typically involve selecting the aggregated column:

```sql
smokey' uNion sElect group_concat(column_name) fRom table_name '
```

A more specific example targeting schema information in MySQL could look like:

```sql
... UNION ALL SELECT 1,2,GROUP_CONCAT(column_name) FROM information_schema.tables WHERE table_schema = 'database_name' -- -
```

This technique is database-specific (the example uses SQLite/MySQL's `group_concat`) and requires knowing the target database type. As seen in the example payload, case variation (`uNion`, `sElect`, `fRom`) might be necessary to bypass filters. This effectively retrieves all data from the desired column in one go, bypassing the single-row output limitation.