Execute formulas stored in database fields with almost standard SQL

It is somewhat tricky to build such an infrastructure and differs from "traditional" backend software, but it is what it is with Databases that heavily rely on state. The benefit is that you save a lot of bandwidth when you process the data on the machine where they live, and the architecture of the IT landscape is way simpler.

So I get what you're saying, but at the same time that means your business data can't ever include formulas.

I've worked in areas where the formulas were business data (i.e. modeling). You could hard-code all your models/correlations/aggregation functions. But then it becomes much harder to make UIs that do things like "find all formulas that involve this thermometer reading" or allow a technician to make temporary edits without risking them screwing up your entire application.

Was also one of the most common vectors for vulnerability chaining.

But also, this is the same argument people generally give against using DB stored procedures. And, despite using sprocs often myself, I generally agree with them — the tooling around sprocs really sucks. Sprocs are "DB objects as state" (like rows in a table are state); but they could be a lot more than that.

My secret dream, as an infrastructure engineer and DBA, is that, on boot, each version of each application that uses an RDBMS could "register" or "zero-install" a client schema with said RDBMS — i.e. a namespace of persistent virtual DB objects, unique to that build of that application — but where the underlying physical DB objects are not necessarily unique, instead being shared as needed between builds and even between applications. (Think: what would happen if you put the Plan9 designers in charge of the evolution of the SQL standard.)

Such a "client schema" would specify, among other things, the full source code of any sprocs the application expects to be able to call. These would be able to be internally deduplicated — e.g. registered by content-hash into an sproc store on the DB (compare/contrast: Redis EVALSHA), and then "symbolically linked" to a particular name in the client schema.

Ideally, in applications that use client schemas, every SQL query that's a static literal at app build time, would be compiled into a boot-time registration of, and runtime call to, such a registered virtual sproc definition. Think SQL "prepared statements" — but "prepared" at compile time on the client and ensure-bound to RDBMS-side equivalents at app boot time.

(Why? Well, think about what RDBMSes could do to dynamically compile / optimize / JIT queries — and dynamically create/optimize indices to serve queries — if they could know the bounded set of in-use queries at any given time, and do long-term statistics collection on the behavior of those queries. No existing RDBMS is designed to do this, despite sprocs existing, only because they're so little used. Instead, almost all "app queries" to RDBMSes are just regular DML statements — which read to an RDBMS as one-offs needing to be freshly compiled and planned. RDBMSes could do so much better — if apps simply had the language to communicate their needs clearly!)

The article did throw in a couple of bullet points for that:

  - Security risks and best practices for handling user-defined formulas.
  - Is SQL injection a hazard when using formula fields?

We'll make sure to keep you in the loop about any new articles we post on our website about Colbert if you subscribe on our mail-form at https://www.colbert.nl/mail-form/