"QueryStrings are more flexible than the Value type in that they can be used to parameterize column and table names. However, their values are never sanitized, which causes them to be more susceptible to SQL injection attacks. "
So I want to ask the silly question, why aren’t these sanitized within Ignition?
Second silly question, supposing there is a good reason not to sanitize those in some cases, is there a possibility to make a parameter type that can be columns/tables names and yet are sanitized within Ignition?
It would be very useful to me to have the ability to switch between tables or columns dynamically without worrying if I have sanitized correctly.
You’ll have to worry about it. It isn’t implemented because there are so many possible ways to want to sanitize that one tool just isn’t going to cut it.
Sanitizing valued parameters into named queries has a direct way of doing it - dates need to be enclosed in quotes, text will need to have special characters escaped, integers can just be put there without quotes etc.
How would Ignition sanitize say column names or your table names? Ignition only connects your gateway to the database, but it itself does not create a model of your database internally to know which tables and columns are valid. So checking existence of columns or tables wouldn’t work. As for column names, you might have the SQL Server style with [brackets] or you might not.
I think you’ll want multiple named queries. Otherwise you’re going to be copying and pasting a bunch of column names from one window to the next when you want the datasource to be the same. Better one unchanging source of truth for datasources.
If this is data that is going into a table view, select all the columns you could need and just hide the ones you don’t need at that given time.
To expand a bit on what Phil said, Value parameters allow us to internally use prepared statements, which are a common database feature that helps protect from SQL injection. But prepared statements have limitations – for example, you can’t use them for dynamic column or database (schema) names. The limitations we placed on Value parameters are the limitations inherent in prepared statements.
But we know some users need more flexibility than that, so we also give you the more dangerous query strings. In order to give you the maximum flexibility, we really can’t do any sanitization of your user’s input. You’re the one who knows best what you’re trying to achieve, whether you’re using anything that you don’t have control over (i.e. any string that can in any way originate from a user), and how much risk tolerance you have. Unfortunately that places the burden of security on you.
One way to dynamically switch between tables or columns with a little more safety is to not allow users to type anything in – either your script has 100% control over the possibilities (e.g. an if statement with hardcoded parameter strings) or your user can only select values from a dropdown or radio button group that you control, or something similar. Just remember to think through the whole chain of how you get those values to make sure they don’t originate with a user further up the line.
I imagined a built-in parameter type that specifically sanitized to the table name or a type for matching specifically to a column name.
I wondered if this was possible, but not practical.
Possible and practical
Or outright not possible
Hmmm. Imagine another parameter type, “identifier”, perhaps. Could be sanitized against information_schema, and the DB flavor’s quote style automatically applied, including for multi-part identifiers. That sounds worthy of an entry on the ideas portal.
To expand on some excellent info:
We actually do have a ticket on the backlog to add some extra logic to query string parameters, at the named query level; something like a regex filter or whitelist of possible params. It wouldn’t be dynamic on table names, but would be a kind of in-between measure between no querystrings and ‘totally-wide-open’ querystrings.