SQL: passing part of query as input parameter

[ZbigniewK]

I have simple SQL query:

SELECT [column 1], [column 2], [column3] ...

  FROM [table] WHERE [column1]= 31.

In my case, conditions are chaning, sometimes I have no WHERE part of querry, sometimes I have 1 column, sometimes 3, depending on operators behaviour.
So in query, I added variable filter as parameter, and make a querry like:

SELECT [column 1], [column 2], [column3] ...

  FROM [table] [[filter]]

and filter would generated by external service and can be empty if all data needed, but can be also WHERE [column1]=31, or any other combination of WHERE.

Unfortunatelly, no matter if I keep filter value empty, or I do put there my WHERE conditions, I receive 

 "Unable to Invoke Service ... on ... : Execute Query failed. Please check Database logs for more details."
Have tried to call this service manually, have tried to call by other service, changed name of variable to be sure it's not forbidden word... no changes. 

Have tried with filter as both, String and Text type. 
Any body has idea how to cope with this issue, without generating extra querries for every possible case?

TWX version: ThingWorx 9.5.1-b359

[Velkumar · Accepted solution]

Hi @ZbigniewK 

Please use below query to pass condition as Input

select * from public."testtable" <<condition>>

Output 

/VR

[ZbigniewK]

So, the solution was to put my condition in << >> instead of [[ ]]?

Do I assume well, that we use [[ ]] when we want to put our variable as value which we want to compare with datas from SQL, and << >> when we want to add part of code/query? 

[Rocko]

Correct. see also articles https://www.ptc.com/en/support/article/CS289219 and https://www.ptc.com/en/support/article/CS261346

The box bracket notation can use a Prepared Statement, i.e. the syntax is fixed and you fill in values only.

The angle bracket notation allows for syntactically different statements, which is why one needs to be extra careful to avoid SQL injection.

[ZbigniewK]

I'm not very familiar with SQL injections avoiding, but I hope if WHERE conditions are based on date/time pickers and radio buttons, system is not vulnerable for that. Especially, there will be access control for mashup and services, so only few users will be able to use it But will have to remember about this risk in future, if using this solution again.  
Thanks for advices. 

[Rocko]

Access control is good, but keep also in mind mashups are not the only way to execute services, you could also use REST. This means instead of a date coming in from a date picker widget, it can always be any string, whatever one puts into the POST request.

Example: if your service accepts startdate and enddate. and you build the query "select id, name from mytable where timestamp> <<startdate>> and timestamp< <<enddate>>", someone could enter for enddate the value "GETDATE() UNION select name,password from user_table" and you end up spilling private data.

Therefore. in the service, validate the input parameters to only contain dates.

[ZbigniewK]

Do not know, if our servers have this function allowed, for sure nobody uses it, so maybe I should asked our administrator if it is blocked.

Still,
1. If I have startdate input declared as DateTime, and somebody will send string like you mentioned - will it call a function and will execute whole code, or will return error?

2. First, I've made condition = "WHERE startdate = " + startdate, but it was in long format, like Wednesday, July 22th... and I was afraid if my query will work with such date/time format. So, I've changed it to condition = "WHERE startdate = " + startdate.toISOString(), so maybe SQL injection prevention was not intentional, but I hope if any string will be send, it will stop service, because string variable will have no toISOString() method? Do I guess corretly, it will prevent SQL injection.

3. Still, thanksfully, I do not have any data in this database which will be valueable if stollen or cracked

[Rocko]

-The REST interface is always enabled, it can't be switched off.

-Passing in something else than Datetime does not necessarily raise an exception

-You could be ok with startdate.toISOString, but attackers are creative, better be safe than sorry and wrap your SQL service with a Javascript service where you do the type checking and filtering.

-Reminder you would potentially also reveal passwords, usage profiles, PII and data from other schemas in the same database.