Dynamic Where

Dynamic Where allows you to dynamically change the WHERE statement of any SQL statement that goes to the database server.

For example, you now can restrict the data fetched from the table to one meeting a certain condition, such as:

 (CustomerID = 'ALFKI' or CompanyName like 'A%')

Previously, this could only be achieved by hard-coding the parameters in the SQL statement. This was a problem if each call required a different set of data. The advantage of Dynamic Where lies in its flexibility without sacrificing security (as discussed in the next section). You can dynamically compose any WHERE clause any time you need to.

The main type of expressions which form WHERE clauses are binary expressions, BinaryExpression. Every WHERE clause has at least one binary expression. Binary expressions can consists of another binary expression by logical operation (AND, OR, XOR). The simplest binary expression is formed of atomic expressions and operations between them.

The atomic expression can be of the following type:

Other types that are supported by Data Abstract.

  • Null expression. This expression is used for null values in expressions.
  • List expression. This expression contains a list of constants.
  • Macro expression. This expression represents a call to a macro function, which will be processed by Macro Processor.
  • Parameter expression. This expression is used for setting the parameters (variables);
  • Unary expression. This expression represents expressions, based in the application of a unary operator (like the change of sign or logical negation) to another, inner expression;

Operations between atomic expressions can be:

  • comparison (less, less or equal, equal, greater or equal, greater, not equal).
  • likewise (like) for string types.
  • arithmetic (addition, substraction, multiplication, division).
  • occurrences (in) for list expressions.

The value of the expression can be changed by unary operators (minus, not).

Data Abstract for Cocoa does not have analogues of the expressions described above. Instead it can translate various NSPredicate expressions into Dynamic Where XML which can be used in the GetData request. You can find more details in the article Filtering Data with NSPredicate

Security

Since the table's SQL query is being dynamically changed, security is a concern. Data arriving at the server needs to be well structured in order to allow the server to check/validate the Dynamic Where clause and prevent any attempt of SQL injection or other unauthorized SQL access techniques that are possible if you transmit the WHERE clause as plain text (as we did, for example, in DA4/Delphi). That is why we decided to use XML for the Dynamic Where transmission.

When the data is fully structured, the DA Server can validate it. As a result of such validation, you cannot refer to a field that doesn't exist in the table definition and any constants will be passed as parameters with their types and sizes specified.

Also, its worth mentioning that the server side service has got a new Boolean property called AllowDynamicWhere, that, by default, is set to False. With the help of this property you can allow or forbid using the Dynamic Where feature.

How it works

Dynamic Where can be passed to the request via the TableRequestInfoV5, which has a new WhereClause field that can hold the Dynamic Where value as an XmlNode.

When the server processes the request, it retrieves the Dynamic Where and validates it according to the table's schema. Assuming that the Dynamic Where is safe, DA builds the WHERE clause and adds it to the table's SELECT statement.

Note: when you apply Dynamic Where on a table based on a non-AutoSQL statement, you need to add a {WHERE} macro to the statement. This macro will be replaced with the Dynamic Where string by the Macro Processor. If SQL statement does not have this macros then on attempt of applying Dynamic Where, server will raise an exception "DataTable does not support Dynamic Where".

For tables based on AutoSQL statements, this macro is added automatically.