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?
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Row-Level - Inclusion of filter which nullifies other filters
or 1=1. RDBMS permissions systems are suitable for this: Views could be leveraged.
- Field-Level - Inclusion of column, which is individually non-permitted, but others are. RDBMS permissions systems are suitable for this: Views could be leveraged.
- 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.
- 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)