Prerequisites
A system running on Simplifier version >= 6.0 hotfix 141
General information
- A bad word filter is applied to avoid SQL injection. This means your SQL statement will be rejected if it includes certain reserved words like ‘INSERT’, ’UPDATE’ in the WHERE clause (find the complete list of words in the respective section of this article). As a result an error message will be returned when executing this Connector Call.
- Connector Calls which use dynamic WHERE clauses or are set to mode ‘repeatable statement’ can only be executed from within a Server-side Business Object or via the test function in the Simplifier UI.
- Dynamic WHERE clause can only be used in modes simple, query & execute.
Dynamic WHERE clause
Let’s see how we need to configure our Connector Call to make use of this function.
Here you can see that you only need to write your regular SQL statement without the WHERE clause after it in the ‘SQL’ tab of the Connector Call.
In the input mapping we need to specify a new parameter called ‘where’ which acts as a placeholder for your WHERE clause that you want to append to the SQL statement.
Here’s how our result looks like if we use different values for the ‘where’ parameter and test the Connector Call in the Simplifier UI:
And this is a demonstration of the aforementioned bad word filter if you try to pass reserved words from the list (please see below) to the ‘where’ parameter.
There are several bad words that will result in a rejection of the call:
- ALTER
- CREATE
- DELETE
- DROP
- EXEC(UTE)
- INSERT (INTO)
- MERGE
- SELECT
- UPDATE
- UNION (ALL)
Repeatable statement mode
We introduced a new mode to SQL Connector Calls called ‘repeatable statement”.
You can simply select it from the dropdown menu as you are used to with other modes like ‘execute’, ‘query’ etc.
After selecting this mode you can write any SELECT, INSERT or UPDATE statement that you want to have executed for each entry in a collection that is passed to the Connector Call by means of a new parameter called ‘parameterCollection’.
You can specify it as an input parameter in your call and then pick the appropriate collection data type for it.
Repeatable Statement in combination with Transactions
Since Simplifier version 6.5.201, the repeatable statement mode can be used with multiple SQL statements (similar to the transaction mode), separated by a delimiter.
All defined statements are executed for all objects in the input parameter collection in one transaction.
In the example in the screenshot above, two SQL statements are defined in the SQL editor. Both statements will be executed for each object that is inside the input parameter collection.