Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Don’t we all just want to use SQL on the front end?

vjpr.medium.com

Post reply on HN
Print view View original

121–130 of 184 posts

Re: Don’t we all just want to use SQL on the front end?

#121

Post by falcolas »

droopyEyelids wrote: post #109
ipnon wrote: post #87

Earlier quoted context omitted.

It seems that the typical software engineer doesn't give security a single thought.

It feels like you and the parent commenters are framing this as a choice between allowing public access to existing databases, or continuing to build new tools for accessing the data. The way I understood the article, the ideas is 'can we expose a safe subset of SQL or fix the security issues instead of continuing to build entirely new systems'

Here's the root of my concern - safely and securely emitting SQL from an untrusted client is exponentially harder than running canned SQL queries from a trusted client.

People forget (or don't care) about security or safety when it lets them move faster.

"Move fast and break things," is a terrible security model.

Re: Don’t we all just want to use SQL on the front end?

#122

Post by dragonwriter »

falcolas wrote: post #115
dragonwriter wrote: post #105

Earlier quoted context omitted.

> For one, permissions around SQL are already crap No, they aren’t. At least not Postgres, and AFAIK its true of every other major RDBMS, too. Non-DB-specialists typical level of knowledge of DB permissions may be, but that’s a whole different problem.

> No, they aren’t. At least not Postgres Yes, Row Based Security exists. But not by default. It also requires you to have one database user per external user. Something I (and most InfoSec professionals) wouldn't be keen on automating or managing, for fear of getting it wrong. [EDIT] The following was removed from the parent post. It was in response to "you can't audit queries". > No, you don’t Citation needed. Yelli…

> It also requires you to have one database user per external user.

So?

You are adding a database entry per external user, along with data identifying their roles/permissions in the app, one way or the other.

You can either use custom code you’ve built on top of the DB engine to apply this, or you can use the far more battle-tested code in the database engine.

I’m not sure why so many developers believe reinventing the wheel on database security is more effective than understanding their tools.

Re: Don’t we all just want to use SQL on the front end?

#123

Post by viklove »

ggregoire wrote: post #119
nromiun wrote: post #30

How many people are actually using raw SQL on the backend? I thought people got tired of maintaining strings of raw SQL and migrated to query builders or ORMs. > I usually end up with a bunch of Lodash (groupBy, filter, map, reduce) to shape the data I get from the server. I mean, we are also doing the same thing with ORMs on the backend.

> How many people are actually using raw SQL on the backend? I sure do. Nothing beats being able to just copy/paste a SQL query from a code base to your client to see what's going on. Then tweak that said query until it works as expected. When I do backend development involving the database a lot (and I do use a lot of views, functions and triggers), I spend more time in my database client than in my IDE.

I think ActiveRecord beats this, since the code ends up more readable, but you can still run it in the rails repl, and the generated SQL syntax is printed in both the webserver logs and the repl output.

Re: Don’t we all just want to use SQL on the front end?

#124

Post by dvdkon »

BiteCode_dev wrote: post #96
falcolas wrote: post #31

As many SQL Injection issues (and various other injection forms, including Javascript) as we have with backend code, fuck no we don't want SQL being issued from the frontend. For one, permissions around SQL are already crap. It takes the smallest screwup to expose data in co-mingled databases. No need to make it even worse. For two, at least if the SQL is on the backend, a fix for an exponential query DDOSing your DB…

Not to mention: - super hard to cache if any client can basically generate an infinite number of different queries. - which dialect of SQL ? If you change DB or upgrade version, do your ask all clients to change as well ? - how do you do query that involves several data sources ? Some response data come from redis + elastic search + postgres. That's why FB created graphql. That's also why it's so limited in scope, so…

Direct SQL from the client definitely isn't for everyone, but these problems aren't insurmountable.

Caching: This is true of any API allowing, for example, advanced search. If you can get by without it, great, but it's often a requirement.

Dialect: I don't think it's a terrible idea to bet on one (ideally stable and FLOSS) database, it's just a risk to be managed, and I think it can provide benefits in some cases. Or you could be fancy and use jOOQ's SQL dialect translator (this could also be used to create a "custom dialect" without some features).

Multiple data sources: You could use PostgreSQL's Foreign Data Wrappers. An unorthodox solution, but it allows seamless access with fast joins.

Complex SQL features: I don't think it's really a problem, in complex analytics code I think some users would actually appreciate having all these features. It can be a code quality challenge, but I think that's better solved on a personal level.

This approach does have real problems, like it being hard to prevent DoS attacks, but I don't think there are as many problems as it seems on first glance.

Re: Don’t we all just want to use SQL on the front end?

#125

Post by vaughan »

wayneftw wrote: post #64

For offline apps, WebSQL would have been awesome. Thanks to some ivory tower types at Mozilla we can't have it [1]. We got SQLite in a computer on Mars but apparently that's not enough to base a web standard around because some people think that It WoUlD Be BaD iF wE oNlY hAve oNe implEmentation! Oh noooo! (This despite the fact that every Linux distro uses the same kernel and Linux hasn't suffered some sort of monoc…

They also criticized it for poor "developer aesthetics". [1]

> We were resolved that using strings representing SQL commands lacked the elegance of a “web native” JavaScript API, and started looking at alternatives.

> In another article, we compare IndexedDB with Web SQL Database, and note that the former provides much syntactic simplicity over the latter.

IndexedDB and "syntactic simplicity". For anyone who has worked with that API, boy oh boy.

[1]: https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...

Re: Don’t we all just want to use SQL on the front end?

#126

Post by falcolas »

dragonwriter wrote: post #122
falcolas wrote: post #115

Earlier quoted context omitted.

> No, they aren’t. At least not Postgres Yes, Row Based Security exists. But not by default. It also requires you to have one database user per external user. Something I (and most InfoSec professionals) wouldn't be keen on automating or managing, for fear of getting it wrong. [EDIT] The following was removed from the parent post. It was in response to "you can't audit queries". > No, you don’t Citation needed. Yelli…

> It also requires you to have one database user per external user. So? You are adding a database entry per external user, along with data identifying their roles/permissions in the app, one way or the other. You can either use custom code you’ve built on top of the DB engine to apply this, or you can use the far more battle-tested code in the database engine. I’m not sure why so many developers believe reinventing t…

How many millions, or billions, of database users are you comfortable managing?

0 millions for me.

I'll stick with the well tested and explored method of having a users table with foreign keys on the ID to other tables to identify data ownership.

The idea of using one DB user per end user is, at best, novel and untested. We don't know where or how it will fail. Scaling would be a real bear too. And I'm fairly certain an InfoSec professional would have kittens if they saw that attempted in a production environment.

Re: Don’t we all just want to use SQL on the front end?

#127

Post by dragonwriter »

Its worth noting, as most of the discussion here focuses on the imagined variation of the headline of “don’t we all want raw SQL access to our backend DB on the front end”, that the article isn’t about that at all and is actually about having an in-client SQL database with local copies of the relevant subset of backend records in the same shape, with a plug-and-play syncing mechanism rather than per app custom front end data request, caching, etc. logic.

Re: Don’t we all just want to use SQL on the front end?

#128

Post by ggregoire »

viklove wrote: post #123
ggregoire wrote: post #119

Earlier quoted context omitted.

> How many people are actually using raw SQL on the backend? I sure do. Nothing beats being able to just copy/paste a SQL query from a code base to your client to see what's going on. Then tweak that said query until it works as expected. When I do backend development involving the database a lot (and I do use a lot of views, functions and triggers), I spend more time in my database client than in my IDE.

I think ActiveRecord beats this, since the code ends up more readable, but you can still run it in the rails repl, and the generated SQL syntax is printed in both the webserver logs and the repl output.

> ActiveRecord beats this, since the code ends up more readable

It's subjective and I tend to disagree. Especially for very simple and very complex queries.

Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM, you have to redefine your tables, columns and relationships a second time and keep them up-to-date with every change, which is a huge hassle.

Obviously if your app is a simple CRUD app, might be simpler to just use Rails/Django/Symfony with an ORM and embrace the code-first approach.

Re: Don’t we all just want to use SQL on the front end?

#129

Post by mobilemidget »

quasarj wrote: post #84
nromiun wrote: post #30

How many people are actually using raw SQL on the backend? I thought people got tired of maintaining strings of raw SQL and migrated to query builders or ORMs. > I usually end up with a bunch of Lodash (groupBy, filter, map, reduce) to shape the data I get from the server. I mean, we are also doing the same thing with ORMs on the backend.

I'm sure some did, but I got sick of the slowness and lack of expressive ability with every ORM I tried and just went back to SQL strings.

If you don't fully understand what happens under the hood with those ORMs and limit yourself to its, well limitations, then somebody is going to have a bad time. And in my experience too, with almost with all (read all that I tried) of them, some queries end up to be way easier to write yourself, and most frequent this is performance/resource issue.

Re: Don’t we all just want to use SQL on the front end?

#130

Post by dragonwriter »

falcolas wrote: post #126
dragonwriter wrote: post #122

Earlier quoted context omitted.

> It also requires you to have one database user per external user. So? You are adding a database entry per external user, along with data identifying their roles/permissions in the app, one way or the other. You can either use custom code you’ve built on top of the DB engine to apply this, or you can use the far more battle-tested code in the database engine. I’m not sure why so many developers believe reinventing t…

How many millions, or billions, of database users are you comfortable managing? 0 millions for me. I'll stick with the well tested and explored method of having a users table with foreign keys on the ID to other tables to identify data ownership. The idea of using one DB user per end user is, at best, novel and untested. We don't know where or how it will fail. Scaling would be a real bear too. And I'm fairly certain…

> The idea of using one DB user per end user is, at best, novel and untested

Its a technique older in continuous use with RDBMSs and more battle-tested than, say, the web itself, and plenty of enterprises (even the kind that have apps that not only don’t provided direct DB access to their frontend, but don’t even provide their backend access to tables or views but mediate all external access to the DB through sprocs) have it as a security norm and (correctly) view apps that manage end-user access outside of the database as taking a relatively novel, untested, and risky approach.

Post reply on HN