Close the SQL injection paths in a PHP PDO endpoint

SQL injection is not caused by “bad characters.” It appears when untrusted data is allowed to change the grammar of a SQL statement. Escaping guesses at syntax; parameterization keeps the statement structure and its data in separate channels.

Recognize the vulnerable boundary

$id = $_GET['id'];
$sql = "SELECT id, username, role FROM users WHERE id = $id";
$user = $pdo->query($sql)->fetch();

The input is concatenated into executable SQL. Validation may improve business correctness, but it is not a substitute for binding. The repair begins by making the SQL template constant:

$pdo = new PDO($dsn, $dbUser, $dbPassword, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$stmt = $pdo->prepare(
    'SELECT id, username, role FROM users WHERE id = :id'
);
$stmt->execute(['id' => $_GET['id']]);
$user = $stmt->fetch();

PHP’s PDO manual explicitly requires user-supplied values to be represented by parameter markers. Do not put quotes around the placeholder; the driver handles the value representation.

Validate meaning as well as syntax safety

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($id === false || $id === null || $id < 1) {
    http_response_code(400);
    exit('Invalid user id');
}

$stmt = $pdo->prepare(
    'SELECT id, username, role FROM users WHERE id = :id'
);
$stmt->bindValue('id', $id, PDO::PARAM_INT);
$stmt->execute();

Binding prevents the value from becoming SQL grammar. Validation separately rejects values that do not make sense for the endpoint. Keep those responsibilities distinct so future code review can see both controls.

Placeholders cannot represent identifiers or keywords

This does not work safely:

$stmt = $pdo->prepare(
    'SELECT id, name FROM products ORDER BY :column :direction'
);

Parameters represent complete data literals—not column names, table names, operators or sort directions. Map client choices to an allow-list:

$sortMap = [
    'name' => 'name',
    'price' => 'price_cents',
    'newest' => 'created_at',
];

$sort = $sortMap[$_GET['sort'] ?? 'newest'] ?? 'created_at';
$direction = ($_GET['direction'] ?? '') === 'asc' ? 'ASC' : 'DESC';

$sql = "SELECT id, name, price_cents
        FROM products
        ORDER BY {$sort} {$direction}
        LIMIT :limit";
$stmt = $pdo->prepare($sql);
$stmt->bindValue('limit', 50, PDO::PARAM_INT);
$stmt->execute();

The interpolated fragments originate exclusively from server-defined constants. Raw request values never enter the statement.

Build a variable-length IN clause correctly

$ids = array_values(array_filter(
    array_map('intval', $_GET['ids'] ?? []),
    static fn (int $id): bool => $id > 0
));

if ($ids === []) {
    return [];
}

$marks = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare(
    "SELECT id, name FROM products WHERE id IN ({$marks})"
);
$stmt->execute($ids);
$products = $stmt->fetchAll();

One placeholder represents one value. Generate only the placeholder punctuation, then pass values separately. Apply an upper bound to the number of IDs so an otherwise safe query cannot become an unbounded resource request.

Search patterns are data, but wildcards still have semantics

$term = str_replace(['\', '%', '_'], ['\\', '\%', '\_'], $term);
$stmt = $pdo->prepare(
    "SELECT id, title FROM articles
     WHERE title LIKE :term ESCAPE '\\'"
);
$stmt->execute(['term' => "%{$term}%"]);

Parameterization blocks SQL injection, yet % and _ remain LIKE wildcards. Escape them only when the product requirement is literal matching. If wildcard search is intended, retain them deliberately and control query cost.

Reduce the consequence of a missed query

• Use a dedicated application database account, never an administrative account.
• Grant only the tables and operations the application needs.
• Keep schema migration privileges separate from runtime credentials.
• Do not expose SQL errors, DSNs or stack traces to the client.
• Log a request correlation ID and server-side exception without logging secrets.

Prepared statements are the primary defense, not permission to give the runtime account unrestricted power.

Test the repair through the endpoint

Create automated cases for a normal value, invalid type, missing input, boundary values and strings containing quotes or SQL-like tokens. The expected result is not merely “the database survived”; malicious-looking input must be handled as data or rejected by the endpoint contract.

curl -i 'https://app.test/users?id=42'
curl -i --get 'https://app.test/users'   --data-urlencode "id=1' OR '1'='1"

Confirm the second request returns a validation error or no matching user, never additional rows or a database error. Add static analysis and code review checks for string concatenation near query(), exec() and raw framework expressions.

Prepared statements protect values. Allow-lists protect the parts of SQL that cannot be values. Least privilege limits what remains if either control is missed.

Sources: the long-running solved Stack Overflow question (CC BY-SA), PHP’s official SQL injection guidance, and the PDO::prepare reference.