Dynamic filter expressions for SQL

[paullatzelsperger]

Problem statement

Many endpoints of the Data Management API accept a QuerySpec object, through which clients can supply a filterExpression. In all current SQL implementations, those filterExpression fields are not used, we only take the limit and offset fields.

This is a severe limitation to the flexibility of those SQL stores, because it requires to create a dedicated method for every single use case. This stings even more when remembering that CosmosDB- and in-memory implementations do have those capabilities already.

Having flexible filter expressions can be used to create advanced queries, that (in the case of SQL) would then be translated into an SQL SELECT ... WHERE clause.

Problems, risks

During the technical design of this feature, we should be aware of the following potential pitfalls.

Exposes the schema

In SQL implementations the schema of the database will likely differ from the actual object model with regards to field/column names and table schema. Without any translation layer, this would mean that clients accessing the API would have to have intimate knowledge about the internal SQL schema.
Another problem along those lines is, that without any translation layer the entire construct will not be resilient against refactoring the DB schema. All clients would have to update their requests when the schema changes.

Potential security problems

Having clients directly provide SQL statements (or parts thereof) could be a dangerous security hole with regards to SQL injection or overwhelming the database with intentionally inefficient statements.

Solution proposal

In order to counter the aforementioned potential downfalls, there are a couple of mitigations we can employ.

Use a caching layer

This would essentially mean that all query operations of the SQL stores hit the cache, which is in essence an in-memory list, rather than the database directly. For that, we could reuse the existing implementation (ReflectionBasedQueryResolver). Since that uses Predicates, we could leverage a tried-and-true way to express filters.
The performance and suitability of caching strategies will largely depend on the expected amount and rate of change of persistent items.
Also, in clustered environments the cache would either have to be

• distributed between replicas
• synchronized through some protocol between replicas
• able to cope with out-of-date situations, which would probably work best in low-change scenarios

There is issue #1173 that should serve as implementation platform for the caching topic.

Implement a translation layer

Another way to counter the problems described earlier is to implement a translation/transformation layer, that converts the left-hand operand of filterExpressions into actual SQL table/column names before composing the statement itself.
For simple queries that, we could re-use the SqlConditionExpression to compose parameterized WHERE/AND clauses. Currently only "in", "like" and "=" operators are supported, but that could be expanded.

Issue #323 could contribute to this.

Conclusion

• Having a safe, well-performing and stable query API is not trivial.
• Using a cache will (by design) eliminate all of the problems stated here and is expected to be faster, but requires engineering a well-designed and reusable cache, and that will depend largely on the use-case. It would also put a hard dependency on the usage of the cache.
• Using a translation layer will mitigate some of the security risk, but close attention must be paid to prevent SQLi attacks. Clustered scenarios are a quick-win, but more stress will be put on the database.

[ndr-brt]

Nice write-up, personally I would go through the hard path: the translation layer won't be the easiest thing but at the end we'll have something that would also improve the current abstraction.

Implementing a caching system to solve a modeling issue doesn't sound like the best thing to do, as other issues will be introduced (as you already mentioned).