executing a sql query constructed dynamically fails

[rkandasamy-2]

Hi Pai,

I have written a thingworx service as follows,

GetDetails:

String = "select * from sample;";

var params = {

  fullQueryString: String /* STRING */

};

// result: INFOTABLE dataShape: "undefined"

var result = me.SQLQuery(params);

I have written an SQLQuery   (SQL)as follows,

[[fullQueryString]]

But when i execute the root javascript service "GetDetails" i am getting the error as follows,

Error executing service

Wrapped org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 1 Cause: ERROR: syntax error at or near "$1" Position: 1

Please let me know if  i am missing anything.

[supandey]

Radhakrishnan, not sure if it'll help but did you test without the ";" within the double quotation mark?

[rkandasamy-2]

Hi Sushant,

I have tried with out semicolon as well but still it is not working. To keep it simple, i directly tried to run the query service alone where just [[fullQueryString]] is given. I have just passed both 'select * from sample' and select * from sample;'. But still both the ways are not working.

In the query service, if i give directly as 'select * from sample'   (without quotes)  it works.

If possible,Please try once from you end and confirm if you are execute with just [[fullQueryString]] param.

[supandey · Accepted solution]

Does the error remains the same? Did you also test with <<query >> instead of [[query]] ? I think in the past i have tested the substitution with <<>> which worked. Which was also discussed in the old thread Re: How can I execute a sql query constructed dynamically? where you posted this question previously.

If you'll check Pai's response in that thread he highlighted the fact that << >> means String substitution contrary to [[ ]] which also leads to next important point that please do ensure that there is proper validation to prevent against the SQL injection (do refer to the response from Pai in that thread for more)

Hope this helps.

[rkandasamy-2]

Thanks for the prompt reply Sushant. I am able to dynamically pass the query and execute the same by using the query as <<>>.

Regarding SQL injection, the control is at the service level and not at the query level. So Apart from using System User to restrict the SQL injection at service level, if there are any other methods to control at the query level or any other steps to be taken care, Please update here.