MySQL SQL Injection Prevention

MySQL SQL Injection: How Attacks Work and How to Stop Them

SQL injection happens when untrusted user input is concatenated directly into a MySQL query string, allowing an attacker to alter the SQL command itself. Attackers use SQL injection to bypass login checks, dump sensitive tables, or delete database records.

Vulnerable Query vs. Parameterized Query

Never build SQL strings by interpolating variables directly from HTTP requests:

// VULNERABLE: Attacker sends email = "' OR '1'='1"
$sql = "SELECT id, email FROM demo_customers WHERE email = '" . $_POST['email'] . "'";

When the attacker submits ' OR '1'='1, the query becomes WHERE email = '' OR '1'='1', which evaluates to true for every single row in the table.

Practical Fix Using Parameterized Queries

Always pass user values through prepared statement placeholders so the database engine treats input strictly as literal data—never executable SQL:

// SAFE: Real prepared statement with bound parameter
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('SELECT id, first_name, email FROM demo_customers WHERE email = ?');
$stmt->execute([$_POST['email']]);
$customer = $stmt->fetch(PDO::FETCH_ASSOC);

Defense-in-Depth Checklist

• Use Prepared Statements Everywhere: Every SELECT, INSERT, UPDATE, and DELETE containing dynamic values must use parameterized placeholders (? or :named).
• Allowlist Dynamic Identifiers: Because placeholders cannot bind table names or ORDER BY columns, validate user-selected sort fields against a strict associative map (match ($sort) { 'price' => 'price', default => 'id' }).
• Escape LIKE Wildcards: After parameterizing a LIKE ? search term, escape literal % and _ in user input so search queries cannot trigger slow wildcard scans.
• Apply Least-Privilege Accounts: Never connect web apps as root or grant FILE / DROP privileges unnecessarily.

Frequently Asked Questions

Is mysqli_real_escape_string() enough to prevent SQL injection?

Escaping is fragile compared to prepared statements because charset misconfigurations or unquoted numeric parameters can still be exploited; always prefer parameterized queries.

Are ORMs like Laravel Eloquent safe from SQL injection?

Standard Eloquent methods (where('email', $email)) use prepared bindings automatically, but raw methods like whereRaw("name = '$input'") are still vulnerable unless bindings are passed as the second array argument.

Does PDO::ATTR_EMULATE_PREPARES => false matter?

Yes, disabling emulation forces native MySQL server-side prepared statements instead of client-side string escaping.