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

31–40 of 184 posts

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

#31

Post by falcolas »

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 is fairly quick; you don't have to worry about some client keeping a cached copy of the frontend around for months at a time.

Finally, if you let the frontend send SQL, you have lost any and all ability to do a static audit against the queries that will be run against your database - because you have no control over what the client does.

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

#32

Post by smallnamespace »

AaronFriel wrote: post #27

Because it exposes a lot of inner details and potential security/privacy risks if clients are able to change parameters. But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters, e.g.: Signed blob: { sql: "SELECT name, email, ... FROM users WHERE users.id =…

I’m basically doing this for an app - each endpoint is just a parametrized query and RLS in the database enforces security/auth constraints.

The middleware doesn’t do anything except populate query parameters and do a bit of sanity checking.

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

#34

Post by vaughan »

phpnode wrote: post #15

a thing a I tried, and ran out of steam with, was writing SQL queries in my client side code but statically extracting them with babel, so you have some code like this: const users = await sql.query`select * from users`; console.log(users); and in production this becomes something like const users = await fetch('/query?id=19a1f14efc0f221d30afcb1e1344bebd'); console.log(users); and the query itself stays on the server…

This doesn't help with optimistic UI updates though.

The article is less about remotely executing SQL, but more about having a relational data model as a cache in the frontend.

However, when sending the query through to the backend on a cache miss, would be useful to only allow queries that have been extracted like so.

Its similar to how Apollo does query [caching][1].

I really like it!

[1]: https://www.apollographql.com/docs/apollo-server/performance...

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

#35

Post by WrtCdEvrydy »

phpnode wrote: post #15

a thing a I tried, and ran out of steam with, was writing SQL queries in my client side code but statically extracting them with babel, so you have some code like this: const users = await sql.query`select * from users`; console.log(users); and in production this becomes something like const users = await fetch('/query?id=19a1f14efc0f221d30afcb1e1344bebd'); console.log(users); and the query itself stays on the server…

We've done this with octo-cli and OpenFaaS now.

Legacy codebases are hard to remove because it's difficult but once you turn every SQL call into an HTTP call... the remaining part is the logic which can be rewritten into something more modern.

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

#36

Post by beders »

We are currently settling for something in between: EQL (using Pathom resolvers)

This gives front-end devs the flexibility of GraphQL, composability for free (EQL's DSL is just a data-structure you can manipulate easilty) and is database agnostic.

In fact, Pathom makes it easy to link different datasources and make them available in a uniform way to the front-end devs. All the while, resolvers (running on the backend) keep control and prevent malicious clients from doing harm.

Same issues you would have with GraphQL though: N+1 queries need special consideration when writing resolvers.

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

#37

Post by asguy »

AaronFriel wrote: post #27

Because it exposes a lot of inner details and potential security/privacy risks if clients are able to change parameters. But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters, e.g.: Signed blob: { sql: "SELECT name, email, ... FROM users WHERE users.id =…

Or you could could use Row Level Security, and/or stored functions/procedures for access controls. You can put these constraints in the DB, and just give each of your users a DB role.

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

#38

Post by zachrip »

AaronFriel wrote: post #27

Because it exposes a lot of inner details and potential security/privacy risks if clients are able to change parameters. But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters, e.g.: Signed blob: { sql: "SELECT name, email, ... FROM users WHERE users.id =…

Another issue is part of what makes a company valuable is their biz logic. Moving all of that to the frontend means you can no longer guard it. Even if you can extract it to the backend, how many REST/GQL endpoints use a single query? And where do the parameters get supplied if not from a route specific handler?

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

#39

Post by throwawaygh »

AaronFriel wrote: post #27

Because it exposes a lot of inner details and potential security/privacy risks if clients are able to change parameters. But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters, e.g.: Signed blob: { sql: "SELECT name, email, ... FROM users WHERE users.id =…

This is a really interesting interface design, but seems like for any non-trivial database it would be too easy to make mistakes and give the client too much access. Also seems like it's be really easy to end up with performance issues. Also, there's basically zero information hiding. The database is your interface. Changing the schema in any way whatsoever is a breaking change to the API! You haven't really saved yourself from the hassle of designing and maintaining backward compatibility for the API. You've just turned your database schema into the API :'(

But I haven't actually seen a codebase that does this, so maybe I'm wrong. Have you used this approach in a project with more than a few developers and a large database? How did it go?

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

#40

Post by ownagefool »

AaronFriel wrote: post #27

Because it exposes a lot of inner details and potential security/privacy risks if clients are able to change parameters. But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters, e.g.: Signed blob: { sql: "SELECT name, email, ... FROM users WHERE users.id =…

Not quite sure I get what's signing the request. Presumably if it's the client, it can be tampered with, so we're guessing that there's still an API layer signing things?

That said, I guess you could just add row / column ACLs to the database user, and then map the database user to the app user.

Post reply on HN