• Resources
    • Documentation
    • Knowledge Base
    • Forum
    • Webinars
    • Courses
    • FAQ
    • Changelog
    • Blog
    • Swag Shop
  • Marketplace
  • Access Playground
  • Login
  • Resources
    • Documentation
    • Knowledge Base
    • Forum
    • Webinars
    • Courses
    • FAQ
    • Changelog
    • Blog
    • Swag Shop
  • Marketplace
  • Access Playground
  • Login
home/Knowledge Base/Integrations/Dynamic where-clause and repeatable statement

Dynamic where-clause and repeatable statement

324 views 0

Written by Vitali
July 1, 2021

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)
Please note that this filter will only trigger if it finds the exact same word in your WHERE clause.

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.

Download Sample Application

Was this article helpful?

Yes  No
Related Articles
  • Simplifer setup with recommended environment using docker-compose
  • Logs and Monitoring in Simplifier
  • Version and Release Management in Simplifier
  • How to send and receive Push Notifications
  • How to set Simplifier environment variables in Open Shift
  • How to use TimePicker widget
Leave A Comment Cancel reply

You must be logged in to post a comment.

Latest Articles
  • Process Designer: Keeping an overview
  • Simplifer setup with recommended environment using docker-compose
  • Logs and Monitoring in Simplifier
  • Contact | Imprint | Privacy | © 2021 Simplifier AG. All Rights Reserved.