meet it

Placeholders keep visitors' text out of your SQL.

Never glue a visitor's text into SQL. A name like Bob'; DROP TABLE users would end your text early at the quote, and the rest would run as SQL. That is , one of the most common attacks on websites.

Instead, the SQL with a , ?, wherever a value goes: $db->prepare("INSERT INTO users (name) VALUES (?)"). Then execute([$name]) sends the values separately, in order. The database treats them as plain data, never as commands.

prepare gives back an object of its own. Call execute on it as often as you like, then fetch or fetchAll after a SELECT.

<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE users (name TEXT, town TEXT)");
$add = $db->prepare("INSERT INTO users (name, town) VALUES (?, ?)");
$add->execute(["Ana", "Leeds"]);
$add->execute(["Bob'; DROP TABLE users", "Hull"]);
$find = $db->prepare("SELECT name FROM users WHERE town = ?");
$find->execute(["Hull"]);
echo $find->fetch(PDO::FETCH_ASSOC)["name"] . "\n";

Placeholders take values only, so table and column names are always written into the SQL by you.

guess first

One note has apostrophes in it. What will this show?

Guess before you run it. Wrong guesses are how you learn, and nothing is marked.

<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE notes (text TEXT)");
$save = $db->prepare("INSERT INTO notes (text) VALUES (?)");
foreach (["Buy milk", "It's Tom's turn"] as $note) {
    $save->execute([$note]);
}
$rows = $db->query("SELECT text FROM notes")->fetchAll(PDO::FETCH_ASSOC);
echo count($rows) . ": " . $rows[1]["text"] . "\n";

your turn

Your turn. Cast a vote safely.

Type a name and a favourite colour. Apostrophes are fine now, so try a name like O'Neill. Then run it.

Your code

press run to see what happens

fix it

D'Arcy cannot sign the guest list. Can you fix it?

Broken code to fix

press run to see what happens

The PDOException says something is wrong near Arcy. What does the apostrophe in D'Arcy do to the SQL?
The name is glued into the SQL, so its apostrophe ends the text early. Send it separately instead.
Replace line 5 with $add = $db->prepare("INSERT INTO guests (name) VALUES (?)"); and $add->execute([$_POST["name"]]);, then run.

nailed it

your database is safe from sneaky input. :D

Never glue a visitor's text into SQL.
prepare the SQL with ? placeholders.
execute([...]) sends the values as plain data.

new words in your collection

SQL injection
prepare
placeholder

This lesson isn’t saved. A free account remembers every lesson you finish.