PHP
Prepared statements with prepare()
Learn to create a prepared statement with prepare() and why it is safer.
What does prepare() do?
The prepare() method builds a SQL template and separates data from the SQL command. The fixed part stays in the text; variable values go into placeholders — either ? or :name.
PDO compiles the template but does not run it; the returned PDOStatement object is yours to control. You pass the values later with execute().
This approach boosts security: SQL injection attacks are blocked and the same query can be re-run quickly.
The two-step flow
A prepared statement runs in two steps. First prepare() takes the template and returns a PDOStatement object. Then execute() binds values to placeholders and runs the query.
Values are passed as an array; for example execute([2]). You can re-run the same $stmt object with execute() using different values, which boosts performance.
Results are retrieved with fetch() or fetchAll().
Exercise Find a single row with prepare
Build the template 'SELECT name FROM users WHERE id = ?' with prepare(). Run it with execute([2]), fetch the row with fetch(PDO::FETCH_ASSOC) and print the name field.
Show example solution
:FETCH_ASSOC). Print with echo $row['name'] . "\n".
Example solution:
<?php
// Build a ? placeholder template with prepare and run it with execute
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
$stmt->execute([2]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'] . "\n";
Test yourself
-
What advantage does prepare() provide?
- Separates the SQL template from data, prevents injection
- Always runs the query faster
- Never throws an error when run
- Removes the need to create tables
Show answer
Separates the SQL template from data, prevents injection — Correct! prepare() separates SQL from data, blocking injection.
-
Complete the prepare() call
$stmt = $pdo->____("SELECT id FROM users WHERE name = ?");
Show answer
$stmt = $pdo->prepare("SELECT id FROM users WHERE name = ?");
-
Match each method to its job
- prepare()
- Builds the query template
- execute()
- Runs the prepared statement
- fetch()
- Returns one result row
Next lesson: Running with execute()
