SQL-Zugriff

Nette Database bietet zwei Wege: Sie können SQL-Abfragen selbst schreiben (SQL-Zugriff) oder sie automatisch generieren lassen (siehe Explorer). Der SQL-Zugriff gibt Ihnen die volle Kontrolle über die Abfragen und gewährleistet gleichzeitig deren sichere Erstellung.

Details zur Verbindung und Konfiguration der Datenbank finden Sie im Kapitel Verbindung und Konfiguration.

Grundlegende Abfragen

Für Abfragen an die Datenbank dient die Methode query(). Sie gibt ein ResultSet-Objekt zurück, das das Ergebnis der Abfrage repräsentiert. Im Fehlerfall löst die Methode eine Ausnahme aus. Das Ergebnis der Abfrage kann mit einer foreach-Schleife durchlaufen werden, oder es können einige der Hilfsfunktionen verwendet werden.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

Für das sichere Einfügen von Werten in SQL-Abfragen verwenden wir parametrisierte Abfragen. Nette Database macht diese maximal einfach – fügen Sie einfach ein Komma und den Wert nach der SQL-Abfrage hinzu:

$database->query('SELECT * FROM users WHERE name = ?', $name);

Bei mehreren Parametern haben Sie zwei Schreibmöglichkeiten. Entweder können Sie die SQL-Abfrage mit Parametern „durchsetzen“:

$database->query('SELECT * FROM users WHERE name = ?', $name, 'AND age > ?', $age);

Oder schreiben Sie zuerst die gesamte SQL-Abfrage und fügen dann alle Parameter an:

$database->query('SELECT * FROM users WHERE name = ? AND age > ?', $name, $age);

Schutz vor SQL-Injection

Warum ist es wichtig, parametrisierte Abfragen zu verwenden? Weil sie Sie vor einem Angriff namens SQL-Injection schützen, bei dem ein Angreifer eigene SQL-Befehle einschleusen und so Daten in der Datenbank gewinnen oder beschädigen könnte.

Fügen Sie Variablen niemals direkt in eine SQL-Abfrage ein! Verwenden Sie immer parametrisierte Abfragen, die Sie vor SQL-Injection schützen.

// ❌ GEFÄHRLICHER CODE - anfällig für SQL-Injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Sichere parametrisierte Abfrage
$database->query('SELECT * FROM users WHERE name = ?', $name);

Machen Sie sich mit den möglichen Sicherheitsrisiken vertraut.

Abfragetechniken

WHERE-Bedingungen

WHERE-Bedingungen können als assoziatives Array geschrieben werden, wobei die Schlüssel Spaltennamen und die Werte Daten zum Vergleich sind. Nette Database wählt automatisch den am besten geeigneten SQL-Operator basierend auf dem Werttyp aus.

$database->query('SELECT * FROM users WHERE', [
	'name' => 'John',
	'active' => true,
]);
// WHERE `name` = 'John' AND `active` = 1

Im Schlüssel können Sie auch explizit einen Operator für den Vergleich angeben:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // verwendet Operator >
	'name LIKE' => '%John%', // verwendet Operator LIKE
	'email NOT LIKE' => '%example.com%', // verwendet Operator NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Nette behandelt automatisch Sonderfälle wie null-Werte oder Arrays.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // verwendet Operator =
	'category_id' => [1, 2, 3], // verwendet IN
	'description' => null,      // verwendet IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Für negative Bedingungen verwenden Sie den NOT-Operator:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // verwendet Operator <>
	'category_id NOT' => [1, 2, 3], // verwendet NOT IN
	'description NOT' => null,      // verwendet IS NOT NULL
	'id' => [],                     // wird ausgelassen
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Zum Verknüpfen von Bedingungen wird der AND-Operator verwendet. Dies kann mit dem Platzhalter ?or geändert werden.

ORDER BY-Regeln

Die ORDER BY-Sortierung kann mithilfe eines Arrays angegeben werden. In den Schlüsseln geben wir die Spalten an, und der Wert ist ein Boolean, der angibt, ob aufsteigend sortiert werden soll:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // aufsteigend
	'name' => false, // absteigend
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Einfügen von Daten (INSERT)

Zum Einfügen von Datensätzen wird der SQL-Befehl INSERT verwendet.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

Die Methode getInsertId() gibt die ID der zuletzt eingefügten Zeile zurück. Bei einigen Datenbanken (z. B. PostgreSQL) muss der Name der Sequenz, aus der die ID generiert werden soll, als Parameter mit $database->getInsertId($sequenceId) angegeben werden.

Als Parameter können wir auch Spezielle Werte wie Dateien, DateTime-Objekte oder Enum-Typen übergeben.

Einfügen mehrerer Datensätze auf einmal:

$database->query('INSERT INTO users ?', [
	['name' => 'User 1', 'email' => 'user1@mail.com'],
	['name' => 'User 2', 'email' => 'user2@mail.com'],
]);

Ein mehrfacher INSERT ist viel schneller, da nur eine Datenbankabfrage anstelle vieler einzelner ausgeführt wird.

Sicherheitshinweis: Verwenden Sie niemals unvalidierte Daten als $values. Machen Sie sich mit den möglichen Risiken vertraut.

Aktualisieren von Daten (UPDATE)

Zum Aktualisieren von Datensätzen wird der SQL-Befehl UPDATE verwendet.

// Aktualisierung eines einzelnen Datensatzes
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Die Anzahl der betroffenen Zeilen wird von $result->getRowCount() zurückgegeben.

Für UPDATE können wir die Operatoren += und -= verwenden:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // Inkrementierung von login_count
], 1);

Beispiel für das Einfügen oder Ändern eines Datensatzes, falls er bereits existiert. Wir verwenden die Technik ON DUPLICATE KEY UPDATE:

$values = [
	'name' => $name,
	'year' => $year,
];
$database->query('INSERT INTO users ? ON DUPLICATE KEY UPDATE ?',
	$values + ['id' => $id],
	$values,
);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Beachten Sie, dass Nette Database erkennt, in welchem Kontext des SQL-Befehls wir den Parameter mit dem Array einfügen, und daraus den SQL-Code entsprechend erstellt. So wurde aus dem ersten Array (id, name, year) VALUES (123, 'Jim', 1978) erstellt, während das zweite in die Form name = 'Jim', year = 1978 umgewandelt wurde. Wir gehen darauf im Abschnitt SQL-Erstellungs-Hinweise näher ein.

Löschen von Daten (DELETE)

Zum Löschen von Datensätzen wird der SQL-Befehl DELETE verwendet. Beispiel zur Ermittlung der Anzahl gelöschter Zeilen:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

SQL-Erstellungs-Hinweise

Ein Hinweis ist ein spezieller Platzhalter in einer SQL-Abfrage, der angibt, wie der Wert des Parameters in einen SQL-Ausdruck umgeschrieben werden soll:

Hinweis	Beschreibung	Automatisch verwendet
`?name`	Wird zum Einfügen von Tabellen- oder Spaltennamen verwendet	–
`?values`	Generiert `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Generiert Zuweisung `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Verknüpft Bedingungen im Array mit dem `AND`-Operator	`WHERE ?`, `HAVING ?`
`?or`	Verknüpft Bedingungen im Array mit dem `OR`-Operator	–
`?order`	Generiert `ORDER BY`-Klausel	`ORDER BY ?`, `GROUP BY ?`

Für das dynamische Einfügen von Tabellen- und Spaltennamen in die Abfrage dient der Platzhalter ?name. Nette Database kümmert sich um die korrekte Behandlung von Bezeichnern gemäß den Konventionen der jeweiligen Datenbank (z. B. das Einschließen in Backticks in MySQL).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `name` FROM `users` WHERE id = 1 (in MySQL)

Warnung: Verwenden Sie das Symbol ?name nur für Tabellen- und Spaltennamen aus validierten Eingaben, andernfalls setzen Sie sich einem Sicherheitsrisiko aus.

Andere Hinweise müssen normalerweise nicht angegeben werden, da Nette beim Erstellen der SQL-Abfrage eine intelligente Autoerkennung verwendet (siehe dritte Spalte der Tabelle). Sie können ihn jedoch beispielsweise in einer Situation verwenden, in der Sie Bedingungen mit OR anstelle von AND verknüpfen möchten:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

Spezielle Werte

Neben den üblichen skalaren Typen (String, Int, Bool) können Sie auch spezielle Werte als Parameter übergeben:

Dateien: fopen('image.gif', 'r') fügt den binären Inhalt der Datei ein
Datum und Uhrzeit: DateTime-Objekte werden in das Datenbankformat konvertiert
Enum-Typen: enum-Instanzen werden in ihren Wert konvertiert
SQL-Literale: Erstellt mit Connection::literal('NOW()') werden direkt in die Abfrage eingefügt

$database->query('INSERT INTO articles ?', [
	'title' => 'My Article',
	'published_at' => new DateTime,
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

Bei Datenbanken, die keine native Unterstützung für den Datentyp datetime haben (wie SQLite und Oracle), wird DateTime in den Wert konvertiert, der in der Datenbankkonfiguration durch den Eintrag formatDateTime festgelegt ist (Standardwert ist U – Unix-Timestamp).

SQL-Literale

In einigen Fällen müssen Sie SQL-Code direkt als Wert angeben, der jedoch nicht als Zeichenkette verstanden und maskiert werden soll. Dafür dienen Objekte der Klasse Nette\Database\SqlLiteral. Sie werden durch die Methode Connection::literal() erstellt.

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Oder alternativ:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

SQL-Literale können Parameter enthalten:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Dadurch können wir interessante Kombinationen erstellen:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Daten abrufen

Abkürzungen für SELECT-Abfragen

Zur Vereinfachung des Datenabrufs bietet Connection mehrere Abkürzungen, die den Aufruf von query() mit dem anschließenden fetch*() kombinieren. Diese Methoden akzeptieren die gleichen Parameter wie query(), d.h. die SQL-Abfrage und optionale Parameter. Eine vollständige Beschreibung der fetch*()-Methoden finden Sie unten.

`fetch($sql, ...$params): ?Row`	Führt die Abfrage aus und gibt die erste Zeile als `Row`-Objekt zurück
`fetchAll($sql, ...$params): array`	Führt die Abfrage aus und gibt alle Zeilen als Array von `Row`-Objekten zurück
`fetchPairs($sql, ...$params): array`	Führt die Abfrage aus und gibt ein assoziatives Array zurück, wobei die erste Spalte den Schlüssel und die zweite den Wert darstellt
`fetchField($sql, ...$params): mixed`	Führt die Abfrage aus und gibt den Wert des ersten Feldes der ersten Zeile zurück
`fetchList($sql, ...$params): ?array`	Führt die Abfrage aus und gibt die erste Zeile als indiziertes Array zurück

Beispiel:

// fetchField() - gibt den Wert der ersten Zelle zurück
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – Iteration über Zeilen

Nach Ausführung der Abfrage wird ein ResultSet-Objekt zurückgegeben, das es ermöglicht, die Ergebnisse auf verschiedene Arten zu durchlaufen. Der einfachste Weg, eine Abfrage auszuführen und Zeilen zu erhalten, ist die Iteration in einer foreach-Schleife. Diese Methode ist am speicherschonendsten, da die Daten schrittweise zurückgegeben und nicht auf einmal im Speicher abgelegt werden.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
	// ...
}

ResultSet kann nur einmal iteriert werden. Wenn Sie wiederholt iterieren müssen, müssen Sie die Daten zuerst in ein Array laden, zum Beispiel mit der Methode fetchAll().

fetch(): ?Row

Gibt eine Zeile als Row-Objekt zurück. Wenn keine weiteren Zeilen existieren, wird null zurückgegeben. Verschiebt den internen Zeiger auf die nächste Zeile.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // lädt die erste Zeile
if ($row) {
	echo $row->name;
}

fetchAll(): array

Gibt alle verbleibenden Zeilen aus dem ResultSet als Array von Row-Objekten zurück.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // lädt alle Zeilen
foreach ($rows as $row) {
	echo $row->name;
}

fetchPairs (string|int|null $key = null, string|int|null $value = null): array

Gibt die Ergebnisse als assoziatives Array zurück. Das erste Argument gibt den Namen der Spalte an, die als Schlüssel im Array verwendet wird, das zweite Argument gibt den Namen der Spalte an, die als Wert verwendet wird:

$result = $database->query('SELECT id, name FROM users');
$names = $result->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Wenn wir nur den ersten Parameter angeben, ist der Wert die gesamte Zeile, d.h. das Row-Objekt:

$rows = $result->fetchPairs('id');
// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

Bei doppelten Schlüsseln wird der Wert aus der letzten Zeile verwendet. Bei Verwendung von null als Schlüssel wird das Array numerisch ab Null indiziert (dann treten keine Kollisionen auf):

$names = $result->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Alternativ können Sie einen Callback als Parameter angeben, der für jede Zeile entweder den Wert selbst oder ein Schlüssel-Wert-Paar zurückgibt.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// Der Callback kann auch ein Array mit einem Schlüssel-Wert-Paar zurückgeben:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Gibt den Wert des ersten Feldes der aktuellen Zeile zurück. Wenn keine weiteren Zeilen existieren, wird null zurückgegeben. Verschiebt den internen Zeiger auf die nächste Zeile.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // lädt den Namen aus der ersten Zeile

fetchList(): ?array

Gibt eine Zeile als indiziertes Array zurück. Wenn keine weiteren Zeilen existieren, wird null zurückgegeben. Verschiebt den internen Zeiger auf die nächste Zeile.

$result = $database->query('SELECT name, email FROM users');
$row = $result->fetchList(); // ['John', 'john@example.com']

getRowCount(): ?int

Gibt die Anzahl der von der letzten UPDATE- oder DELETE-Abfrage betroffenen Zeilen zurück. Bei SELECT ist dies die Anzahl der zurückgegebenen Zeilen, diese ist jedoch möglicherweise nicht bekannt – in diesem Fall gibt die Methode null zurück.

getColumnCount(): ?int

Gibt die Anzahl der Spalten im ResultSet zurück.

Informationen zu Abfragen

Zu Debugging-Zwecken können wir Informationen über die zuletzt ausgeführte Abfrage abrufen:

echo $database->getLastQueryString();   // gibt die SQL-Abfrage aus

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // gibt die SQL-Abfrage aus
echo $result->getTime();           // gibt die Ausführungszeit in Sekunden aus

Zur Anzeige des Ergebnisses als HTML-Tabelle kann verwendet werden:

$result = $database->query('SELECT * FROM articles');
$result->dump();

ResultSet bietet Informationen zu Spaltentypen:

$result = $database->query('SELECT * FROM articles');
$types = $result->getColumnTypes();

foreach ($types as $column => $type) {
	echo "$column ist vom Typ $type->type"; // z.B. 'id ist vom Typ int'
}

Abfrage-Protokollierung

Wir können eine eigene Abfrage-Protokollierung implementieren. Das Ereignis onQuery ist ein Array von Callbacks, die nach jeder ausgeführten Abfrage aufgerufen werden:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Query: ' . $result->getQueryString());
	$logger->info('Time: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Large result set: ' . $result->getRowCount() . ' rows');
	}
};