Skip to main content
rkandasamy-21-Visitor
1-Visitor
May 29, 2017
Solved

executing a sql query constructed dynamically fails

  • May 29, 2017
  • 4 replies
  • 3710 views

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.

Best answer by supandey

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.

4 replies

supandey5-Regular Member
5-Regular Member
May 29, 2017

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

rkandasamy-21-VisitorAuthor
1-Visitor
May 30, 2017

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.

supandey5-Regular MemberAnswer
5-Regular Member
May 30, 2017

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.

    Terms & ConditionsAccessibility statement