Alsavoudomila

SQL Direct picoCTF Writeup

author:

rudy_candy

category:

Description

Connect to this PostgreSQL server and find the flag!

Additional details will be available after launching your challenge instance.

📝 Challenge Overview
In SQL Direct (AUTHOR: MUBARAK MIKAIL / LT “SYREAL” JONES) you are asked to connect to a remote PostgreSQL server and find the flag stored in a database table. This is a beginner‑friendly exercise in using psql (the PostgreSQL client), running simple SQL queries, and reading query output to locate the flag.

🔧 Step 1: Install PostgreSQL client (if needed)

  1. On an Ubuntu/Debian machine, install the server and client tools (server install not strictly required for connecting to a remote host, but postgresql-client is).
sudo apt install postgresql postgresql-contrib
sudo apt-get install postgresql-client

📝 Explanation: Installing postgresql-client gives you psql, the command‑line tool used to connect to and query PostgreSQL servers. The server package is optional for local DB work.

🔗 Step 2: Connect to the remote database with psql

  1. Use psql to connect to the remote host (replace host, port, user, db as required):
psql -h saturn.picoctf.net -p 62349 -U postgres pico
  1. Provide the password when prompted. Example connection banner you might see:
psql (12.12 (Ubuntu ...), server 14.2 ...)
WARNING: psql major version 12, server major version 14.
Type "help" for help.

📝 Explanation: -h specifies host, -p port, -U user, and the last argument is the database name. psql will prompt for the user’s password. The version warning is informational — psql can still work across minor version differences.

📋 Step 3: Inspect tables and query for flags

  1. List available tables:
select * from pg_tables;
  1. If you suspect a table named flags (common in CTFs), query it:
select * from flags;
  1. Example result from the remote server:
 id | firstname | lastname  |                address
----+-----------+-----------+----------------------------------------
  1 | Luke      | Skywalker | picoCTF{L3arN_S0m3_5qL_t0d4Y_31fd14c0}
  2 | Leia      | Organa    | Alderaan
  3 | Han       | Solo      | Corellia
(3 rows)

📝 Explanation: SELECT * FROM flags; fetches all rows from the flags table. The flag was stored in the address column of row id = 1. Many CTF challenges hide flags in obvious tables named flag, flags, or secret.

🏁 Capture the Flag
🎉 The flag found in the flags table is:
picoCTF{L3arN_S0m3_5qL_t0d4Y_31fd14c0}

Summary

StepCommand / ActionPurposeKey Result
1sudo apt install postgresql postgresql-contrib / sudo apt-get install postgresql-clientInstall PostgreSQL tools and psql clientpsql available locally
2psql -h saturn.picoctf.net -p 62349 -U postgres picoConnect to remote PostgreSQL databaseConnected to remote DB (prompt for password)
3select * from pg_tables; then select * from flags;Discover tables and read the flags tableRetrieved picoCTF{...} from address column

💡 Beginner Tips

  • 🔎 Try \dt in psql to quickly list tables (\dt is a meta‑command).
  • 🧾 Use \c to switch databases and \q to quit psql.
  • 🛡️ Avoid running destructive SQL (like DROP TABLE) on remote CTF hosts — read only unless instructed otherwise.
  • 🗂 Columns sometimes hold flags (e.g., note, comment, address) — inspect all columns in small tables.

🎓 What you learn (takeaways)

  • How to install and use psql to connect to remote PostgreSQL instances.
  • Basic SQL querying (SELECT * FROM table) is often enough to find flags in beginner CTFs.
  • Table and column names are clues — flags, secret, notes are common places to look.
  • psql meta‑commands (\dt, \d tablename) accelerate exploration.

⚡ Short explanations for commands / techniques used

  • sudo apt install postgresql postgresql-contrib
    • What: Installs PostgreSQL server and extra utilities on Debian/Ubuntu.
    • Why: Useful if you want to run a local PostgreSQL server in addition to the client.
  • sudo apt-get install postgresql-client
    • What: Installs only the client tools (includes psql).
    • Why: Enough to connect to and query remote PostgreSQL databases without running a local server.
  • psql -h <host> -p <port> -U <user> <database>
    • What: Connects to a PostgreSQL server using psql.
    • Why: Standard way to access a remote PostgreSQL database from the terminal.
  • select * from pg_tables;
    • What: Queries the system view listing tables visible to the current user.
    • Why: Helps discover which tables exist in the database.
  • select * from flags;
    • What: Selects all rows and columns from the flags table.
    • Why: Direct way to retrieve stored flags when the table name is known or suspected.
  • psql meta‑commands (\dt, \d table, \q)
    • What: \dt lists tables, \d table describes a table’s columns, \q quits.
    • Why: Faster, interactive navigation inside psql.

Load more posts