MySQL Prepared Statements

Prepared statements separate the SQL from the data. The database parses the SQL once and binds your values safely — no string concatenation, no injection.

Why prepared

Positional placeholders

$stmt = $pdo->prepare('SELECT id, name FROM customers WHERE country = ?');
$stmt->execute(['AU']);
print_r($stmt->fetchAll());

Named placeholders

$stmt = $pdo->prepare(
    'SELECT * FROM customers WHERE country = :c AND active = :a'
);
$stmt->execute([':c' => 'AU', ':a' => 1]);

bindValue vs bindParam vs execute(array)

When the type matters

For LIMIT / OFFSET placeholders, MySQL needs an integer. With EMULATE_PREPARES = false, you must bind explicitly:

$stmt = $pdo->prepare('SELECT * FROM customers LIMIT ?');
$stmt->bindValue(1, 20, PDO::PARAM_INT);
$stmt->execute();

What you CAN'T bind

• Table or column names — only values.
• ORDER BY direction — that's part of the SQL grammar.
• LIMIT inside an IN list — but you can build the placeholder string dynamically.

For these, use an allow-list — never accept user input directly into the SQL string.

Example

<?php
// ✗ injection risk
// $pdo->query("SELECT * FROM users WHERE email = '$email'");
// ✓ safe
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);

Exercise

Test yourself