Vorbereitete Anweisungen (Prepared Statements)
Die MySQL-Datenbank unterstützt vorbereitete Anweisungen. Vorbereitete Anweisungen oder parametrisierte Anweisungen ermöglichen die wiederholte und effiziente Ausführung derselben Anweisung und schützen gleichzeitig vor SQL-Injections.
Grundlegender Ablauf
Die Ausführung einer vorbereiteten Anweisung besteht aus zwei Phasen: der Vorbereitung und der Ausführung. In der Vorbereitungsphase wird eine Anweisungsvorlage an den Datenbankserver gesendet. Der Server führt eine Syntaxprüfung durch und initialisiert Server-interne Ressourcen für die spätere Verwendung.
Der MySQL-Server unterstützt die Verwendung des anonymen,
positionsbezogenen Platzhalters ?.
Auf das Vorbereiten folgt das Ausführen. Während der Ausführung bindet der Client die Parameterwerte und sendet sie an den Server. Der Server führt die Anweisung mit den gebundenen Werten unter Verwendung der zuvor erstellten internen Ressourcen aus.
Beispiel #1 Vorbereitete Anweisung
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Nicht-vorbereitete Anweisung */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
/* Vorbereitete Anweisung, Stufe 1: vorbereiten */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");
/* Vorbereitete Anweisung, Stufe 2: binden und ausführen */
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" bedeutet, dass $id als Integer und
// $label als Zeichenkette gebunden ist
$stmt->execute();
Mehrmalige Ausführung
Eine vorbereitete Anweisung kann mehrmals ausgeführt werden. Bei jeder Ausführung wird der aktuelle Wert der gebundenen Variablen ausgewertet und an den Server gesendet. Die Anweisung wird nicht erneut analysiert und die Anweisungsvorlage wird nicht erneut an den Server übertragen.
Beispiel #2 INSERT einmal vorbereitet, mehrfach ausgeführt
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Nicht-vorbereitete Anweisung */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
/* Vorbereitete Anweisung, Stufe 1: vorbereiten */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");
/* Vorbereitete Anweisung, Stufe 2: binden und ausführen */
$stmt->bind_param("is", $id, $label); // "is" bedeutet, dass $id als Integer und
// $label als Zeichenkette gebunden ist
$data = [
1 => 'PHP',
2 => 'Java',
3 => 'C++'
];
foreach ($data as $id => $label) {
$stmt->execute();
}
$result = $mysqli->query('SELECT id, label FROM test');
var_dump($result->fetch_all(MYSQLI_ASSOC));
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
array(3) {
[0]=>
array(2) {
["id"]=>
string(1) "1"
["label"]=>
string(3) "PHP"
}
[1]=>
array(2) {
["id"]=>
string(1) "2"
["label"]=>
string(4) "Java"
}
[2]=>
array(2) {
["id"]=>
string(1) "3"
["label"]=>
string(3) "C++"
}
}
Jede vorbereitete Anweisung beansprucht Ressourcen auf dem Server, weshalb sie sofort nach ihrer Verwendung explizit geschlossen werden sollte. Falls dies nicht explizit geschieht, wird die Anweisung geschlossen, wenn das Anweisungs-Handle von PHP freigegeben wird.
Die Verwendung einer vorbereiteten Anweisung ist nicht immer die
effizienteste Art, eine Anweisung auszuführen. Eine vorbereitete Anweisung,
die nur einmal ausgeführt wird, verursacht mehr Client-Server-Umläufe
(Roundtrips) als eine nicht-vorbereitete Anweisung. Aus diesem Grund wird
die SELECT-Anweisung nicht als vorbereitete Anweisung
ausgeführt.
Außerdem sollte für INSERTs die Verwendung der multi-INSERT-Syntax von MySQL in Betracht gezogen werden. Für das Beispiel erfordert multi-INSERT weniger Umläufe zwischen Server und Client als die oben gezeigte vorbereitete Anweisung.
Beispiel #3 Weniger Umläufe durch multi-INSERT-SQL
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$values = [1, 2, 3, 4];
$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
$stmt->bind_param('iiii', ...$values);
$stmt->execute();
Datentypen der Werte in der Ergebnismenge
Das MySQL-Client-Server-Protokoll definiert unterschiedliche
Datenübertragungsprotokolle für vorbereitete Anweisungen und
nicht-vorbereitete Anweisungen. Vorbereitete Anweisungen verwenden das
sogenannte Binärprotokoll. Der MySQL-Server sendet die Ergebnisdaten "as is"
(wie sie sind) im Binärformat. Die Ergebnisse werden vor dem Senden nicht
zu Zeichenketten serialisiert. Die Client-Bibliotheken empfangen die
binären Daten und versuchen, die Werte in geeignete PHP-Datentypen
umzuwandeln. Zum Beispiel werden Ergebnisse aus einer
SQL-INT-Spalte als PHP-Integer-Variablen bereitgestellt.
Beispiel #4 Native Datentypen
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Nicht-vorbereitete Anweisung */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
id = 1 (integer) label = PHP (string)
Dieses Verhalten unterscheidet sich von nicht-vorbereiteten Anweisungen. Standardmäßig geben nicht-vorbereitete Anweisungen alle Ergebnisse als Zeichenketten zurück. Diese Vorgabe kann mit einer Verbindungsoption geändert werden. Wenn diese Verbindungsoption verwendet wird, gibt es keine Unterschiede.
Ergebnisse über gebundene Variablen abrufen
Ergebnisse von vorbereiteten Anweisungen können entweder durch Binden der Ausgabevariablen oder durch Anfordern eines mysqli_result-Objekts abgerufen werden.
Die Ausgabevariablen müssen nach der Ausführung der Anweisung gebunden werden. Für jede Spalte der Ergebnismenge der Anweisung muss eine Variable gebunden werden.
Beispiel #5 Binden der Ausgabevariablen
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Nicht-vorbereitete Anweisung */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$stmt->bind_result($out_id, $out_label);
while ($stmt->fetch()) {
printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
id = 1 (integer), label = PHP (string)
Vorbereitete Anweisungen geben standardmäßig ungepufferte Ergebnismengen
zurück. Die Ergebnisse der Anweisung werden nicht implizit vom Server
abgerufen und zur clientseitigen Pufferung zum Client übertragen. Die
Ergebnismenge nimmt solange Serverressourcen in Anspruch, bis alle
Ergebnisse vom Client abgerufen wurden. Es wird daher empfohlen, die
Ergebnisse frühzeitig abzurufen. Wenn ein Client nicht alle Ergebnisse
abrufen kann oder der Client die Anweisung schließt, bevor er alle Daten
geholt hat, müssen die Daten implizit mit mysqli
abgerufen werden.
Mit mysqli_stmt::store_result() ist es auch möglich, die Ergebnisse einer vorbereiteten Anweisung zu puffern.
Abrufen der Ergebnisse über die mysqli_result-Schnittstelle.
Anstatt gebundene Ergebnisse zu verwenden, können die Ergebnisse auch über die mysqli_result-Schnittstelle abgerufen werden. mysqli_stmt::get_result() gibt eine gepufferte Ergebnismenge zurück.
Beispiel #6 Verwendung von mysqli_result zum Abrufen von Ergebnissen
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Nicht-vorbereitete Anweisung */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
var_dump($result->fetch_all(MYSQLI_ASSOC));
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
array(1) {
[0]=>
array(2) {
["id"]=>
int(1)
["label"]=>
string(3) "PHP"
}
}
Die Verwendung der mysqli_result-Schnittstelle bietet den zusätzlichen Vorteil einer flexiblen clientseitigen Navigation in der Ergebnismenge.
Beispiel #7 Gepufferte Ergebnismenge für flexibles Auslesen
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Nicht-vorbereitete Anweisung */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");
$stmt = $mysqli->prepare("SELECT id, label FROM test");
$stmt->execute();
$result = $stmt->get_result();
for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
$result->data_seek($row_no);
var_dump($result->fetch_assoc());
}
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
array(2) {
["id"]=>
int(3)
["label"]=>
string(3) "C++"
}
array(2) {
["id"]=>
int(2)
["label"]=>
string(4) "Java"
}
array(2) {
["id"]=>
int(1)
["label"]=>
string(3) "PHP"
}
Maskierung und SQL-Injection
Die gebundenen Variablen werden getrennt von der Abfrage an den Server gesendet und können diese daher nicht beeinflussen. Der Server verwendet diese Werte erst zum Zeitpunkt der der Ausführung, nachdem die Anweisungsvorlage geparst wurde. Die gebundenen Parameter müssen nicht müssen nicht maskiert werden, da sie nie direkt in die Abfragezeichenkette eingefügt werden. Dem Server muss der Typ der gebundenen Variablen mitgeteilt werden, um eine geeignete Umwandlung zu ermöglichen. Siehe mysqli_stmt::bind_param() für weitere Informationen.
Diese Trennung wird oft als die einzige Möglichkeit angesehen, sich gegen SQL-Injection zu schützen, aber tatsächlich kann das gleiche Maß an Sicherheit auch mit nicht-vorbereiteten Anweisungen erreicht werden, wenn alle Werte korrekt formatiert sind. Es ist wichtig, zu beachten, dass eine korrekte Formatierung nicht dasselbe ist wie die Maskierung, und mehr Logik beinhaltet. Daher sind vorbereitete Anweisungen einfach ein bequemerer und weniger fehleranfälliger Ansatz, um dieses Niveau an Datenbanksicherheit zu erreichen.
Clientseitige Emulation vorbereiteter Anweisungen
Die API enthält keine Emulation für die clientseitige Emulation von vorbereiteten Anweisungen.
Ein kurzer Vergleich von vorbereiteten und nicht-vorbereiteten Anweisungen
Die folgende Tabelle vergleicht serverseitige vorbereitete und nicht-vorbereitete Anweisungen.
| Vorbereitete Anweisung | Nicht-vorbereitete Anweisung | |
|---|---|---|
| Client-Server-Umläufe, SELECT, einmalige Ausführung | 2 | 1 |
| Anweisung vom Client zum Server übertragen | 1 | 1 |
| Client-Server-Umläufe, SELECT, (n-malige) wiederholte Ausführung | 1 + n | n |
| Anweisung vom Client zum Server übertragen | 1 Vorlage, n-mal gebundener Parameter, falls erforderlich | n-mal und jedes Mal geparst |
| API für die Bindung von Eingabeparametern | Ja | Nein, manuelles Maskieren der Eingabe |
| API für die Bindung von Ausgabevariablen | Ja | Nein |
| Unterstützt die Verwendung der mysqli_result-API | Ja, Verwendung von mysqli_stmt::get_result() | Ja |
| Gepufferte Ergebnismengen | Ja, Verwendung von mysqli_stmt::get_result() oder Bindung mit mysqli_stmt::store_result() | Ja, Voreinstellung von mysqli::query() |
| Ungepufferte Ergebnismengen | Ja, verwenden Sie die API für die Ausgabebindung | Ja, verwenden Sie mysqli::real_query() mit mysqli::use_result() |
| Variante des MySQL-Client-Server-Protokolls für die Datenübertragung | Binär-Protokoll | Text-Protokoll |
| SQL-Datentypen der Werte in der Ergebnismenge | Werden beim Abrufen beibehalten | In Zeichenkette konvertiert oder beim Abrufen beibehalten |
| Unterstützt alle SQL-Anweisungen | Neuere MySQL-Versionen unterstützen die meisten, aber nicht alle | Ja |
Siehe auch
