> 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-0406.md).

# SQLi Data Extraction

***

Once you've identified database tables and columns, extracting data is typically achieved by crafting standard SQL `SELECT` statements through the injection point.

For example, using `UNION SELECT` to list all usernames from an `admins_table`:

```sql
' UNION Select username FROM [admins_table] WHERE username LIKE '%' --
```

You can also use aggregate functions like `COUNT()` to determine the number of rows matching criteria, or filter specific entries using `WHERE` clauses (e.g., `WHERE username = '...'` to get associated data like passwords, or `WHERE username != '...'` to find other entries).

A `UNION` attack is a technique that allows an attacker to retrieve data from other tables within the database. It works by adding a `SELECT` query to the original query using the `UNION` keyword. For a `UNION SELECT` query to work, two key requirements must be met:

1. Both original and injected queries must return the same number of columns.
2. The data types in each column returned by the injected query must be compatible with the data types of the columns in the original query.

To determine the number of columns, one common technique is to use the `ORDER BY` clause, incrementing the column index until an error occurs. The highest number that does not cause an error indicates the number of columns. For example:

```sql
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
...
```

Alternatively, `UNION SELECT` can be used with a varying number of `NULL` values until the correct number is found:

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

Once the number of columns is known, you can identify which columns can hold text data by injecting strings. If a column can hold text, the injected string will appear in the output.

```sql
' UNION SELECT 'a', 'b', NULL, NULL...--
```

After determining the number and type compatibility of columns, you can begin extracting data. You can retrieve database version and user information using functions like `version()` and `database()`:

```sql
' UNION SELECT NULL, version()--
' UNION SELECT NULL, database()--
```

To enumerate tables and columns, you can query the database's information schema. For example, in MySQL, `information_schema.tables` lists all tables, and `information_schema.columns` lists all columns.

To list tables:

```sql
' UNION SELECT table_name, NULL FROM information_schema.tables--
```

To list columns for a specific table (e.g., `users`):

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

Finally, you can extract sensitive data like usernames and passwords from identified tables and columns:

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

If you need to retrieve multiple values within a single column (e.g., if only one column is displayed or compatible), you can concatenate them using database-specific functions like `concat()` or `||`:

```sql
' UNION SELECT username || '~' || password FROM users--
```