Prepared Statements

Bisher haben wir stets feste Suchkriterien gehabt, beispielsweise alle User mit einer ID kleiner 5. Dies ist wenig spannend und meistens wollen wir die Suchkriterien dynamisch gestalten, beispielsweise wenn der Besucher nach einer gewissen E-Mail-Adresse suchen möchte.

Gefährlicher Code und SQL-Injections

Der einfachste Gedanke wäre wie folgt (diese Variante ist nicht zu empfehlen):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');

if(isset($_GET['id'])) {
   $id = $_GET['id'];
} else {
   die("Bitte eine ?id übergeben");
} 

echo "User mit der ID $id: <br>";
$sql = "SELECT * FROM users WHERE id = $id";
foreach ($pdo->query($sql) as $row) {
   echo $row['vorname']." ".$row['nachname']."<br />";
   echo "E-Mail: ".$row['email']."<br /><br />";
}
?>

Dies funktioniert zwar, ist aber anfällig für sogenannte SQL Injections. Ein Angreifer kann über den GET-Parameter unsere SQL-Abfrage manipulieren und weiteren SQL-Code einschleusen. Im schlimmsten Fall werden dadurch sensible Daten ausgegeben, Tabelle verändert oder gar ganze Tabellen gelöscht.

Ruft die Seite wie folgt auf um dies zu sehen:
seite.php?id=1 OR id > 1

Wie ihr seht, werden nun alle Benutzer ausgegeben, denn an die Datenbank wird folgender Befehl gesendet:

Prepared Statements

Um SQL-Injections zu verhindern empfiehlt sich der Einsatz von prepared statements . Sobald ihr irgendwelche Daten vom Benutzer an die Datenbank übergebt, sollte ihr stets auf prepared Statements zurückgreifen.

Unser Script von oben sieht dann wie folgt aus:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');

if(isset($_GET['id'])) {
   $id = $_GET['id'];
} else {
   die("Bitte eine ?id übergeben");
} 

echo "User mit der ID $id: <br>";
$statement = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$statement->execute(array($id));   
while($row = $statement->fetch()) {
   echo $row['vorname']." ".$row['nachname']."<br />";
   echo "E-Mail: ".$row['email']."<br /><br />";
}
?>

Dies scheint auf den ersten Blick etwas komplizierter als zuvor zu sein, mit etwas Hintergrundwissen aber recht einfach. Zuerst führen wir $pdo->prepare($sql) und erhalten ein prepared Statement, also eine vorbereitetes Statement. In diesem Statement haben wir definiert, dass wir den Wert für id gerne als Parameter vom Benutzer setzen wollen indem wir dort ein ? stehen haben.

Mit $statement->execute(array($id)); führen wir das Statement aus und übergeben als Wert für den Parameter den Wert in der Variable $id. Anschließend können wir mittels der while-Schleife durch alle Einträge iterieren. Das $statement->fetch() liefert einen neuen Datenbankeintrag, oder false, falls keine weiteren Einträge mehr vorhanden sind.

Mittels prepared Statements seid ihr gegen SQL-Injections geschützt. Wenn ihr die Seite mittels seite.php?id=1 OR id > 1 aufruft, werdet ihr feststellen, dass kein Eintrag ausgegeben wird. Denn in der Datenbank wird geschaut, ob ein Eintrag mit der ID '1 OR id > 1' vorhanden ist, was nicht der Fall ist.

Benannte Parameter

Neben anonymen Parametern wie im vorherigen Beispiel die ihr mittels ? angebt, könnt ich auch benannte Parameter nutzen. Statt dem Fragezeichen schreibt ihr dann :name, sprich, ihr beginnt mit einem Doppelpunkt und dann dem Namen. Ein Minus darf im Namen nicht enthalten sein, nutzt dort stattdessen den Unterstrich.

Die Benennung von Parametern ist besonders praktisch, wenn ihr mehr als nur einen Parameter in dem SQL-Query habt. So lauft ihr nicht Gefahr, aus Versehen die falschen Werte zu übergeben. Eure Parameternamen müssen nicht so heißen wie eure Spaltennamen, auch wenn dies stark zu empfehlen ist:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');

$vorname = "Max";
$nachname = "Mustermann";
$email = "[email protected]";

$statement = $pdo->prepare("SELECT * FROM users WHERE vorname = :vorname OR nachname = :nachname OR email = :email");
$statement->execute(array('vorname' => $vorname, 'nachname' => $nachname, 'email' => $email));   
while($row = $statement->fetch()) {
   echo $row['vorname']." ".$row['nachname']."<br />";
   echo "E-Mail: ".$row['email']."<br /><br />";
}
?>

Möchten wir mittels LIKE und % nach einem gewissen Wort suchen, dann geht dies korrekterweise wie folgt:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');

$suchwort = "Diet";
$statement = $pdo->prepare("SELECT * FROM users WHERE vorname LIKE :vorname");
$statement->execute(array('vorname' => "%$suchwort%"));   
while($row = $statement->fetch()) {
   echo $row['vorname']." ".$row['nachname']."<br />";
   echo "E-Mail: ".$row['email']."<br /><br />";
}
?>

LIMIT mit Parametern

Möchten wir die LIMIT-Anweisung mit Parametern ausstatten, beispielsweise damit der Benutzer entscheiden kann wie viele Datensätze anzeigt werden sollen, so müssen wir einen kleinen Trick anwenden damit dies mittels Prepared Statements funktioniert.

Folgender Code führt zu einer Fehlermeldung:

Diese Fehlermeldung resultiert daraus, dass PDO standardmäßig Prepared Statements nur emuliert (weitere Infos zu emulierten Statements). Da hierbei nicht unterschieden wird ob wir eine Zahl oder einen String als Parameter angegeben wurde, konstruiert PHP daraus folgenden Query, der natürlich fehlerhaft ist:

Um dieses Problem zu lösen, existieren zwei Vorgehensweisen. Die erste ist die Deaktivierung der emulierten Prepared Statements, dadurch werden die tatsächlichen von MySQL bereitgestellten Prepared Statements verwendet. Der Code sieht dann wie folgt aus:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$statement = $pdo->prepare("SELECT * FROM users LIMIT :limit");
$statement->execute(array('limit' => 5));   
while($row = $statement->fetch()) {
   echo $row['vorname']." ".$row['nachname']."<br />";
   echo "E-Mail: ".$row['email']."<br /><br />";
}
?>

Durch das setzen des Attributs PDO::ATTR_EMULATE_PREPARES auf false wird das Emulieren deaktiviert. Das Deaktivieren hat keine negativen Auswirkungen auf eure Scripts. Diese sind immer noch genauso performant und genauso sicher wie zuvor. Das Abschalten der Emulation funktioniert nur, wenn ihr  MySQL5 oder neuer verwendet. Mehr Informationen.

Solltet ihr mit einer veralteten MySQL-Version arbeiten müssen, oder mit einer anderen Datenbank die keine Prepared Statements unterstützt, so könnt ihr das obige Problem lösen indem ihr den Typ des Parameters als int definiert:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');

$limit = 5;

$statement = $pdo->prepare("SELECT * FROM users LIMIT :limit");
$statement->bindParam('limit', $limit, PDO::PARAM_INT);
$statement->execute();   
while($row = $statement->fetch()) {
   echo $row['vorname']." ".$row['nachname']."<br />";
   echo "E-Mail: ".$row['email']."<br /><br />";
}
?>

Mittels der Methode $statement->bindParam() wird die Variable $limit mit dem Parameter :limit verbunden. Als Typ für den Parameter wurde mittels der Konstanten PDO:PARAM_INT definiert dass es sicher hierbei um einen Integer handelt.