Prepared Statements in PHP und Co.: Grundlagen und Beispiele
Datenbankmanagementsysteme (DBMS), die mit der Datenbanksprache SQL arbeiten, erfreuen sich zwar großer Beliebtheit, sind aber auch seit jeher anfällig für Manipulationen bei der Dateneinspeisung. So sind Benutzereingaben, die nicht ausreichend maskiert sind und Metazeichen wie das Anführungszeichen oder das Semikolon enthalten, für Angreifer ein gefundenes Fressen. Eine mögliche Antwort auf dieses Problem ist der Einsatz von Prepared Statements, also vorgefertigten Anweisungen an die Datenbank, die erst zur Laufzeit mit Werten versehen werden.
Was macht diese Technik so besonders und wo kommt sie überall zum Einsatz? Wir zeigen am Beispiel von MySQL, wie Prepared Statements funktionieren und zum Datenbankmanagement eingesetzt werden können.
Was sind Prepared Statements?
Prepared Statements (dt. „vorbereitete Anweisungen“) sind einsatzfertige Muster für Abfragen in SQL-Datenbanksystemen, die keine Werte für die einzelnen Parameter enthalten. Stattdessen arbeiten diese Anweisungsmuster (auch Anweisungstemplates genannt) mit Variablen bzw. Platzhaltern, die erst innerhalb des Systems durch die tatsächlichen Werte ersetzt werden – anders als bei der manuellen Eingabe, wo die Werte bereits zum Zeitpunkt der Ausführung eines Befehls zugeordnet sind.
Alle großen Datenbankmanagementsysteme auf SQL-Basis wie MySQL, MariaDB, Oracle, Microsoft SQL Server oder PostgreSQL unterstützen Prepared Statements, wobei die meisten dieser Anwendungen zu diesem Zweck auf ein NoSQL-Binärprotokoll zurückgreifen. Einige Systeme – u. a. MySQL – nutzen allerdings auch gewöhnliche SQL-Syntax für die Implementierung. Zudem unterstützen einige Programmiersprachen wie Java, Perl, Python und PHP Prepared Statements durch ihre Standardbibliotheken oder Erweiterungen. Nutzen Sie etwa letztgenannte Skriptsprache für den Datenbankzugriff, können Sie wahlweise die objektorientierte Schnittstelle PHP Data Objects (PDO) oder die PHP-Erweiterung MySQLi nutzen, um Prepared Statements einzusetzen.
Warum ist der Einsatz von Prepared Statements in MySQL und Co. so sinnvoll?
Der entscheidende Grund dafür, in Datenbankmanagementsystemen wie MySQL mit Prepared Statements zu arbeiten, ist der Aspekt Sicherheit. Das wohl größte Problem an standardmäßigen Zugriffen auf Datenbanken mit SQL-Sprachhintergrund ist nämlich die Tatsache, dass diese sich leicht manipulieren lassen. Man spricht in diesem Fall von einer SQL-Injection, bei der Code hinzugefügt oder angepasst wird, um an sensible Daten zu gelangen oder gar gänzlich die Kontrolle über das Datenbanksystem zu erlangen. Prepared Statements in PHP bzw. anderen Sprachen bieten eine solche Sicherheitslücke nicht, da ihnen erst innerhalb des Systems konkrete Werte zugeordnet werden.
Voraussetzung für den hohen Sicherheitsstandard von Prepared Statements ist, dass keine Komponente einer solchen vorgefertigten Anweisung aus einer externen Quelle generiert wird.
Der Schutz vor SQL-Injections ist aber nicht das einzige Argument, das für den Einsatz von Anweisungsmustern spricht: Einmal analysiert und kompiliert, können diese vom jeweiligen Datenbanksystem im Anschluss immer wieder verwendet werden (mit den jeweiligen, abgeänderten Werten). Dadurch verbrauchen Prepared Statements wesentlich weniger Ressourcen und sind schneller als manuelle Datenbankabfragen, wann immer es um SQL-Aufgaben geht, die wiederholt ausgeführt werden müssen.
Wie funktioniert die Prepared-Statement-Nutzung genau?
Lässt man die Syntax der zugrundeliegenden Skriptsprache und Eigenheiten des jeweiligen Datenbankmanagementsystems außer Acht, erfolgt die Einbindung und Verwendung von Prepared Statements generell in folgenden Phasen:
Phase 1: Vorbereitung des Prepared Statements
Der erste Schritt besteht darin, dass ein Anweisungstemplate erzeugt wird – in PHP z. B. mit der Funktion prepare(). Anstelle der konkreten Werte für die relevanten Parameter werden die bereits erwähnten Platzhalter eingebaut, die man auch als Bind-Variablen bezeichnet. Typischerweise sind diese wie im folgenden Beispiel durch ein „?“ gekennzeichnet:
INSERT INTO Produkte (Name, Preis) VALUES (?, ?);Vollständige Prepared Statements werden dann an das jeweilige Datenbankmanagementsystem weitergeleitet.
Phase 2: Verarbeitung des Anweisungsmusters durch das DBMS
Vom Datenbankmanagementsystem wird das Anweisungsmuster zunächst geparst (analysiert), damit es im nächsten Schritt kompiliert, also in eine ausführbare Anweisung umgewandelt werden kann. Bei diesem Vorgang wird das Prepared Statement außerdem optimiert.
Phase 3: Ausführung des Prepared Statements
Zu einem späteren Zeitpunkt kann das verarbeitete Muster im Datenbanksystem beliebig häufig ausgeführt werden. Voraussetzung hierfür ist lediglich der passende Input durch die angeschlossene Anwendung bzw. eine Datenquelle, die die entsprechenden Werte für die Platzhalter-Felder liefern muss. Passend zum oben aufgeführten Code-Beispiel (Phase 1) könnte es sich beispielsweise um die Werte „Buch“ (Name) und „10“ (Preis) oder auch um „Computer“ und „1000“ handeln.
Tutorial: So verwenden Sie Prepared Statements in MySQL mit MySQLi
Nachdem wir die Funktionsweise von Prepared Statements im Allgemeinen beschrieben haben, soll das nun folgende Tutorial die Verwendung der praktischen Anweisungen mithilfe konkreter Beispiele erläutern. Exemplarisch stützt sich die Anleitung dabei auf
- MySQL als Datenbankmanagementsystem und
- PHP als Prepared-Statement-Sprache.
Aktuelle Versionen von MySQL unterstützen den serverseitigen Einsatz von Prepared Statements auf Basis eines Binärprotokolls, das alle SQL-Befehle enthält, die Daten aktualisieren, und zudem sämtliche Aktualisierungen seit der letzten Datensicherung protokolliert. Als Interface für den Zugriff dient in diesem Tutorial die PHP-Erweiterung MySQLi, die Prepared Statements über das Binärprotokoll ebenfalls unterstützt.
Eine gute, häufig verwendete Alternative zu MySQLi als Prepared-Statement-API ist die objektorientierte Schnittstelle PDO (PHP Data Objects). Diese Variante gilt im Allgemeinen als einsteigerfreundlichere Lösung.
PREPARE, EXECUTE und DEALLOCATE PREPARE: Die drei elementaren SQL-Befehle für die Nutzung von Prepared Statements
Drei SQL-Befehle spielen für die Anwendung von Prepared Statements in MySQL-Datenbanken eine entscheidende Rolle:
Das Kommando „PREPARE“ wird dazu benötigt, ein Prepared Statement für den Einsatz vorzubereiten und diesem u. a. einen eindeutigen Namen zuzuordnen, unter dem die Anweisung im späteren Verlauf angesteuert werden kann.
PREPARE stmt_name FROM preparable_stmtFür die Ausführung vorbereiteter SQL-Anweisungen benötigen Sie den Befehl „EXECUTE“. Auf das jeweilige Prepared Statement beziehen Sie sich durch die Angabe des mit „PREPARE“ generierten Namens. Wie oft Sie ein Statement ausführen, liegt dabei in Ihrer Hand: So können Sie wahlweise beliebige, unterschiedliche Variablen definieren oder unbegrenzt neue Werte für die gesetzten Variablen übergeben.
EXECUTE stmt_name
[USING @var_name [, @var_name] ...]Um ein PHP-Prepared-Statement wieder freizugeben, verwendet man die Anweisung „DEALLOCATE PREPARE“. Alternativ werden Statements beim Beenden einer Sitzung automatisch freigegeben. Die Freigabe ist insofern relevant, als dass andernfalls schnell die Obergrenze erreicht wird, die durch die Systemvariable max_prepared_stmt_count vorgegeben ist. Dann könnten Sie keine neuen Prepared Statements erzeugen.
{DEALLOCATE | DROP} PREPARE stmt_nameDiese SQL-Anweisungen können Sie als MySQL-Prepared-Statements verwenden
Sie können nahezu alle von MySQL unterstützten SQL-Anweisungen als Prepared Statements aufbereiten und ausführen. Eine Ausnahme bilden dabei einzig die Diagnose-Anweisungen, die zur Einhaltung des SQL-Standards als Prepared Statements ausgeschlossen sind. Im Detail handelt es sich also um folgende Statements:
- SHOW WARNINGS
- SHOW COUNT(*) WARNINGS
- SHOW ERRORS
- SHOW COUNT(*) ERRORS
Zudem können Sie keine Muster für SQL-Abfragen mit Bezug auf die Systemvariablen warning_count und error_count generieren.
Nutzbar sind hingegen folgende Statements:
ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
| LOGS | STATUS | MASTER | SLAVE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE}
REVOKE
SELECT
SET
SHOW {WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATESyntaktische Besonderheiten der SQL-Syntax von Prepared Statements in PHP
Im Vergleich zur standardmäßigen SQL-Syntax weist die Syntax von Prepared Statements ein paar Besonderheiten auf, die es unbedingt zu beachten gilt. Allen voran steht hierbei die Verwendung von Platzhaltern für Parameter-Werte, die die vorbereiteten Anweisungen so interessant für den Zugriff auf Datenbankmanagementsysteme macht. Ab MySQL 8.0 sind solche Platzhalter beispielsweise auch für „OUT“- und „INOUT“-Parameter in „PREPARE“- und „EXECUTE“-Statements möglich – für „IN“-Parameter sind diese sogar unabhängig von der Datenbanksystem-Version verfügbar. Weitere spezifische Eigenschaften der Prepared-Statement-Syntax sind folgende:
- SQL-Syntax für PHP-Prepared-Statements kann nicht verschachtelt werden. Eine Anweisung, die an ein „PREPARE“-Statement übergeben wurde, kann also nicht gleichzeitig selbst ein „PREPARE“-, „EXECUTE“- oder „DEALLOCATE PREPARE“-Statement sein.
- Prepared Statements lassen sich in gespeicherten Prozeduren (Funktion zum Aufruf kompletter Abläufe von Anweisungen) verwenden.
- Mehrere Anweisungen, auch Multi Statements genannt, sind innerhalb eines vorbereiteten Statements bzw. innerhalb einer Zeichenkette durch Trennung per Semikolon nicht möglich.
Prepared Statements in MySQLi: Beispiel
Wie eine Eingabe mit PHP-Prepared-Statements in MySQLi genau aussieht, zeigt dieses Beispiel:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Verbindung erstellen
$conn = new mysqli($servername, $username, $password, $dbname);
// Verbindung überprüfen
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Vorbereiten der Prepared Statements
$stmt = $conn->prepare("INSERT INTO MeineKunden (Vorname, Nachname, Mail) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $vorname, $nachname, $mail);
// Setzen der Parameter und Ausführung
$Vorname = "Max";
$Nachname = "Mustermann";
$Mail = "max@beispiel.de";
$stmt->execute();
$Vorname = "Erika";
$Nachname = "Mustermann";
$Mail = "erika@beispiel.de";
$stmt->execute();
$Vorname = "Erik";
$Nachname = "Mustermann";
$Mail = "erik@beispiel.de";
$stmt->execute();
echo "Neue Einträge erfolgreich angelegt";
$stmt->close();
$conn->close();
?>Dieses PHP-Skript baut zunächst die Verbindung zur MySQL-Datenbank auf ($conn), wobei die individuellen Serverdaten wie Hostname, Benutzername, Passwort und Datenbankname anzugeben sind.
Mit der Zeile "INSERT INTO MeineKunden (Vorname, Nachname, Mail) VALUES (?, ?, ?)" beginnt der entscheidende Prepared-Statement-Teil: Die Kundendatenbank „MeineKunden“ soll Input erhalten (INSERT INTO) – jeweils für die Spalten „Vorname“, „Nachname“ und „Mail“. Für die Werte (VALUES) werden zunächst Platzhalter verwendet, die durch das Fragezeichen-Symbol (?) gekennzeichnet sind.
Im Anschluss müssen die Parameter gebunden werden (bind_param). Zudem benötigt die Datenbank Informationen darüber, um was für einen Typ von Daten es sich handelt. Das hier verwendete Argument „sss“ zeigt beispielsweise an, dass es sich bei allen drei Parametern um Strings, also Zeichenketten handelt. Mögliche Alternativen wären an dieser Stelle:
- i: INTEGER (ganzzahliger Wert)
- d: DOUBLE (annähernd numerischer Datenwert)
- b: BLOB (großes binäres Datenobjekt)
Zur Anzeige dieses Videos sind Cookies von Drittanbietern erforderlich. Ihre Cookie-Einstellungen können Sie hier aufrufen und ändern.