SQL Connector Parameters

Christopher Steinbach
4 years ago #21736

Hi,

I experienced that there are some SQL statements won’t work in the current SQL Connector implementation.

For example IN statements or LIKE statements:

SELECT * FROM sometable WHERE username LIKE :username: -> Will not work

SELECT * FROM sometable WHERE username IN (:usernamelist:) -> Will also not work

My current workaround is to define the whole request statement in a BO where I’m on my own to escape sql injections.

Is there another placeholder syntax for LIKE statements (e.g. @username) or any solution to get this working inside the connector?

Thanks for your help in advance.
Jennifer Häfner
Has successfully completed the online course Intermediate (200)
Has successfully completed the online course Advanced (300)
Has successfully completed the online course Basics (100)
Has successfully completed the online course Advanced (310)
Has successfully completed the online course Advanced (320)
Has successfully completed the Intermediate Certification
Has successfully completed the Advanced Certification
3 years ago #27304

Up
1
Down
::
I know that this is an old post, but since Simplifier version 6.0.138-LTS.141, SQL statements with a dynamic WHERE clause are possible.
The WHERE clause needs to be added as a parameter in the Connector call. Inside the WHERE clause, statements like ‘id IN (1,2,3)’ or ‘name LIKE “John”‘ can be specified.
Connector calls with a dynamic WHERE clause can only be executed inside a Server Side Business Object for security reasons. Also, words like ALTER, CREATE, DELETE, DROP, EXEC(UTE), INSERT (INTO), MERGE, SELECT, UPDATE, UNION (ALL) are not allowed inside a dynamic WHERE clause.

Attachments:
You must be logged in to view attached files.
Christopher Steinbach
3 years ago #27310

Up
1
Down
::
Perfect, thank you. That is the solution I have looked for.
Kahn
Has successfully completed the online course Introduction
Has successfully completed the online course Intermediate (200)
Has successfully completed the online course Advanced (300)
3 years ago #27644

Up
1
Down
::
Hello,

due to the fact that sanitation is always depending on the use-case. No sanitation is performed by the connector EXCEPT the check if one of the bad words (case-insensitive and trimmed) is provided inside the WHERE statement. Words like DROPSHIP or other that contains the words as a prefix/infix/suffix are ignored, however scenarios where the words as a whole inside single quotation marks or table names will be also detected as BAD Words albeit they are not bad words in such contexts.

Example:

SELECT * FROM table A WHERE status=’UPDATE’

SELECT * FROM table B WHERE update=false

The main security benefit lies in the fact, that this mode can only be executed inside a server-side business object and where sanitation can be applied by a use-case to use-case basis. Automatic sanitation by the connector cannot be provided (and will not be provided) as there are no generic rules that can be applied to it, as it ALWAYS depends on the use case.

Thus you are responsible for the sanitation in the business object.

I hope this helps you.

Best Regards
Jennifer Häfner
Has successfully completed the online course Intermediate (200)
Has successfully completed the online course Advanced (300)
Has successfully completed the online course Basics (100)
Has successfully completed the online course Advanced (310)
Has successfully completed the online course Advanced (320)
Has successfully completed the Intermediate Certification
Has successfully completed the Advanced Certification
2 years ago #34108

Up
1
Down
::
Hi,

if you have a simple SQL statement, like SELECT * FROM Table WHERE name = :autofieldValue:, you do not need a dynamic WHERE statement. In your connector call, just define one input parameter for the autofield value. Then, in the Process Designer, open the input mapping of the connector call and connect the input parameter to the autofield. The same applies for global variables.

If your use case and SQL statement is more complex, please explain it in more detail (you can also add screenshots), so we can find a solution for your problem.
Muzzammil Ahmed
2 years ago #34109

Up
1
Down
::
Hi,

I am still facing issues while inserting and getting values back from sql connector calls with variables. Can you please share any detailed guide to insert,update and get values using variables from a SQL connector call.

Thanks.
Kahn
Has successfully completed the online course Introduction
Has successfully completed the online course Intermediate (200)
Has successfully completed the online course Advanced (300)
4 years ago #21737

Up
0
Down
::
Hello,

unfortunately it is not possible inside the connector. You are also limited to the following data types

String Integer Float (it is a double actually) and Boolean. You cannot pass null explicitly, if you do not pass anything it will be converted into NULL

As you said, you can define it in the your business object. As it is still a prepared statement on the underlying JDBC-driver SQL injections can only occur when your statement itself is not a prepared statement.

The following statement as a provided request:

SELECT * FROM MYTABLE WHERE user = :user:String:

will be interpreted as a prepared statement with the parameter user whereas

SELECT * FROM MYTABLE WHERE user = “myUser”

will be interpreted as a prepared statement without parameters.

So by defining the request in a parameterized in your BO and passing it to the connector with the appropriate parameters you can avoid injections without your own sanitation logic.

However, this does not mean that by defining statements in a non-parameterized way is wrong, as there are enough use cases, which are relaying on dynamically created SQL-Statements.
Christopher Steinbach
4 years ago #21777

Up
0
Down
::
Yes I’m aware that I can prepare statement but the connector will not resolve certain parameters correct due to statment type or data type.

For example a LIKE statement

SELECT * FROM sometable WHERE username LIKE :username:

will in simplifier not resolved to

SELECT * FROM sometable WHERE username LIKE ‘%userxy%’

It seemes that the LIKE statement is not support with prepared statements.

Another example is the IN statement with an ListOfString input parameter

SELECT * FROM sometable WHERE username IN (:usernamelist:)

will not resolved to

SELECT * FROM sometable WHERE username IN (‘user1’, ‘user2’, ‘user3’)

As an inspriation: How the mysql javascript implementation handles this:

Different value types are escaped differently, here is how:

Numbers are left untouched
Booleans are converted to true / false
Date objects are converted to ‘YYYY-mm-dd HH:ii:ss’ strings
Buffers are converted to hex strings, e.g. X’0fa5′
Strings are safely escaped
Arrays are turned into list, e.g. [‘a’, ‘b’] turns into ‘a’, ‘b’
Nested arrays are turned into grouped lists (for bulk inserts), e.g. [[‘a’, ‘b’], [‘c’, ‘d’]] turns into (‘a’, ‘b’), (‘c’, ‘d’)
Objects that have a toSqlString method will have .toSqlString() called and the returned value is used as the raw SQL.
Objects are turned into key = ‘val’ pairs for each enumerable property on the object. If the property’s value is a function, it is skipped; if the property’s value is an object, toString() is called on it and the returned value is used.
undefined / null are converted to NULL
NaN / Infinity are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.
This escaping allows you to do neat things like this:

var post = {id: 1, title: ‘Hello MySQL’};
var query = connection.query(‘INSERT INTO posts SET ?’, post, function (error, results, fields) {
if (error) throw error;
//
});
Christopher Steinbach
3 years ago #27426

Up
0
Down
::
I have testet the new WHERE-Statement and there is one last security concern for me:

If I’m going to prepare a WHERE-Parameter in an BO, how is this input sanitized?

For example:

DELETE FROM orders

WHERRE-Statement = “customer_name = ‘” + input.customerName + “‘”

input.customerName = “maleware’ AND ‘hack’=’hack”

input.customerName has to be sanitized or all orders will be deleted!

Is there a built in utility function in a BO to fix this? Or do I something completly wrong?

WHERRE-Statement = “customer_name = ‘” + sanitize(input.customerName) + “‘”
Muzzammil Ahmed
2 years ago #34106

Up
0
Down
::
Hello,
Is there any possibility to put where condition comparing it with the auto-fields value?

I am trying to create a connector call that displays only those records which has the value equals to my auto-field value.

Thanks.

Kind regards,
Muzzammil Ahmed.
Muzzammil Ahmed
2 years ago #34107

Up
0
Down
::
++

Even while inserting the values into the database, I am struggling to insert the auto-fields and global variable value through my connector call.

I request you to please guide me with this procedure/workflow.

Thanks.