SpiritCode.blog

SQL Injection Prevention in PHP: Stop Raw Queries

SQL Injection Prevention in PHP: Stop Raw Queries

Meta description: I’ve audited dozens of PHP codebases and SQL injection is still the #1 vulnerability I find — here’s exactly how I fix it using prepared statements and PDO.

Last updated: June 2026

The Bug That Should Have Killed That App

SQL injection prevention in PHP is non-negotiable — and I say that after finding this exact vulnerability in a live production system with real customer data. A few years ago I audited a PHP e-commerce app for a client. The login form looked fine on the surface. Then I tried entering ' OR '1'='1 in the username field. I was in — as admin — without a password.

The root cause was a single line:

$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";

That’s it. One string concatenation, full database compromise. I’ve seen this pattern hundreds of times, and it’s still shockingly common in raw PHP codebases today.

TL;DR

  • Never concatenate user input directly into SQL strings — ever, under any circumstances.
  • Use PDO prepared statements with named or positional placeholders as your default approach in all new PHP code.
  • Input validation and escaping are complements to prepared statements, not replacements.

Why SQL Injection in PHP Is Still Rampant

SQL injection has been OWASP’s #1 or #2 web application security risk for over a decade. [SOURCE: https://owasp.org/www-project-top-ten/]

PHP makes it dangerously easy to write vulnerable code because string interpolation is so natural to the language. A developer in a hurry writes:

$result = mysqli_query($conn, "SELECT * FROM orders WHERE id = $id");

…and doesn’t think twice. If $id comes from $_GET['id'], an attacker can append ; DROP TABLE orders; -- and your day is ruined.

The old mysql_real_escape_string() function — which many legacy codebases still rely on — is not sufficient either. It can be bypassed with multi-byte character encoding attacks, and the mysql_* extension is removed entirely in PHP 7+.

Prerequisites

To follow the examples in this guide you’ll need:

  • PHP 7.4+ (PDO is built in; no extension to install)
  • A MySQL or PostgreSQL database (examples use MySQL syntax)
  • Basic familiarity with PHP classes and OOP syntax

How to Fix SQL Injection Vulnerabilities in Raw PHP Queries

Step 1: Replace mysqli_query Concatenation with PDO Prepared Statements

PDO (PHP Data Objects) is the right abstraction for database access in modern PHP. It supports prepared statements natively across MySQL, PostgreSQL, SQLite, and others.

First, establish a PDO connection with security-hardened options:

<?php
$dsn = 'mysql:host=localhost;dbname=myapp;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false, // CRITICAL: use real prepared statements
];

try {
    $pdo = new PDO($dsn, 'db_user', 'db_password', $options);
} catch (PDOException $e) {
    // Never expose $e->getMessage() to the user in production
    error_log($e->getMessage());
    die('Database connection failed.');
}

Security Note: Setting PDO::ATTR_EMULATE_PREPARES => false is essential. When set to true (the PDO default in some environments), PDO emulates prepared statements by doing string escaping client-side — which can be bypassed. With false, the actual parameterized query is sent to the database engine.

Step 2: Use Named Placeholders for SELECT Queries

// VULNERABLE — do not do this
$stmt = $pdo->query("SELECT * FROM users WHERE email = '" . $_POST['email'] . "'");

// SAFE — do this instead
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute([':email' => $_POST['email']]);
$user = $stmt->fetch();

The :email placeholder is never interpolated into the SQL string. The database driver sends the query structure and the parameter value separately. An attacker can’t escape the parameter context.

Step 3: Use Positional Placeholders for Simpler Queries

For short queries, positional ? placeholders are cleaner:

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

Both styles are equally safe. I prefer named placeholders in complex queries because the code is easier to read and maintain six months later.

Step 4: Secure INSERT, UPDATE, and DELETE Statements

The same approach applies to write operations:

// INSERT
$stmt = $pdo->prepare(
    'INSERT INTO orders (user_id, product_id, quantity) VALUES (:user_id, :product_id, :qty)'
);
$stmt->execute([
    ':user_id'    => $userId,
    ':product_id' => $productId,
    ':qty'        => $quantity,
]);

// UPDATE
$stmt = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');
$stmt->execute([':email' => $newEmail, ':id' => $userId]);

// DELETE
$stmt = $pdo->prepare('DELETE FROM sessions WHERE token = :token AND user_id = :uid');
$stmt->execute([':token' => $token, ':uid' => $userId]);

Step 5: How Do You Handle Dynamic ORDER BY Clauses Safely in PHP?

This is the gotcha that catches even experienced developers. Prepared statements cannot parameterize column names, table names, or SQL keywords like ORDER BY direction.

If you need a user-controlled sort column, use an allowlist:

$allowed_columns = ['name', 'created_at', 'price'];
$sort_by = $_GET['sort'] ?? 'created_at';

// Validate against allowlist — never trust user input here
if (!in_array($sort_by, $allowed_columns, strict: true)) {
    $sort_by = 'created_at'; // safe default
}

// Now safe to interpolate because we control the value
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY {$sort_by} ASC");
$stmt->execute();

The same pattern applies to ASC/DESC direction: validate it explicitly before interpolating.

Important: This is one of the most misunderstood areas of SQL injection prevention in PHP. Developers assume prepared statements protect everything — they don’t protect identifiers. Always allowlist-validate any identifier that comes from user input.

Step 6: Use MySQLi Prepared Statements as an Alternative

If you’re maintaining a legacy codebase that uses mysqli and a full PDO migration isn’t feasible, here’s the safe mysqli equivalent:

$conn = new mysqli('localhost', 'user', 'pass', 'mydb');

$stmt = $conn->prepare('SELECT * FROM users WHERE username = ? AND active = ?');
$stmt->bind_param('si', $_POST['username'], $active); // s=string, i=integer
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
$stmt->close();

The bind_param type string ('si') enforces type safety too, which adds a second layer of validation. That said, I always recommend PDO for new code because the API is more consistent and it supports more database engines.

[INTERNAL LINK: related article on PHP security hardening checklist]

[SOURCE: https://www.php.net/manual/en/pdo.prepared-statements.php]

Real-World PHP Security Tips I Use in Production

Wrap your PDO connection in a singleton or DI container. Creating a new PDO connection per query is wasteful and can exhaust connection limits. I inject a shared PDO instance via a service container in every project.

Log query errors, never display them. PDO::ERRMODE_EXCEPTION throws PDOException on errors — catch it at the application layer, log the message server-side, and show the user a generic “something went wrong” message. Stack traces in browser output are free information for attackers.

Pair prepared statements with input validation. Prepared statements prevent SQL injection, but they don’t prevent application logic abuse. Validate that $quantity is a positive integer, that $email matches a valid format, and so on. Defense in depth.

Audit legacy code with a static analyzer. Tools like phpstan with the phpstan-dba extension or psalm can flag unsafe query construction automatically. I run these in CI on every PR for PHP projects.

Common PDO Errors and How I Fixed Them

Error: PDOException: SQLSTATE[HY093]: Invalid parameter number — The number of bound parameters doesn’t match the number of placeholders in the query. Count them carefully, especially in dynamic queries built with loops.

Error: PDOException: could not find driver — The PDO MySQL extension isn’t enabled. On Ubuntu/Debian: sudo apt install php-mysql then restart PHP-FPM. On shared hosting, check phpinfo() for available PDO drivers.

Silent empty results after migration — I’ve seen this when ATTR_EMULATE_PREPARES is false and the MySQL server version is old (pre-5.1). The server can’t handle true binary protocol prepared statements. Upgrade MySQL, or as a temporary fix only, set emulation back to true while you plan the upgrade.

FAQ

Q: What is the most effective way to prevent SQL injection in PHP without using an ORM? A: PDO prepared statements with ATTR_EMULATE_PREPARES set to false is the most effective raw-PHP approach. They send query structure and parameters separately to the database engine, making injection structurally impossible — not just escaped.

Q: Is mysqli_real_escape_string enough to prevent SQL injection in PHP? A: No. It’s insufficient for several reasons: it can be bypassed with multi-byte encoding in certain MySQL configurations, it doesn’t protect column or table identifiers, and it relies on correct usage (developers forget to call it). Prepared statements are the correct solution.

Q: Can SQL injection happen even when I use PDO in PHP? A: Yes, in one specific scenario: when you interpolate unsanitized user input directly into the query string before calling prepare(), or when you interpolate column/table names without allowlist validation. Prepared statements protect values — not SQL identifiers.

Q: How do I prevent SQL injection in PHP when building dynamic WHERE clauses? A: Build the placeholder list dynamically, then bind values separately. For example, for an IN clause: generate ?,?,? placeholders based on array length, then pass the array to execute(). Never concatenate the values themselves.

Q: What tools can I use to detect SQL injection vulnerabilities in an existing PHP codebase? A: For static analysis: phpstan with security extensions, psalm, or semgrep with PHP rulesets. For dynamic testing: SQLMap against a staging environment. For manual audits: search the codebase for ->query(, mysqli_query(, and any string where $_GET, $_POST, or $_REQUEST appears inside a SQL string.

Conclusion

SQL injection prevention in PHP is one of the oldest and most preventable problems in web development — and it’s still being introduced into codebases every day. Switching to PDO prepared statements is a one-time habit change that eliminates an entire class of attacks.

If you found a SQL injection issue in your own codebase after reading this, I’d love to hear about it in the comments. Share this post with your team — it might save someone a very bad Monday.

About the Author

I’m a software engineer and security practitioner with over 8 years of experience in PHP, Python, and DevSecOps. I’ve audited production PHP applications across e-commerce, fintech, and SaaS, and I specialize in building secure-by-default backend systems. I write about web security and backend engineering on SpiritCode.