Tags: sqli python sql
Rating:
### DB_Secret
This flag also involves the Paperbots application, and is available to us once we've figured out how to [log in successfully](https://github.com/AndreyRainchik/Writeups/tree/master/35c3%20junior#logged-in "Writeup for how to log in"). Now, we need to extract a `DB_Secret` from an SQL database that the application uses. The `init_db()` function in the [source code](https://github.com/AndreyRainchik/Writeups/blob/master/35c3%20junior/files/ctf_files/wee_server.py "Source code for the application") shows that the `DB_SECRET` value is stored into the `secrets` table in the database as the `secret` value.
```python
def init_db():
with app.app_context():
db = get_db()
with open(MIGRATION_PATH, "r") as f:
db.cursor().executescript(f.read())
db.execute("CREATE TABLE `secrets`(`id` INTEGER PRIMARY KEY AUTOINCREMENT, `secret` varchar(255) NOT NULL)")
db.execute("INSERT INTO secrets(secret) values(?)", (DB_SECRET,))
db.commit()
```
Looking at the `/api/getprojectsadmin` endpoint reveals a possible SQL injection, as an HTTP POST request to this API will take an `offset` value and use it directly in an SQL query without any input validation.
```python
# Admin endpoints
@app.route("/api/getprojectsadmin", methods=["POST"])
def getprojectsadmin():
# ProjectsRequest request = ctx.bodyAsClass(ProjectsRequest.class);
# ctx.json(paperbots.getProjectsAdmin(ctx.cookie("token"), request.sorting, request.dateOffset));
name = request.cookies["name"]
token = request.cookies["token"]
user, username, email, usertype = user_by_token(token)
json = request.get_json(force=True)
offset = json["offset"]
sorting = json["sorting"]
if name != "admin":
raise Exception("InvalidUserName")
sortings = {
"newest": "created DESC",
"oldest": "created ASC",
"lastmodified": "lastModified DESC"
}
sql_sorting = sortings[sorting]
if not offset:
offset = datetime.datetime.now()
return jsonify_projects(query_db(
"SELECT code, userName, title, public, type, lastModified, created, content FROM projects WHERE created < '{}' "
"ORDER BY {} LIMIT 10".format(offset, sql_sorting), one=False), username, "admin")
```
To be able to access this resource, we have to be logged in as admin, so we can use the `/api/login` endpoint to get the verification code for the admin account and then `/api/verify` to get the necessary value for the token cookie. Setting the token cookie and setting the name cookie to "admin" will let us get access to the SQL injection. Now, we need to create the actual string we'll be injecting.
The offset parameter is what we'll be exploiting, since the `sortings` parameter only has three different options, none of which we can define ourselves. As the SQL query used in `api/getprojectsadmin` accesses the `projects` table and we want to get at the `secrets` table, we'll use a `UNION` statement. The `UNION` statement needs the same amount of result columns on both sides of it, so we'll use `UNION SELECT secret, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM secrets` to make the statement valid. Then, we'll add a Python datetime string to the front so that the initial SQL query runs successfully, and we'll add a single quotation mark in between to allow us to escape from the query. Adding `--` to the end will comment out everything after our injection, and we've got a complete SQL injection of `2019-01-03 02:15:00.002180' UNION SELECT secret, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM secrets --`
A [Python script](https://github.com/AndreyRainchik/Writeups/blob/master/35c3%20junior/files/flag_scripts/dbsecret.py "Python script to get the flag") I wrote will automate this whole process and display our flag of `35C3_ALL_THESE_YEARS_AND_WE_STILL_HAVE_INJECTIONS_EVERYWHERE__HOW???`
