Is it plausible to enforce authorisation on SQL generated from "Client-side"?

Question

Background

We know that SQL Injection (OWASP Top 10) is the most prevalent security vulnerability on websites today. The strategies for mitigating the risk are well known. But are these only reinforcing an imperfect/inefficient software architecture?

For me, working with MS SQL Server which had licensing limited to users, meant that authorization had to be moved into the Web Application layer to be financially user-scalable. Of course, I also wanted to present a RESTful API which SQL Server didn't include in the RDBMS directly. And one expects to be able to express deeper functionality in a web application layer in addition, instead of using stored procedures etc..

I have a post on softwareengineering.stackexchange about this: https://softwareengineering.stackexchange.com/questions/374085/do-we-build-authentication-in-web-applications-because-sql-server-charged-be-us

In my experience, one creates a RESTful interface which receives view-models which define the query and parameters; these are turned into SQL which runs on an RDBMS. So aren't Web APIs, therefore, an indirect serialization protocol for SQL? (Excluding business-logic considerations for now)

So what if we invert the whole problem? What if instead of carefully inserting parameters of static queries/views, instead, we allowed the client-side to pass the full SQL to the server?

---

Question

I hypothesize that it is plausible to develop a framework for "secure" execution of SQL received directly from a "client" authenticated user, that is:

• As secure as current techniques
• Relatively easy and practical to build/find/acquire tools/libraries

Ideally, a successful answer would falsify my assumptions, while being mindful of the seemingly successful ways of implementing this. However, I expect the successful answer will come from a well written and comprehensive confirmation of this idea, referencing external discourse and examples, linking to existing frameworks and designs, and perhaps some novel high-level design ideas.

---

Kicking off answer ideas

More details and ideas how it could work:

1. The server-side (middleware) could parse the SQL statement string in full into a memory-model of the query, enabling entity authorization checks. By parsing an SQL string in full (just as an RDBMS would) the security would be the same.
2. Direct web-interface into RDBMS which already includes SQL parser, users, roles, and entity security (if not row security directly or via views). This isn't a full solution though, see Background above. But perhaps Web Application software should use more of RDBMS inbuilt features, and more closely integrate.
3. A machine-readable model of SQL which is easier to be parsed by middleware. I do expect SQL to be transmitted from the client to the server, but in the end, it's the "expression" of SQL in the client that is sought. Parsing could occur in the client-side perhaps. This excludes client-side LINQ-like capabilities.
4. Discussion of the fact that SQL is like JSON, a serialization format. It's human readable. The SQL defines a specification, which the RDBMS should carry out (in its own way).

---

Clarifying what known threats need to be protected against (a good start)

The spectrum of SQL-Injection attacks to consider (thanks Steffen Ullrich), or in this case the "inclusion" in the SQL string of non-permitted data access and functions.

1. Entity-Level - Inclusion of non-permitted entities (Tables/Views) - All tables referenced in SQL MUST be permitted for the user. RDBMS permissions systems are great at this.
2. Entity-Level - Inclusion of non-permitted Read/Write commands on Entity (Table) - In addition, some entities may be read-only (SELECT), while some allow UPDATE or INSERT or DROP, or mixture. RDBMS permissions systems are great at this.
3. Row-Level - Exclusion of filter which contains rows the user is permitted to see. RDBMS permissions systems are suitable for this: Views could be leveraged.
4. Row-Level - Inclusion of filter which nullifies other filters or 1=1. RDBMS permissions systems are suitable for this: Views could be leveraged.
5. Field-Level - Inclusion of column, which is individually non-permitted, but others are. RDBMS permissions systems are suitable for this: Views could be leveraged.
6. Field-Level - Inclusion of sub-query in SELECT column-list. RDBMS permissions systems are suitable at this (as underlying entities are already protected)

I have highlighted that an RDBMS's permissions systems are generally suitable for handling non-permitted data access and operations. This shows that idea 2 above is at least valid, and therefore the whole theory is plausible.

However, if one needs even more complex permissions systems, RDBMS would become less suitable. Many have other reasons why direct RDBMS permission systems are not directly used, and so it isn't a silver bullet.

Therefore, the broader evidence is sought for ideas posted above, and perhaps other avenues should also be explored.

---

Repercussions of confirming my theory

If this is true, thick web-clients with their own business-layer would be enabled. Such a class of software system types would, therefore, receive more attention, consideration, and development. I believe such system types would be good choices for small projects for rapid development and prototyping.

Or further:

• Compared to the separation of 'code and data' this would be directly more expressive, and do away with the custom code required to be written between the client and RDBMS to handle view-models, static queries, and interpolation of query parameters.
• And as a result being, of benefit for web software making it quicker/simpler to build (More of a StackOverflow point)

Answer 1

You are essentially proposing to have the query written in domain specific language A and do a translation to language B (SQL) where the translation also thoroughly checks all data. This by itself is not new at all, only that commonly this DSL A are formular uploads, JSON or XML structures etc.

Having SQL is input DSL and also as output language with this transformation does not free you from thoroughly validating all input data. Insofar it does not make the task of proper verification any simpler. Compared to JSON or formular uploads it is probably even harder since SQL is a more complex language to parse properly. On the other hand the similarity of input and output makes it more likely that developers take shortcuts, i.e. pass the input simply through to the output without proper validation or with no validation at all.

Apart from that, the main problem behind most of the injections is having a mix of data (content) and execution (code) within the same "channel" without hard separation. This way content can "spill over" and mutate into code. This is true for XSS (content turns into Javascript), SQL injection (content turns into SQL commands) and also the old phone phreaking (sound turns into commands).

These injection problems are therefore best dealt with a clear separation of content and execution. For SQL this is offered by parameter binding which is therefore the recommended method against SQL injection. Similar the recommended method against (reflected) XSS is to forbid inline script by using a Content-Security-Policy. And signaling in telephony systems is also no longer done in-band to make phreaking impossible.

Thus I recommend against translating remote SQL to local SQL. You'll might though translate remote SQL to local SQL instructions with parameter binding. Only in this case you might still be vulnerable to having SQL injection on the client side, i.e. where the remote SQL gets constructed. Much better is instead to have a clear separation of code and data in all places, which means not using plain SQL (without parameter binding) anywhere.

Answer 2

Maybe. (At least for now, this answer is to lead others to provide something I may not have considered/seen yet).

Scenario

• An attacker enters something like select passwordHash from Users where ID = ID; drop table importantDocuments; and sends it to the database along with cookie or JWT bearer token
• The server-side parses the statement into a data structure. Let's assume this is the perfect data model which encapsulates SQL but in a data structure way.
  • The server detects an unauthorised field Users.passwordHash. That field could be removed from the query, or an exception thrown.
  • The server appends "ID = @UserID" to the SQL query data structure for the users table;
  • The server detects the unauthorized action on the Users table drop. That command could be removed, or an exception thrown.
  • The query data structure is converted to SQL with properly escaped data and executed (if an exception was not thrown).
  • Data is returned to the client

Analysis

• If the server-side is breaking down a query (just as an RDBMS would), it would be able to guarantee "Security", just like an RDBMS can.
• A user can send through something very basic, or very complex with attempts to bypass security, but fail.
• Multiple techniques can be used by the server to both validate and ensure a statement doesn't allow data the user isn't authorized to view or perform actions they're not authorized to perform.
• SQL Parameters may be best handled with the RDBMS-supported parameterized functions. But if a parser and SQL generator can be built once well, and with support from the RDBMS, what is stopping it from being perfectly secure?

Conclusion

If this kind of scenario/design is proven to be plausible, it would be very useful.

Why have coded generic RESTful APIs when you can just write the query for a view (for stable API) or table (internal development speed)? Particularly for CRUD kind of data-entry components/systems. Is the customization truly in the client-side of a web application these days?

I also wonder if RDBMSes could have such security features (or already do). Particularly those that go beyond the scenario above and into row-level, per-row-column-level, and row-state authorization.

I suspect the security industry has been too careful. SQL-Injection is practically the worst issue facing the web today; so the security industry is right to be very cautious. However, once realizing a method that is as secure, that makes software development faster, it's worth a deeper look, with plenty of scrutiny.

Answer 3

Is it plausible to enforce authorisation on SQL generated from “Client-side”?

Yes. (This is Direct web-interface into RDBMS mentioned in the OP.)

Impersonate the user directly on the destination relational database using JWT. see https://colossal.gitbook.io/microprocess/definition/data-web-gateway

(I made a rudimentary implementation of this in a day, and now I am building a comprehensive implementation that I will be releasing open-source.)