16.3 SQL Injection Defenses

As a side note, the usual packet-filtering firewalls won't protect you from SQL injection attacks. They simply lack the application intelligence to know what is going on beyond opening port 80 for web traffic. This is the case for many application-level attacks, such as SQL injection. Network intrusion detection will help, but it will not serve as magic "silver bullet" in this case. There are too many different forms and strings of such attacks to be encoded as an effective signature set. Additionally, if a target site is running SSL, you can evade the IDS by simply moving all the attack activities to TCP port 443 from port 80, which will likely hide all malfeasance.

We will categorize defenses into three main types, as described in Table 16-6.

Table 16-6. SQL injection defenses

Defensive approach	Description	Examples	Counterattacks
Obfuscation	Complicating the attacks by not providing the attacker with any feedback needed (or rather desired) for locating the SQL injection flaws	Generic error messages, limiting database output	"Blind" SQL injection [6]
Using stored procedures instead of dynamically built queries	Trying to avoid building queries from SQL commands and user input by replacing them with database stored procedures (conceptually similar to subroutines)	Use of sp_get_price( ) instead of "SELECT * from price"	Recent advanced SQL injection techniques can inject parameters into stored procedures
External filtering	Trying to only allow legitimate requests to the database (SQL shield) or the web application itself (web shield)	Web firewalls such as Kavado, Sanctum AppShield, etc.	Innovative injection types are not caught by the filter
Correcting the code flaws	Sanitizing the user input so that no SQL can be injected	Use of PHP routine is_numeric( ) , aimed at checking the input	Not possible, provided the input is sanitized well

^[6] A SQL injection type where the user receives no feedback from the application but still manages to accomplish the attack goal.

We will start by covering the relatively less effective defenses, which involve trying to sweep the problem under the carpet rather than solving it.

16.3.1 Obfuscation Defenses

Security by obscurity, or trying to make the controls opaque and hard to understand, is demonized by most security professionals. The important aspect to understand is that security by obscurity is not inherently evil; it is simply poor practice to make it the only defense against the adversary. It's obviously a "good security practices" if the application does not provide unnecessary information to the attacker in addition to being coded correctly.

Unfortunately, skilled attackers have successfully penetrated obfuscation defenses against SQL injection. Such defenses will easily foil simple attacks, such as by adding an apostrophe to the web application URL. The probing methodology of such attacks relies on seeing a response from a web application or even, in some cases, directly from the database. The application might therefore be coded to always provide a generic error page or even to redirect the user back to the referring page. In this case, searching for holes and determining whether an attack succeeded becomes a nightmarish pursuit for the attacker. However nightmarish it is, though, it can be done. Attackers have developed sophisticated probing techniques (such as relying on timing information from a query or a command) to indirectly determine the response of the new injection strings.

Overall, the specific tips for thwarting obfuscation by "blindfolded SQL injection" lie outside of the scope of this book. Some excellent papers on the subject are listed in Section 16.5 at the end of this chapter.

16.3.2 External Defenses

The legend of a "magic firewall," a box that just needs to be turned on to make you secure, continues to flourish. However, there are certain solutions that can protect you from poorly written database-driven applications that are vulnerable to SQL injection. Remember that the attacker interacts with a web application through a browser via a remote connection. Next , the application sends a request for data to the database.

You can try to block the attacks between the attacker and the web application, between the application and the database, or even on the database itself. The conspicuously missing defense ”fixing the application ”is covered in the next section. Possible defense methods are provided in Table 16-7.

Table 16-7. Application blocking

External defenses	Position	Description	Counterattacks
Web shields	Between the client and the web application	Try to filter out the suspicious URL requests to the web application in order to block the attack before it reaches the application.	As with all signature-based technology, one can try to sneak through by crafting yet another URL after a thousand failed attempts; it just might work.
Web scanners	Between the client and the web application	Run the attacks against the application, check their status, and reconfigure the web shield to block them more effectively.	Same as above.
SQL shields	Between the application and the database	Similar to web shields, this defense looks at all the SQL traffic and analyzes it using signature- and anomaly-based techniques.	As with web shields, such a filter may probably be bypassed by patiently trying various attack strings.
Database access controls	On the database	Only allow the minimum needed privileges to the web applications so that no extraneous tables and other structures can be accessed.	Usually, the database access controls cannot be granular enough to block all attacks.

Overall, trying to fix the application problem by dancing around the issue with various tools works to a certain extent. Filters, scanners, and stringent access controls do make the web application harder to hit by SQL injection. These solutions are cost-effective (and may be the only available option) if there is no way to modify the application. Additionally, they provide the needed in-depth defense for database-driven applications. After all, bugs happen, and even the best applications are known to contain errors.

16.3.3 Coding Defenses

The only true defense against SQL injection is "doing things right." As we mentioned in the very beginning of this chapter, SQL injection attacks are successful when the user input is allowed to unduly influence the SQL query, such as by adding parameters or even entire queries to the command. Thus, the user input need to be cleaned. But what are the available options?

First, if the type of user input is well known, the application should only allow that sort of data in the input. For example, if a required field is numeric, the application should not allow anything but a number. The options include rejecting anything else or trying to convert the input to the appropriate format. This is the "default deny" policy, which is always a good security decision.

Second, if the user-input type is not well known, at least what should definitely not be there might be known. In this case, you will have to resort to the "default allow" policy by filtering quotes, commands, or other metacharacters. ^[7] Some of the filtering decisions can be made for the entire application (never pass quotes to the database) and some depend upon the input type (no commas in the email address).

^[7] Metacharacter is a common term for a nonalpahnumeric symbol: i.e., `, #, $, /, etc.

While writing an in-house, database-driven application, or when deploying an open source application, it makes sense to pay attention to such issues and to design the proper input verification. This measure alone will help protect you from SQL injection attacks so that you won't end up as an example in some security book, like PHP-Nuke did (see below).

In order to make life simpler, small snippets of code exist for many of the web application languages. Here is a blurb of PHP code, reported on the mailing list (http://www.securityfocus.com/archive/107/335092/2003-08-24/2003-08-30/0), which can check whether a variable is a number. The code rejects all non-numeric input.

 function sane_integer($val, $min, $max)  {     if (!is_numeric($val))       return false;     if (($val < $min) or ($val > $max))       return false;     return true;  } 

Being aware of coding defenses is important even if you are deploying a commercial application. Just keep in mind that the developers likely made errors and that you will have to take steps to compensate. Such a practice is prudent even if there are no publicly reported vulnerabilities in the application.

16.3.4 Conclusion

Overall, it makes sense to combine several of the above techniques. For example, a well-designed and properly deployed application will do the following:

• Not return any informative error pages; a redirect or a generic page is sufficient

• Sanitize input as much as possible, preferably not allowing any input directly in queries, even if sanitized

• Have a database configured based on a least-privilege principle, with no extraneous access

• Be penetration tested and scanned by a web application scanner on a regular basis

• Be protected by a web shield for layered security

The above might sound like overkill, and we admit that it probably is overkill for a personal site. However, if your business depends solely on a web site, then those excessive measures and the extra expense suddenly start to sound more reasonable.