Learning about dynamic query implementations

[zacharyw.larson]

There are a bunch of posts on dynamic queries.
I made this post to help myself understand and keep this straight some.

-I bound a dropdown to a Querystring paramater to switch which db table my perspective table object displayed.

-then I decided that I should find out how to make dynamic queries for machines

---

I gathered that I needed to set my parameter type to Querystring to make the table selection dynamic, which Querystring type is susceptible to injection attack, so I bound the perspective table object data to a dropdown prop value.

I right click to pick to put the parameter into my query after "From", choosing insert parameter. This was in the named query authoring.

I made a table and a dropdown on a perspective page.

I made two options on the dropdown.
Each value is the name of a table.
Each label is the short name of the machine.

In the table, I set the named query binding for the data.
In the parameter I click the function symbol, then pick the prop value of the drop down.

It worked. Switching between the two options in the dropdown changed my table between the tables for the two different machines.

---

now it seems natural to want to make the queries (columns, table, when, group by, and order by) into more dynamic sanitized selectable values

I see so many posts about this though.
(queries are so useful, and injection attacks are so bad)

I don't have much experience with :
system.db.runPrepUpdate()
or
system.dataset.toPyDataSet()

If I understand, Prepupdate can be used to sanitize inputs for queries in script. However, the table and columns would not be dynamic because the question mark place holder is excluded in SQL standard.

toPyDataSet is for getting datasets from tables to manipulate in scripts mostly.

Querystring parameters in the named queries can modify the columns and table name.
Using querystring parameters is the main way to develop dynamic queries I think.

---

Right now, I have my test page with a table that uses a dropdown to change between which machine it is getting the data from.

If I want to switch which data is being gathered, I need to make the columns into querystrings, and make some kind of picker or dropdown for this I think.

What is recommended for this?
Going to research Perspective tree
I think I could make radial select buttons next to text work

I am not sure.
The number of columns someone will want would be dynamic.
I don't know how to setup dynamic bindings though.

still trying to figure it out. I found a couple posts that looked similar.
Not sure though.

Dynamic binding tags in table

You may want to read all the tag values at once. [code]tagPathsToRead = [] #get values from dataset for row in range(query_ds.rowCount): id=query_ds.getValueAt(row,“id”) tagname=query_ds.getValueAt(row,“TagName”) description=query_ds.getValueAt(row,“Description”) eu=query_ds.getValueAt(row,“EU”) type=query_ds.getValueAt(row,“Type”) Add the tagpath to the list tagPathsToRead.append(tagname) #create new row newRow=[id,tagname,description,eu,type,None] #add this new row data=system.dat…

Insert Query with Dynamic Table Name,Colums and parameters

Hello, I am trying to write a dynamic SQL Insert Query in that all values are dynamic like This: There will be any number of columns that is not fixed. allCols=‘Company,Email,First_Name’ ColValues=‘MuleSoft,maxthemule@mulesoft.com,Max’ Table=‘Mapping_Info’ DB=‘Ignition_Integration’ insertQuery=‘insert into ‘+str(Table)+’ (’+str(allCols)+’) values (?,?,?)’ print ‘insertQuery’,insertQuery value=system.db.runPrepUpdate(insertQuery,[ColValues],str(DB)) But it is throwing exception.Is …

[pturmel]

@zacharyw.larson

If I understand, Prepupdate can be used to sanitize inputs for queries in script. However, the table and columns would not be dynamic because the question mark place holder is excluded in SQL standard.

You're on the right track, but it has nothing to do with the standard for SQL. It is simply how JDBC and ODBC and other database APIs work. These APIs distinguish between query structure (schema/table/column names, keywords, function names, etc) and query data (comparison values, values to insert, etc). Old school SQL construction builds the entire operation as a string, including string constants. The latter need special escaping for non-printable characters and quote characters and characters that need encoding. If you screw up the escaping with user supplied strings, boom--SQL injection vulnerability. Or if you fail to do escaping at all. ):

JDBC and similar APIs avoid the problem by delivering query data separately from query structure, and using efficient native binary formats for that data (no conversion of dates to/from strings, etc).

So, to do the dynamic structure you want, you have to apply some kind of cross-checking on anything you allow. The simplest solution is to verify that any user choice is a valid table name, column name, or schema name (as appropriate). You can do this by querying the information_schema of your DB, where such names are data. If valid, you can then assemble your dynamic SQL with them, with proper structure quoting. (SQL standard is double quotes for structure names, single quotes for data values.) For speed, you might want to just make a list of valid names as a python constant in a project script and check against that.

If you are extra paranoid, you can use lookups everywhere. The user sees dropdowns with numbered choices, which you turn back into names in a private lookup in the gateway.

Personally, I avoid creating any Named Queries with QueryString parameters. Such parameters defeat the anti-SQL-injection purpose of NQs, since they can be run by any client. Where I need dynamic SQL, I do it all with runPrepQuery in a project script, via a gateway message handler for Vision Clients. (Legacy DB access stays OFF.) Only in this way can you verify the dynamic parts of your SQL structure.

[nminchin]

@zacharyw.larson

I understand, Prepupdate can be used to sanitize inputs for queries in script. However, the table and columns would not be dynamic because the question mark place holder is excluded in SQL standard.

As Phil said, but just to clarify this. To make your query tables etc dynamic using runPrepQuery, you would pre-process the query string passed into this function by inserting your dynanic values into the query string e.g. query = 'SELECT * FROM {} where x = ? '.format('sanitisedTableName')

[zacharyw.larson]

I thought if I used a querystring tied to a dropdown list, that was safe because the dropdown list would protect entered values.

Is this not the case?

[bkarabinchak.psi]

The NamedQuery knows nothing about the dropdown list, just that it’s expecting SOME query string to put into your query. Ignition/the namedQuery does not know that it should only accept one of the X different dropdowns items in your component.

Think of it more as you are calling the NamedQuery, and the dropdown list is just one of many different ways you could create the QueryString.

[pturmel]

@zacharyw.larson

Is this not the case?

No. It would protect from a typical user. It does not protect from a malicious hacker faking a client connection, which is the whole point.

[zacharyw.larson]

Oh I see,

they open the page source, change the text there, and send

well that is terrible

[zacharyw.larson]

regular parameters are safe to use?
value type

[victordcq]

Value will be used as value yes.
A querystring could be turned into sql injection.

However data from the client first goes through the gateway, so if you catch the input there and validate it being a legit (though a whitelist /query/tag only from the gateway) you could use a querystring for flexibilty.
You will have to remeber to always filter it though, which is a risky to trust, especially if you work with others.

[pturmel]

@victordcq

You will have to remeber to always filter it though, which is a risky to trust, especially if you work with others.

That is why I don't ever define a Named Query with a QueryString parameter. That leaves the project script via message handler as the only path. People can be taught that turning on legacy DB access means you've screwed up. Then they ask how to fix it.

[victordcq]

Yeah i dont use it either.
We arent really using any queries here actually, we created an orm which makes everything easier xd

[zacharyw.larson]

What do you do for perspective pages?

I thought you had mentioned the message handler for vision.

I think I am looking at using a ton of named queries with only some value type parameters to reduce the number of named queries used.

[pturmel]

@zacharyw.larson

What do you do for perspective pages?

Perspective pages can call the project library functions directly, since those scripts run in the gateway already. You still need to validate anything you would use as query structure. You can't with Named Queries' QueryStrings.