If such SIAAS

    - Checks that query is SELECT query (can be tricky with CTE, requires proper SQL parser)
    - Allows editing said query by superuser only
    - Can be parametrized, including implicit $current_user_id$ parameter
    - Has it's own permissions and users can run the query if they have permissions

Also, the underlying databases were secured. Just because you can send a query to a database does not mean you are exposing additional data - database-level security exists and works well.

STRING SEARCH? If only there was a way to limit connection privileges to read-only DML.

Then the queries can be run only using that database user, and there's no risk of INSERT/UPDATE/DELETE. Injection isn't possible. And you can limit access to only the relevant tables as well, so you can't accidentally leak PII.

For a UI based SQL tool I've used Looker few years ago and thought it was ok. I wonder how much AI tooling Google added since then to help with LookML generation. There are also other open source tools like mathesar (https://github.com/mathesar-foundation/mathesar) which look promising.

And this isn't just about SQL queries. Often, people want to be able to make privileged changes to json config files, etc. etc.

So, I added a feature to the Qbix framework, to do just that. Essentially you have some privileged payloads, signed off by an Auditor role with their private key. You might even require M of N keys.

Then, for each Target (e.g. a database) you can use an ACL or Roles+Permissions for who can actually execute the payload against it.

This can be used on:

  * Smart Contracts

  * SQL Queries on Databases

  * Upgrade Scripts on Nix machines

  * Updates on package managers (versions that were vetted)

Usually, when you have a bunch of "n00bs" who shouldn't be trusted with X, you want to set up a class of Auditors who sign off on the latest changes, and then a class of Admins who manage the roles, including Auditors and Users.

You don't need sys admins or dev ops. Just have standard container images / AMIs etc. and support only those. Don't even allow ssh into the instance, if you can help it. Let the instance be a pristine environment (Trusted Execution Environment) and discover arbitrary updates at well-known URLs, such as on github. You can have mirrors and fallbacks, but the key is that M of N Auditors from the organization's whitelist have signed off on the arbitrary code of any upgrade, whether delivered via a package manager or a git post-commit hook.

And all updates become automatic, and available to the n00bs to use. Role-based access is always enforced per-target when they attempt to use it. Inside the black box you can use simple HMAC to verify authorizations it issued earlier.

It took me a while to understand this, but the only way to solve security is to either have "pristine environments" that can run arbitrary code but always check M of N signatures for updates, with "byzantine fault tolerant consensus" on top. In the first one, you have to trust cloud providers. For the second one, you sacrifice some privacy. (e.g. with the new FreeNet's smart contracts).

PS: the M of N signatures might have to be hardened against quantum computers, so rather than using elliptic curve cryptography you might want to use lattice-based or my favorite quantum-resistant approach: SPHINCS+ based on Lamport signatures.

Typically and sadly, the developer used string concatenation to build queries. Some users figured out (or, perhaps, saw the developer do it) they could abuse "filter" fields in the Win32 thick-client application to get filtering functionality the client otherwise didn't offer.

Contrived example: AR aging report doesn't allow for filtering by Customer city. It does allow filtering by Customer name. In the Customer name filter box enter "' OR [City] = 'Boston" to get an AR aging report for only Boston customers.

The users had built-up a Word document with various magical incantations to get data they couldn't otherwise get.

I was impressed by the ingenuity even though it was all horrifying. (The application ran as the database server's superuser-equivalent with the password hard-coded in the client, so nothing was stop somebody from completely trashing the server. >sigh<)

Is he implying, that tampering with the devil's query engine would get him escorted out of the building, or just trying to hook me up on the next story?

https://adsb.exposed/ - type any query and get a visualization.

https://play.clickhouse.com/ - explore the database as you want without logging in.

I worked for an artist and we had an inventory system that a freelance web developer had built for us. I think it was some sort of php, laravel, mysql stack. There was a search bar that you could use to bring up records of artworks.

Everyone at the studio used this system but I was the main person that used it. Over time, I picked up some little tricks here and there. These were useful because engaging this freelance guy for new features involved a lot of back and forth, so if I could find my own improvement to a workflow that was always the easier option.

We didn't have a clear way to pull up works for a single decade. Until I somehow discovered that you could use `%` as a wildcard character in some cases, so I could pull up paintings from the 1970s but searching for `197%`, for example.

I remember proudly telling this to the freelance dev at one point and his eyes widened and he almost looked panicked. In retrospect I recognize that he was thinking about whether he left the system open to an injection attack.

By the end of my job there I had learned enough about this to realize it was a risk but didn't get curious enough to see if a `; DROP TABLE` query would work.

So every user is a database user and if the database sez they can view the data then they can view the data. And for my use case it worked surprisingly well.

I will note note that I think this is generally a bad idea, I have heard that the postgres role data structures scale poorly. and I personally would be terrified to expose a database to a potentially hostile environment.

See also: the schemaverse a game where you directly interface with the db server. https://github.com/Abstrct/Schemaverse