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.

Siehe auch