Prepared statements

Queries almost always need outside data: an id from the URL, a name from a form. Inserting it straight into the SQL text is dangerous. Prepared statements solve this: the query text and the data are sent to the database separately.

The examples use an in-memory SQLite database and cannot be run here — run them on your own machine.

A query with a parameter

Put a question mark in the query where the value belongs. prepare() prepares the query, and execute() runs it with the given values:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
$pdo->exec("INSERT INTO users (name, age) VALUES ('Anna', 25), ('Oleg', 31)");
 
$statement = $pdo->prepare('SELECT age FROM users WHERE name = ?');
$statement->execute(['Anna']);
 
echo $statement->fetchColumn(); // 25

No quotes are needed around the question mark — the database knows it is a string.

Named parameters

With several parameters it is easier to name them. In the query a name is written with a colon:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
$pdo->exec("INSERT INTO users (name, age) VALUES ('Anna', 25), ('Oleg', 31)");
 
$statement = $pdo->prepare('SELECT name FROM users WHERE age > :min AND age < :max');
$statement->execute(['min' => 20, 'max' => 30]);
 
echo $statement->fetchColumn(); // Anna

Adding a record

INSERT works the same way. lastInsertId() returns the id of the new row:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
 
$statement = $pdo->prepare('INSERT INTO users (name, age) VALUES (:name, :age)');
$statement->execute(['name' => 'Anna', 'age' => 25]);
 
echo $pdo->lastInsertId(); // 1

Updating and deleting

For UPDATE and DELETE the rowCount() method tells you how many rows were affected:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
$pdo->exec("INSERT INTO users (name, age) VALUES ('Anna', 25), ('Oleg', 31)");
 
$statement = $pdo->prepare('UPDATE users SET age = :age WHERE name = :name');
$statement->execute(['age' => 26, 'name' => 'Anna']);
 
echo $statement->rowCount(); // 1

Why it matters

If you build a query by joining strings, a visitor can change its meaning. Here a piece of SQL is passed instead of a name — and the query returns every user:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
$pdo->exec("INSERT INTO users (name, age) VALUES ('Anna', 25), ('Oleg', 31)");
 
$name = "' OR '1'='1";
 
$unsafe = $pdo->query("SELECT * FROM users WHERE name = '$name'")->fetchAll();
echo count($unsafe), "\n"; // 2
 
$statement = $pdo->prepare('SELECT * FROM users WHERE name = ?');
$statement->execute([$name]);
echo count($statement->fetchAll()); // 0

This attack is called SQL injection. In a prepared statement the same string is just an odd name that is not in the table.

A common mistake

A parameter can stand in only for a value. A table or column name cannot be supplied through ?. If it depends on the user's choice — the column to sort by, for example — check it against a list of allowed values.

Thanks for reading!