vorheriger Artikel
Dank eines verpflichtenden Interfaces für Datenbank-Connectors, der "Python Database API Specification v2.0, PEP249", wurden alle aktuellen Connectors so entwickelt, dass Datenbankverbindungen und die SQLs für Datenabrufe und Datentransaktionen über dieselben Befehle gestartet werden können. Mehr oder weniger erhält man auch überall im gleichen Format Ergebnisse zurück. In diesem Punkt gibt es gefühlt noch die größten Abweichungen von der geforderten Vereinheitlichung.
Das sollte aber niemanden davon abschrecken, Python-Skripte als eine flexible Methode für Automatisierungen von Datenbankoperationen zu verwenden.
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.
Best Practice: Parameterized Queries
Alle drei aufgezeigten Alternativen funktionieren. Ich selbst verwende wegen der Schnelligkeit noch hier und da die eine oder andere Schreibweise davon.
Ganz klare Best Practice ist jedoch die Verwendung von "Parameterized Queries". Unabhängig von der Programmiersprache sollte jeder Datenbank-Connector diese Art der Abfragenübermittlung unterstützen. In Python weiß ich zumindest aus der Praxis, dass es für Oracle, MySql, SQLite und PostgreSQL funktioniert.
Die Idee dahinter ist, dass man den SQL-String nicht komplett selbst zusammenstellt und dann an den Connector übergibt, sondern dass man ein Template und die Parameter für das Template übergibt.
Es gibt verschiedene Vorteile. Zum einen übernimmt der Datenbanktreiber jegliche Typenkonvertierungen und Spezialbehandlungen von Sonderzeichen wie Hochkommata. Man muss sich also nicht darum kümmern, wie die jeweiligen Konventionen der Datenbank sind.
Zum anderen bringt es auf manchen Datenbankplattformen Performancevorteile, wenn man denselben Abfragerumpf sehr häufig mit unterschiedlichen Parametern ausführt. Der SQL-Parser muss dann die Abfrage nicht jedes Mal neu parsen, um die Ausführung zu planen, sondern greift auf frühere Ausführungen zurück und wechselt nur die Werte am Platzhalter aus.
Leider ist die Art und Weise der Parametrisierung über verschiedene Datenbankplattformen hinweg alles andere als einheitlich. Ein Python-Datenbanktreiber besitzt zumindest ein Attribut names paramstyle, das angibt, welche Technik zu verwenden ist.
print(sqlite.paramstyle)
# Ausgabe ist 'qmark'
# also
db.execute("INSERT INTO staff (person_id, lastname) VALUES (?, ?)", (51, "Mc'Donald"))
Im SQLite-Beispiel prüfen wir erst, welcher paramstyle wichtig ist. Es ist 'qmark', also question marks/Fragezeichen. Das insert-Statement zeigt die Verwendung. Es werden für jede Position Fragezeichen eingefügt. In der richtigen Reihenfolge müssen dann als zweiter Parameter im Funktionsaufruf die Werte als Tupel übergeben werden. Typenkonvertierungen laufen automatisch, auch bei Datumsobjekten.
In PostgreSQL z. B. gibt es ein anderes Format, das auch benannte Parameter zulässt.
cursor.execute('SELECT * FROM staff WHERE person_id = %d', (99,))
# oder
cursor.execute('SELECT * FROM staff WHERE person_id = %(person_id)d', {'person_id': 99}) Best Practice = Only Practice?
Eigentlich ist der Titel "Best Practice" irreführend. Es sollte eher "Only Practice" heißen. Parametrisierte Abfragen scheinen auf den ersten Blick umständlich zu sein. Vor allem, wenn man das SQL-Statement dynamisch über Stringverkettungen zusammenbaut, ist leicht der Überblick über Parameter zu verlieren, besonders wenn, wie in SQLite, die Platzhalter nur mit Fragezeichen gekennzeichnet sind. Dass man sich aber keine Sorgen über die Typenkonvertierung und Sonderzeichen machen muss, ist dafür schon mal ein großer Benefit.
Normalerweise müsste ich jetzt sagen, dass der Sicherheitsaspekt, nämlich die Vermeidung von SQL-Injections, der wichtigste und allein ausreichende Grund sein sollte, nur die Best Practice zu verwenden.
Im täglichen Data-Science-Umfeld, in dem viele Sicherheitsvorkehrungen getroffen wurden, bis man Zugang zu den Daten erhält, und wo die Anwendung nicht notwendigerweise durch potentiell bösartige User bedient werden kann, ist der Sicherheitsaspekt eher eine Kür. Nur sehr selten hatte ich über Jahre im Arbeitsumfeld die Situation, in der ein potentiell gefährlicher Python-Code anderen außerhalb des direkten Projektes zugänglich war. Und in diesen Fällen hätte eh jeder selbst direkten Zugriff auf die Datenbank gehabt.
Wenn man Python zur SQL-Generierung voll ausreizen will und richtig mächtige dynamische SQL-Abfragen erstellt, muss man irgendwann zwangsläufig wieder der Best Practice untreu werden. Man kann nämlich nur Werte-Inputs parametrisieren. Tabellennamen und Spaltennamen können weiterhin nur durch eine der anderen oben beschriebenen Techniken dynamisch eingesetzt werden.
Effektiv, muss ich gestehen, setze ich deswegen selbst immer einen Mix aus parametrisierten Abfragen und String-Templates mit {} ein. Man muss dann eben stets selbst dafür sorgen, dass nur autorisierte Nutzer Zugriff auf das Skript und die Daten erhalten. Ein erster Schritt ist es schon, dass jeder, der ein Python-Datenbank-Skript bedient, für die Verbindung einen eigenen und keinen generischen User benötigt.
