SQL Injection Prevention

What Is SQL Injection?

In simple terms: SQL injection happens when user input is pasted directly into an SQL query. An attacker can then type carefully crafted text that changes the query itself — logging in without a password, reading other customers' data, or deleting tables. It is one of the oldest and most damaging web vulnerabilities, and it is completely preventable.

Technically: SQL injection occurs when untrusted data is concatenated into an SQL string, so the database parser cannot distinguish code from data. The fix is to never mix them: send SQL with placeholders and the values separately (prepared statements), use allow-lists for SQL parts that cannot be parameters (identifiers, keywords), and limit the damage any query could do (least privilege).

How Attacks Work

The following examples only print the SQL that vulnerable code would build — they show what the database would receive.

1. Bypassing a Login

<?php
$email = "' OR 1=1 -- ";   // typed into the login form

$sql = "SELECT id FROM users WHERE email = '$email' AND active = 1";
echo $sql . "\n";

Output

SELECT id FROM users WHERE email = '' OR 1=1 -- ' AND active = 1

The quote closes the string; OR 1=1 makes the condition true for every row; -- turns the rest of the query into a comment. The query returns all users, and naive login code logs the attacker in as the first one — often an administrator.

2. Reading Other Tables With UNION

<?php
$search = "x' UNION SELECT email, password_hash FROM users -- ";

$sql = "SELECT name, price FROM products WHERE name LIKE '%$search%'";
echo $sql . "\n";

Output

SELECT name, price FROM products WHERE name LIKE '%x' UNION SELECT email, password_hash FROM users -- %'

The search results page now displays every user's email and password hash in place of product names.

3. Numeric Parameters Without Quotes

<?php
$id = '0 OR 1=1';   // from ?id=0%20OR%201=1

$sql = "DELETE FROM orders WHERE id = $id AND customer_id = 7";
echo $sql . "\n";

Output

DELETE FROM orders WHERE id = 0 OR 1=1 AND customer_id = 7

Because AND binds more tightly than OR, this deletes every order of customer 7 — and with slight variations, every order in the table. Numeric values need no quotes to be dangerous, which is why "escaping quotes" is not a reliable defense.

Other Forms

Defense 1: Prepared Statements for Every Value

<?php
require __DIR__ . '/src/pdo.php';

function findActiveUserId(PDO $pdo, string $email): ?int
{
    $stmt = $pdo->prepare('SELECT id FROM users WHERE email = :email AND active = 1');
    $stmt->execute(['email' => $email]);

    $id = $stmt->fetchColumn();
    return $id === false ? null : (int) $id;
}

var_dump(findActiveUserId(pdo(), "' OR 1=1 -- "));

Output

NULL

The database searched for a user whose email is literally ' OR 1=1 -- and found none. With prepared statements, the value is never parsed as SQL — no matter what it contains. This applies to SELECT, INSERT, UPDATE, and DELETE, in PDO (see PDO Prepared Statements) and MySQLi (see MySQL Prepared).

Use placeholders for every value, including:

• values that "cannot contain anything dangerous" (IDs, numbers, dates),
• values from your own database (to prevent second-order injection),
• values from cookies, headers, APIs, and files.

Defense 2: Allow-Lists for Identifiers and Keywords

Placeholders cannot stand for table names, column names, sort directions, or SQL keywords. When these depend on input, map the input to fixed SQL fragments:

<?php
function productOrderBy(?string $sort): string
{
    return match ($sort) {
        'price_asc' => 'price ASC',
        'price_desc' => 'price DESC',
        'newest' => 'created_at DESC',
        default => 'name ASC',
    };
}

echo productOrderBy('price_desc') . "\n";
echo productOrderBy('name; DROP TABLE products') . "\n";

Output

price DESC
name ASC

Input only chooses between fragments written in the code; it never becomes part of the SQL. The same approach applies to column filters, table names in admin tools, and ASC/DESC.

Dynamic Lists and LIKE Patterns

• For IN (...) lists, generate one placeholder per value (see MySQL Prepared).
• For LIKE searches, escape the wildcards % and _ in the value and still pass it as a parameter (see MySQL Where).

Defense 3: Least Privilege and Safe Error Handling

Even with perfect code, limit what a compromised query could do:

-- The web application's account: data access only
GRANT SELECT, INSERT, UPDATE, DELETE ON gocourse_shop.* TO 'gocourse_app'@'localhost';

-- A reporting dashboard's account: read-only
GRANT SELECT ON gocourse_shop.* TO 'gocourse_reports'@'localhost';

What Is Not a Sufficient Defense

Finding Vulnerable Code

• Search the code base for SQL built with variables: query("... $, "WHERE ... " ., sprintf("SELECT, ->whereRaw(.
• Review every query that uses data not written in the code itself.
• Use static analysis tools (PHPStan or Psalm with security rules) in your build pipeline.
• Test your own applications — only with permission — with tools such as sqlmap in a test environment.

Common Mistakes

Prepared Statements With Concatenated Parts

Effect: $pdo->prepare("SELECT * FROM products WHERE category = '$category'") is still vulnerable — preparing a string that already contains input changes nothing.

Fix: Put a placeholder in the SQL and pass the value to execute().

Trusting "Internal" Data

Effect: Second-order injection through stored values.

Fix: Parameterize every value, regardless of its source.

Building ORDER BY From Input

Effect: Injection through sort parameters.

Fix: Allow-list columns and directions.

Displaying Database Errors

Effect: Error messages guide attackers.

Fix: Log errors; show generic messages.

Best Practices

• Use prepared statements with placeholders for every value in every query.
• Map dynamic identifiers and keywords to allow-listed SQL fragments.
• Give the application a database user with minimal privileges.
• Hide database errors from users and log them for developers.
• Review code for string-built SQL and test regularly.

Official reference: https://www.php.net/manual/en/security.database.sql-injection.php