How can I execute a sql query constructed dynamically?

[varansunil]

Hi Team,

I am constructing a SQL query dynamically with varying number of columns.

After I construct this SQL query, How can I execute this to return an infotable?

Can you please share some sample code related to this.

Thanks & Regards,

Sunil

[PaiChung]

obviously this is very dangerous, but often necessary. so make sure you guard against sql injection

but you can setup an sql query that executes a string input. create another service that builds the sql statement and then call and feed that into the other service.

make sure to secure the service that actually executes the sql statement with the system user.

[qn]

I'm trying to do the same thing. I'd like to know how would you setup an sql query that executes a string input (which is the sql statement and is build dynamically).

Thanks

[jlebourhis1]

Easy :

- create an sql service taking a part of your query or all of your query (if you want something generic [but dangerous as Pai said], put a string as parameter that you would name query and just put  <<query>> in your script).

- create a javascript service which will build the sql query as a string

- call the first service you created in the second one by putting the generated query string as parameter

[qngo]

Thanks, I could not find the syntax <<query>> anywhere.

[jlebourhis1]

Hard to find indeed

[PaiChung]

This should be in the Thingworx fundamentals materials.

It also should come with an explicit warning.

<< >> means String substitution (vs. [[ ]]) so

1. make sure that service can only be run under the System User

2. make sure your services that invoke this service (wrapping services) have enough validation to prevent SQL Injection

[qngo]

Hi,

1. I'd like to know what do you mean by "only be run under the System User" ? Here's my use-case

- An external (PostgreSQL) database stocks only data

- From ThingWorx, using a Database Thing to connect to the external database

- In a mashup, using the widget Data Filter to show the data from the external database

That's why I need to build dynamically the SQL query. And this service will be executed by an user.

2. So the dynamic query is constructed based on different values retrieve from different widget in a mashup. The values will be selected in different lists / checkboxes. The textbox widget will be avoided to prevent the SQL injection. Maybe the risk would come from the HTTP request using the REST API, but this option is not opened in my ThingWorx configuration.

I wonder if there are other security risks that I must take in consideration.

[PaiChung]

IMPORTANT: Every Service can be executed through the REST API, that is what you are securing against, not just what people can type in a Mashup!

System user is highlighted in the security section of the Thingworx Fundamentals course. It is a security setting, that allows a service to be invoked by other services but never to be invoked directly.

Read more here: http://support.ptc.com/cs/help/thingworx_hc/thingworx_7.0_hc/ThingWorxHelpCenterDITAFiles/Security/SystemUser.html?query…