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

111–120 of 184 posts

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

#111

Post by shakezula »

maxk42 wrote: post #22

I've spent the last six months working with a codebase that does exactly this. Aside from the obvious problems with exposing your schema to potential attackers, opening up potential DOS vectors, and training front-end engineers on yet another technology, you end up with some code that is very, very difficult to test. If you're using it in your hobby project and you're aware of the pitfalls - fine, go ahead and do wha…

Security problems aside, testing is a much more present and pragmatic reason to say "This is a terrible idea."

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

#112

Post by keanebean86 »

ipnon wrote: post #87
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…

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

Considering the number of times I've had to remind coworkers about sql injection during code reviews I agree.

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

#113

Post by oeskeide »

> We need a restrictive SQL parser to run server-side to restrict what can be run and prevent SQL injection. Maybe we need a query-builder/ORM to generate a safe intermediary SQL language in JSON so that we can validate it. Maybe taking some inspiration from Prisma’s type-safe data-mapper client could be used to help people write safe queries that won’t fail server-side validation unexpectedly.

What you are describing here is an API.

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

#114

Post by starik36 »

kstrauser wrote: post #81
starik36 wrote: post #76

Yes. My point of pain has been ElasticSearch. The Json happy queries with far too many squiggly braces and square braces are just difficult to read. A simple statement like SELECT Age FROM Employee WHERE name = 'Frank' turns into a monstrosity like POST Employee/_search { "query": { "bool": { "must": [ { "match": { "name": "Frank" } } ] } }, "fields": [ "Age" ] } Thankfully they added rudimentary SQL support so I cou…

I wish someone took the SQLite query parser and used it to output Elasticsearch's horrid language so that we could write queries in a language that our devs don't universally loathe.

Ask and you shell receive.

https://sqltoelasticsearch.azurewebsites.net/

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

#115

Post by falcolas »

dragonwriter wrote: post #105
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…

> 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. Yelling "No, you don't" without anything else is fscking useless in moving a conversation forward.

If the query is coming from the frontend, it's coming from a client that is outside of your control. I.E. you don't know what queries will come from the frontend, because the user can execute an arbitrary query.

If you take an effort to extract and bake those queries into a backend (perhaps calling it a proxy), then you're not actually emitting SQL from the front end, you're relying on a backend to emit the actual SQL used after performing a number of security checks. AKA, the status quo.

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

#116

Post by dragonwriter »

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 =…

> 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

Or...you could just expose a schema with views and sprocs where their definitions use only the SQL you are comfortable with against the base tables, which live off in their own schema isolated from external users. Same effect, but doesn’t require a whole new custom layer on top to reimplement what has been a basic feature of RDBMS engines for decades.

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

#119

Post by ggregoire »

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.

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

#120

Post by falcolas »

zozbot234 wrote: post #110
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…

In practice, you would use something like SPARQL (which is already a W3C standard) as opposed to literally feeding raw SQL to the backend database. And SPARQL is designed for reasonable complexity, while still being more flexible than some purely ad-hoc thing like GraphQL (which only really deals well with tree-like hierarchies of data, not anything more general).

Whether you're making a REST API, a graphql endpoint, or a SPARQL (or even plain old SQL), you're still going to end up creating a backend to parse, transform, and apply security policies to the incoming queries so they can then be run against the backend.

The complexity and security costs just keep going up the more logic you shove into an untrusted client.

Post reply on HN