> 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-6/credentials/container/information-gathering-credentials-database.md).

# Database

## Extract Credentials From Sqlite Database

Once you obtain the SQLite database file (e.g., via LFI, RCE, or a file read vulnerability), you can use the `sqlite3` command-line utility to interact with it.

To open the database, execute the command followed by the database file name:

```bash
sqlite3 database.sqlite
```

This will drop you into the `sqlite3` prompt. Inside the prompt, you can issue commands starting with a dot (`.`) or SQL queries.

Before interacting, you can get a list of available dot commands by typing:

```sqlite
.help
```

This command provides a summary of all available dot commands and their usage.

First, list the available tables to understand the database schema.

```sqlite
.tables
```

This command lists all tables in the attached databases.

To see a list of all attached databases, use:

```sqlite
.databases
```

This command shows the database name and the file it is attached to.

To understand the structure of a specific table, you can use the `.schema` command followed by the table name. If no table name is given, it shows the schema for all tables.

```sqlite
.schema users
```

Replace `users` with the actual table name you are interested in. This will display the `CREATE TABLE` statement used to define the table, showing column names and data types.

Before querying data, you might want to format the output for better readability or processing. By default, output might be hard to read.

To display column headers in the output, use:

```sqlite
.headers on
```

To change the output mode, for example, to comma-separated values (CSV), which is useful for exporting data, use:

```sqlite
.mode csv
```

Other modes include `column` (aligned columns), `html`, `insert`, `list` (default, separated by `|`), `tabs`, and `tcl`.

Look for tables that seem likely to contain user data (e.g., `users`, `accounts`, `credentials`). Then query the contents of such tables.

```sqlite
SELECT * FROM users;
```

Replace `users` with the actual table name containing credential information if found. You can use standard SQL queries here. For instance, to count the number of entries in a table:

```sqlite
SELECT COUNT(*) FROM users;
```

To dump the entire database content as SQL statements, which can be useful for backup or transfer, use the `.dump` command.

```sqlite
.dump
```

You can also execute a single SQL query directly from the shell without entering the interactive prompt. This is useful for automation or quick lookups.

```bash
sqlite3 database.sqlite 'SELECT * FROM users;'
```

Replace `database.sqlite` with the database file name and the SQL query within single quotes.

Once you are finished interacting with the database, you can exit the `sqlite3` prompt using the `.quit` command.

```sqlite
.quit
```

***

## SQLite Database Credential Extraction

Locate the SQLite database path, often found via source code analysis (e.g., `app.py`). This can sometimes be identified through source code disclosure vulnerabilities.

```bash
cat app.py
# Look for database path, e.g., instance/database.db
```

Once the path is known, use the `sqlite3` client to access the database file directly. To use the sqlite3 program, just type "sqlite3" followed by the name of the database file on the command-line. If the file does not exist, it will be created.

```bash
sqlite3 instance/database.db
```

This command opens the specified database file and enters the `sqlite>` prompt. You can also execute a single SQL command directly from the shell prompt without entering the interactive mode:

```bash
sqlite3 database_file "SQL_command;"
```

Inside the `sqlite3` prompt, you can use various dot commands that begin with a dot (`.`). These are not SQL commands but commands for the `sqlite3` program itself. To list tables in the currently attached databases, use the `.tables` command.

```sql
.tables
```

This command will list all available tables in the database. To see the schema of a specific table (the `CREATE` statement used to define it), use the `.schema` command followed by the table name.

```sql
.schema user
```

Before querying data, it's often helpful to set the output mode and turn on headers for better readability.

```sql
.mode column
.headers on
```

Finally, query the identified table to extract data, including potential credentials, using a standard SQL `SELECT` statement.

```sql
SELECT * FROM user;
```

You can select specific columns as needed:

```sql
SELECT username, password FROM user;
```

To exit the `sqlite3` prompt, use the `.quit` or `.exit` commands.

```sql
.quit
```