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.

[MA8731174]

Question: Clarification on [[ ]] and String Handling in SQL Queries for Database Thing

I came across this note in the documentation:

"It is not a good practice to use a string in '--[[ ]]' and '[[ ]]' in a SQL query for a Database Thing. Instead, if you want to use a string in the SQL query service, insert that string in the Input variable and then pass that value in the SQL."

I understand that [[ ]] is generally used for binding input parameters to prevent SQL injection, but this note specifically mentions not using it for strings.

My Questions:

1. Does this mean we should never use [[ ]] for string parameters, or is it only a problem when manually formatting strings inside the SQL query?

2. If we have a condition like FREETEXT(serialNumber, [[searchInput]]), should we instead pass searchInput normally as an input variable and bind it using ? instead?

3. Does this also apply to << >> when inserting dynamic parts of SQL (e.g., table names), or is this warning specifically about [[ ]]?

Would appreciate any insights from experienced you guys!

[Rocko]

That comment is unclear and I have asked Doc team for improvement. I suppose is just addresses a special case as https://www.ptc.com/en/support/article/CS413368 clearly states otherwise.

[MA8731174]

Thank you! Otherwise the topic is well explained and i understood.

I have one question. I dont know if you can help me or have any suggestion for me because its not direclty related to Thingworx. I have change my whole project from thingworx to SQL server and now i have 150K entries in the datatable which i want to export to SQL server. I am convined that i can write a file with values and with one insert statement but there comes an error that  

SQL Server returns error stating that the number of row value expressions in the INSERT statement exceeds the maximum allowed limit of 1000. This limitation is preventing me from executing bulk inserts efficiently

Do you have any suggestion for me how can i efficiently do it. 

[Rocko]

This is a limitation of SQL server https://stackoverflow.com/questions/37471803/sql-server-maximum-rows-that-can-be-inserted-in-a-single-insert-statment

but it's a bad idea anyway, since the rollback segment would grow too large doing all rows in the same transaction.

You will have to iterate over all rows in sets of 1000er batches.

Or you do it directly in the DB running an insert-as-select.

[MA8731174]

you are absolutely right and yes its a limitation from SQL server so would that be ok if i write one file in thingworx and prepare it like that

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3),...... upto 1000 rows. 

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3),...... upto 1000 rows. 

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3),...... upto 1000 rows. 

and so on...

but for this i need to iterate on all 150K rows in thingworx and also write INSERT INTO TableName after every 1000 rows and then i will copy paste the file content into SQL server and run this query so that it can insert all the data.? 

would that be okay?

I am also thinking to first insert these all 150K rows in temporary table in SQL server and checks whether everything is fine and data is fetching right from it and then just put this data from this table into Production TABLE in SQL Server... 

Please correct me if you have better suggestion..

[Rocko]

Sounds fine to me