Alle Connectors beinhalten eine execute-Funktion, die ein SQL-Statement als String-Parameter übernimmt und auf Seiten der Datenbank ausführen lässt. So richtig sinnvoll wird der Einsatz von Python hier aber erst, wenn man SQLs dynamisch und datengetrieben erzeugt.
Genau an dieser Stelle möchte ich einhaken und - angefangen bei den einfachsten, aber unklügsten Wegen - verschiedene Alternativen und letztlich eine Best Practice aufzeigen, wie SQL-Strings übergeben werden sollten.
Ich muss zu Beginn auch noch mal klarstellen, dass alle außer der letzten Alternative potentiell gefährliche Sicherheitslücken darstellen. Es ist möglich, wenn nicht andere Sicherheitsvorkehrungen getroffen werden, dass sensible Daten abgerufen oder gar gelöscht werden.
Die naive, gefährlichste Herangehensweise: String-Verkettung
Zunächst erstellen wir eine Testdatenbank in SQLite. Ich wähle SQLite deswegen zur Demonstration, weil SQLite in Python mitgeliefert wird, Datenbanken rein im Arbeitsspeicher für die Laufzeit des Skriptes erstellt werden können und die Beispiele daher bei jedem selbst replizierbar sind. Fehlerfreie Ausführung der Beispiele kann ich aber nur ab Python 3.x garantieren.
import sqlite3
db = sqlite3.connect(':memory:')
db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ")
db.execute("INSERT INTO staff (person_id, lastname) VALUES (1, 'Pavlov') ")
db.execute("INSERT INTO staff (person_id, lastname) VALUES (2, 'Skinner') ")
Im Codebeispiel wurde nach dem Modulimport eine namenlose Datenbank im Speicher initialisiert, indem im connect-Befehl als Speicherort der Datenbank ':memory:' übergeben wurde.
Anschließend wird eine Mitarbeitertabelle namens "staff" erzeugt und mit den ersten Daten befüllt.
So weit, so gut. Wir wollten aber nicht für alle Mitarbeiter, die wir in die Tabelle noch einfügen werden, extra einen kompletten Insert-Befehl verfassen.
Wenn die Mitarbeiternamen bereits als Aufzählung vorliegen, drängt es sich förmlich auf, eine Schleife zu verwenden.
Erster Wurf
db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ")
for person_id, lastname in enumerate(staff_names):
db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")
Das ging trotz aller guter Vorsätze daneben. Die Fehlermeldung "TypeError: Can't convert 'int' object to str implicitly" weist uns darauf hin, dass wir vergessen haben, den Datentyp der person_id von integer auf str zu casten. Auch wenn Python in fast allem sehr flexibel ist, ist es dennoch eine stark typisierte Sprache und inmutables-Strings lassen sich nicht mit Integern kombinieren.
Da muss der Compiler früher aufstehen, nächster Anlauf:
db.execute("CREATE TABLE staff (person_id int, lastname CHAR);")
for person_id, lastname in enumerate(staff_names):
db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + str(person_id) + ", '" + lastname + "') ")
Na bitte, es läuft, aber schön sieht es nicht aus. Vor allem wenn man sehr viele dieser Verkettungen im Code verwendet, ist das Statement sehr zerstückelt. Außerdem muss ich mich immer selbst um die Typkonvertierung kümmern.
Die alte Herangehensweise: String-Templates mit %s
Wenn man Python-Literatur durchblättert, auch so manche aktuelle, und man diverse Foreneinträge in Stack Overflow oder sonst wo liest, sieht man Techniken, die in unserem Beispiel so aussehen:
db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ")
for person_id, lastname in enumerate(staff_names):
db.execute("INSERT INTO staff (person_id, lastname) VALUES (%d, '%s') " % (person_id, lastname))
Funktioniert prima. "%d" ist der Platzhalter für eine Zahl (digit) und "%s" der Platzhalter für einen String.
Sollte man aber mit dieser Schreibweise einen Wert mehrfach gebrauchen müssen, wird es wieder etwas unübersichtlich. Stellen wir uns ein Beispiel vor, in dem wir in einer Abfrage diverse Bedingungen prüfen.
sql = """SELECT lastname , CASE WHEN %d > 10 THEN 'greater' WHEN %d = 10 THEN 'equal' WHEN %d < 10 THEN 'lesser' END vergleich FROM staff WHERE lastname <> '%s' and %d > 0 """ % (person_id, person_id, person_id, lastname, person_id)
Sobald wir hier weitere Platzhalter einfügen, wächst die Fehlergefahr und wir müssen jedes Mal die Positionen im Code abzählen. Besser ist es hier, benannte Platzhalter zu wählen.
sql = """SELECT lastname , CASE WHEN %(person_id)d > 10 THEN 'greater' WHEN %(person_id)d = 10 THEN 'equal' WHEN %(person_id)d < 10 THEN 'lesser' END vergleich FROM staff WHERE lastname <> '%(lastname)d' and %(person_id)d > 0 """ % {'person_id': person_id, 'lastname': lastname)
Wunderbar, der Code ist jetzt viel lesbarer, da wir sofort sehen, was wir wo einfügen.
Diese Schreibweise hat nur ein kleines Problem, und zwar gilt sie als veraltet und wurde zumindest in Python 3 durch eine bessere abgelöst. Vor etwa 3-4 Jahren hab ich mehrfach in Foren gelesen, dass diese Schreibweise sogar als deprecated galt. Das heißt, sie sollte nicht mehr verwendet werden, da ihr Fortbestand in späteren Python-Versionen nicht garantiert ist. Davon ist man wohl heutzutage abgekommen, vermutlich wegen der noch sehr hohen Verbreitung in Modulen.
Die neue Herangehensweise: String-Templates mit {}
Die neue offizielle Schreibweise verwendet geschweifte Klammern. Sie sieht nicht nur anders aus, sondern birgt auch viel mehr Potential in Bezug auf Formatierungsmöglichkeiten. Wenn man sich ohnehin die neue Schreibweise aneignet, da sie mehr kann, warum sie dann nicht durchgängig einsetzen?
Zunächst die einfache Variante:
db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ")
for person_id, lastname in enumerate(staff_names):
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}') ".format(person_id, lastname))
Man beachte, dass man hier, wenn die Darstellung in Bezug auf Kommastellen oder führende Nullen usw. keine Rolle spielt, keine Unterscheidung zwischen Strings oder numerischen Werten für die Platzhalter treffen muss. Ja man kann sogar z. B. Tupel direkt verwenden:
db.execute("SELECT * FROM staff WHERE person_id in {}".format((1,3,4)))
Die Format-Funktion des Strings ruft die __str__-Methode jedes Objekts auf. Das entspricht dann jeweils str(object).
Auch hier gibt es die Schreibweise mit Labels, allerdings übergibt man kein dictionary, sondern schreibt die Zuordnung in Form von Funktionsparametern.
db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ")
for person_id, lastname in enumerate(staff_names):
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(person_id=person_id, lastname=lastname))
Alle, die etwas schreibfaul sind, können auch Tuple Packing und Tuple Unpacking für sich nutzen.
db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ")
for row in enumerate(staff_names):
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}') ".format(*row))
row ist hier in jedem Durchlauf der Schleife ein Tupel, weil enumerate() zwei Werte als Tupel zurückgibt. Diese werden dann in die Variable row gezwungen. Mit der Schreibweise ".format(*row)" entpackt man das Tupel wieder und ruft die Werte in der entsprechenden Reihenfolge ab.
Dasselbe klappt auch mit Dictionaries.
data = {'lastname': 'Mustermann', 'person_id': 12345}
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(**data))
Das extremste Beispiel für Tippfaulheit wäre dieses theoretische Beispiel einer Insert-Funktion.
def insert_staff(person_id, lastname, other_parameter1, other_parameter2):
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(**globals())
Hier holt sich der Format-Befehl die Daten einfach aus den im Namespace des Funktionsaufrufs definierten Variablen, in diesem Fall die Funktionsparameter. Dieses Beispiel habe ich selbst in Python 2.7 verwendet. In Python 3.x geht das wohl nicht mehr - ich denke, das ist auch besser so.
Jetzt stellen wir uns vor, wir bekommen einen neuen Mitarbeiter, den fünfzigsten, namens OʼReilly. Der wird geschwind eingefügt:
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(person_id=50, lastname="O'Reilly"))
SQLite meckert hier "sqlite3.OperationalError: near "Reilly": syntax error". Woran liegt es? Ganz offensichtlich wird der Name zwar in das SQL-Statement eingefügt, aber das Hochkomma im Namen wird als Ende des Namensstrings interpretiert, also " , 'O'Reilly') ".
Hier kann man nun im Codingrausch Abhilfe schaffen, indem man das Hochkomma escapet. Das variiert je nach Datenbank, in SQLite muss man die Hochkommata doppeln.
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(person_id=50, lastname="O'Reilly".replace("'", "''")))
Puh, das funktioniert erst mal, aber ist letztlich nur ein billiger Workaround.
So, jetzt bekommen wir einen neuen Mitarbeiter, den einundfünfzigsten, nämlich Herrn "');DROP TABLE staff;". Komischer Nachname, aber wenn das vom User so eingegeben wurde, dann wird das schon seine Richtigkeit haben.
Wenn wir nicht aus dem Anlass mit Herrn OʼReilly diverse Character escapen würden und wenn wir nicht mit SQLite arbeiten würden, was hier keine 2 Statements in einem execute-String zulässt, würde der Abfragestring wie folgt aussehen.
INSERT INTO staff (person_id, lastname) VALUES(51, '');DROP TABLE staff;
Hier wird durch ungesäuberten User-Input die ganze Tabelle gelöscht. Diese SQL-Injections sind reale Gefahren für die Sicherheit der Datenbank. Vor wenigen Jahren noch bin ich beim Rumprobieren selbst auf größere Online-Händler gestoßen, die in der Produktsuche nicht mal das Hochkomma säuberten.
