SQLDirect

Challenge: SQLDirect

Category: WebExploitation

Point Value: 200

Author: Mubarak Mikail / LT 'syreal' Jones

Description:

Connect to this PostgreSQL server and find the flag! psql -h saturn.picoctf.net -p 57168 -U postgres pico Password is postgres ^{1 2}

Write-Up:

I knew nothing about working with postgres databases, so I had to do some research on how to interact with them etc.
I started by using the built in help function of postgres

Use \? for help or press control-C to clear the input buffer.
pico-# \?

Most interesting to me was this part of the help section:

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index

Looking through the help I found that using the command \d will display tables

pico-# \d
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | flags | table | postgres
(1 row)

We can see here a table named flags. Now we can run '\d flags' to display the tables fields

pico-# \?
pico-# \d flags
                        Table "public.flags"
  Column   |          Type          | Collation | Nullable | Default 
-----------+------------------------+-----------+----------+---------
 id        | integer                |           | not null | 
 firstname | character varying(255) |           |          | 
 lastname  | character varying(255) |           |          | 
 address   | character varying(255) |           |          | 
Indexes:
    "flags_pkey" PRIMARY KEY, btree (id)

pico-# \?
pico-# SELECT * FROM flags
pico-# SHOW
pico-# tsquery('pico')
pico-# ?
pico-# \?
pico-# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | flags | table | postgres
(1 row)

pico-# \dt+
                                   List of relations
 Schema | Name  | Type  |  Owner   | Persistence | Access method | Size  | Description 
--------+-------+-------+----------+-------------+---------------+-------+-------------
 public | flags | table | postgres | permanent   | heap          | 16 kB | 
(1 row)

So at this point I knew I had a table named flags, but didn't know how to access it. I just googled how to display all tables in a postgresDB and found this query.

pico-# ^C
pico=# SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND 
    schemaname != 'information_schema';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | flags     | postgres   |            | t          | f        | f           | f
(1 row)

After running the query I could see it displayed the same info as before, and after a bit more reading I realized all I needed to do was run the following query since I knew the table name. And as you can tell I forgot my semi-colon. >.>

pico=# TABLE flags
pico-# ;
 id | firstname | lastname  |                address                 
----+-----------+-----------+----------------------------------------
  1 | Luke      | Skywalker | picoCTF{L3arN_S0m3_5qL_t0d4Y_472538a0}
  2 | Leia      | Organa    | Alderaan
  3 | Han       | Solo      | Corellia
(3 rows)

pico=# 

And found the flag!

FLAG:

picoCTF{L3arN_S0m3_5qL_t0d4Y_472538a0}

Footnotes

1. Included links to the source code may be out of date as they were what I recorded during the competition, and may be different now. ↩

2. This instance in launched on demand, and you're given the login creds at that time ↩