Hauptinhalt
Programmierung
Kurs: Programmierung > Lerneinheit 3
Lektion 4: Datenbanken mit SQL aktualisierenDein SQL sicherer machen
SQL ist eine schöne Sache, aber es kann auch gefährlich sein. Wenn du SQL für den Datenbankzugriff aus einer App mit hunderten, tausenden oder sogar Millionen von Benutzern benutzt, musst du sehr vorsichtig sein. Denn du kannst sehr schnell aus Versehen die Daten beschädigen oder löschen. Es gibt jedoch verschiedene Techniken, wie du SQL sicherer machen kannst.
Vermeide schlechte Updates/Deletes
Bevor du ein
UPDATE durchführst, mache immer zuerst ein SELECT mit der gleichen WHERE-Bedingung, um sicherzustellen, dass du die richtigen Spalten und Zeilen aktualisierst.Bevor du zum Beispiel
UPDATE users SET deleted = true WHERE id = 1;ausführst, könntest du
SELECT id, deleted FROM users WHERE id = 1;ausführen. Nachdem du dich entschieden hast, das Update durchzuführen, kannst du mit dem Operator LIMIT sicherstellen, dass du nicht versehentlich zu viele Zeilen löschst.
UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;Oder wenn du löschst:
DELETE users WHERE id = 1 LIMIT 1;Transaktionen verwenden
Wenn wir einen SQL-Befehl, welcher die Datenbank verändert, verwenden, startet dieser eine "Transaktion". Eine Transaktion ist eine Reihe oder Sequenz von Operationen, welche als ein logischer Arbeitsschritt (wie eine Banktransaktion) betrachtet werden. In der Welt der Datenbanken muss eine Transaktion die ACID-Regeln befolgen, damit alle Operationen korrekt ausgeführt werden.
Immer, wenn wir einen Befehl wie
CREATE, UPDATE, INSERT oder DELETE verwenden, starten wir automatisch eine Transaktion. Wir können aber auch mehrere Befehle in einer größeren Transaktion zusammenfassen. Es kann sein, dass wir nur dann ein UPDATE durchführen wollen, wenn ein anderes UPDATE ebenfalls durchgeführt worden ist. Dann fassen wir beide in einer Transaktion zusammen.In diesem Fall schreiben wir die Befehle zwischen
BEGIN TRANSACTION und COMMIT:BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;Wenn die Datenbank nicht in der Lage ist, beide
UPDATE-Befehle komplett auszuführen, dann kann sie die Transaktion rückgängig machen und die Datenbank in ihren Zustand vor Beginn der Transaktion zurücksetzen.Wir benutzen ebenfalls Transaktionen, wenn wir sicher sein wollen, dass all unsere Befehle auf den gleichen Daten ausgeführt werden. Dies heißt, wir wollen sicher sein, dass keine anderen Transaktionen die Daten gleichzeitig verändern. Wenn du eine Reihe von Befehlen betrachtest, kannst du dir überlegen, was passieren würde, wenn ein anderer Benutzer zur gleichen Zeit andere Befehle ausführen würde. Könnten deine Daten dann in einen inkonsistenten Zustand gelangen? In diesem Fall solltest du eine Transaktion benutzen.
Die folgenden Befehle fügen zum Beispiel eine neue Zeile ein, welche für ein Abzeichen (Badge), welches der Benutzer erworben hat, steht. Danach wird die letzte Aktivität des Benutzers aktualisiert.
INSERT INTO user_badges VALUES (1, "SQL Master", "4pm");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;Zur gleichen Zeit könnte ein anderer Prozess dem Benutzer ein zweites Abzeichen vergeben.
INSERT INTO user_badges VALUES (1, "Great Listener", "4:05pm");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;Diese Befehle würden dann in der folgenden Reihenfolge ausgeführt:
INSERT INTO user_badges VALUES (1, "SQL Master");
INSERT INTO user_badges VALUES (1, "Great Listener");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;Seine letzte Aktivität wäre nun "Earned SQL Master badge", obwohl sein letztes erworbenes Abzeichen "Great listener" war. Dies ist zwar nicht das Ende der Welt, aber es ist nicht das, was wir wollten.
Wir können diese stattdessen in einer Transaktion ausführen, um sicherzustellen, dass dazwischen keine anderen Transaktionen ausgeführt werden.
BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "SQL Master");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
COMMIT;Erstellen von Backups
Du solltest diese Tipps wirklich befolgen, aber manchmal passieren trotzdem Fehler. Genau deshalb machen die meisten Firmen Backups ihrer Datenbanken. Je nach Größe der Datenbank und vorhandenem Platz machen sie dies stündlich, täglich oder wöchentlich. Wenn etwas schief geht, können sie die Daten von einem der Backups für die betroffenen Tabellen importieren und so die verlorenen Daten wiederherstellen. Die Daten sind dann vielleicht nicht mehr ganz aktuell, aber veraltete Daten sind immer noch besser als gar keine Daten.
Replikation
Eine ähnliche Vorgehensweise ist die Replikation. Dabei werden immer mehrere Kopien der Datenbanken an verschiedenen Orten gespeichert. Wenn aus irgendeinem Grund eine bestimmte Kopie der Datenbank nicht verfügbar ist (wie z. B., wenn ein Blitz im Gebäude eingeschlagen ist), dann wird die Abfrage einfach an eine andere Kopie der Datenbank, welche hoffentlich noch funktioniert, geschickt. Wenn die Daten sehr wichtig sind, dann sollten diese repliziert werden, damit die Verfügbarkeit garantiert werden kann. Wenn z. B. ein Doktor eine Liste der Allergien seiner Patienten für Notfallsituationen braucht, dann kann er nicht auf einen Techniker warten, welcher zuerst die Daten aus einem Backup wiederherstellen muss.
Es bedeutet viel mehr Aufwand, Datenbanken zu replizieren, und oft führt es auch zu Leistungseinbußen, da Schreiboperationen in allen Replika durchgeführt werden müssen. Daher müssen sich Unternehmen entscheiden, ob die Vorteile der Replikation die Kosten wert sind, und herausfinden, welche Umsetzung die beste für ihre Umgebung ist.
Vergabe von Berechtigungen
Viele Datenbanksysteme haben Benutzer und Berechtigungen, da sie sich auf einem Server befinden, auf welchen mehrere Benutzer zugreifen können. In den SQL-Scripts auf der Khan Academy werden Benutzer und Berechtigungen nicht behandelt, da SQLite üblicherweise nur mit einem Benutzer benutzt wird und du damit in die DB schreiben kannst, solange du Zugriff auf die Datei hast.
Aber wenn du eines Tages ein Datenbanksystem auf einem Server benutzt, dann solltest du unbedingt von Anfang an die Benutzer und ihre Berechtigungen korrekt konfigurieren. Grundsätzlich sollten nur wenige Benutzer vollen Zugriff auf die Datenbank haben (z. B. Backend-Ingenieure). Denn dieser volle Zugriff kann gefährlich sein.
Hier ein Beispiel, wie wir einem bestimmten Benutzer vollen Zugriff gewähren:
GRANT FULL ON TABLE users TO super_admin;Und hier ein Beispiel, wie wir einem bestimmten Benutzer nur SELECT-Abfragen gewähren:
GRANT SELECT ON TABLE users TO analyzing_user;In einer großen Firma möchtest du oft nicht einmal jedem Benutzer die Berechtigung
SELECT gewähren. Denn die Tabellen können private Daten enthalten, die geschützt werden müssen, wie die E-Mail oder den Namen des Benutzers. Viele Firmen verwenden anonymisierte Versionen der Datenbanken, auf welchen sie Abfragen durchführen können, ohne sich um den Datenschutz Gedanken machen zu müssen.Bonus: Lies dieses bekannte XKCD Comic über sicheres SQL (plus diese Erklärung).
Willst du an der Diskussion teilnehmen?
Noch keine Beiträge.