picoCTF Solutions
PathPart of the Web Exploitation path · Step 4 of 5: SQL Injection
picoCTF 2022Web ExploitationMedium

SQL Direct picoCTF 2022 Solution

Published: July 20, 2023

Description

The challenge exposes a PostgreSQL instance with a single table called flags. Connect with psql, list the relations, and dump the flag.

Setup

Connect using the supplied command: psql -h saturn.picoctf.net -p 51070 -U postgres pico (password postgres).

List the tables with \dt and note the flags table.

Select everything from the table, then verify the output matches picoCTF{...} before submitting.

bash
psql -h saturn.picoctf.net -p 51070 -U postgres pico
bash
\dt
sql
SELECT * FROM flags;
bash
\copy flags TO flag.csv CSV
bash
grep -oE 'picoCTF\{[^}]+\}' flag.csv

Solution

Want to try it yourself first?

The guided walkthrough reveals hints one step at a time.

Walk me through it
No injection here. The server hands you direct psql access with default credentials, so this is one SELECT away from the flag. If the connection rejects you with psql: error: connection to server ... password authentication failed, the credentials in the challenge prompt have been re-issued; reconnect with the new ones rather than guessing. The SQL Injection for CTF guide covers the actual injection cases (auth bypass, UNION, blind SQLi, sqlmap) for the picoCTF challenges that need them.
  1. Step 1
    Enumerate relations
    Observation
    I noticed the challenge exposed a live PostgreSQL port with default credentials, which meant I had an authenticated session and needed to discover what tables existed before I could query anything useful.
    \dt lists the available tables (only flags). The column of interest contains the picoCTF value.
    What didn't work first

    Tried: Typing \dt literally into the terminal shell instead of inside the psql prompt.

    The backslash meta-commands only work inside psql after the pico=# prompt appears. If you run \dt from your regular bash shell you will get a 'command not found' error or bash will try to interpret the backslash. You need to connect first with psql -h ... -U postgres pico and then type \dt at the psql prompt.

    Tried: Running SHOW TABLES; to list the tables instead of \dt.

    SHOW TABLES; is MySQL syntax and does not work in PostgreSQL - it returns an error. The psql meta-command \dt or the standard SQL query SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; are the correct approaches for PostgreSQL.

    Learn more

    psql is the official command-line client for PostgreSQL, one of the most widely used open-source relational databases. It supports both standard SQL and meta-commands (prefixed with \) that are specific to psql. \dt lists all tables (relations) in the current database; \d tablename shows a table's schema; \l lists all databases.

    In this challenge, the database is intentionally exposed with default credentials (postgres/postgres) - a critical misconfiguration seen in real-world environments. Default credentials on database servers are a top finding in penetration tests. Tools like Metasploit's postgres_login scanner and hydra automate credential testing against exposed database ports.

    PostgreSQL runs on port 5432 by default. During network recon, port scans with nmap flag open database ports, which are then probed for default or weak credentials. Once inside, information_schema.tables (standard SQL) or \dt (psql-specific) quickly reveals the database structure.

  2. Step 2
    Dump the flag
    Observation
    I noticed that \dt revealed a table named flags, which directly pointed to a simple SELECT query or client-side copy command as the final step to retrieve the picoCTF value.
    Either run TABLE flags; directly in psql or copy the table to a CSV and parse it locally with grep/cut.
    What didn't work first

    Tried: Using COPY flags TO 'flag.csv' CSV; (no backslash) instead of \copy flags TO flag.csv CSV.

    The server-side COPY command (no backslash) requires the PostgreSQL server process to have write permission to that file path on the server, and typically requires superuser privileges. It will error with 'must be superuser to COPY to or from a file' or a permission denied. The client-side \copy (with backslash) writes the file on your local machine as the psql client user, which works without elevated privileges.

    Tried: Forgetting the semicolon and pressing Enter after SELECT * FROM flags, then seeing the prompt change to pico-# and thinking the command failed.

    PostgreSQL waits for a semicolon to end a statement. If you press Enter without one, the prompt changes from =# to -# indicating it is waiting for more input. Just type a semicolon on the next line and press Enter to execute the query.

    Learn more

    SELECT * FROM flags; is the most basic SQL query - it retrieves every row and column from the table. TABLE flags; is a PostgreSQL shorthand for the same thing. For larger tables, add LIMIT 10 to preview the first 10 rows, or WHERE column LIKE '%picoCTF%' to filter.

    The \copy meta-command exports query results to a local file (running client-side). This is distinct from COPY (no backslash), which runs server-side and requires superuser privileges to write to the server filesystem. Both support CSV, binary, and tab-delimited formats.

    In real incident response or data exfiltration scenarios, attackers with database access commonly use SELECT to extract entire tables, pg_dump to export the full database, or COPY TO to write files to the server. Defense-in-depth means: don't expose database ports to the internet, use strong unique credentials, enable SSL, restrict user privileges with least privilege, and audit all connections via database logs.

Interactive tools
  • SQL Injection Payload GeneratorGenerate SQL injection payloads for auth bypass, UNION extraction, blind SQLi, NoSQL operator injection, and sqlmap commands. Supports MySQL, PostgreSQL, SQLite, and MSSQL.

Flag

Reveal flag

picoCTF{L3arN_S0m3_5qL_t0d4Y_31fd...}

PostgreSQL’s meta-commands (`\dt`, `\copy`, etc.) make exploratory tasks like this very quick.

Key takeaway

Database servers left internet-accessible with default or unchanged credentials are one of the most common findings in real-world penetration tests. Once an attacker has a valid login, the database client itself provides everything needed to enumerate schema, read sensitive tables, and export entire datasets. Hardening requires removing default accounts, enforcing network-level access controls so database ports are never exposed publicly, and applying least-privilege so application accounts can only reach the tables they actually need.

Related reading

Want more picoCTF 2022 writeups?

Browse picoCTF 2022Full library

Useful tools for Web Exploitation

Do these first

What to try next