4 ms·

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

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.

ipnon 6y ago
It seems that the typical software engineer doesn't give security a single thought.
- droopyEyelids 6y ago
  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'
  - falcolas 6y ago
    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.
- keanebean86 6y ago
  Considering the number of times I've had to remind coworkers about sql injection during code reviews I agree.
- neonological 6y ago
  I mean the guy is front end. Front end development doesn't involve any security.
  - ipnon 6y ago
    I think you would be surprised how quickly and easily an attacker can automatically export vulnerabilities in web applications with programs such as Ffuf and SQLmap.
    - neonological 6y ago
      Not disagreeing. I'm just saying people on the front end never actually deal with security, hence this is the mentality that is the instigator of the suggestion of using SQL on the front end.
BiteCode_dev 6y ago
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 that complexity is manageable. - do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ?
- dragonwriter 6y ago
  > do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ? No, you implement an exposed schema for each app (or possibly more granular) that consists of objects (mostly views, possibly sprocs) optimized for the planned access pattern of the app (or whatever client/component thr schema is for), including convenience views abstracting those things away to the extent appropriate for the use case. That’s been a widely known RDBMS best practice for decades.
- dvdkon 6y ago
  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.
dragonwriter 6y ago
> 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.
- falcolas 6y ago
  > 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.
  - dragonwriter 6y ago
    > 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.
    - falcolas 6y ago
      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.
zozbot234 6y ago
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).
- falcolas 6y ago
  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.
- leipert 6y ago
  In my practical experience having worked with SPARQL and GraphQL, I would very much prefer GraphQL. SPARQL is really nice for querying graph data, but I'd argue that for most applications tree-like hierarchies are good enough. Also GraphQL and associated frontend libraries are written to be consumed by browsers and results are much easier to handle than results from a SPARQL query, JSON+LD is not the easiest format to handle. The tooling around SPARQL is not great compared to GraphQL either. Furthermore I'd argue that SPARQL queries are really hard to statically analyze / optimize on the Backend. Different SPARQL engines behave differently and whether you run your query against e.g. Stardog or Virtuoso can have vast performance differences. SPARQL being hard to analyze statically actually becomes apparent once you start thinking about authorization of resources, it is not a trivial problem to know to which resources a client should have access to or not. And while it is true that you can model provenance, authorization and all kind of other niceties in SPARQL as well, you will likely be on your own building all those tools yourself. I believe SPARQL is a great query language for public / open data, e.g. Wiki Data, Government data, etc, but in a business context I'd rather choose REST or GraphQL over it.

ipnon 6y ago

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

droopyEyelids 6y ago
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'
- falcolas 6y ago
  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.
keanebean86 6y ago
Considering the number of times I've had to remind coworkers about sql injection during code reviews I agree.
neonological 6y ago
I mean the guy is front end. Front end development doesn't involve any security.
- ipnon 6y ago
  I think you would be surprised how quickly and easily an attacker can automatically export vulnerabilities in web applications with programs such as Ffuf and SQLmap.
  - neonological 6y ago
    Not disagreeing. I'm just saying people on the front end never actually deal with security, hence this is the mentality that is the instigator of the suggestion of using SQL on the front end.

BiteCode_dev 6y ago

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 that complexity is manageable. - do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ?

dragonwriter 6y ago
> do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ? No, you implement an exposed schema for each app (or possibly more granular) that consists of objects (mostly views, possibly sprocs) optimized for the planned access pattern of the app (or whatever client/component thr schema is for), including convenience views abstracting those things away to the extent appropriate for the use case. That’s been a widely known RDBMS best practice for decades.
dvdkon 6y ago
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.

dragonwriter 6y ago

> 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.

falcolas 6y ago
> 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.
- dragonwriter 6y ago
  > 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.
  - falcolas 6y ago
    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.

zozbot234 6y ago

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).

falcolas 6y ago
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.
leipert 6y ago
In my practical experience having worked with SPARQL and GraphQL, I would very much prefer GraphQL. SPARQL is really nice for querying graph data, but I'd argue that for most applications tree-like hierarchies are good enough. Also GraphQL and associated frontend libraries are written to be consumed by browsers and results are much easier to handle than results from a SPARQL query, JSON+LD is not the easiest format to handle. The tooling around SPARQL is not great compared to GraphQL either. Furthermore I'd argue that SPARQL queries are really hard to statically analyze / optimize on the Backend. Different SPARQL engines behave differently and whether you run your query against e.g. Stardog or Virtuoso can have vast performance differences. SPARQL being hard to analyze statically actually becomes apparent once you start thinking about authorization of resources, it is not a trivial problem to know to which resources a client should have access to or not. And while it is true that you can model provenance, authorization and all kind of other niceties in SPARQL as well, you will likely be on your own building all those tools yourself. I believe SPARQL is a great query language for public / open data, e.g. Wiki Data, Government data, etc, but in a business context I'd rather choose REST or GraphQL over it.