PHP Sql Injection Prevention

SQL injection is one of the oldest and most damaging web application vulnerabilities: it happens when untrusted input (like a form field or URL parameter) is inserted directly into a SQL query string, letting an attacker change the meaning of that query. In PHP, SQL injection is entirely preventable once you understand why it happens and how to write queries that separate data from code. This lesson covers the mechanics of the attack, the correct defenses (prepared statements with PDO and MySQLi), and the habits that keep your database code safe in real applications.

Overview: How SQL Injection Happens

A SQL injection vulnerability exists whenever a query is built by concatenating raw, untrusted strings into SQL syntax. Consider a login check built with string concatenation:

"SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'"

If an attacker submits ' OR '1'='1 as the username, the query becomes SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...' — a condition that can be true for every row, potentially bypassing authentication entirely. Worse, an attacker can chain additional statements, extract data with UNION SELECT, or use boolean/time-based blind techniques to exfiltrate an entire database one bit at a time, even when the page shows no visible error.

The root cause is that the database driver cannot tell the difference between “SQL syntax the developer wrote” and “data that happens to contain SQL-like characters.” The fix is not to sanitize or escape strings by hand — it’s to never let user data become part of the SQL text in the first place. That’s exactly what prepared statements do: the query structure is sent to the database first, compiled, and only then is the user data bound in as pure data, which the database engine can never reinterpret as SQL syntax.

Why escaping alone is not enough

Functions like the old mysql_real_escape_string() (removed in PHP 7) or manually replacing quotes can reduce risk in narrow cases, but they are fragile: they depend on the developer remembering to escape every single value, on every code path, correctly, for the current character set and quoting context (string literal, identifier, LIKE pattern, numeric context, etc.). Miss one spot and you have a vulnerability. Prepared statements remove the need to think about escaping at all for query parameters, which is why they are the industry-standard defense.

Syntax

Both major PHP database extensions support prepared statements. The general pattern is the same for both:

1. Prepare the query with placeholders instead of raw values
2. Bind or pass the real values separately
3. Execute the statement
4. Fetch the results
Placeholder style Example Extension
Positional (?) WHERE id = ? PDO, MySQLi
Named (:name) WHERE id = :id PDO only
  • Placeholder — marks where a value belongs; never used for table or column names.
  • Bound value — the actual PHP variable, sent to the database as typed data, not text glued into the query.
  • execute() — runs the compiled statement with the bound values.

Examples

Example 1: Safe search with PDO and a positional placeholder

<?php
declare(strict_types=1);

$pdo = new PDO(
    'mysql:host=localhost;dbname=shop;charset=utf8mb4',
    'db_user',
    'db_pass',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$searchTerm = "O'Brien's Widgets"; // could contain a quote safely

$stmt = $pdo->prepare('SELECT name, price FROM products WHERE name LIKE ? LIMIT 5');
$stmt->execute(['%' . $searchTerm . '%']);

echo "Query prepared with placeholder, bound value: " . $searchTerm . "\n";
echo "SQL sent: SELECT name, price FROM products WHERE name LIKE ? LIMIT 5\n";

Output:

Query prepared with placeholder, bound value: O'Brien's Widgets
SQL sent: SELECT name, price FROM products WHERE name LIKE ? LIMIT 5

Notice the apostrophes in O'Brien's Widgets never break the query. Because the value is bound after the statement is compiled, the database treats it purely as data — there is no way for it to close a string literal or inject new SQL syntax.

Example 2: Named placeholders for a login check

<?php
declare(strict_types=1);

function findUser(PDO $pdo, string $username, string $passwordHash): array|false
{
    $stmt = $pdo->prepare(
        'SELECT id, username FROM users WHERE username = :username AND password_hash = :hash'
    );
    $stmt->execute([
        'username' => $username,
        'hash' => $passwordHash,
    ]);

    return $stmt->fetch(PDO::FETCH_ASSOC);
}

$attackerInput = "admin' OR '1'='1";
echo "Attempted username: " . $attackerInput . "\n";
echo "Treated as literal data, not SQL, so the query safely returns no match.\n";

Output:

Attempted username: admin' OR '1'='1
Treated as literal data, not SQL, so the query safely returns no match.

Named placeholders (:username, :hash) make queries with several parameters easier to read and less error-prone than counting ? marks in order. The classic ' OR '1'='1 injection payload is neutralized because it is bound as a single string value, not parsed as SQL.

Example 3: The same defense with MySQLi

<?php
declare(strict_types=1);

$mysqli = new mysqli('localhost', 'db_user', 'db_pass', 'shop');
$mysqli->set_charset('utf8mb4');

$productId = 42;

$stmt = $mysqli->prepare('SELECT name, stock FROM products WHERE id = ?');
$stmt->bind_param('i', $productId);
$stmt->execute();

$result = $stmt->get_result();
echo "Prepared MySQLi query for product id: " . $productId . "\n";
echo "Type specifier used: 'i' (integer)\n";

Output:

Prepared MySQLi query for product id: 42
Type specifier used: 'i' (integer)

MySQLi requires an explicit type string in bind_param() (i integer, d double, s string, b blob) for each bound value, in order. PDO does not require this — it infers types automatically unless you call bindValue() with an explicit PDO::PARAM_* constant.

How It Works Under the Hood

When you call prepare(), PHP sends the SQL text — with placeholders still in place — to the database server (or, in emulated mode, to the PDO driver itself). The server parses and compiles that statement into an execution plan before it has ever seen your data. When you later call execute(), only the bound values travel to the server, tagged as parameters for the already-compiled plan. Because the plan’s structure is frozen at compile time, no value bound afterward can alter which columns, tables, or clauses are used — a bound string can never turn into a new WHERE clause or a second statement. This is fundamentally different from string concatenation, where the “data” and the “code” are the same text stream at the moment the server parses it.

One nuance: PDO’s default emulated prepares mode (PDO::ATTR_EMULATE_PREPARES, true by default for MySQL) builds the final query client-side using proper escaping before sending it, rather than using the server’s native prepare protocol. It is still safe against injection because PDO does the escaping correctly and consistently — but for the strongest guarantees and better performance with repeated queries, many developers explicitly disable it with $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); to force true native server-side prepares.

Common Mistakes

Mistake 1: Concatenating user input into the query string

<?php
$id = $_GET['id'];
$result = $pdo->query("SELECT * FROM users WHERE id = $id");

This is the classic vulnerability: $id could be 1 OR 1=1 or worse. Any value that reaches query() as part of the SQL string is a potential injection point, even if it “looks like” it should just be a number.

Fixed version:

<?php
$id = (int) $_GET['id'];
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$id]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

Mistake 2: Trying to bind table or column names as parameters

<?php
$column = $_GET['sort'];
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY ?');
$stmt->execute([$column]); // does NOT work as expected

Placeholders only work for values, never for identifiers like column or table names — the database will try to sort by the literal string value, not use it as a column reference, and the query will fail or behave incorrectly. Identifiers must be validated against a strict allow-list instead.

Fixed version:

<?php
$allowedColumns = ['name', 'price', 'created_at'];
$column = in_array($_GET['sort'], $allowedColumns, true) ? $_GET['sort'] : 'name';
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY {$column}");
$stmt->execute();

Mistake 3: Assuming type-casting alone is a full defense

Casting with (int) is a good practice for numeric IDs, but it only helps for that one narrow case. It does nothing for strings, dates, or search terms, so it should be treated as a bonus safeguard on top of prepared statements — never a replacement for them.

Best Practices

  • Always use prepared statements with bound parameters for any query that includes a variable, no exceptions.
  • Never build SQL by concatenating $_GET, $_POST, $_COOKIE, or any other user-controlled data into the query string.
  • Use a strict allow-list to validate dynamic table or column names, since placeholders cannot bind identifiers.
  • Set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so database errors surface as exceptions instead of failing silently.
  • Apply the principle of least privilege: the database user your application connects as should only have the permissions it actually needs (no DROP/GRANT rights for a read-mostly web app account).
  • Validate and cast input types (e.g. (int) for IDs) as defense in depth, in addition to — never instead of — prepared statements.
  • Avoid legacy APIs like the removed mysql_* extension; use PDO or MySQLi exclusively.
  • Log and monitor unusual query errors, which can indicate injection probing attempts.

Practice Exercises

  • Exercise 1: Rewrite this vulnerable snippet using a PDO prepared statement: $pdo->query("SELECT * FROM orders WHERE customer_email = '" . $_POST['email'] . "'").
  • Exercise 2: Write a function findProductsByCategory(PDO $pdo, string $category): array that safely queries a products table filtered by category using a named placeholder, and returns all matching rows as an associative array.
  • Exercise 3: Given a user-supplied sort column and sort direction from $_GET['sort'] and $_GET['dir'], write code that safely applies them to an ORDER BY clause using allow-lists for both, since neither can be a bound placeholder.

Summary

  • SQL injection happens when untrusted data becomes part of the SQL syntax itself, usually through string concatenation.
  • Prepared statements (PDO or MySQLi) fix this by compiling the query structure before any data is bound, so bound values can never change the query’s meaning.
  • PDO supports both positional (?) and named (:name) placeholders; MySQLi supports positional placeholders with explicit type specifiers via bind_param().
  • Placeholders only work for values, never for table or column names — use a strict allow-list for dynamic identifiers.
  • Escaping functions and type casting are useful defense-in-depth measures, but prepared statements are the primary, non-negotiable defense against SQL injection in PHP.