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:

• Constant expression. This expression represents a constant value, which can be any data.
• Field expression. This expression represents a reference to a Schema Data Table field.
• Parameter expression. This expression represents request parameters;
• Null expression. This expression represents database NULL value.

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

• Function call expression. This expression represents a server-side call to a certain SQL function.
• Date Part. This expression represents a call to the SQL function that extracts a part of given date like year, month etc.
• Current server date and time. This expression represents current database server date and time.
• Current server date. This expression represents current database server date.

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.

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.