mitmario.dev

Vorbereitete Anweisungen

PHP Sandbox 4 Min Lesezeit 4 BeispieleLektion 4 von 9

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

Derselbe Name, zweimal gesucht
<?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.

Wo aus einem Wert SQL wird
<?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.

Einmal vorbereiten, mehrmals ausführen
<?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

Ein Platzhalter steht für einen Wert
<?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, kostenlos

Zu 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.