CWE-89: SQL Injection - PHP

Overview

SQL Injection occurs when an application incorporates untrusted data into a SQL query without proper validation or parameterization. Attackers can manipulate queries to bypass authentication, access unauthorized data, modify or delete records, or execute administrative operations.

Primary Defence: Use prepared statements with PDO or MySQLi, or use modern ORMs like Laravel Eloquent which use prepared statements internally.

Common Vulnerable Patterns

String Concatenation

<?php
// VULNERABLE - Direct concatenation with single quotes
$email = $_POST['email'];
$sql = "SELECT * FROM users WHERE email = '$email'";
$result = $pdo->query($sql);
// Attack: email = admin'-- → SELECT * FROM users WHERE email = 'admin'--'

// VULNERABLE - Direct concatenation without quotes (numeric context)
$id = $_GET['id'];
$sql = "DELETE FROM products WHERE id = $id";
$pdo->exec($sql);
// Attack: id = 1 OR 1=1 → DELETE FROM products WHERE id = 1 OR 1=1

// VULNERABLE - Double-quoted string interpolation
$username = $_POST['username'];
$sql = "SELECT * FROM users WHERE username = \"$username\"";
// Attack: username = admin" OR "1"="1 → SELECT * FROM users WHERE username = "admin" OR "1"="1"

Why this is vulnerable:

String concatenation inserts user input directly into SQL queries, allowing attackers to inject SQL operators like ' OR '1'='1 or '; DROP TABLE users; -- to manipulate query logic or execute arbitrary SQL commands.
The database cannot distinguish between the intended query structure and the attacker's injected code.

String Interpolation and sprintf()

<?php
// VULNERABLE - Variable interpolation in double-quoted strings
$role = $_GET['role'];
$sql = "SELECT * FROM users WHERE role = '$role' AND active = 1";
$result = $mysqli->query($sql);
// Attack: role = admin' OR '1'='1

// VULNERABLE - sprintf with user input
$search = $_GET['search'];
$sql = sprintf("SELECT * FROM items WHERE name LIKE '%%%s%%'", $search);
$pdo->query($sql);
// Attack: search = %' OR '1'='1' -- 

// VULNERABLE - Heredoc/Nowdoc with interpolation
$status = $_POST['status'];
$sql = <<<SQL
SELECT * FROM orders 
WHERE status = '$status'
SQL;

Why this is vulnerable:

Variable interpolation ("...$var...") and sprintf() both embed user input into the SQL string before sending it to the database.
This allows attackers to break out of the intended context and inject arbitrary SQL.
The database receives a fully-formed malicious query with no way to distinguish parameters from commands.

Dynamic Table/Column Names from User Input

<?php
// VULNERABLE - User-controlled table name
$table = $_GET['table'];
$sql = "SELECT * FROM $table";
$result = $pdo->query($sql);
// Attack: table = users WHERE 1=1 UNION SELECT password FROM admin_users--

// VULNERABLE - User-controlled column name
$orderBy = $_GET['sort'];
$sql = "SELECT * FROM products ORDER BY $orderBy";
$pdo->query($sql);
// Attack: sort = (SELECT password FROM users LIMIT 1)

// VULNERABLE - User-controlled column in WHERE clause
$column = $_POST['field'];
$value = $_POST['value'];
$sql = "SELECT * FROM users WHERE $column = '$value'";
// Attack: field = email' OR '1'='1' --

Why this is vulnerable:

Prepared statement placeholders cannot be used for table or column names - they only work for values.
User-controlled identifiers allow attackers to inject arbitrary SQL structure, including UNION queries, subqueries, or complete query rewrites.
Even with validation, direct use of user input in identifiers creates injection risk.

LIKE Patterns with Concatenated User Input

<?php
// VULNERABLE - Concatenating user input in LIKE pattern
$search = $_POST['search'];
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE '%$search%'");
$stmt->execute();
// Attack: search = %' OR '1'='1' -- 
// Results in: WHERE name LIKE '%%' OR '1'='1' --%'

// VULNERABLE - Concatenating wildcards around bound parameter is OK, but THIS is vulnerable
$search = $_GET['q'];
$sql = "SELECT * FROM items WHERE description LIKE '%" . $search . "%'";
$result = $mysqli->query($sql);
// Attack: q = %' UNION SELECT password FROM users--

Why this is vulnerable:

While the query uses prepare(), the LIKE pattern itself contains concatenated user input ('%$search%'), which bypasses parameterization.
The attacker can inject SQL operators by closing the string with ', adding malicious SQL, then commenting out the rest with --.
The correct approach is to bind the entire pattern as a parameter: `$pattern = '%' .
$search .
'%'; $stmt->execute([$pattern]);`

IN Clause with String Concatenation

<?php
// VULNERABLE - Building IN clause with concatenation
$ids = $_GET['ids'];  // "1,2,3"
$sql = "SELECT * FROM products WHERE id IN ($ids)";
$result = $pdo->query($sql);
// Attack: ids = 1) OR 1=1 -- 
// Results in: WHERE id IN (1) OR 1=1 --)

// VULNERABLE - Imploding array without binding
$categories = $_POST['cats'];  // ['electronics', 'books']
$inClause = "'" . implode("','", $categories) . "'";
$sql = "SELECT * FROM products WHERE category IN ($inClause)";
// Attack: cats[] = ') OR '1'='1

Why this is vulnerable:

Building IN clauses by concatenating comma-separated values or imploding arrays creates injection points.
Attackers can close the IN clause early with ) and append arbitrary SQL.
Each value in the IN list must be a separate bound parameter, or use ORM methods like whereIn() that handle parameterization automatically.

Mixing User Input in Complex WHERE Clauses

<?php
// VULNERABLE - Building dynamic WHERE conditions with concatenation
$filters = [];
if (!empty($_GET['status'])) {
    $filters[] = "status = '" . $_GET['status'] . "'";
}
if (!empty($_GET['category'])) {
    $filters[] = "category = '" . $_GET['category'] . "'";
}
$sql = 'SELECT * FROM products WHERE ' . implode(' AND ', $filters);
$pdo->query($sql);
// Attack: status = active' OR '1'='1

// VULNERABLE - Conditional logic with interpolation
$condition = $_POST['premium'] ? "subscription = 'premium'" : "subscription = '" . $_POST['level'] . "'";
$sql = "SELECT * FROM users WHERE $condition";
// Attack: level = basic' OR '1'='1

Why this is vulnerable:

Dynamic WHERE clause construction that concatenates user input creates injection vulnerabilities.
Each concatenated filter string can be manipulated to break out of quotes and inject SQL operators.
The correct approach uses an array of placeholder conditions (status = ?) and a separate array of parameter values for execute().

Unsafe Use of PDO::quote()

<?php
// MISLEADING: PDO::quote() is manual escaping and easy to misuse
$safeValue = $pdo->quote($_GET['search']);
$sql = "SELECT * FROM items WHERE name = $safeValue";  // Works, but still manual string building
$results = $pdo->query($sql)->fetchAll();
// PDO::quote() adds quotes, but this pattern is still error-prone

// MISUSE: Trusting PDO::quote() in complex or dynamic SQL
$value1 = $pdo->quote($_GET['val1']);
$value2 = $pdo->quote($_GET['val2']);
$sql = "SELECT * FROM data WHERE col1 = $value1 OR col2 = $value2";
// While technically quoted, this is error-prone - use prepared statements instead

Why this is vulnerable:

PDO::quote() is manual escaping and easy to misuse or apply inconsistently.
It does not help with identifiers (table/column names) or dynamic SQL fragments.
The database still receives a complete SQL string with embedded values, so mistakes become injection risks.
Prefer prepared statements for reliable parameter binding.

Escape Functions Without Quotes

<?php
// VULNERABLE - Escaped but not quoted
$safe = $mysqli->real_escape_string($_POST['email']);
$sql = "SELECT * FROM users WHERE email = $safe";  // Missing quotes!
$result = $mysqli->query($sql);
// Attack: "1 OR 1=1" → SELECT * FROM users WHERE email = 1 OR 1=1

// VULNERABLE - pg_escape_string without quotes
$conn = pg_connect("host=localhost dbname=app");
$safe = pg_escape_string($conn, $_POST['input']);
$sql = "SELECT * FROM items WHERE id = $safe";  // Missing quotes!
// Attack: "5 OR 1=1" → SELECT * FROM items WHERE id = 5 OR 1=1

Why this is vulnerable:

Escape functions like mysqli_real_escape_string() and pg_escape_string() only escape special characters - they don't add quotes.
Without quotes in the SQL, numeric-looking attacks like 1 OR 1=1 bypass the escaping entirely.
Even with quotes, escape functions are error-prone and inferior to prepared statements.

Legacy MySQL Functions (Deprecated)

<?php
// VULNERABLE - Using deprecated mysql_* functions (removed in PHP 7.0)
$conn = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('database', $conn);

$id = mysql_real_escape_string($_GET['id']);
$query = "SELECT * FROM users WHERE id = $id";  // Missing quotes!
$result = mysql_query($query);

// Even with quotes, still vulnerable to multi-byte character attacks
$name = mysql_real_escape_string($_GET['name']);
$query = "SELECT * FROM users WHERE name = '$name'";
// Some character encodings can bypass mysql_real_escape_string()

Why this is vulnerable:

The deprecated mysql_* extension lacks support for prepared statements and is vulnerable to character encoding attacks.
mysql_real_escape_string() can be bypassed with certain multi-byte character sequences (e.g., GBK encoding).
These functions were removed in PHP 7.0 - always use MySQLi or PDO with prepared statements.

Misusing ORM Raw Query Methods

<?php
// VULNERABLE - Laravel raw query without bindings
$email = request()->input('email');
$users = DB::select("SELECT * FROM users WHERE email = '$email'");
// Attack: email = admin' OR '1'='1

// VULNERABLE - Doctrine raw SQL without parameters
$email = $_POST['email'];
$sql = "SELECT * FROM users WHERE email = '$email'";
$users = $em->getConnection()->executeQuery($sql)->fetchAllAssociative();

// VULNERABLE - Laravel whereRaw with concatenation
$status = request()->input('status');
DB::table('orders')->whereRaw("status = '$status'")->get();
// Attack: status = pending' OR '1'='1

Why this is vulnerable:

ORMs provide safe query builders, but also expose raw SQL methods for complex queries.
Using these raw methods with string concatenation bypasses the ORM's parameterization protections.
Laravel's DB::select() without a bindings array, whereRaw() with interpolation, and Doctrine's executeQuery() with concatenated SQL all create injection vulnerabilities.
Always use the second parameter for bindings: DB::select($sql, [$email]) or use query builder methods.

Secure Patterns

PDO Prepared Statements

<?php
// SECURE - PDO with prepared statements
$pdo = new PDO('mysql:host=localhost;dbname=app', $user, $pass);
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status = :status');
$stmt->execute([
    ':email' => $_POST['email'],
    ':status' => 'active'
]);
$results = $stmt->fetchAll();

// Alternative positional parameter syntax
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = ? AND price < ?');
$stmt->execute([$_GET['category'], $_GET['max_price']]);
$products = $stmt->fetchAll();

// Best practice: Configure PDO for real prepared statements
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  // Use real prepared statements

Why this works:

PDO prepared statements separate SQL structure from data.
Named parameters (:email) or positional placeholders (?) are replaced with parameterized values by the database driver, preventing SQL injection regardless of input content.
The PDO::ATTR_EMULATE_PREPARES setting ensures true prepared statements are used at the database level, not just emulated in PHP.
When you call execute() with an array, the database treats each value as pure data, never as executable SQL.

MySQLi Prepared Statements

<?php
// SECURE - MySQLi with prepared statements
$mysqli = new mysqli('localhost', $user, $pass, 'database');

// Using bind_param
$stmt = $mysqli->prepare('SELECT id, name FROM users WHERE email = ? AND role = ?');
$stmt->bind_param('ss', $_POST['email'], $role);
$role = 'admin';
$stmt->execute();
$result = $stmt->get_result();

// Modern syntax (PHP 8.2+): mysqli::execute_query() accepts parameter arrays
$result = $mysqli->execute_query(
    'SELECT * FROM orders WHERE user_id = ? AND status = ?',
    [$_SESSION['user_id'], 'pending']
);
$orders = $result->fetch_all(MYSQLI_ASSOC);

Why this works:

MySQLi prepared statements use ? placeholders that are bound to variables via bind_param() or passed as an array to execute().
The database treats these as parameters, not SQL code, preventing injection.
The first argument to bind_param() specifies types: s for string, i for integer, d for double, b for blob.

PostgreSQL Parameterized Queries

<?php
// SECURE - Use pg_query_params with parameters
$conn = pg_connect("host=localhost dbname=app");
$result = pg_query_params($conn, 
    'SELECT * FROM items WHERE name = $1 AND category = $2',
    [$_POST['search'], $_GET['category']]
);

Why this works:

pg_query_params() uses $1, $2 placeholders for positional parameters.
The function sends the query and values separately to PostgreSQL, which binds them as parameters, preventing SQL syntax injection.

SQLite3 Prepared Statements

<?php
// SECURE - SQLite3 with prepared statements
$db = new SQLite3('database.db');
$stmt = $db->prepare('SELECT * FROM table WHERE col = :value');
$stmt->bindValue(':value', $_GET['value'], SQLITE3_TEXT);
$result = $stmt->execute();

// Positional parameters
$stmt = $db->prepare('SELECT * FROM table WHERE col = ?');
$stmt->bindValue(1, $_GET['value'], SQLITE3_TEXT);
$result = $stmt->execute();

Why this works:

SQLite3 prepared statements with bindValue() create parameterized queries.
The :value placeholder or ? is replaced with a bound parameter that SQLite treats as data, not code, preventing injection.

ORM/Query Builder (Laravel)

<?php
// SECURE - Eloquent ORM (uses prepared statements internally)
$users = User::where('email', $_POST['email'])
    ->where('status', 'active')
    ->get();

// SECURE - Query Builder
$products = DB::table('products')
    ->where('category', $_GET['category'])
    ->where('price', '<', $_GET['max_price'])
    ->get();

// SECURE - whereIn with array
$products = DB::table('products')
    ->whereIn('id', $_POST['ids'])
    ->get();

Why this works:

Laravel's Eloquent ORM and Query Builder automatically use prepared statements for all data values.
Method chaining (where(), whereIn(), etc.) builds parameterized queries internally - the library generates placeholders and binds user input as parameters.
This eliminates manual SQL writing while providing the same protection as PDO/MySQLi prepared statements.

Basic Prepared Statements

<?php
// SECURE - PDO with named parameters
$pdo = new PDO('mysql:host=localhost;dbname=app', $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  // Use real prepared statements

$stmt = $pdo->prepare('SELECT id, name, email FROM users WHERE role = :role AND status = :status');
$stmt->execute([
    ':role' => $_GET['role'],
    ':status' => 'active'
]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// SECURE - MySQLi with positional parameters
$mysqli = new mysqli('localhost', $user, $pass, 'database');
$stmt = $mysqli->prepare('INSERT INTO logs (user_id, action, ip) VALUES (?, ?, ?)');
$stmt->execute([$_SESSION['user_id'], $_POST['action'], $_SERVER['REMOTE_ADDR']]);

Why this works:

PDO's named parameters (:role) and MySQLi's positional parameters (?) both create parameterized queries where the SQL structure and data values are sent separately to the database.
The PDO::ATTR_EMULATE_PREPARES setting ensures true prepared statements are used at the database level, not just emulated in PHP.
When you call execute() with an array, the database treats each value as pure data, never as executable SQL, preventing injection regardless of special characters.
MySQLi's prepare() and execute() methods follow the same principle, binding values as parameters that cannot alter the query structure.

IN Clause with Multiple Values

<?php
// SECURE - Build placeholders dynamically
$ids = $_POST['ids'];  // Array of IDs
if (!is_array($ids) || empty($ids)) {
    throw new InvalidArgumentException('Invalid IDs');
}

// Create placeholders: ?, ?, ?
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "SELECT * FROM products WHERE id IN ($placeholders)";
$stmt = $pdo->prepare($sql);
$stmt->execute($ids);
$products = $stmt->fetchAll();

// Laravel Query Builder
$products = DB::table('products')->whereIn('id', $_POST['ids'])->get();

Why this works:

The implode() approach builds a placeholder string like "?, ?, ?" based on array length, then passes the actual values to execute() as parameterized data.
While the placeholder count is dynamic, each value remains a bound parameter, not concatenated SQL.
Laravel's whereIn() method automatically generates the correct number of placeholders and binds each array element as a parameter.
Both approaches ensure the query structure (placeholder count) is determined safely, while user data never enters the SQL string directly.
The initial validation ensures the input is an array, preventing type juggling attacks.

Complex Queries with Multiple Conditions

<?php
// SECURE - Build query safely
$conditions = [];
$params = [];

if (!empty($_GET['status'])) {
    $conditions[] = 'status = ?';
    $params[] = $_GET['status'];
}

if (!empty($_GET['min_price'])) {
    $conditions[] = 'price >= ?';
    $params[] = $_GET['min_price'];
}

if (!empty($_GET['category'])) {
    $conditions[] = 'category = ?';
    $params[] = $_GET['category'];
}

$sql = 'SELECT * FROM products';
if (!empty($conditions)) {
    $sql .= ' WHERE ' . implode(' AND ', $conditions);
}

$stmt = $pdo->prepare($sql);
$stmt->execute($params);

Why this works:

This pattern safely handles dynamic WHERE clauses by separating query structure building from data binding.
The $conditions array contains only static SQL fragments with ? placeholders, while the $params array holds user-provided values.
The implode(' AND ') joins SQL fragments, but user input never enters these strings - only the placeholder symbols.
When execute($params) runs, PDO binds each parameter value safely.
This architecture allows flexible query construction while maintaining strict separation between SQL syntax and user data, preventing injection even with variable numbers of conditions.

Stored Procedures

<?php
// SECURE - Call stored procedure with parameters
$stmt = $pdo->prepare('CALL get_user_orders(:user_id, :start_date, :end_date)');
$stmt->execute([
    ':user_id' => $_SESSION['user_id'],
    ':start_date' => $_GET['start'],
    ':end_date' => $_GET['end']
]);
$orders = $stmt->fetchAll();

Why this works:

Stored procedures with parameterized calls maintain injection protection by treating parameter values as data, not SQL code.
When you use prepare('CALL proc_name(:param1, :param2)') with PDO, the parameter values passed to execute() are bound as parameters to the stored procedure call.
The database executes the procedure with these parameter values, ensuring they're treated as data inputs.
This is secure even if the stored procedure uses dynamic SQL internally, as long as the procedure itself uses proper parameterization.
The CALL statement structure is fixed, and only the parameter values vary, preventing injection at the calling layer.

Framework-Specific Best Practices

Laravel

<?php
// Use Eloquent or Query Builder (automatic protection)
User::where('email', $request->input('email'))->first();
DB::table('users')->where('email', $email)->get();

// Raw queries with bindings
DB::select('SELECT * FROM users WHERE email = ?', [$email]);

// Avoid raw queries without bindings
DB::select("SELECT * FROM users WHERE email = '$email'");  // VULNERABLE

Symfony/Doctrine

<?php
// Use DQL with parameters
$query = $em->createQuery('SELECT u FROM App\Entity\User u WHERE u.email = :email');
$query->setParameter('email', $email);
$users = $query->getResult();

// Query Builder
$qb = $em->createQueryBuilder();
$users = $qb->select('u')

    ->from('App\Entity\User', 'u')
    ->where('u.email = :email')
    ->setParameter('email', $email)
    ->getQuery()
    ->getResult();

CWE-89: SQL Injection - PHP

Overview

Common Vulnerable Patterns

String Concatenation

String Interpolation and sprintf()

Dynamic Table/Column Names from User Input

LIKE Patterns with Concatenated User Input

IN Clause with String Concatenation

Mixing User Input in Complex WHERE Clauses

Unsafe Use of PDO::quote()

Escape Functions Without Quotes

Legacy MySQL Functions (Deprecated)

Misusing ORM Raw Query Methods

Secure Patterns

PDO Prepared Statements

MySQLi Prepared Statements

PostgreSQL Parameterized Queries

SQLite3 Prepared Statements

ORM/Query Builder (Laravel)

Basic Prepared Statements

IN Clause with Multiple Values

Complex Queries with Multiple Conditions

Stored Procedures

Framework-Specific Best Practices

Laravel

Symfony/Doctrine

Additional Resources