Dynamic Where

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

For example, it is possible to 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 in the case when each call required a different set of data. The advantage of Dynamic Where lies in its flexibility without sacrificing security. It allows to dynamically compose any WHERE clause when needed.

The main type of expressions which form WHERE clauses are binary expressions. Every WHERE clause has at least one binary expression. Binary expressions can contain other binary expressions combined using logical operation such as AND, OR, XOR. The simplest binary expression contains value expressions and operations between them.

The value expression can be of the following type:

Dynamic Where supports following functional expressions that are represented by SQL function calls in the resulting SQL statement:

Other available expression types are:

  • Between expression. This expression represents a check that provided value checks belongs to the provided values range.
  • List expression. This expression represents contains a list of constants.
  • Macro expression. This expression represents a call to a macro function, which will be processed by Macro Processor.
  • Unary expression. This expression represents expressions, based in the application of a unary operator (like the change of sign or logical negation) to the inner expression.

The following operations can be applied to expressions:

  • Comparison (less, less or equal, equal, greater or equal, greater, not equal).
  • Arithmetic operations (addition, subtraction, multiplication, division).
  • Pattern-based string comparison AKA LIKE operator.
  • Occurrence check (in) for list expressions.

Note: 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 based on client application request, security can be 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.

Dynamic Where expressions are sent to the server as a structured XML representation. As a result the Data Abstract server can validate such requests. For example it is not possible to refer to a field or a table that are not exposed in the server Schema. All constants are passed to the database engine as query parameters thus preventing making the the SQL injection attacks not possible.

How it works

Dynamic Where can be passed to the request via the TableRequestInfoV5, which has a 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 target Schema Data Table. Assuming that the received Dynamic Where expression is safe, Data Abstract reconstructs the WHERE clause and adds it to the table's SELECT statement.

Note: Data Abstract requires a {WHERE} macro to be present in non-AutoSQL table statements. If SQL statement does not have this macros then on attempt of applying Dynamic Where the server application will raise an exception stating that "DataTable does not support Dynamic Where".