It is indeed a very risky move without a lot to gain for him personally. But I could guess McDonald's would have forced him to ignore it and shut up about it if he disclosed this to the higher ups himself, in which case I would have gladly left myself instead.
Supabase unironically do exactly that. You've got your client, you login through OAuth, then use your JWT to connect directly to postgres.
Your JWT contains your user id, which is used with row level security rules to determine which rows you can and cannot access. It's pretty amazing what you can do with PG alone. The tooling is not quite there yet but that's probably where we're headed.
I got dumped with fixing some bugs in a project written by a contractor who had literally done this but with extra steps.
Backend was sql server and c#/asp.
There was an api endpoint that took json, used xslt to transform to xml. Then called the stored procedure specified in request passing the xml as a parameter.
The stored procedure then queried the xml for parameters, executed the query, and returned results as xml.
Another xslt transformed that to json and returned to the client.
It was impressive how little c# there was.
Despite holding all the business logic, the sql was not in source control.
Worst thing with databases. Used to quote to my interns, "No spooky action at a distance" logic has to be in front of you and in git. Anything else is a recipe for bugs and undetectable errors.
Yeah, maybe the contractor thought he'd get more work fixing it but he was long gone by the time I got it so i never met him
One of bugs I got was performance because the search didn't work, with about 600,000 assets in database it would timeout searching for one by exact match on ID. It took 45 minutes to return 1 result.
We did that for a Plotly dashboard in Python. We copied the database into a read-only in-memory sqlite database (it is quite small, only a couple thousand entries) to prevent any damages outside the dashboard. The data only gets updated every couple of days. You could skip this step. Then with sqlite you can restrict what action a query can use (SELECT, JSON, etc.) and you can restrict the instructions per query to prevent denial of service. It works like a charm and is much simpler than providing a REST API. Also the user might already know SQL.
I am actually planning something similar for a task management web app I am building at the moment (additionally to providing a REST API). No need to learn another query language like in Jira.
Security. Even when you limit operations or table access it's very easy to mess something up. Some new employee starts storing sensitive data in the wrong place or a db admin accidentally turns off the wrong permissions, etc...
It's secretly more overengineered than a standard api despite looking simpler. If your app needs extremely robust query capabilities then you probably have a use case for an entire analytics stack and could use an open source option. Otherwise your users probably just need basic search, filtering, sorting, etc...
Ungodly, Flex Tape tier tight coupling. Part of the purpose of an api is to abstract away implementation details and present a stable contract. Now if you want to migrate/upgrade the database or add a new data source, everyone has to know about it and it's potentially a major breaking change.
Familiarity. If someone else steps in to maintain it it's much easier to get up to speed with a more standard stack. You don't need a seven layer salad of enterprise abstraction bullshit, but it's useful to see a familiar separation of auth, queries, security, etc...
Having the option to do business logic outside of the database can save countless headaches. Instead of inventing views or kludging sprocs to do some standard transformation, you can pull in a mature library. Some things, such as scrubbing PII, are probably damn near impossible without a higher tier layer to work in.
Client support. Your browser/device probably has a few billion options for consuming a REST/HATEOAS/graphql/whatever api. I doubt there's many direct sql options with wide support.
I probably wouldn't do it outside of a tiny solo project. There are plenty of frameworks which do similar things (such as db driven apis) without compromising on flexibility, security or features.
Does ReST mean anything anymore? It was originally a set of principles guiding the development of the HTTP 1.1 spec. Then it meant mapping CRUD to HTTP verbs so application-agnostic load balancers could work right. And now I guess it’s just HTTP+JSON?
I understand it for normal words. But for an acronym? About a body of technical research? How are we supposed to refer to the thing that Fielding meant when he coined the term?
Lots of technical terms are used quite loosely I think. If you ask 10 people what a compiler is, or what functional programming is or what goes where on the OSI layers you’ll get different answers.
It’s not practical but that’s the world we live in.
It's not that bad that you might think, the db user just need to have readonly access permissions to specific database tables.
Ofc all data in tables ofc need to be public, so more like simple public facing page, app should not have any notion of users in any way, data probably populated by some automated system, and UI just to make it easier for anonymouse users to view that data in a friendly way.
On top of that it will be a hell for the sysops as they will need to know the whole db structure and such of even a single part of db would contain non-public data, but that overall the best guy to handle security in the first place.
And because all data is totally public in the first place you could give the task of creating frontend to any junior or LLM and it will be still secure.
But in truth it is very bad idea (even it it is possible), because most likely the database connections would be reused so you could for example change current connection session timezone or other params and that ofc would nit change the data in db but still could affect other users by showing wrongly formatted or shifted data.
CouchDB (a no-sql db, but whatever) automatically provides a REST API that's designed to be exposed directly to clients. It even implements its own client-facing authentication system. "queries" are configured in advance from the admin side, and clients just pull the results, allowing for very efficient caching. Basically, if you RTFM enough to get a couchdb instance running, you have 90%-100% of your backend complete. You could create an entire scalable full-stack app using only client-side code... and if you're clever with HTMX, you might even be able to do it without writing any javascript at all! (I tried once, but failed because I'm not that clever, but it's definitely probably possible)
So TL;DR: I like couchdb, and the idea of exposing your database directly to users isn't unprecedented. I wonder if there are any SQL databases that offer a similar thing?
also stop putting in extra work to handle queries; the user knows what they want so let them enter the queries themselves and save development time. database sanitization is just pointless busywork.
I mean…. Let’s say you set up a Postgres user for all of your application users, with appropriate roles and row level security policies, you could actually do it without Bobby tables issues. I think.
Does the database use the same authentication and permissions as the API? If the API authenticates against the DB with a technical user, it may be still be an exploitable vulnerability for people who can't access the DB directly but can access the API. I don't know what database it is, what other databases run on the same server and what privileges might be achievable or escalatable, but generally "there are worse weaknesses" isn't a solid security policy.
You could give me a VPN access and I'll take a look around :p
(Please don't, actually – in case it needs to be said, running pentests on prod is a dangerously bad idea already even before we get to the whole "trusting a stranger on the Internet just because they sound sorta knowledgeable" issue)
Yeah, if I heard a junior dev say that in earshot I'll be pulling them into a room for the list-of-reasons-that's-dumb. Even the AI code assistants would directly say it's not advisable.
I would honestly prefer it if more websites just did that and gave you access to whatever they have about you in their database. Would be much easier to figure out which data they actually store about me. If you set up the access lsits appropriately it could be OK from the security perspective too (of course better to do it over something other than SQL, but who am I to judge).
What could possibly go wrong. Little Bobby Tables would be proud.
The XKCD being referenced
Stop over-engineering shit, just do everything client-side like McDonald's: https://bobdahacker.com/blog/mcdonalds-security-vulnerabilities
Good old shooting the messenger.
I mean, they were an employee who was exploring security vulnerabilities with a non-employee who has a blog. I would have fired them too.
It is indeed a very risky move without a lot to gain for him personally. But I could guess McDonald's would have forced him to ignore it and shut up about it if he disclosed this to the higher ups himself, in which case I would have gladly left myself instead.
Lmfao
Exposed deprecated cred-inclusion URI format, wheeeee
And the db name is short for “analysis”, of course
🤓🫠
This person was probably a scientist (of any kind).
But also, perhaps a proctologist
Analytics, most likely
GraphQL:
I work with several people who would think this is a good idea.
When they push it to prod, and our WAF goes
403on every request, then suddenly it's my problem to "fix".Can I just say, I love that little round gif at the end. That look so cool
Thanks :)
My home instance has some top-shelf custom emojis, so I try to use them. Janeway's eye roll gets a lot of mileage.
(one of my favorite memes)
"I get why we have a WAF, but can't you just, like, separate the good SQL injection from the bad SQL injection?" -- Developers I work with 😆
I think that's called "Heisenberg's Uncertain SQL Injection Principle"
Unfortunately, our WAF appliances don't have a Heisenberg compensator.
Are your coworkers 12?
This is still over engineered. Just connect directly to the database from the client instead of having an API endpoint.
Too much overengineering there as well. Just copy the entire database into a google spreadsheet
I thought that was the joke.
Supabase unironically do exactly that. You've got your client, you login through OAuth, then use your JWT to connect directly to postgres.
Your JWT contains your user id, which is used with row level security rules to determine which rows you can and cannot access. It's pretty amazing what you can do with PG alone. The tooling is not quite there yet but that's probably where we're headed.
Idk, let's just make a public google sheets and share the link
grapql in a nutshell
And OData!
Great idea. How can we submit this to all AI scrapers?
/cybersec red teamer
I got dumped with fixing some bugs in a project written by a contractor who had literally done this but with extra steps.
Backend was sql server and c#/asp.
There was an api endpoint that took json, used xslt to transform to xml. Then called the stored procedure specified in request passing the xml as a parameter.
The stored procedure then queried the xml for parameters, executed the query, and returned results as xml.
Another xslt transformed that to json and returned to the client.
It was impressive how little c# there was.
Despite holding all the business logic, the sql was not in source control.
Worst thing with databases. Used to quote to my interns, "No spooky action at a distance" logic has to be in front of you and in git. Anything else is a recipe for bugs and undetectable errors.
Seems very secure. As in job security. Because why the fuck did they make it so complicated.
Yeah, maybe the contractor thought he'd get more work fixing it but he was long gone by the time I got it so i never met him
One of bugs I got was performance because the search didn't work, with about 600,000 assets in database it would timeout searching for one by exact match on ID. It took 45 minutes to return 1 result.
Why not?
We did that for a Plotly dashboard in Python. We copied the database into a read-only in-memory sqlite database (it is quite small, only a couple thousand entries) to prevent any damages outside the dashboard. The data only gets updated every couple of days. You could skip this step. Then with sqlite you can restrict what action a query can use (SELECT, JSON, etc.) and you can restrict the instructions per query to prevent denial of service. It works like a charm and is much simpler than providing a REST API. Also the user might already know SQL.
I am actually planning something similar for a task management web app I am building at the moment (additionally to providing a REST API). No need to learn another query language like in Jira.
Couple of reasons of varying importance:
I probably wouldn't do it outside of a tiny solo project. There are plenty of frameworks which do similar things (such as db driven apis) without compromising on flexibility, security or features.
/anal
That's a backdoor
Does ReST mean anything anymore? It was originally a set of principles guiding the development of the HTTP 1.1 spec. Then it meant mapping CRUD to HTTP verbs so application-agnostic load balancers could work right. And now I guess it’s just HTTP+JSON?
The meaning of words can change yes.
https://www.merriam-webster.com/wordplay/words-that-used-to-mean-something-different
I understand it for normal words. But for an acronym? About a body of technical research? How are we supposed to refer to the thing that Fielding meant when he coined the term?
Lots of technical terms are used quite loosely I think. If you ask 10 people what a compiler is, or what functional programming is or what goes where on the OSI layers you’ll get different answers.
It’s not practical but that’s the world we live in.
it's called microservice
I'm 100% for this. Because we'll find out VERY quickly what sites are dumb enough to do this. And then we know to stay away from them! :-D
It's not that bad that you might think, the db user just need to have readonly access permissions to specific database tables.
Ofc all data in tables ofc need to be public, so more like simple public facing page, app should not have any notion of users in any way, data probably populated by some automated system, and UI just to make it easier for anonymouse users to view that data in a friendly way.
On top of that it will be a hell for the sysops as they will need to know the whole db structure and such of even a single part of db would contain non-public data, but that overall the best guy to handle security in the first place.
And because all data is totally public in the first place you could give the task of creating frontend to any junior or LLM and it will be still secure.
But in truth it is very bad idea (even it it is possible), because most likely the database connections would be reused so you could for example change current connection session timezone or other params and that ofc would nit change the data in db but still could affect other users by showing wrongly formatted or shifted data.
Most DBs have some way to reset reused connections. Postgres is one of those.
The actual problem, even with public data, is that it's trivial to overload a database with bad queries.
CouchDB (a no-sql db, but whatever) automatically provides a REST API that's designed to be exposed directly to clients. It even implements its own client-facing authentication system. "queries" are configured in advance from the admin side, and clients just pull the results, allowing for very efficient caching. Basically, if you RTFM enough to get a couchdb instance running, you have 90%-100% of your backend complete. You could create an entire scalable full-stack app using only client-side code... and if you're clever with HTMX, you might even be able to do it without writing any javascript at all! (I tried once, but failed because I'm not that clever, but it's definitely probably possible)
So TL;DR: I like couchdb, and the idea of exposing your database directly to users isn't unprecedented. I wonder if there are any SQL databases that offer a similar thing?
also stop putting in extra work to handle queries; the user knows what they want so let them enter the queries themselves and save development time. database sanitization is just pointless busywork.
only good things could be happening in the anal database
I mean…. Let’s say you set up a Postgres user for all of your application users, with appropriate roles and row level security policies, you could actually do it without Bobby tables issues. I think.
Hilariously enough, just today I read a blog post about a service where the client interacts with the database directly - https://clickhouse.com/blog/building-a-paste-service-with-clickhouse. While it's not your traditional OLTP database, it still kinda fits.
It's just like using MongoDB instead of a structured database!
Hehehehe
You absolutely won't be disappointed by explosing your database to SQL directly from the API.
I did this just to reduce network latency. It's not for public use, and tbh, I don't think you can even get at it from outside the VPN.
Pentesters: "Bet"
I haven't been down to test their public wifi in the cafe to see if that can access it.
The guy who installed it used to work for us and is a known clown, so it's entirely possible.
Although if it is, there's way worse things they can do from there. Like connect to the actual database for a start.
Does the database use the same authentication and permissions as the API? If the API authenticates against the DB with a technical user, it may be still be an exploitable vulnerability for people who can't access the DB directly but can access the API. I don't know what database it is, what other databases run on the same server and what privileges might be achievable or escalatable, but generally "there are worse weaknesses" isn't a solid security policy.
You could give me a VPN access and I'll take a look around :p
(Please don't, actually – in case it needs to be said, running pentests on prod is a dangerously bad idea already even before we get to the whole "trusting a stranger on the Internet just because they sound sorta knowledgeable" issue)
This would eventually become graphql.
I wish I could go back to rest apis. My company is all in on graphql and it fucking sucks so much ass.
some of the comments here are concerning....
Yeah, if I heard a junior dev say that in earshot I'll be pulling them into a room for the list-of-reasons-that's-dumb. Even the AI code assistants would directly say it's not advisable.
I would honestly prefer it if more websites just did that and gave you access to whatever they have about you in their database. Would be much easier to figure out which data they actually store about me. If you set up the access lsits appropriately it could be OK from the security perspective too (of course better to do it over something other than SQL, but who am I to judge).
Isn't this Firebase?
Programmer Shitposting
Heh. Netsuite does this and it's terrible.
Chan I use different database name?