addiks/stored-sql

Stored SQL

WARNING: Unfinished, Work in progress!

This library provides a very flexible and dynamic toolset to analyse and manipulate SQL statements and SQL-segments. In contrast with other SQL libraries, this can deal with snippets of SQL. A snippet like WHERE foo.bar = "Lorem" is a perfectly fine and valid piece of SQL. It may not be runnable on a server itself (and this library knows this), but it can be worked with even if the rest of the query is unknown and / or missing.

The main purpose of this is to be able to store SQL parts in the database and use them as very flexible, user-definable rules that can easily be validated and merged into a real SQL query that then runs on the server.

For example, if you have a warehousing system and want to determine which warehouse should fulfill a delivery, and have this rule user-definable and changable at any time, you could store the rules in the database like this:

ID	warehouse_name	delivery_rule	priority
jkl	QA (Handles 3%)	WHERE RANDOM() % 30 = 1	1000
ghi	Nightshift	WHERE TIME() NOT BETWEEN "06:00" AND "20:00"	400
def	Warehouse for USA	LEFT JOIN deliveries d ON(w.id = d.warehouse) WHERE d.countryCode = "USA"	200
abc	Fallback Warehouse	WHERE 1	100

As you can see, we have stored very complex rules in the database without the need for any additional tables and in a way that can make use of the complete SQL capabilities.

The security question

So, to answer the obvious next question: Why is this not a huge security risk? What if someone enters a "rule" like DROP TABLE orders? Would that not delete the whole orders table?

The answer to that is: No, it would simply produce an error.

Every SQL snippet is tokenized and parsed into an AST tree, you only need to look at the root nodes to understand what the query is. A snippet like LEFT JOIN foo f ON(f.a=b.id) WHERE f.bar=e.baz would produce just two root nodes: A SqlAstJoin node and a SqlAstWhere node, so you can control very easily what types of SQL-snippets are allowed and which should be rejected.

Additionally to that , the parser is build in a modular way so that you can limit the SQL that it understands. You know that at a certain point you expect only ORDER BY snippets with a very simple condition consisting of only simple operations and a few functions like COUNT or SUM, then only include these modules in the parser. If someone tries to be sneaky and submits something like ORDER BY EVAL("DROP TABLE orders"), the parser will reject the unexpected call to the "EVAL" function.

This also automatically excludes any type of SQL specialities that might be a loophole, because these specialities are not included in this SQL library (unless you code that in yourself).

In short: This library gives YOU control over what SQL is allowed and what is not allowed. You define a white-list, and anything not on that whitelist is rejected. (Similar to an HTML-filter in a forum)

The client side

This library has two sides: A server side in PHP, and a client side written in Typescript. Both parts contain the same dynamic SQL parser, but the client side also contains some UI code to provide the user with an easy-to-use widget to modify the SQL parts. After all, not everyone understands SQL.

Both sides (client and server) are testet against the same set of fixtures to ensure that both parsers are compatible.

Warning: the client side is far from finished and needs much work.