More SQLi picoCTF 2023 Solution

Description

1. Step 1

   Initial exploration

   Observation

   I noticed the challenge is a login form with no visible source, which suggested starting with a generic credential attempt to trigger error messages that might reveal the underlying SQL query structure.

   Let's start by trying "admin" for both the username and password to see what happens.

   

   Looking at the error, we can see the SQL query being executed. Notice two important things: the password field comes first in the query, and it uses single quotes (not double quotes). This tells us where to inject our payload.

   For SQL injection, common comment characters are # and --.

   sql

   SELECT id FROM users WHERE password = 'admin' AND username = 'admin'

   Copied!

   What didn't work first

   Tried: Injecting into the username field first because that is the more common target in login forms.

   The error message shows the query puts the password field first: WHERE password = '...' AND username = '...'. Injecting into the username field appends after the AND, which the database still evaluates after the password check - the tautology trick needs to go in the password field to short-circuit the whole WHERE clause.

   Tried: Using double quotes instead of single quotes in the injection payload because some SQL engines accept both.

   The verbose error reveals the application wraps values in single quotes. Submitting a double quote leaves the string literal open and causes a syntax error rather than closing it cleanly. Always match the quote style shown in the error output.

   Learn more

   SQL injection (SQLi) occurs when user-supplied input is concatenated directly into a SQL query without sanitization or parameterization. The database engine cannot distinguish between the developer's intended query structure and the attacker's injected SQL code, so it executes both together.

   The error message here is invaluable: it reveals the exact query template. Knowing that the password field appears first and uses single quotes tells you precisely where to inject and which quote character to close. Verbose error messages - which reveal internal query structure - are themselves a security vulnerability (CWE-209: Information Exposure Through an Error Message) that should be disabled in production.

   The two most common SQL comment styles are -- (ANSI standard, supported by PostgreSQL, SQLite, SQL Server) and # (MySQL-specific). Knowing which database backend is running helps you choose the right syntax. Stack traces and error messages often reveal the database type.

2. Step 2

   Bypassing the login

   Observation

   I noticed the error message showed the password field appears first in the WHERE clause and values are wrapped in single quotes, which suggested injecting a tautology payload using a single quote followed by OR 1=1 and a SQL comment to bypass authentication.

   Now let's use a classic SQL injection payload in the password field. The first single quote closes the password string, OR 1=1 is always true, and the -- comment makes everything after it irrelevant.

   This transforms the SQL query into:

   

   Success! We're in and now see a database interface with another input field, which means more SQL injection is needed.

   bash

   ' OR 1=1 --

   Copied!

   sql

   SELECT id FROM users WHERE password = '' OR 1=1 --' AND username = 'does not matter'

   Copied!

   What didn't work first

   Tried: Using # instead of - as the comment character to terminate the remainder of the query.

   The # comment is MySQL-specific. This application uses SQLite (revealed by later schema queries), and SQLite does not treat # as a comment delimiter. The query fails with a syntax error, while - works across SQLite, PostgreSQL, and SQL Server.

   Tried: Injecting ' OR '1'='1 without a trailing comment to avoid needing to know what comes after the payload.

   Without a comment, the rest of the original query - AND username = '...' - must still produce valid SQL. This form requires crafting the payload so the closing quote of the injected string lines up with the opening quote of the username check, which is fragile. Using - to discard the remainder is simpler and more reliable.

   Learn more

   The payload ' OR 1=1 -- works by exploiting the query structure. The opening single quote closes the string literal that started with the password field. OR 1=1 adds a condition that is always true, so the WHERE clause evaluates to true for every row. The -- comments out the rest of the original query (the username check), making it irrelevant. The result: the query returns at least one row, and the application logs you in.

   This is one of the most well-known attack patterns in web security - it appears in OWASP's Top 10 as "Injection." The definitive fix is parameterized queries (also called prepared statements), where SQL structure and user data are sent to the database separately: SELECT id FROM users WHERE password = ? AND username = ?. The ? placeholders are filled by the database driver, which treats the values as data - never as SQL code - regardless of what they contain.

3. Step 3

   Finding the number of columns

   Observation

   I noticed the post-login interface exposed another injectable input field, which suggested using a UNION SELECT attack to extract data and that I first needed to determine the exact column count using null placeholders.

   First, we need to figure out how many columns the query returns. We'll use a UNION SELECT with null values.

   bash

   ' UNION SELECT null,null,null;--

   Copied!

   Before:

   

   After:

   

   Perfect! The query works with three null values, which means we're dealing with three columns.

   What didn't work first

   Tried: Starting with ' UNION SELECT 'a','a','a';-- to detect displayed columns at the same time as counting them.

   Substituting visible strings before confirming the column count fails if there are more or fewer than three columns - the UNION errors before any output appears. Using null first confirms the count is correct, then you replace nulls one at a time to find which positions are displayed.

   Tried: Using ' UNION SELECT 1,2,3;-- with integers instead of null to find both column count and display position in one shot.

   Integer literals are not type-compatible with every column, so this can fail with a type mismatch error in strictly typed databases. null is universally compatible and is the standard enumeration value for this exact reason.

   Learn more

   UNION-based SQL injection appends a second SELECT statement to the original query using the SQL UNION operator. For UNION to succeed, both SELECT statements must return the same number of columns with compatible data types. The standard technique to discover the column count is to try UNION SELECT null,null,... with increasing numbers of nulls until the query succeeds instead of erroring. null is used because it is compatible with every data type.

   Once the column count is confirmed, you identify which columns are displayed to the user (they must be of string type) by replacing nulls one at a time with a visible string like 'a'. The position where 'a' appears in the output is the column you will use to exfiltrate data in subsequent queries.

   This enumeration process is tedious to do manually. Tools like sqlmap automate the entire workflow - detection, column counting, type detection, and data extraction - but understanding the manual process is essential for CTF challenges where sqlmap may be blocked or too noisy.

4. Step 4

   Enumerating the database schema

   Observation

   I noticed the app is a small CTF web challenge, which strongly suggested SQLite as the backend and pointed to querying the sqlite_master table to retrieve all table names and column definitions in one UNION SELECT.

   Since this is likely SQLite (common in CTF challenges), we can query sqlite_master, an internal table that contains schema information. The UNION must keep the same 3-column shape we discovered above, so we put sql in one slot and pad the rest with null.

   

   Excellent! From the output, we can see there's a table called more_table with a column named flag. That's exactly what we need!

   bash

   ' UNION SELECT sql,null,null FROM sqlite_master;--

   Copied!

   What didn't work first

   Tried: Querying information_schema.tables instead of sqlite_master to list all tables.

   information_schema is a MySQL and PostgreSQL construct; SQLite does not implement it. The query returns an error stating the table does not exist. Small CTF web apps almost always run SQLite, so sqlite_master is the correct metadata table.

   Tried: Querying sqlite_master without specifying WHERE type='table', which returns all object types including indexes and triggers.

   While this still works and returns table definitions, the output includes extra rows for indexes and internal objects that can obscure the table you are looking for. Adding WHERE type='table' produces a clean list, though in this challenge the output is short enough that it barely matters.

   Learn more

   Every database engine exposes metadata about its own structure through special tables or views. In SQLite, sqlite_master contains one row per database object (table, index, view, trigger) with the original CREATE statement in the sql column. This lets an attacker enumerate every table name and column definition in a single query.

   Equivalent metadata tables in other databases: MySQL/MariaDB uses information_schema.tables and information_schema.columns; PostgreSQL uses pg_tables and information_schema; Microsoft SQL Server uses sys.tables and sys.columns. Knowing which system you're targeting determines which metadata query to use.

   This schema enumeration step is critical in real-world SQLi attacks - it tells the attacker exactly what data exists before they start extracting it. Modern web application firewalls (WAFs) often block queries containing information_schema or sqlite_master, which is why understanding multiple bypass techniques is valuable.

5. Step 5

   Extracting the flag

   Observation

   I noticed the schema query revealed a table called more_table with a column named flag, which directly identified the target and meant a final UNION SELECT on that table and column would return the flag.

   Now we just need to select the flag from more_table. You can put the flag in any column position.

   

   And there's the flag!

   bash

   ' UNION SELECT null,null,flag FROM more_table;--

   Copied!

   bash

   ' UNION SELECT flag,null,null FROM more_table;--

   Copied!

   Learn more

   With the table and column names confirmed, the final UNION SELECT directly reads the target data. Placing the interesting column in position 1, 2, or 3 (whichever appears in the output) lets you retrieve it. If the table has multiple rows, all of them appear in the result set - a UNION returns all matching rows from both SELECT statements.

   In real-world SQL injection, the "flag" is whatever sensitive data the attacker is after: password hashes, credit card numbers, session tokens, private keys, or PII. The attack chain is always the same: bypass authentication, enumerate schema, extract target columns. The entire process takes minutes with sqlmap, or an hour manually - which is why parameterized queries are the only reliable defense.

   For learning, try practicing SQLi on intentionally vulnerable applications like DVWA (Damn Vulnerable Web Application), WebGoat, or HackTheBox web challenges. Understanding both the attack and the defense makes you a better developer and security tester.

Flag