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.
- Each human user has to use their own dedicated account.
- Every query leaves a trail that can't be tampered with.
- If the database contains sensitive data (personal info, payment data, ...) then the database provides a snapshot guarantee, so that we can inspect whose personal data/payment data were leaked by query X executed at instant T by a bad actor.
- List of humans who can access the feature is vetted regularly.
- Any access that can modify the data in database requires at least two separate humans to agree on the query before it can run.
- Any query that can hamper application throughput is either forbidden, happens on a replica database, or requires at least two separate humans to agree before it can run.
It was thought to be safe enough, because "nobody could guess" the URL of that page.
As it is the URL string, you can share it easily.
STRING SEARCH? If only there was a way to limit connection privileges to read-only DML.
Technically DQL, if you want to be pedantic.
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.
It's a little horrifying seeing the hoops people are jumping through to solve a problem that's more quickly, more easily, and more fully solved with your database of choice's equivalent of a CREATE USER statement; and maybe creating some views on tables if you have a need to hide individual columns from users.
The trivial example is sorting.
A more nuanced example, in the world of Oracle, is block cleanout. A block may be encountered that has been modified by a past transaction where the commit status is not immediately known; the query session is responsible for either updating the (confirmed) commit status, or rolling back the block from the UNDO/rollback disk image.
So readers always write, likely in every database to some degree.
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<)
I was shocked to find that this seems to be the norm, at least in B2G. Development shops “recommend” it and IT depts are relieved if they can get the invariably batshit arcane on-prem deployments to work at all.
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.
adontz•7h ago
If such SIAAS
It's safe enough. I've seen and applied such "Edit raw SQL in HTML form" many times. It's super flexible, especially combined with some CSV-to-HTML, CSV-to-PDF, or CSV-to-XLS rendering engine.leprechaun1066•6h ago
For example the user might ask for data with the constraint
which kdb+ parses into As a dev on the system I can then have a function which takes in this constraint and a list of clients that I want to restrict the result to. That list of clients could come from another function related to the entitlements of the user who made the request: Which results in an extension of the constraint like this for two clients A and B: Then that gets passed to the function which executes the query on the table (kdb+ supports querying tables in a functional manner as well as with a structured query language) and the result has the restrictions applied.It's really nice because, once parsed, it's list processing like in a lisp and not string processing which is a pain.
indigo945•4h ago
Not only is this difficult parsing-wise, there's also no reason to assume that a select query is read-only even when no CTE or subqueries are involved. Function calls in the select clause can also write data.
> - Has it's own permissions and users can run the query if they have permissions
This is the important one. If the role the query runs as doesn't have write permissions on any table, then the user can't write data, period.
Note that this is often not as easy to implement as it seems. For example, in PostgreSQL, neither set role nor set session authorization actually prevent the user from doing malicious things, because the user can just reset role or reset session authorization in the query. For PostgreSQL to properly respect a role's permissions, the SIAAS needs to actually connect to the database as that role.
Common GUC-based row level security approaches are also incompatible with this idea.
kogus•3h ago
https://learn.microsoft.com/en-us/sql/relational-databases/u...
Tostino•3h ago
chasil•1h ago
https://en.wikipedia.org/wiki/SQL/PSM
Within this ADA-esque world, packages, procedures, and functions may initiate DML.
Assuming these objects are in the default "definer rights" context, the DML runs with the full privilege of the owner of the code (this can be adjusted to "invoker rights" with a pragma).
Perhaps this is why Microsoft ignores it (as Sybase did before it).
Tostino•3h ago
Trusting a select to be read only is naive.