Bindevariablen in Statements

Allgemeines

Um die Sicherheit und Effizienz bei der Datenverarbeitung erhöhen, wird die Nutzung von Bindevariablen bei Datenbankinteraktionen innerhalb des Customizings ermöglicht.

Die bestehenden Funktionen wie ppms.db_select(query) und ppms.db_modify(query) werden um einen optionalen Parameter für benamte Variablen erweitert.

Das Query und die Bindevariablen werden an den Server übermittelt und dort entsprechend aufgelöst.

Technische Details

Implementierung

Es wurde ein zusätzlicher optionaler Parameter hinzugefügt, über den Bindevariablen angegeben werden können. Diese werden NICHT als positionelle Parameter realisiert, sondern als benamte Parameter.

Die Funktionen, die erweitert wurden, sind:

parDictionary ist optional, d.h., wenn es nicht angegeben wird, können wie bisher Statements ohne Parameter ausgeführt werden.

Die Bindevariablen werden als Dictionary bzw. HashMap übergeben, wobei der Key ein String mit dem Namen des Parameters sein muss und der Wert ein Objekt, dessen Klasse der entsprechenden Spalte in der Datenbank entspricht.

Bitte darauf achten, dass die benamten Parameter im Statement selbst durch : markiert werden, aber in der Parameterliste ohne : erscheinen.

Beispiele

HQL-Statements führen eine interne Konvertierung von Parametern durch, so, dass gewisse Freiheit besteht, welche Parameter angegeben werden können, z.B. kann ein Datum als Date-Objekt, als String oder als Integer (Tage seit 1.1.1970) übergeben werden. Auch Integer, Double, UUID- und String-Werte werden automatisch konvertiert:

PY

hql_statement = "from Project project where project.created_on = :created"
date_as_datetime = datetime(year = 2006, month = 2, day = 22)
parameters = { 'created' : date_as_datetime }
res = hql.search_record(hql_statement, parameters)

PY

hql_statement = "from Project project where project.created_on = :created"
date_as_string = '22.02.2006'
parameters = { 'created' : date_as_string }
res = hql.search_record(hql_statement, parameters)

PY

hql_statement = "from Project project where project.created_on = :created"
date_as_days_since_1970 = 13201
parameters = { 'created' : date_as_days_since_1970 }
res = hql.search_record(hql_statement, parameters)

CODE

statement = "select id from Project project where project.pr_id = :pr"
pars = { 'pr' : '4811' }
uuid = hql.query(statement, pars)[0][0]
new_statement = "select project.pr_id from Project project where project.id  = :eidie"
new_pars = { 'eidie' : uuid }
res = hql.query(new_statement, new_pars)

SQL

statement = "select project.pr_id from Project project where project.id  = :id"
pars = { 'id' : 'C361BADCBF0C948EE040A8C0F879597F' }
res = hql.query(statement, pars)

CODE

statement = "update Event event set ends_never = :ends where event_type = :oins or event_type = :zwoi"
pars = { 'ends' : 0, 'oins' : 714, 'zwoi' : 716 }
res = hql.modify(statement, pars)

CODE

statement = "update Event event set ends_never = :ends where event_type = :oins or event_type = :zwoi"
pars_of_dubious_types = { 'ends' : 0, 'oins' : "714", 'zwoi' : 716.161616 }
res = hql.modify(statement, pars_of_dubious_types)

In Jython sähe das in etwa so aus (ohne Customizing-Convenience-Funktionen):

PY

dbhandler = WebHqlQueryHandler()
date_as_datetime = datetime(year = 2024, month = 12, day = 30)
params = {'id' : '8000', 'prio' : 430, 'req_start' : date_as_datetime}
hashmap = HashMap()
for key, value in params.items():
    hashmap.put(key, value)
hql_change = "update Project project set project.requested_start = :req_start where project.pr_id = :id and project.pr_prio = :prio"
hql_query = "select project.requested_start from Project project where project.pr_id = :id and project.pr_prio = :prio"
dbhandler.hqlModify(hql_change, hashmap)
hashmap.remove('req_start')
retVal = dbhandler.hqlQuery(hql_query, hashmap)

SQL ist etwas stringenter, besonders, was Datumsobjekte und UUID-Felder angeht:

SQL

statement = "select * from dt472 where DI001419 = :costs"
pars = { 'costs' : 1333.5 }
res = ppms.db_select(statement, pars)

SQL

statement = "Update dt461 set di001015 = :posi where di001001 = :name"
pars = { 'posi' : 3, 'name' : '4811' }
res = ppms.db_modify(statement, pars)

SQL

statement = "select uuid from dt461 where di001001 = :project"
pars = { 'project' : '4811' }
uuid = ppms.db_select(statement, pars)[0][0]
statement = "select di001001 from dt461 where uuid = :eidie"
new_pars = { 'eidie' : uuid }
res = ppms.db_select(statement, new_pars)

SQL

uuid = uuid.UUID('C361BAD5F306948EE040A8C0F879597F')
statement = "select di001001 from dt461 where uuid = :eidie"
new_pars = { 'eidie' : uuid }
res = ppms.db_select(statement, new_pars)

SQL

statement = "SELECT COUNT(*) FROM DT461 WHERE DI001005 = :created"
new_pars = { 'created' :  datetime(year = 2012, month = 10, day = 9) }
res = ppms.db_select(statement, new_pars)

Einschränkungen

In MSSQL funktioniert das Schlüsselwort ‚like‘ in Zusammenhang mit UUID nicht als Parameter.
Also, dieses Statement hier z.B. wird in MSSQL keine Daten finden:

SQL

 from ModelParameters m where m.id like :some_uuid

Diese Varianten funktionieren:

SQL

from ModelParameters m where m.id = :some_uuid

SQL

from ModelParameters m where m.id = ‘12345678123456781234567812345678’

SQL

from ModelParameters m where m.id like ‘12345678123456781234567812345678’

Customizing

Wenn für Customizer positionelle Parameter verwendet werden sollen, muss es dort einen Mechanismus geben, der aus einer Liste ein entsprechendes Dictionary mit fest generierten Namen macht.
Es wird empfohlen, diese generierten Namen nicht parameter1, parameter2 etc. zu nennen, da Parameter mit diesem Namen auch intern vom Server generiert werden (s. dazu das Kapitel Literale in Statements).

Literale in Statements

Literale in SQL/HQL-Statements sind aus folgenden Gründen problematisch:

Statements mit Literalen sind weniger performant
- Statements mit Bindevariablen werden in SQL gecached, wenn sich der Wert von Bindevariablen verändert, während Statements mit Literalen nicht gecached werden, wenn sich lediglich ein Literal geändert hat
Sicherheitsgründe (SQL-Injection)

Aus diesen Gründen ist eine zweite Funktionalität, die unabhängig von Bindevariablen im Customizing eingefügt wurde, das interne Parsen von Statements (HQL + SQL) im Server und Ersetzen von allen Literalen durch interne Bindevariablen.

Also, wenn z.B. dieses Statement ausgeführt wird:

PY

ppms.db_select("SELECT * FROM users WHERE id = {id}'".format(id=user_id))

wird es intern im Server durch JSQLParser geparst und transformiert zu

PY

ppms.db_select("SELECT * FROM users WHERE id = :parameter1)

wobei das übergebene Literal (in diesem Fall eine UUID) extrahiert und als Parameter namens parameter1 übergeben wird.

Dies funktioniert auch im Zusammenspiel mit Bindevariablen, die extern definiert wurden.

Wenn also z.B. dieses Statement (mit einer Bindevariable) übergeben wird,

SQL

statement = "select * from dt472 where DI001419 = :parameter1 and DI001395 = ‘8000’"

wird daraus intern dieses Statement

SQL

statement = "select * from dt472 where DI001419 = :parameter1 and DI001395 = :parameter2"

und entsprechend mit zwei Parametern an die Datenbank übergeben.

Sofern beim Parsen des Statement oder beim Ausführen eines Statements, in dem Literale durch Parameter ersetzt wurden, Fehler aufgetreten sind, wird danach noch einmal versucht, das Statement in der originalen Form (also mit den Literalen) auszuführen, bevor eine Exception geworfen wird.

Diese Fälle werden im Log-File als Warnings gelogged.

Es gibt neue Funktionen, um herauszufinden, ob ein HQL-bzw. SQL-Statement Literale enthält:

In Python:
- bool ppms.statement_uses_literals_sql(String statement)
- bool ppms.statement_uses_literals_hql(String statement)
In Jython:
- public bool hqlStatementUsesLiterals(String statment)

Deaktivieren von Literal-Ersetzen

Das interne Parsen und Ersetzen von Literalen kann mit dem neuen Konfigurationsparameter transform_sql_literals in der globals.conf global ausgeschaltet werden.

Um das interne Ersetzen von Literalen temporär in einer bestimmten Session zu deaktivieren bzw. reaktivieren, falls transform_sql_literals global aktiviert ist, können die folgenden neuen Methoden verwendet werden:

In Python:
- ppms.disable_literals_sql_transformation()
- ppms.enable_literals_sql_transformation()
In Jython:
- public void enableLiteralsSQLTransformation();
- public void disableLiteralsSQLTransformation();

Dies Alles beeinflusst NICHT Bindevariablen, die von Python oder Jython als Parameter übergeben werden. Diese werden immer ausgewertet, unabhängig von diesen Konfig-Variablen oder temporären Deaktivierungen.