Abschnitt 14 · Lektion 4
Vorbereitete Anweisungen
Ein Suchwort kommt aus einem Formular und soll in eine Abfrage. Klebst du es in den Text, schreibt
ab da der Besucher an deinem SQL mit. Schon ein Name wie O'Brien zerlegt dann die Abfrage.
Der naheliegende Weg
<?php
declare(strict_types=1);
// Sechs Notizen von drei Leuten. Einer davon traegt einen
// Apostroph im Namen, und darum geht es in dieser Lektion.
//
// Die Tabelle wird bei jedem Lauf neu aufgebaut. An dieser Datei
// aenderst du nichts.
$db = new PDO("sqlite:" . __DIR__ . "/daten.db", null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("DROP TABLE IF EXISTS notizen");
$db->exec("CREATE TABLE notizen (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
verfasser TEXT NOT NULL
)");
$einfuegen = $db->prepare("INSERT INTO notizen (titel, verfasser) VALUES (?, ?)");
foreach ([
["Milch", "Mia"],
["Brot", "O'Brien"],
["Kaffee", "Mia"],
["Tee", "O'Brien"],
["Kerzen", "Mia"],
["Seife", "Ben"],
] as $notiz) {
$einfuegen->execute($notiz);
} <?php
declare(strict_types=1);
// Derselbe Name, zweimal gesucht. Der Verfasser heisst O'Brien, und
// dieser eine Apostroph ist alles, was es braucht.
require __DIR__ . "/vorbereiten.php";
$name = "O'Brien";
echo "Zusammengeklebt:\n";
try {
$zeilen = $db->query("SELECT titel FROM notizen WHERE verfasser = '" . $name . "'")->fetchAll();
echo " ", implode(", ", array_column($zeilen, "titel")), "\n";
} catch (PDOException $fehler) {
echo " ", $fehler->getMessage(), "\n";
}
echo "\nMit Platzhalter:\n";
$abfrage = $db->prepare("SELECT titel FROM notizen WHERE verfasser = :name");
$abfrage->execute([":name" => $name]);
echo " ", implode(", ", array_column($abfrage->fetchAll(), "titel")), "\n";
echo "\nUnd so sieht die zusammengeklebte Abfrage aus, wenn man sie ausschreibt:\n";
echo " SELECT titel FROM notizen WHERE verfasser = '", $name, "'\n"; Den Wert in den Abfragetext zu kleben, geht mit dem Punkt aus dem Abschnitt über Text. Es sieht
harmlos aus und funktioniert bei vielen Eingaben auch. array_column($zeilen, "titel") holt im
Beispiel übrigens aus jeder Zeile den Titel und gibt eine einfache Liste zurück, und die verbindet
implode() dann mit Kommas.
Bei O’Brien funktioniert das Kleben nicht. In der letzten Zeile des Beispiels steht die Abfrage
ausgeschrieben, WHERE verfasser = 'O'Brien'. Für SQLite endet der Text am zweiten Apostroph,
direkt nach dem O. Danach steht dort ein Wort namens Brien, und damit kann es nichts anfangen. Das
steht auch in der Meldung, near "Brien": syntax error. Bei diesem Wort ist SQLite
steckengeblieben.
Der Apostroph ist dabei nur der Auslöser. Die Datenbank kann deinen Wert und dein SQL nicht mehr auseinanderhalten, weil beides als ein einziger Text ankommt. Sie muss raten, wo das eine aufhört und das andere anfängt, und die Regeln dafür kennt dein Besucher genauso gut wie du.
<?php
declare(strict_types=1);
// Sechs Notizen von drei Leuten. Einer davon traegt einen
// Apostroph im Namen, und darum geht es in dieser Lektion.
//
// Die Tabelle wird bei jedem Lauf neu aufgebaut. An dieser Datei
// aenderst du nichts.
$db = new PDO("sqlite:" . __DIR__ . "/daten.db", null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("DROP TABLE IF EXISTS notizen");
$db->exec("CREATE TABLE notizen (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
verfasser TEXT NOT NULL
)");
$einfuegen = $db->prepare("INSERT INTO notizen (titel, verfasser) VALUES (?, ?)");
foreach ([
["Milch", "Mia"],
["Brot", "O'Brien"],
["Kaffee", "Mia"],
["Tee", "O'Brien"],
["Kerzen", "Mia"],
["Seife", "Ben"],
] as $notiz) {
$einfuegen->execute($notiz);
} <?php
declare(strict_types=1);
// Eine Vorfuehrung an der eigenen Datenbank, gegen niemanden:
// Was passiert, wenn ein Wert SQL sein darf.
require __DIR__ . "/vorbereiten.php";
$eingabe = "' OR 1=1 --";
echo "Die Datenbank hat ", $db->query("SELECT count(*) FROM notizen")->fetchColumn(), " Notizen.\n";
echo "Gesucht wird nach einem Verfasser, den es nicht gibt:\n";
echo " ", $eingabe, "\n\n";
$zusammengeklebt = "SELECT titel FROM notizen WHERE verfasser = '" . $eingabe . "'";
echo "Zusammengeklebt ergibt das diese Abfrage:\n";
echo " ", $zusammengeklebt, "\n";
echo " Treffer: ", count($db->query($zusammengeklebt)->fetchAll()), "\n\n";
$abfrage = $db->prepare("SELECT titel FROM notizen WHERE verfasser = :name");
$abfrage->execute([":name" => $eingabe]);
echo "Mit Platzhalter bleibt es eine Suche nach einem seltsamen Namen:\n";
echo " Treffer: ", count($abfrage->fetchAll()), "\n"; Im zweiten Beispiel ist die Eingabe ein Stück SQL. Zusammengeklebt ergibt sie eine Abfrage, die nach
niemandem sucht und trotzdem alle sechs Notizen liefert. Das -- am Ende ist in SQL ein
Kommentarzeichen, damit fällt der Rest der ursprünglichen Abfrage einfach weg.
Sechs Notizen sind harmlos. In einer Anmeldung heißt dieselbe Lücke, dass jemand ohne Passwort hereinkommt. In einer Tabelle mit Kundendaten heißt sie, dass er die Daten mitnimmt. Das Ganze hat einen Namen, SQL-Injection, und darum geht es in Abschnitt 15.
Der richtige Weg hat zwei Schritte
Statt einer fertigen Abfrage geht eine mit Lücken an die Datenbank. Die Werte kommen getrennt hinterher.
$db->prepare($sql) gibt die Abfrage an die Datenbank, ohne sie auszuführen. Wo ein Wert hingehört,
steht ein Platzhalter (englisch placeholder), entweder ein Name mit Doppelpunkt davor wie
:name oder ein schlichtes Fragezeichen. Die Datenbank sieht sich die Abfrage an, versteht sie und
merkt sich den fertigen Plan. Zurück kommt ein PDOStatement.
$stmt->execute([":name" => $wert]) liefert die Werte nach und führt aus. Entscheidend ist, dass
die Abfrage zu diesem Zeitpunkt schon feststeht. Der Wert wandert in eine Lücke, die vorher als
Lücke für einen Wert festgelegt wurde. Er kann kein SQL mehr werden, denn an dieser Stelle wird gar
kein SQL mehr erwartet. Ob dort O'Brien steht, ' OR 1=1 -- oder ein Absatz Shakespeare, es
bleibt ein Wert.
Das gilt für SQLite. Für MySQL gilt es erst mit der Option PDO::ATTR_EMULATE_PREPARES => false,
die beim Verbinden schon einmal vorkam. Ohne sie setzt PDO bei MySQL die Werte selbst in den
Abfragetext ein, bevor es ihn abschickt.
Bei SQLite wird deshalb auch nichts maskiert. Maskieren heißt, gefährliche Zeichen unschädlich zu machen, und dafür muss man alle kennen. Hier kommt der Wert schlicht nicht in die Nähe der Anweisung.
<?php
declare(strict_types=1);
// Sechs Notizen von drei Leuten. Einer davon traegt einen
// Apostroph im Namen, und darum geht es in dieser Lektion.
//
// Die Tabelle wird bei jedem Lauf neu aufgebaut. An dieser Datei
// aenderst du nichts.
$db = new PDO("sqlite:" . __DIR__ . "/daten.db", null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("DROP TABLE IF EXISTS notizen");
$db->exec("CREATE TABLE notizen (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
verfasser TEXT NOT NULL
)");
$einfuegen = $db->prepare("INSERT INTO notizen (titel, verfasser) VALUES (?, ?)");
foreach ([
["Milch", "Mia"],
["Brot", "O'Brien"],
["Kaffee", "Mia"],
["Tee", "O'Brien"],
["Kerzen", "Mia"],
["Seife", "Ben"],
] as $notiz) {
$einfuegen->execute($notiz);
} <?php
declare(strict_types=1);
// Einmal vorbereiten, dreimal ausfuehren. Und zwei Schreibweisen
// fuer Platzhalter.
require __DIR__ . "/vorbereiten.php";
$abfrage = $db->prepare("SELECT titel FROM notizen WHERE verfasser = :name ORDER BY id");
foreach (["Mia", "Ben", "O'Brien"] as $name) {
$abfrage->execute([":name" => $name]);
echo str_pad($name . ":", 10), implode(", ", array_column($abfrage->fetchAll(), "titel")), "\n";
}
echo "\nDer Doppelpunkt im Array darf auch weg:\n";
$abfrage->execute(["name" => "Mia"]);
echo " ", implode(", ", array_column($abfrage->fetchAll(), "titel")), "\n";
echo "\nUnd statt Namen gehen Fragezeichen, dann zählt die Reihenfolge:\n";
$zwei = $db->prepare("SELECT titel FROM notizen WHERE verfasser = ? AND titel = ?");
$zwei->execute(["Mia", "Kaffee"]);
echo " ", implode(", ", array_column($zwei->fetchAll(), "titel")), "\n"; Von diesem ersten Schritt hat die Sache ihren Namen, vorbereitete Anweisung (englisch prepared
statement), und die Methode prepare() ebenso. Die Anweisung ist wirklich vorbereitet und lässt
sich mehrmals ausführen, jedes Mal mit anderen Werten, und die Datenbank macht den Plan dabei nur
einmal. Bei SQLite ist der Unterschied klein. Tausendmal ausgeführt sind es gemessen rund 1 ms gegen
rund 3 ms.
Der Doppelpunkt im Array darf weg, ["name" => $wert] funktioniert genauso. Mit Fragezeichen statt
Namen geht eine einfache Liste an execute(), und dann zählt die Reihenfolge. Bei einer Handvoll
Werte lesen sich Namen leichter, gerade wenn beim Anpassen später einer dazukommt.
Wofür ein Platzhalter nicht gedacht ist
<?php
declare(strict_types=1);
// Sechs Notizen von drei Leuten. Einer davon traegt einen
// Apostroph im Namen, und darum geht es in dieser Lektion.
//
// Die Tabelle wird bei jedem Lauf neu aufgebaut. An dieser Datei
// aenderst du nichts.
$db = new PDO("sqlite:" . __DIR__ . "/daten.db", null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("DROP TABLE IF EXISTS notizen");
$db->exec("CREATE TABLE notizen (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
verfasser TEXT NOT NULL
)");
$einfuegen = $db->prepare("INSERT INTO notizen (titel, verfasser) VALUES (?, ?)");
foreach ([
["Milch", "Mia"],
["Brot", "O'Brien"],
["Kaffee", "Mia"],
["Tee", "O'Brien"],
["Kerzen", "Mia"],
["Seife", "Ben"],
] as $notiz) {
$einfuegen->execute($notiz);
} <?php
declare(strict_types=1);
// Ein Platzhalter steht fuer einen Wert. Fuer nichts sonst.
require __DIR__ . "/vorbereiten.php";
echo "Ein Prozentzeichen für LIKE gehört in den Wert:\n";
$suche = $db->prepare("SELECT titel FROM notizen WHERE titel LIKE :teil ORDER BY id");
$suche->execute([":teil" => "%ee%"]);
echo " LIKE :teil mit dem Wert %ee% -> ";
echo implode(", ", array_column($suche->fetchAll(), "titel")), "\n";
echo "\nSteht es dagegen im SQL, ist der Platzhalter keiner mehr:\n";
try {
$falsch = $db->prepare("SELECT titel FROM notizen WHERE titel LIKE '%:teil%' ORDER BY id");
$falsch->execute([":teil" => "ee"]);
echo " durchgelaufen\n";
} catch (PDOException $fehler) {
echo " ", $fehler->getMessage(), "\n";
}
echo "\nUnd ein Spaltenname ist kein Wert. Das hier sortiert nicht,\n";
echo "und es beschwert sich auch nicht:\n";
$sortiert = $db->prepare("SELECT titel FROM notizen ORDER BY ?");
$sortiert->execute(["titel"]);
echo " ORDER BY ? mit dem Wert titel -> ";
echo implode(", ", array_column($sortiert->fetchAll(), "titel")), "\n";
echo " ORDER BY titel ausgeschrieben -> ";
echo implode(", ", array_column($db->query("SELECT titel FROM notizen ORDER BY titel")->fetchAll(), "titel")), "\n"; Ein Platzhalter steht für einen Wert, also für das, was in einer Spalte stehen könnte. Für nichts sonst.
Ein Spaltenname ist kein Wert. ORDER BY ? mit dem Wert titel sortiert deshalb nach dem Text
titel und nicht nach der Spalte, und der Text ist bei jeder Zeile gleich. Es kommt keine
Fehlermeldung, die Zeilen stehen nur unsortiert da. Für eine wählbare Sortierung prüfst du die
Eingabe gegen eine Liste erlaubter Spaltennamen, und der geprüfte Name kommt selbst in die Abfrage.
Bei LIKE gehören die Prozentzeichen in den Wert und nicht in den Abfragetext. LIKE :teil mit dem
Wert %ee% ist richtig. LIKE '%:teil%' ist falsch, denn dort steht der Platzhalter in einem Text
in Apostrophen und ist damit gar keiner mehr. Die Meldung dazu lautet column index out of range,
und viel hilft sie nicht. Gemeint ist, dass ein Wert ankam, für den es keine Lücke gibt.
Welche Werte als Platzhalter gehen
Ich setze jeden Wert, der nicht im Quelltext steht, als Platzhalter ein, auch den aus der eigenen
Konfigurationsdatei und auch die Nummer, die ganz sicher eine Nummer ist. Mit jeder Ausnahme gäbe es
eine Stelle, an der jemand nachdenken muss, ob sie diesmal in Ordnung ist. Der Preis ist eine
Codezeile mehr für prepare().
Die Titelsuche in der Aufgabe bricht an einem Apostroph ab.
Zum Mitnehmen
prepare() schickt die Abfrage mit Platzhaltern los, execute() liefert die Werte getrennt nach. So bleibt ein Wert ein Wert, auch wenn ein Apostroph oder ein Stück SQL darin steht.
Jetzt du
Basis Konto, kostenlosZu dieser Lektion gehört eine Aufgabe. Du schreibst den Code selbst, und nach jedem Lauf sagt dir eine Prüfliste, was schon stimmt.
Dafür brauchst du das Basis Konto. Es kostet nichts, und ein Passwort gibt es auch nicht.
In diesem Kurs läuft dein Code auf einem Server. Dafür hat das Basis Konto 1 Stunde im Monat, mehr Zeit gibt es mit dem Premium Konto.
Was in dieser Lektion steckt
-
Artikel mit 4 Beispielen zum Ausprobieren
Steht hier, ohne Konto lesbar.
-
Aufgabe, dein Code läuft auf einem Server
Öffnet sich mit dem Basis Konto.