Parameterised Queries and Prepared Statements

The definitive defence against SQL injection is parameterised queries, also called prepared statements. Instead of interpolating user input into the query string, the developer writes the query with placeholders (? or :name) and passes the values separately. The database driver sends the query structure and the data as distinct packets; the database server compiles the query plan before it ever sees the user data, making injection structurally impossible. All major database drivers and ORMs support parameterised queries. PHP PDO and MySQLi both support prepared statements. Python's DB-API uses ? or %s placeholders. Node's pg library accepts $1 parameters. Java uses PreparedStatement. ORMs like Eloquent, SQLAlchemy, and Sequelize parameterise queries automatically unless the developer explicitly opts into raw query mode. There are two edge cases where parameterisation alone is insufficient. First, dynamic identifiers such as table names and column names cannot be parameterised — they must be validated against a strict allowlist. Second, ORDER BY column names come from user input in many UIs; validate them against an allowlist of allowed column names. Never use a denylist for SQL injection defence; allowlists are far more robust because they reject anything not explicitly permitted.

Example

<?php
// SAFE: PDO prepared statements

$dsn = 'mysql:host=localhost;dbname=myapp;charset=utf8mb4';
$pdo = new PDO($dsn, 'app_web', getenv('DB_PASS'), [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,  // use true prepared statements
]);

// Named placeholders (recommended for clarity)
$stmt = $pdo->prepare(
    'SELECT id, username, email FROM users WHERE username = :username AND active = :active'
);
$stmt->execute(['username' => $_POST['username'], 'active' => 1]);
$user = $stmt->fetch();

// ----------------------------------------------------------------
// Python (psycopg2 — PostgreSQL)
// ----------------------------------------------------------------
// import psycopg2
// conn = psycopg2.connect(DATABASE_URL)
// cur  = conn.cursor()
// cur.execute(
//     "SELECT id, email FROM users WHERE username = %s AND active = %s",
//     (username, True)   # values passed separately — safe
// )

// ----------------------------------------------------------------
// Node.js (pg — PostgreSQL)
// ----------------------------------------------------------------
// const { rows } = await pool.query(
//     'SELECT id, email FROM users WHERE username = $1 AND active = $2',
//     [username, true]
// );

// ----------------------------------------------------------------
// Dynamic ORDER BY — allowlist approach
// ----------------------------------------------------------------
$allowed_columns = ['username', 'email', 'created_at'];
$order_by = in_array($_GET['sort'] ?? '', $allowed_columns, true)
    ? $_GET['sort']
    : 'created_at';
$stmt = $pdo->prepare("SELECT * FROM users ORDER BY {$order_by} DESC");
$stmt->execute();

HTML

Full Editor

Related Resources

Web Security Reference

Complete tag & property list

Web Security How-To Guides

Step-by-step practical guides

Web Security Exercises

Practice what you've learned