meet it

See SQL injection work, and you will never glue SQL again.

Treat every form field, link and file as . Most visitors are kind, but an attacker sends a on purpose: text built to break in. Here the payload ' OR '1'='1 lands in a login query glued together with ..

The quote ends your string early, and the rest becomes SQL that is always true. Every row matches, so the attacker is in without the password. Placeholders cure this, but they only hold values. A column name for sorting must come from an of names you wrote yourself.

<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE users (name TEXT, password TEXT)");
$db->exec("INSERT INTO users VALUES ('mia', 'hunter2')");
$_POST = ["name" => "mia", "password" => "' OR '1'='1"];
$sql = "SELECT COUNT(*) FROM users WHERE name = '" . $_POST["name"]
    . "' AND password = '" . $_POST["password"] . "'";
echo $sql . "\n";
$found = $db->query($sql)->fetchColumn();
echo "Matching users: $found\n";

fetchColumn() hands back the first value of the next row, which is ideal for a COUNT(*).

guess first

Same payload, but with placeholders. 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 users (name TEXT, password TEXT)");
$db->exec("INSERT INTO users VALUES ('mia', 'hunter2')");
$_POST = ["name" => "mia", "password" => "' OR '1'='1"];
$query = $db->prepare("SELECT COUNT(*) FROM users WHERE name = ? AND password = ?");
$query->execute([$_POST["name"], $_POST["password"]]);
echo "Matching users: " . $query->fetchColumn() . "\n";

your turn

Your turn. Sort films by a column from the link.

Line 5 pretends the link asked to sort by something. Type year, title, or a sneaky payload. The allow-list only lets real column names into the SQL.

Your code

press run to see what happens

fix it

Liam is on the guest list, but the search crashes. Fix it.

Broken code to fix

press run to see what happens

The PDOException says there is a syntax error near Brien. What does the apostrophe in O'Brien do to the glued SQL?
The apostrophe ends the SQL string early, exactly like a payload would. Gluing hurts honest visitors too.
Replace line 9 with a prepare that uses ?, then execute([$_GET["name"]]), and echo its fetchColumn().

nailed it

your queries shut out injection. :D

Treat every outside value as untrusted input, honest or not.
Placeholders keep values as data, whatever quotes they contain.
Column names cannot be placeholders, so pick them from an allow-list.

new words in your collection

untrusted input
payload
allow-list

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