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. When using QueryStrings, it is best to avoid situations where the user can manually type in the value that will be passed to the Name Query. Additionally, if you are using a QueryString for a string in the where clause, you would need to provide quotation marks.
Database type parameters cannot be created manually. Instead, it is automatically created when the Database Connection dropdown on the Authoring section is set to <Parameter>. Additionally, this parameter is not used in the body of the query. This type allows you to parameterize the database connection when the Named Query is called. This way the Named Query can run against multiple database connections specified by the resource that made the request.
Parameters while Authoring a Named Query
New parameters can be created in the Authoring section of a Named Query by clicking the add button () next to the Parameters table.
In Named Queries, Parameters are referenced by their name, so renaming the Parameter will require you to update it on any other resources that are using it.
Each parameter has three properties represented by different columns in the table. These may be edited by double-clicking on the cell you wish to modify:
- Type: Changes the type between Value and QueryString.
- Name: Determines the name of the parameter, and how it will appear in the query. Names are not case-sensitive and must be unique. Additionally, they may only use letters, numbers, dashes and underscores.
- Data Type: Specifies the datatype of the parameter. The Type of the parameter determines which data types are available. Note, that QueryStrings may only be configured as strings, where as Value-type parameters have more types available.
As of Ignition 7.9.9, parameters in a named query may now be configured with a byte array datatype, which can in turn be used to pass files into a named query.
Using Parameters in the Query
Once created, parameters can be inserted into the Query field by drag-and-drop from the Parameter table onto the Query field, or by using the right-click menu in the Query field.
Additionally, the parameters may be typed in manually, but the correct syntax must be used.