Using Output from one SQL Query in a different SQL Query

How do I?
,
#1

I am trying to use the result of one SQL query as a variable in a SELECT statement in another query.

Below is what I have at the moment;

select {{custSitesOverviewTable.selectedRow.data.cust_loc_util}} from dev_types where dev_type = 'Firewall' and devs = 'Palo Alto Firewall';`

However, the data contained in custSitesOverviewTable.selectedRow.data.cust_loc_util is returned as "low" which does not work, the query just returns the word low. The reason for this is that the word low is between quote marks.

Is there a way to transform the data or parse the variable custSitesOverviewTable.selectedRow.data.cust_loc_util in order to remove the quote marks?

To be clear, the raw query that is run against the DB now is:

SELECT "low" from dev_types where dev_type = 'Firewall' and devs = 'Palo Alto Firewall';

What I need is:

SELECT low from dev_types where dev_type = 'Firewall' and devs = 'Palo Alto Firewall';
#2

Try reading this as it might help - using additonalScope is an easy way to pass in arguments to querys -

#3

Hi Scott,

Thanks for the reply. My JavaScript skills are pretty basic and I cannot see an example on that page of how to run a SQL query in a JavaScript, i.e. I cannot see a SELECT statement or similar so I am not sure how to construct this query.

Do you have any additional help you could provide?

Cheers,

Simon

#4

Check this out: Set additionalScope data to triggerQuery when called from a component

#5

Hey Scott,

Thanks for the additional reference that really helped.

I know have a JS query with this content.

getDevMPS.trigger({
  additionalScope: {siteUtil: custSitesOverviewTable.selectedRow.data.cust_loc_util},
  
  onSuccess: function(data){
    console.log('Sucessfully ran!')
  },
  
  onFailure: function(error){
    console.log('Something went wrong!')
  }
})

Which runs this SQL query.

SELECT {{siteUtil}} FROM dev_types where dev_type = 'Firewall' and devs = 'Palo Alto Firewall';

However, I get the same result as when I just pull in the variable value from the table directly. i.e. the value that I am passing into the SQL SELECT statement have double quote marks around it and so the SQL just returns that.

Any more thoughts?

Simon

#6

Try removing the quotes using replace() when you set siteUtil...

custSitesOverviewTable.selectedRow.data.cust_loc_util.replace('"','');

#7

Hi Scott,

Thanks again for the help.

Unfortunately, this didn't work.

I believe the issue is that when a text variable is stored, quote marks are added to it because it's text and that's the only way it can be stored. When the variable is called, no matter how it's been populated, because it's text the quote marks are also retrieved which is causing the issue in the SQL statement.

I could be wrong but that seems to be what's happening if I look at the SQL log.

#8

Hi @simonhuber! What type of sql query is this (postgres, myssql, etc)? Do you have disable prepared statements toggled on?

1292×244 17.1 KB

#9

Hi Tess,

The backend is MySQL and I have just tried checking the setting you suggested and it seems to have resolved the issue. Although it does now leave a gap from a security perspective.

Thanks.

#10

Hi @simonhuber

Yes, unfortunately, it does have a security gap. :disappointed: Disabling prepared statements can also break other existing queries. If that's something your use case requires, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

1 Like
#11

Great suggestion!

It did break other parts of my application having that setting configured everywhere so like you said I duplicated resource and am just using it for that specific query.

Thanks for your help.