3

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)
  • How do you determine which queries the client is allowed to execute? That's the difficult bit, and you don't say anything about it! – Gilles Jul 12 '18 at 6:44
  • @Gilles I do, in the "Kicking off answer ideas" section, one way is to parse the SQL fully (and reliably and therefore securely). Another could be to directly query an RDBMS like PostgreSQL which has a built-in security system for this. I wouldn't say it's "difficult" but perhaps it's not something people tend to normally thinking about. And hence this question. – Todd Jul 12 '18 at 7:24
  • I was expecting substantial "against" posts from those who are very well trained in SQL-INJECTION threats. Your posts are valued, and I will do my best to rebut, and if successful will strengthen the credibility of my hypothesis if it survives. I do also hope to find some people who see problems and also solutions (with references) in the same frame, with an optimism for the theory. – Todd Jul 12 '18 at 13:00
  • Here's an Sql parser for c# and java: dpriver.com/blog/… – Todd Jul 15 '18 at 11:03
  • Of course antlr is highly recommended for generating any parser from a defined grammar: antlr.org – Todd Jul 15 '18 at 11:22
3

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.

  • Thanks Steffen. I'm thinking high-level here. For example, all RDBMS's might implement a Web API just like PostgreSQL (apparently) do. Web developers would leverage that. As a middleware, the SQL could always be passed on, the parsing is only there to check security and check for SQL injection. That is, a secure and correct (perhaps open source) SQL parser would for example detect when a SELECT statement contains other statements. You have added a lot to think about, but not necessarily strongly confirming nor disproving. – Todd Jul 12 '18 at 7:31
  • 3
    @Todd: "... the parsing is only there to check security and check for SQL injection" - SQL injection means a valid but unintended SQL statement. This means there is no generic detection of SQL injection which works just by looking at a given SQL statement - it always needs to know in detail which kind of SQL instructions should be allowed - which is specific to the application and probably also specific to the current context inside the application. – Steffen Ullrich Jul 12 '18 at 7:39
  • That's good point, thanks for expanding on that. Given a simple SELECT statement, how could an intermediate checking system know if a filter parameter such as 1=1 was added. That is, Injection isn't always a drop table statement, sometimes it just defeats filters. I still believe it's possible though, and I will update my OP to clarify - they will be numbered i-iv initially. – Todd Jul 12 '18 at 12:35
  • Correction, I added the Clarifying what known threats need to be protected against section. This demonstrates that it is possible directly with RDBMS. Also, ...which is specific to the application... - Web apps will generally have a users/roles system. There is certainly no unconfigured generic security model anywhere that could secure any remote access. It's a matter of configuring or specializing the security layer, wherever it is. Therefore, it's not a strong point to falsify my hypothesis. – Todd Jul 12 '18 at 12:57
  • @Todd: one argument you use is that the build in user based access control for the database could be used. This assumes that a every different remote user is also a different database user, which is not the case with most web applications. But if you have a fine granular access control within the database already using the remote users, then SQL injection by itself is less a problem since in theory any user can only do what the access control allows. In this case your question essentially asks if the access control of databases are powerful enough to let users speak directly SQL with the db. – Steffen Ullrich Jul 12 '18 at 13:08
0

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.

  • Take GraphQL for example. It uses JSON? between the client and the server, and it is a fully fledged query language. So that JSON is read and validated for security (then SQL is generated and run). So if a server can read SQL and validate and run, why can't our client-side applications write direct SQL? It's already a query language, just extend the authorisations checks into middleware and even front-end software. – Todd Nov 14 '18 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.