FlawOpen

flawopen.com/sql-injection/Php

● CWE-89 · Critical
FlawOpen Security Research

SQL Injection in PHP (PDO, mysqli, Laravel)

A variable interpolated into a double-quoted SQL string lets a request parameter rewrite your query and return every row (CWE-89). The safe call for PDO, mysqli and Laravel, why real_escape_string() still leaks, and how to sort without orderByRaw.[1], [2], [3], [4], [10], [6]

💡 Plain English Explainer (ELI5)

Imagine a stage manager’s cue sheet. If an actor can write on the master script, a line meant to fill in a name can become a new direction for the whole crew. Interpolating $email or concatenating a value into SQL edits that script before the database reads it. PDO::prepare() keeps the SQL plan separate from execute() values; mysqli prepared statements do the same, while real_escape_string() only edits text and cannot protect an unquoted LIMIT value. Laravel where() binds values, but whereRaw() and orderByRaw() accept SQL expressions. Treat sort columns as stage cues: choose them from a fixed allowlist before calling orderBy().[1], [2], [10], [4], [6], [7]

Core Concepts & Subsystem Terms

PDO::prepare() and execute()
prepare() sends SQL with ? or :name markers; execute() supplies the values separately, so they can never change the statement. It only helps when the prepared SQL itself contains no interpolated variables.[2], [8]
PDO::ATTR_EMULATE_PREPARES
With pdo_mysql, PDO emulates prepares by default: it escapes values client-side and sends one finished string. That is safe when the connection charset is set in the DSN (charset=utf8mb4). Setting ATTR_EMULATE_PREPARES to false uses real server-side prepares and native types.[7], [8], [9]
mysqli_real_escape_string()
Escapes quotes, but only matters inside a quoted string. In a numeric or unquoted position, such as LIMIT $n or WHERE id = $id, there is no quote to escape, so 0 OR 1=1 passes straight through.[10], [1], [6]
mysqli::execute_query()
Added in PHP 8.2: prepares, binds and executes in one call, $db->execute_query($sql, [$a, $b]). It removes the boilerplate that tempted developers to use query() with interpolation.[3]
Laravel whereRaw / orderByRaw
Laravel's where('email', $email) binds values. whereRaw, selectRaw, orderByRaw and DB::raw() insert their string as SQL; they are safe only with ? bindings passed as the second argument. Column names in orderBy() are not bound, so they must come from an allowlist.[4], [6]

Framework mappings

Step-by-Step Attack Flow

Step 1

Request value reaches the query

A controller passes $_GET['id'] or $request->input('id') to findEmail() without validating it as an integer. Both sources are user-controlled; depending on the request shape, the value can also be an array rather than a string.[11], [12], [13]

Step 2

Interpolation builds the SQL

"... WHERE id = $userId" expands the variable inside the double-quoted string. With the benign test value 0 OR 1=1, the query reads WHERE id = 0 OR 1=1.[16], [1], [6]

Step 3

PDO::query() runs it as written

query() takes no parameters, so MySQL parses OR 1=1 as part of the WHERE clause. With pdo_mysql, multi-statement queries are also allowed by default.[2], [14], [8], [1]

Step 4

Every row comes back

The condition is true for every row. The same happens in the mysqli example despite real_escape_string(), because the unquoted LIMIT value was never escaped at all.[1], [10], [6]

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW
<?php
// UserQueries.php: every query below interpolates request data into SQL

use App\Models\User;
use Illuminate\Support\Facades\DB;

// PDO: the double-quoted string expands $userId before MySQL sees it
function findEmail(PDO $pdo, string $userId): ?string
{
    $row = $pdo->query("SELECT email FROM users WHERE id = $userId")->fetch();
    return $row['email'] ?? null;
}

// mysqli: escaping protects the quoted value but not the unquoted LIMIT
function findOrders(mysqli $db, string $status): array
{
    $status = $db->real_escape_string($status);
    return $db->query("SELECT id FROM orders WHERE status = '$status' LIMIT " . $_GET['limit'])
              ->fetch_all(MYSQLI_ASSOC);
}

// Laravel: whereRaw inserts the interpolated string as SQL
function findByEmail(string $email): ?object
{
    return DB::table('users')->whereRaw("email = '$email'")->first();
}

// orderByRaw with a request value is raw SQL
function listUsers(string $sort)
{
    return User::orderByRaw($sort)->get();
}
HARDENED SECURE PATCH
<?php
// UserQueries.php: values are bound, sort columns come from an allowlist

use App\Models\User;
use Illuminate\Support\Facades\DB;

const SORTABLE = ['id' => 'id', 'email' => 'email', 'created' => 'created_at'];

// PDO: named marker, value supplied separately to execute()
function findEmail(PDO $pdo, int $userId): ?string
{
    $stmt = $pdo->prepare('SELECT email FROM users WHERE id = :id');
    $stmt->execute(['id' => $userId]);
    $row = $stmt->fetch();
    return $row['email'] ?? null;
}

// mysqli (PHP 8.2+): prepare, bind and execute in one call
function findOrders(mysqli $db, string $status, int $limit): array
{
    return $db->execute_query('SELECT id FROM orders WHERE status = ? LIMIT ?', [$status, $limit])
              ->fetch_all(MYSQLI_ASSOC);
}

// Laravel: the query builder binds the value
function findByEmail(string $email): ?object
{
    return DB::table('users')->where('email', $email)->first();
}

// allowlist lookup; orderBy only ever sees a known column
function listUsers(string $sort)
{
    return User::orderBy(SORTABLE[$sort] ?? 'id')->get();
}

Engineering & System Hardening Checklist

Sources