Escaping SQL Identifiers Safely in PHP

Dynamic table and column names must be validated against a fixed allowlist.

The examples focus on the operation itself, including the important distinction: Prepared placeholders safely bind values, not SQL syntax.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

Read the input requirements before applying this pattern to production data.

Example 1: Basic Use

Only known column names from the allowlist can become SQL identifiers; PDO placeholders cannot represent identifiers.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$requested = 'email';
$allowed = ["id", "email", "active"];
if (!in_array($requested, $allowed, true)) { throw new InvalidArgumentException("Invalid sort column"); }
$stmt = $pdo->query("SELECT id, email FROM users ORDER BY `" . $requested . "` LIMIT 10");
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

Only known column names from the allowlist can become SQL identifiers; PDO placeholders cannot represent identifiers.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$requested = 'id';
$allowed = ["id", "email", "active"];
if (!in_array($requested, $allowed, true)) { throw new InvalidArgumentException("Invalid sort column"); }
$stmt = $pdo->query("SELECT id, email FROM users ORDER BY `" . $requested . "` LIMIT 10");
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

Only known column names from the allowlist can become SQL identifiers; PDO placeholders cannot represent identifiers.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$requested = 'active';
$allowed = ["id", "email", "active"];
if (!in_array($requested, $allowed, true)) { throw new InvalidArgumentException("Invalid sort column"); }
$stmt = $pdo->query("SELECT id, email FROM users ORDER BY `" . $requested . "` LIMIT 10");
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

Bound parameters cannot replace SQL identifiers, and escaping identifier text alone does not enforce authorization.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Dynamic table and column names must be validated against a fixed allowlist.
• Bound parameters cannot replace SQL identifiers, and escaping identifier text alone does not enforce authorization.
• Prepared placeholders safely bind values, not SQL syntax.

Use Escaping SQL Identifiers Safely in PHP when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.