SQL Injection

Adapted, with permission, from the Data Security and Ethics lecture materials by Martin Lester (University of Reading).

Overview

What is SQL injection?
How attackers exploit database queries
Impact on web applications
Common attack vectors
Prevention and mitigation

What is SQL?

SQL (Structured Query Language)
Standard language for managing relational databases
Used to query, insert, update, and delete data
Common operations:
SELECT - retrieve data
INSERT - add new records
UPDATE - modify existing records
DELETE - remove records

Typical Database Query

Safe query example:

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

This query: - Retrieves user record matching username/password - Returns row if credentials match - Used in login forms

What is SQL Injection?

SQL injection occurs when:

User input is concatenated directly into SQL queries
Attacker manipulates input to change query logic
Database executes unintended commands

Example vulnerable code:

query = "SELECT * FROM users WHERE username = '" + username + "'"
cursor.execute(query)

Classic SQL Injection Attack

Attacker input: admin' OR '1'='1

Resulting query:

SELECT * FROM users
WHERE username = 'admin' OR '1'='1';

Effect: - '1'='1' is always true - Query returns ALL users - Attacker bypasses authentication

Another Attack Example

Attacker input: anything'; DROP TABLE users; --

Resulting query:

SELECT * FROM users
WHERE username = 'anything'; DROP TABLE users; --';

Effect: - DROP TABLE users deletes entire user table - -- comments out rest of query - Data destruction

Types of SQL Injection

In-band SQLi - Error-based: extract info from error messages - Union-based: use UNION to combine queries
Blind SQLi - Boolean-based: infer data from true/false responses - Time-based: infer data from response delays
Out-of-band SQLi - Data exfiltrated via alternate channel (DNS, HTTP)

Union-Based SQL Injection

Uses UNION operator to combine attacker query with original:

SELECT name, price FROM products
WHERE id = '1'
UNION
SELECT username, password FROM users;--

Requirements: - Attacker must know table/column structure - Number of columns must match - Data types must be compatible

Attacker cannot see data directly, but can infer via conditions:

SELECT * FROM users WHERE id = '1'
AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 'a';

If response is different: first char of password is 'a' Attacker iterates character by character

Infer data using time delays:

SELECT * FROM users WHERE username = 'admin'
AND (SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE 0 END);

If response takes 5 seconds: condition is true Commonly used when no visible output returned

Impact of SQL Injection

Authentication bypass - log in as admin without password
Data theft - extract entire database contents
Data modification - change prices, grades, balances
Data deletion - DROP TABLE, DELETE FROM
Server compromise - write files, execute commands
Lateral movement - access other systems via DB server

Real-World Incidents

Heartland Payment Systems (2008)
SQL injection exposed 130M credit card numbers
Cost: $140M+ in damages
Yahoo Voices (2012)
Union-based SQL injection exposed 450k emails/passwords
British Airways (2018)
SQL injection in booking flow
380k payment cards compromised

Preventing SQL Injection

1. Use Parameterised Queries (Prepared Statements)

# VULNERABLE:
query = f"SELECT * FROM users WHERE username = '{username}'"

# SAFE:
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,))

Database driver handles escaping automatically

2. Use ORM (Object-Relational Mapping)

SQLAlchemy, Django ORM, Entity Framework
Handles parameterisation automatically

# SQLAlchemy example
user = session.query(User).filter(User.username == username).first()

3. Input Validation

Whitelist validation: username matches [a-zA-Z0-9_]{3,20}
Reject input not matching expected pattern
Not sufficient alone (defence in depth)

4. Principle of Least Privilege

Application DB user should have only needed permissions
SELECT, INSERT, UPDATE only where required
Never give DROP, CREATE, ALTER to web app user

5. Web Application Firewalls (WAF)

ModSecurity, Cloudflare WAF
Can detect and block common injection patterns
Not a replacement for secure coding

Detecting SQL Injection

Manual testing: - Enter ' in input fields, observe errors - Try OR 1=1, OR 1=0 - Use Burp Suite Intruder for automated fuzzing

Automated scanners: - sqlmap: automated exploitation tool - OWASP ZAP: security testing framework - Burp Suite: professional web app testing

SQL Injection in Different Contexts

Web forms: login, search, registration
URL parameters: ?id=1 OR 1=1
Cookies: user_id=1; DROP TABLE users; --
HTTP headers: User-Agent: ' OR 1=1 --
JSON/XML APIs: {"username":"admin' OR '1'='1"}

Any user-controlled input reaching SQL is a potential vector

Ethical Considerations

Responsible disclosure when discovering vulnerabilities
Testing only systems you own or have permission to test
SQL injection tools have dual use (defence/offence)
Legal frameworks: Computer Misuse Act (UK), CFAA (US)

Summary

SQL injection: attacker controls SQL queries via input
Common types: in-band, blind, out-of-band
Prevention: parameterised queries, ORM, least privilege
Detection: scanning, WAF, secure code review
Impact: data theft, modification, destruction, system compromise