All these solutions have one thing in common though: they go to great lengths to place the blame for sql injections squarely at the feet of the web application developer. Granted, there are many defensive techniques which a developer should employ in order to foil sql injection attacks among other things, but in my opinion the fundamental security holes that make these attacks possible can only be fixed by sql database vendors themselves and not by web application developers. Let me explain.
There was a time when operating systems opened all ports on a computer by default, when smtp servers allow anyone to relay mail through them, when dns servers allow anyone to submit recursive queries and perform zone transfers. By the by, all these features were found to be exploitable holes and blocked by all modern software. However, it is not just that these holes are now commonly blocked — what is vitally important is that they are now commonly blocked by default.
This attitude of being secure by default has proven time and time again to be the correct one to take whenever an exploitable feature is identified or recognized. It has prevented countless unanticipated attacks against misconfigured computers while at the same time allowing experts to open these holes under controlled conditions when necessary. The problem with sql injection attacks today is that the insecure features that make these attacks possible have not been recognized as such even though they have been identified for quite some time.
What are these features, you ask. There are two of them and for the purposes of this article I will refer to them as multistatement query execution (MQEXEC) and union query execution (UQEXEC). The first feature allows an sql database engine to execute multiple statements in one query. For example, when faced with a query such as
SELECT a FROM table1; DROP table2;
the database engine will happily execute both statements in the query one after the other. The second feature (UQEXEC) allows the database engine to combine the results of multiple substatements into one dataset. For example, when faced with a query such as
SELECT a,b FROM table1 UNION SELECT c,d FROM table2;
the database engine will happily combine the results of each select substatement into one dataset (subject to certain restrictions which are not relevant here).
The problem with these features, and the reason they are such a huge security hole in today's computing environment, is that they allow an attacker to modify queries in web applications in unforeseeable ways with devastating consequences for all parties concerned. For example, it is quite natural for a web application developer to write a query such as
SELECT a,b FROM table1 WHERE c = '+ParameterValue;
along with a text box that allows the web application user to enter the parameter value. But an attacker can instead enter a parameter value that will turn this simple query into a multistatement query with malicious side-effects. Many pundits will no doubt blame the developer for writing a query like this in the first place. Yet that is not really the problem here. The problem here, really, is that when the developer wrote this query, he or she wrote it under a very specific assumption and most database engines do not provide a way for him or her to require that this assumption be enforced.
The specific assumption made by the developer here is that this query will not ever be a multistatement query. Unfortunately for the developer, however, the sql dialect of most database engines does not provide any way for the developer to make this assumption explicit or to require its enforcement. Even worse for the developer is the fact that these database engines operate in a mode that enables the MQEXEC and UQEXEC features by default — thereby making the attacker's task tremendously easier than the developer's, with the expectation that the developer should work around this sorry state of affairs in some way.
But suppose for a moment that sql database engines offer ways to selectively enable the MQEXEC and UQEXEC features e.g. in Microsoft SQL Server, one might have the statements
SET {MQEXEC|UQEXEC} {ON|OFF}similar in many ways to the familiar NOEXEC option. Suppose further that the default mode for these options is OFF. Then the vast majority of sql injection attacks will be thwarted by default at the database engine level.
This recommendation has an additional benefit for security auditing in that by simply flipping these options to OFF, all areas in an application where multistatement and union queries are needed or assumed will fail unconditionally. Security experts can then review the code in these areas carefully and selectively enable the options as necessary.
With the MQEXEC and UQEXEC options OFF by default, the only sql injection attacks possible are those involving single statement, non-union queries. These generally tend to have less devastating consequences but can in any case be guarded against using application-level techniques such as query parametrization. But relying exclusively on application-level techniques to fight sql injection attacks is a recipe for disaster as real-world experiences continue to show.
