cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

How can I execute a sql query constructed dynamically?

Highlighted
Newbie

How can I execute a sql query constructed dynamically?

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

8 REPLIES 8
Highlighted

Re: How can I execute a sql query constructed dynamically?

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.

Highlighted

Re: How can I execute a sql query constructed dynamically?

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

Highlighted

Re: How can I execute a sql query constructed dynamically?

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

Highlighted

Re: How can I execute a sql query constructed dynamically?

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

Highlighted

Re: How can I execute a sql query constructed dynamically?

Hard to find indeed

Highlighted

Re: How can I execute a sql query constructed dynamically?

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

Highlighted

Re: How can I execute a sql query constructed dynamically?

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.

Highlighted

Re: How can I execute a sql query constructed dynamically?

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/S...

Announcements

Thingworx Navigate content has a new home! Click here to access the new Thingworx Navigate forum!