Substitution variables

Since it is useful to run DQL queries that vary only by the values in any given term, it is tempting to build them using string construction. But building DQL queries using string construction can be a security weakness. DQL supports substitution variables in its syntax so a query can remain unchanged but differing values can be supplied.

A Java™ example of queries built using string construction is the following, where order_num and order_origination are fields with values supplied from outside the code itself.

String query = "order_no = " + order_num + " and order_origin = '" + order_origination + "'";

When this technique is used to build queries, a security problem is introduced. Users or external malware can modify query syntax by inputting legal DQL that changes the results of the query to find many more documents, perhaps all documents in the database. For example, if order_num contained the following value, that part of the query would find every document in the database. This is the infamous SQL Injection exposure.

"32890 or order_origin <= ''"

To eliminate this weakness, DQL supports substitution variables in its syntax. Substitution variables allow a query to remain unchanged while supplying differing values that produce the variety of results desired. There are two types of substitution variables, positional and named.

Positional substitution variables supply question marks in the query. In the following example, values are supplied via 1-based assignment, where the order_no value is variable 1 and order_origin is variable 2.

String query = "order_no = ?  and order_origin = ?";

Named substitution variables supply question marks followed by alphanumeric and special character text names of 1-15 bytes that comprise the name. Values are supplied using the text following the question mark only. Here is the same query with named substitution variables:

String query = "order_no = ?order_num  and order_origin = ?order_origin";

Domino® Java™ and LotusScript® classes support named substitution variables only.