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

SQL Direct picoCTF 2022 Solution

Access a remote database and query it directly to retrieve a protected flag.

Published: July 20, 2023Updated: August 25, 2026

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 1Enumerate relations
    Observation
    The challenge exposes a live PostgreSQL port with default credentials, so the session is already authenticated. What is missing is knowing which tables exist before any useful query can run.
    \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 exist inside psql, once its own prompt appears. Run one from bash and you get a command-not-found error, or bash tries to interpret the backslash itself. Connect with psql first, then type the meta-command at its 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 2Dump the flag
    Observation
    Listing the tables reveals one called flags. A plain SELECT, or a client-side copy, is the last step to pull the value out.
    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 needs the PostgreSQL process itself to have write permission at that path, and usually superuser rights, so it errors about requiring superuser or denies permission outright. The backslash form runs client-side, writing the file on your own machine as the psql user, with no elevated privileges needed.

    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 before executing. Press Enter without one and the prompt shifts to show it wants more input. Type a semicolon on the next line and the query runs.

    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
  • Regex TesterTest regular expressions against a string with live match highlighting, flag toggles, and common CTF pattern shortcuts.
  • Strings ExtractorPull printable text from any binary, library, or image. ASCII and UTF-16 detection, configurable minimum length, flag-like highlight, no command line needed.

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

A database server left reachable from the internet with default credentials is among the most common findings in real penetration tests. Once an attacker holds a valid login, the database client alone is enough to enumerate the schema, read sensitive tables, and export whole datasets. Hardening means removing default accounts, keeping database ports off the public network entirely, and granting application accounts only the tables they actually need.

Related reading

Useful tools for Web Exploitation

Where to go next

Do these first

Try these next