5 ms·

Execute formulas stored in database fields with almost standard SQL

22 points by BVegter 3y ago

some_furry 3y ago
The article isn't loading for me (hug of death?), but from the title, I expect this capability to be of interest to exploit developers.
- Cieric 3y ago
  Yeah same, wayback also seems to be lacking a backup so that can't really be used either.
- deleted 3y ago
  [deleted]
- mtmail 3y ago
  https://archive.is/uDlPY https://archive.is/uDlPY
curiousllama 3y ago
Is this a thing? This feels like it throws separation of concerns out the window. When my business logic sits in a script, I get a lot of free tooling to help me (code editors, git, jira, etc.). If it sits in a db field, I lose all that along with the benefits (e.g., git blame) they provide.
- atishay811 3y ago
  You have to get it into the db with a a migration checked into your git repo.
- devmor 3y ago
  Yes, this reminds me of the horrible things WordPress did early on in my career - executing scripts found in a database, embedding HTML found in a database. Was also one of the most common vectors for vulnerability chaining.
- bloaf 3y ago
  https://www.dolthub.com/ https://www.dolthub.com/ 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.
  - BVegter 3y ago
    With our approach in Colbert, you can provide your table that holds a formula field with a corresponding tag field and apply formulas to your data using a WHERE clause on the tag field. This allows you to safely test different scenarios. Furthermore, in Colbert, you have the capability to inquire about the parameters used in each formula. For example, you can test the appearance of a specific parameter by querying: SELECT * FROM Employees WHERE params bonus contains 'Birthdate'
- tyingq 3y ago
  It seems to be a proposal for now. 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?
- PartiallyTyped 3y ago
  You'd be surprised how common runtime SQL generation and execution is :')
- phartenfeller 3y ago
  You can store SQL queries in views that you can version in Git. In "enterprise" databases like Postgres, Oracle and MSSQL, you can also store statements in procedures/functions that you can also store in Git. We also do Git based migration scripts that create new / update existing views, tables, procedures, data, etc. This happens automatically on test servers and can be rolled out to production. 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.
- derefr 3y ago
  That assumes your database cells aren't themselves revision-controlled (e.g. Dolt: https://www.dolthub.com/ https://www.dolthub.com/) 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!)
mtmail 3y ago
https://archive.is/uDlPY https://archive.is/uDlPY
ako 3y ago
The example in the article could easily be implemented in a view. Even if you have different type of rules, you could union multiple strategies based on rol into one overall view. Not sure how the presented approach improves on this.
- naasking 3y ago
  You're not limited to calculating a formula based on a role, each individual row can have its own formula (say each employee can negotiate their own salary/compensation package). You would need a union of an infinite number views to equal the number of formulae possible with an expression column type. So it's definitely more expressive and powerful.
netcraft 3y ago
Where I have needed things like this before it is generally that there is a finite list of functions with some intersection of arguments, and the classification of which function (strategy) you need is what goes in the DB and the code lives in, well, the code. That way its unit testable, version controlled, and way better understandable. If it has to happen in SQL (which I def dont have a problem with personally), then its a case statement - but still, it lives in code, not in the DB. I def wouldnt want anyone editing formulas in a crud screen, they should be picking from available options. If your options are too varied to support this though, I dunno, maybe you need to get more creative - but balancing that with needing to know that what gets set is correct and valid seems challenging. No way im doing this with something that is calculating payments!
keybored 3y ago
Why aren’t spreadsheet languages more SQL-like? In particular Excel. (I hope this isn’t too off-topic)
- fimdomeio 3y ago
  Google spreadsheets allow queries in sql. I would say for most common use cases it's a level of complexity most people are not interested in.
solidsnack9000 3y ago
Pretty cool. There are many situations where users want to store flexible rules like this and many are already familiar with SQL syntax and concepts for writing such expressions. It would be great to be able to subscribe to a newsletter for Colbert -- there's definitely some interesting thinking going on there.
- BVegter 3y ago
  Thank you for your interest! 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/ https://www.colbert.nl/mail-form/
BVegter 3y ago
Apparently, people are shocked by the title 'Execute formulas stored in database fields', which they associate with bad experiences involving scripting, SQL generation, stored procedures, SQL injections, and other vulnerabilities. However, the point is that the approach presented here is intended to address these vulnerabilities and provide a robust, secure environment. We are accustomed to applying formulas to entire columns using calculated fields in SQL and have no problem with this; in fact, we gratefully use this option. The approach presented here offers the same safe opportunity but now at a record level. Therefore, there is no scripting, SQL generation, stored procedures, or SQL injection involved. Just as you can safely define a calculated field, you similarly define an expression that returns a value without any possible side effects. Furthermore, it is not strange to include a formula in a database field. In databases, we register attributes of objects, and sometimes that attribute is an expression, such as the agreed bonus with your employer. Formula fields allow such an attribute to be registered and calculated within SQL. This provides us with a much more flexible, safer, and transparent method than writing a separate program outside the database.

22 points by BVegter 3y ago

The article isn't loading for me (hug of death?), but from the title, I expect this capability to be of interest to exploit developers.

Cieric 3y ago
Yeah same, wayback also seems to be lacking a backup so that can't really be used either.
deleted 3y ago
[deleted]
mtmail 3y ago
https://archive.is/uDlPY https://archive.is/uDlPY

curiousllama 3y ago

Is this a thing? This feels like it throws separation of concerns out the window. When my business logic sits in a script, I get a lot of free tooling to help me (code editors, git, jira, etc.). If it sits in a db field, I lose all that along with the benefits (e.g., git blame) they provide.

atishay811 3y ago
You have to get it into the db with a a migration checked into your git repo.
devmor 3y ago
Yes, this reminds me of the horrible things WordPress did early on in my career - executing scripts found in a database, embedding HTML found in a database. Was also one of the most common vectors for vulnerability chaining.
bloaf 3y ago
https://www.dolthub.com/ https://www.dolthub.com/ 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.
- BVegter 3y ago
  With our approach in Colbert, you can provide your table that holds a formula field with a corresponding tag field and apply formulas to your data using a WHERE clause on the tag field. This allows you to safely test different scenarios. Furthermore, in Colbert, you have the capability to inquire about the parameters used in each formula. For example, you can test the appearance of a specific parameter by querying: SELECT * FROM Employees WHERE params bonus contains 'Birthdate'
tyingq 3y ago
It seems to be a proposal for now. 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?
PartiallyTyped 3y ago
You'd be surprised how common runtime SQL generation and execution is :')
phartenfeller 3y ago
You can store SQL queries in views that you can version in Git. In "enterprise" databases like Postgres, Oracle and MSSQL, you can also store statements in procedures/functions that you can also store in Git. We also do Git based migration scripts that create new / update existing views, tables, procedures, data, etc. This happens automatically on test servers and can be rolled out to production. 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.
derefr 3y ago
That assumes your database cells aren't themselves revision-controlled (e.g. Dolt: https://www.dolthub.com/ https://www.dolthub.com/) 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!)

mtmail 3y ago

https://archive.is/uDlPY https://archive.is/uDlPY

ako 3y ago

The example in the article could easily be implemented in a view. Even if you have different type of rules, you could union multiple strategies based on rol into one overall view. Not sure how the presented approach improves on this.

naasking 3y ago
You're not limited to calculating a formula based on a role, each individual row can have its own formula (say each employee can negotiate their own salary/compensation package). You would need a union of an infinite number views to equal the number of formulae possible with an expression column type. So it's definitely more expressive and powerful.

netcraft 3y ago

Where I have needed things like this before it is generally that there is a finite list of functions with some intersection of arguments, and the classification of which function (strategy) you need is what goes in the DB and the code lives in, well, the code. That way its unit testable, version controlled, and way better understandable. If it has to happen in SQL (which I def dont have a problem with personally), then its a case statement - but still, it lives in code, not in the DB. I def wouldnt want anyone editing formulas in a crud screen, they should be picking from available options. If your options are too varied to support this though, I dunno, maybe you need to get more creative - but balancing that with needing to know that what gets set is correct and valid seems challenging. No way im doing this with something that is calculating payments!

keybored 3y ago

Why aren’t spreadsheet languages more SQL-like? In particular Excel. (I hope this isn’t too off-topic)

fimdomeio 3y ago
Google spreadsheets allow queries in sql. I would say for most common use cases it's a level of complexity most people are not interested in.

solidsnack9000 3y ago

Pretty cool. There are many situations where users want to store flexible rules like this and many are already familiar with SQL syntax and concepts for writing such expressions. It would be great to be able to subscribe to a newsletter for Colbert -- there's definitely some interesting thinking going on there.

BVegter 3y ago
Thank you for your interest! 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/ https://www.colbert.nl/mail-form/

BVegter 3y ago

Apparently, people are shocked by the title 'Execute formulas stored in database fields', which they associate with bad experiences involving scripting, SQL generation, stored procedures, SQL injections, and other vulnerabilities. However, the point is that the approach presented here is intended to address these vulnerabilities and provide a robust, secure environment. We are accustomed to applying formulas to entire columns using calculated fields in SQL and have no problem with this; in fact, we gratefully use this option. The approach presented here offers the same safe opportunity but now at a record level. Therefore, there is no scripting, SQL generation, stored procedures, or SQL injection involved. Just as you can safely define a calculated field, you similarly define an expression that returns a value without any possible side effects. Furthermore, it is not strange to include a formula in a database field. In databases, we register attributes of objects, and sometimes that attribute is an expression, such as the agreed bonus with your employer. Formula fields allow such an attribute to be registered and calculated within SQL. This provides us with a much more flexible, safer, and transparent method than writing a separate program outside the database.