Parameterized Queries

Preventing the SQL Injection

SQL injection is one of the major security threats when running queries against a database. Best protection against the SQL injection is to pass all your constant values as parameters.

There are at least three ways to do this:

• do-it-yourself solution – pack yourself all your data into custom parameters, with @paramXXX names, and populate a popup before query’s execution.
• automatically let the framework generate your SQL with internal query parameters for all user input. When you switch to SQL mode, you’ll see your query using @paramXXX names where user input was expected, and pairs of @paramXXX = value at the end. A query like SELECT ‘abc’ becomes SELECT @param123, with @param123 = ‘abc’.
• automatically let the framework always transparently execute your SQL with internal query parameters for all user input. To have a what you see is what you get, it’s recommended to turn the second option on as well when you use this setting.

Custom and Internal Query Parameters

User-defined query parameters – in the form unquotted @ followed by something, like in @parameter_name – are custom parameters, with the default value of NULL. You can execute the query with these default parameter values, or open a popup and customize them. The query where they are defined becomes a custom parameterized query.

The query below shows all departments with number greater then a value you’ll pass as @myDept, or with a name identical with a name you’ll pass as @myDeptName. By default both parameters are Null, so the result will be empty:

The query is also parameterized if it uses a nested parameterized query. Outer queries collect parameters from all their nested subqueries. Parameters with the same name are considered the same: if a subquery has a @param123 parameter, and the outer query is using a @param123 parameter, giving a value to this parameter will fill it in for both queries.

But the framework has two options to eventually transform ALL constant values into parameters – in the generated SQL query and/or when executing the query – and they become internal parameters. The query may become parameterized, but is not custom (if no custom parameter was defined).

You may choose to show ALL constant values as query parameters in the generated SQL, but to continue execute the query without these transformations. This might be practical when you need to see your queries protected to the SQL injection. The opposite option would be to see the queries as usual, but to transparently make them SQL injection proof before execution. These options may be confusing, use with care. Because you do not actually execute your queries how you display them, and when something fails it is hard to properly locate what.

Parameterized Query Instances

There are cases where the framework transparently saves for you (permanently or temporarily) a specific set of parameter values for your parameterized queries. For one single parameterized query there may  be many sets of associated parameter values. These are called instances, and are listed below, with the kind of related queries.

Object Queries/Instances (Bookmarks)

We internally generate a parameterized query per table, as an object query (in Admin mode, you can see these hidden queries in the Object Queries folder), when we’re required to bookmark objects, as persisted row references. Generic parameters, in this case, are on all the primary key values.

Bookmarks (i.e. individual table records or rows, as object instances) are saved under the Bookmarks folder and they may be later used for fast drill-down or to generate a non-parameterized query returning that row. Whenever a result row contains all PK values of a table, you can double click on the row header to bookmark that specific row in the Bookmarks folder. To later use the bookmark as a subquery, save it as a query first.

Turn Admin mode on and double-click on the dept table. Its data will appear in Results mode. Select second row (with DEPTNO = 20) and double-click on its row-header. A “dept(20)” visible and persistent bookmark (under the Bookmarks folder) for this row will be created (if not already there) and opened on screen for you. Its object query should be the “dept(@deptno)” hidden query under the Queries/Object Queries folder:

Relationship Queries/Instances

Each time you drill-down on a relationship, we transparently create and persist on the fly (if not already there) a hidden relationship query for that specific join (that you can see in Admin mode under the Relationship Queries folder), with a specific volatile relationship instance to that row values (that you can see in Admin mode under the Relationship Instances folder). Generic parameters, in this case, are on the keys defining the relationship.

Turn Admin mode on and double-click on the dept table. Its data will appear in Results mode. Click on the + sign to drill-down on the second row (with DEPTNO = 20). Check the “dept<<emp>>(@deptno)” hidden relationship query under Queries/Relationship Queries folder, and the related “dept<<emp>>(20)” relationship instance under the Bookmarks/Relationship Instances folder (the instance is volatile, it will not be saved and it will not be there next time you restart the application):

Group Queries/Instances

Each time you expose a drill-down All Aggregates tab of a group query, we transparently create and persist on the fly (if not already there) a hidden group query for that specific join (that you can see in Admin mode under the Group Queries folder), with a specific volatile group instance to that row values (that you can see in Admin mode under the Group Instances folder). Generic parameters are on the GROUP BY fields.

Create a new group query as below. Click on the + sign to drill-down on the second row (with DEPTNO = 20). Let’s see what All Aggregates created here. Look for a “dept…(@deptno, @dname)” hidden group query under Queries/Group Queries folder, and the related “dept…(20, RESEARCH)” group instance under the Bookmarks/Group Instances folder (the instance is volatile, it will not be saved and it will not be there next time you restart the application):