Bind Variables in Statements

General

In order to increase the security and efficiency of data processing, the use of bind variables is made possible for database interactions within customizing.

The existing functions such as ppms.db_select(query) and ppms.db_modify(query) are extended by an optional parameter for named variables.

The query and the bind variables are transmitted to the server and resolved there accordingly.

Technical Details

Implementation

An additional optional parameter has been added that can be used to specify bind variables. They are NOT implemented as positional parameters, but as named parameters.

The functions that have been expanded are:

parDictionary is optional, i.e. if it is not specified, statements can be executed without parameters as before.

The bind variables are transferred as a dictionary or HashMap, whereby the key must be a string with the name of the parameter and the value an object the class of which corresponds to the respective column in the database.

Please note that the named parameters are marked with : in the statement itself, but appear without : in the parameter list.

Examples

HQL statements carry out an internal conversion of parameters so that there is a certain freedom as to which parameters can be specified, e.g. a date can be transferred as a date object, as a string or as an integer (days since 1.1.1970). Integer, Double, UUID and string values are converted automatically:

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)

From server HF 38 it is also possible to hand over IN structures as lists:

CODE

pars = { 'id_list' : [ '0099JB', '0099AN' ] }
query = "select count(id_) from Module where id_ in :id_list"
res = hql.query(query, pars)

In Jython it would look something like this (without customizing convenience functions):

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 is somewhat more stringent, especially when it comes to date objects and UUID fields:

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)

CODE

pars = { 'id_list' : [ '0099JB', '0099AN' ] }
query = "select count(DI000123) from DT405 where DI000123 in :id_list"
res = ppms.db_select(query, pars)

Restrictions

In MSSQL, the keyword 'like' does not work as a parameter in connection with UUID.
The following statement, for example, will not find any data in MSSQL:

SQL

 from ModelParameters m where m.id like :some_uuid

These variants work:

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’

Literals in Statements

Literals in SQL/HQL statements are problematic for the following reasons:

Statements with literals are less performant
- Statements with bind variables are cached in SQL if the value of bind variables changes, while statements with literals are not cached if only one literal has changed
Security reasons (SQL injection)

For these reasons, a second functionality - the internal parsing of statements (HQL + SQL) in the server and the replacement of all literals with internal bind variables - has been added independently of bind variables in customizing is.

If, for example, this statement is executed:

PY

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

it is parsed internally in the server by JSQLParser and transformed to

PY

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

where the passed-on literal (in this case a UUID) is extracted and passed on as a parameter called parameter1.

This also works in conjunction with bind variables that have been defined externally.

For example, if this statement (with a bind variable) is passed on,

SQL

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

it becomes this statement internally

SQL

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

and is passed on to the database with two parameters accordingly.

If errors occur when parsing the statement or when executing a statement in which literals have been replaced by parameters, an attempt is then made to execute the statement again in its original form (i.e. with the literals) before an exception is thrown.

These cases are logged in the log file as warnings.

There are new functions for finding out whether an HQL or SQL statement contains literals:

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)

Deactivate Literal-Replacement

The internal parsing and replacement of literals can be switched off globally with the new configuration parameter transform_sql_literals in globals.conf.

To temporarily deactivate or reactivate the internal replacement of literals in a specific session if transform_sql_literals is activated globally, the following new methods can be used:

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

All of this does NOT affect binding variables that are passed on as parameters by Python or Jython. They are always evaluated, regardless of these config variables or temporary deactivations.