Dynamic Where
From RemObjects Software
This is a Data Abstract Feature topic
Feel free to add your notes to this topic below.
Dynamic Where Overview
Dynamic Where is the a feature introduced by the 'Vinci' release. It allows you to dynamically change the WHERE statement of any SQL statement that goes to the database server.
For example, we would like to restrict the data fetched from the table to that 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 is in its flexibility but without sacrificing security (as discussed in the next section). We can dynamically compose any WHERE clause any time we need.
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 so 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 help of this property you can allow or forbid using 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. For tables based on AutoSQL statements, this macro is added automatically.
See Also
Product: RemObjects Data Abstract
Current version: Data Abstract 'Vinci' (5.0)
Lists — Glossary — Features — How To — Drivers — Components — Tools — Samples — Articles — Architecture — Issues
