> ## Documentation Index
> Fetch the complete documentation index at: https://docs.jaspervanzeir.be/llms.txt
> Use this file to discover all available pages before exploring further.
# Ottergram
Hint: SQL Injection
The hint for this lab explicitly mentioned SQL Injection. As always, my first step was to fire up Burp Suite to inspect the requests in the HTTP history. When I loaded the homepage, I noticed the application was a sort of Instagram clone for otters called "Ottergram." I immediately spotted a user named `admin`. In CTF challenges, if there is an admin user, the flag is almost always their password. My goal was set: use SQL injection to retrieve the admin's password.
I clicked on the admin's profile to see how the application fetched user data.
***
## Enumeration & SQLi Confirmation
I found the `GET /api/profile/admin` request in Burp's HTTP history and sent it to Repeater to test for vulnerabilities in the URL path.
To confirm if the endpoint was vulnerable to SQL injection, I used a classic boolean testing method. First, I appended a true statement to the URL:
```shellscript theme={null}
GET /api/profile/admin' and 1=1 -- -
```
This returned a `200 OK` response. Next, I tested a false statement to see if the database logic would change the application's response:
```shellscript theme={null}
GET /api/profile/admin' and 1=2 -- -
```
When this returned an error (or empty result), it confirmed my suspicion: the input was being directly evaluated by the database, meaning SQL injection was possible.
## Exploitation
### Determining the Number of Columns
With SQLi confirmed, my next step was to figure out how many columns the original SQL query was returning. To do this, I used a `UNION SELECT` attack. The goal is to incrementally add numbers until the request succeeds without a column mismatch error.
*Side note: Another way to do this is using* `ORDER BY `*(e.g.,* `ORDER BY 1`*,* `ORDER BY 2`*), increasing the number until you hit an error, which means the previous number was the maximum column count. However, for the sake of this writeup and my own flow, I stuck with the* `UNION SELECT `*method.*
I kept incrementing until I got a working, successful response with 7 columns:
```shellscript theme={null}
GET /api/profile/admin' union select 1,2,3,4,5,6,7 -- -
```
### Identifying the DBMS and Version
Now that I knew there were 7 columns, I needed to identify which Database Management System (DBMS) was running and its version. The syntax for this differs slightly between systems.
I initially assumed it was MySQL, so I replaced the 7th column with `version()`:
```shellscript theme={null}
GET /api/profile/admin' union select 1,2,3,4,5,6,version() -- -
```
This failed and didn't give me the result I wanted. Since CTFs frequently use lightweight databases, my next guess was SQLite. I changed the payload to use `sqlite_version()`:
```shellscript theme={null}
GET /api/profile/admin' union select 1,2,3,4,5,6,sqlite_version() -- -
```
Success! The application rendered the version number `3.44.2`, confirming that the backend was running SQLite.
### Extracting the Database Schema
The next step was to map out the database to find where user credentials were stored. I needed to list all the tables. Since I wasn't entirely familiar with SQLite's specific syntax for this, I used a [Datacamp tutorial on SQLite commands](https://www.datacamp.com/tutorial/sqlite-show-tables) as a reference.
The tutorial noted that you can query the `sqlite_master` table using `SELECT name FROM sqlite_master WHERE type='table';`. Because I wanted to output all the table names at once in my injection point, I wrapped the column name in a `group_concat()` function:
```shellscript theme={null}
GET /api/profile/admin' union select 1,2,3,4,5,6,group_concat(tbl_name) from sqlite_master where type = 'table'-- -
```
The response revealed all the tables in the database, and the `users` table immediately stood out.
### Extracting the Flag
To get the admin's password, I first needed to know the exact column names within the `users` table. In SQLite, you can retrieve column information using `pragma_table_info()`:
```shellscript theme={null}
GET /api/profile/admin' union select 1,2,3,4,5,6,group_concat(name) FROM pragma_table_info('users')-- -
```
This returned a list of columns, and both `username` and `password` were clearly visible.
Knowing the table name (`users`), the column names (`username`, `password`), and the target user (`admin`), I crafted my final payload to extract the credentials, placing the `username` and `password` in the 6th and 7th column slots so they would render on the page:
```shellscript theme={null}
GET /api/profile/admin' union select 1,2,3,4,5,username,password from users where username = 'admin'-- -
```
Once I sent this request, the application spit out the admin's password. Exactly as I had hoped, it wasn't hashed, it was the flag in plain text!
***
## Tools Used
* Burp Suite (Community Edition): Proxy, HTTP History, and Repeater for finding the injection point and iterating payloads.
* Online Documentation: Datacamp tutorial for SQLite-specific syntax.
***
## Summary
* Key Steps: I identified an SQL injection point in the URL path, determined the column count via `UNION SELECT`, identified the database as SQLite, extracted the table schema from `sqlite_master`, and finally dumped the `admin` credentials from the `users` table.
* What I Learned: I reinforced my knowledge of SQLite-specific payloads. Specifically, using `sqlite_version()`, querying `sqlite_master` for table names, and using `pragma_table_info()` to extract column names.
* Crucial Mistakes/Takeaways: My initial assumption that the database was MySQL cost me a few minutes. Getting an error on `version()` was a great reminder not to assume the backend technology and to systematically test for different DBMS fingerprints (like transitioning to `sqlite_version()`) when standard payloads fail.