02 — Root Cause Analysis: CWE-89 SQL Injection

The Fundamental Vulnerability

CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')

At its core: User input is trustingly executed as code.

SQL Basics (For Non-Database Developers)

SQL queries are instructions to databases:

SELECT * FROM users WHERE username='alice' AND password='secret123'

Breaking it down:

SELECT *: Return all columns
FROM users: From the 'users' table
WHERE: Apply conditions
username='alice': Database matches this value

The Injection Vulnerability

SQL uses special characters to denote structure: - ' — String delimiter - ; — Statement terminator - -- — Comment - /* */ — Multi-line comment - UNION, SELECT — Keywords

When user input is concatenated without escaping:

# VULNERABLE CODE
username = request.form['username']  # User supplies: ' OR '1'='1
query = "SELECT * FROM users WHERE username='" + username + "'"
# Becomes: SELECT * FROM users WHERE username='' OR '1'='1'
#                                              ↑ This closes the string
#                                                   ↑ Logical OR always true
#                                                       ↑ Always matches

Attack Vectors

1. Authentication Bypass

-- Normal login query
SELECT * FROM users WHERE username='alice' AND password='correct'

-- Attack input
Username: admin' --
Password: anything

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

2. UNION-Based Data Extraction

-- Original query
SELECT name, email FROM users WHERE id=1

-- Attack input
id = 1 UNION SELECT username, password FROM admin_accounts --

-- Results in
SELECT name, email FROM users WHERE id=1
UNION SELECT username, password FROM admin_accounts --

-- Attacker gets admin credentials in the results

-- No visible errors, but injected query executes
-- Attacker uses time delays to infer data

id = 1 AND (SELECT COUNT(*) FROM users WHERE username='admin' AND password LIKE 'a%')=1 WAITFOR DELAY '00:00:05'

-- If it delays, password starts with 'a'
-- Attacker can brute-force character by character

4. Stacked Queries (Database-Dependent)

-- Some databases allow multiple statements
id = 1; DROP TABLE users; --

-- This executes two commands
-- Original query returns results
-- Then ENTIRE USERS TABLE IS DELETED

Database-Specific Variations

Database	Special Risk	Example
MySQL	File operations	`LOAD_FILE()`, `INTO OUTFILE`
PostgreSQL	OS command execution	`copy ... from program`
Oracle	DBMS_SQL package	Execute arbitrary procedures
SQL Server	xp_cmdshell	Execute OS commands as database user
SQLite	ATTACH DATABASE	Access other database files

Why Input Validation Alone Fails

Common flawed defenses:

Blacklist Approach (UNSAFE)

# WRONG - trying to blacklist dangerous characters
def sanitize(input_str):
    blocked = ["'", '"', ";", "--", "/*", "*/"]
    for char in blocked:
        input_str = input_str.replace(char, "")
    return input_str

Why it fails: - Attackers use: - Unicode escapes: \u0027 (unicode for single quote) - Hex encoding: 0x27 (hex for single quote) - Case variation: UnIoN sElEcT - Comments: sel/**/ect (insert comment in middle of keyword) - Whitespace: SELECT → 10 spaces between S and E

Type Checking (UNSAFE)

# UNSAFE - type checking doesn't prevent concatenation
id = int(request.form['id'])  # Correctly converts to int
query = "SELECT * FROM users WHERE id=" + str(id) + " AND status='active'"

# Attacker sends: id=1 OR 1=1; DELETE FROM logs;
# This is correctly converted to int: 1
# But then concatenated:
# SELECT * FROM users WHERE id=1 OR 1=1; DELETE FROM logs; AND status='active'
# Still vulnerable!

The Secure Approach: Parameterized Queries

SAFE - Using parameterized queries (prepared statements):

# Python with parameterized query
query = "SELECT * FROM users WHERE username=? AND password=?"
cursor.execute(query, (username, password))

# Java with parameterized query
String query = "SELECT * FROM users WHERE username=? AND password=?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();

# PHP (MySQLi with parameterized query)
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username=? AND password=?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();

Why this works: - SQL structure (query) is defined FIRST - User input is sent SEPARATELY to the database - Database engine knows: "This is a value, not code" - Even if input = ' OR '1'='1, it's treated as the literal string value - Cannot be interpreted as SQL code

ORMs As Additional Layer

Object-Relational Mappers (ORMs) provide parameterization automatically:

# Django ORM (safe)
from django.contrib.auth.models import User
User.objects.filter(username=username, password=password)

# SQLAlchemy (safe)
from sqlalchemy import select, and_
session.execute(
    select(User).where(
        and_(User.username == username, User.password == password)
    )
)

# These automatically parameterize queries under the hood

KGCS Causal Chain

CWE-89 (SQL Injection)
  ├─ Root cause: Improper neutralization of special elements
  ├─ Demonstrated by → CAPEC-66 (SQL Injection direct)
  ├─ Demonstrated by → CAPEC-250 (XML Injection - NoSQL variant)
  └─ Implements → T1190 (Exploit Public-Facing Application)
       └─ Can lead to: T1565 (Data Manipulation), T1078 (Valid Accounts)

Next Steps

Read: 03 Attack Scenarios — Real-world attack examples

Or Jump To: 05 Defense Playbook — How to prevent

Sources: CWE-89 Definition, OWASP SQL Injection, SQL Injection Prevention Cheat Sheet

KGCS Documentation