Tip

SQL injection detection tools and prevention strategies

In my last article I looked at the threat of cross-site scripting (XSS) attacks and methods to thwart them. In this tip, I want to look at another injection-based attack that has been making the news for some time: SQL injection. What it is, how to prevent it and the tools available to assist you.

In the U.S. quite recently, hackers used a SQL injection attack to break into the 7-Eleven Inc. and Hannaford Brothers Co. networks and steal credit card data. Although the same hackers used network sniffers to capture card data from Heartland Payment Systems, it was a SQL injection attack that led to the initial compromise of their servers, allowing the attackers to plant their malware. SQL injection attacks represent a serious threat to any website with a database backend and require nothing more than port 80 to be open, often working even if a system's patches are up to date.

Although the risk from SQL injection attacks has been well documented, you can see they are still providing hackers with plenty of opportunities to access data or take control of a compromised machine. The methods behind these attacks are easy to learn, and hackers have automated them to expedite the process of finding and exploiting vulnerable websites. This means that unless you take protective measures, like employing application security best practices or using SQL injection detection tools, the site is much more likely to be uncovered and exploited.

What is SQL injection?
SQL injection is a form of attack that takes advantage of applications that generate SQL queries using user-supplied data without first checking or pre-processing it to verify that it is valid. The objective is to deceive the database system into running malicious code that will reveal sensitive information or otherwise compromise the server. By modifying the expected Web application parameters, an attacker can submit SQL queries and pass commands directly to the database.

Many webpages take input from users, such as search terms, feedback comments or username and password, and use them to build a SQL query which is passed to the database. If these inputs are not validated, there is nothing to stop an attacker inputting malicious code, for example, that could instead instruct the database to delete a specific table of client records.

Getting the SQL syntax right is not necessarily so simple and may require a lot of trial and error, but by adding additional conditions to the SQL statement and evaluating the Web application's output, an attacker can eventually determine whether, and to what extent, an application is vulnerable to SQL injection. If the code achieves an immediate result, it is an example of a first-order attack. If the malicious input is stored in a database to be retrieved and used later, such as providing input to a dynamic SQL statement on a different page, it is referred to as a second-order attack. Second-order attacks can be very successful because once data is in a database it is often deemed to be clean and so is not revalidated prior to use.

SQL injection prevention tips
A simple test to see if your site might be susceptible to SQL injection is to...

Requires Free Membership to View

...enter the quote character (') in a Web form or query-based URL to see what type of error message you get. The trailing single quote will confuse the database if it is not handled correctly.

SQL injection needs to be tackled on many levels, but the key defence is the use of parameterized stored procedures. This is where requests to the database are made using parameters and user-defined subroutines instead of building SQL commands using values supplied directly by a user. Parameters are not only type safe (ie they are in a correct format), but they greatly reduce the likely success of a SQL injection attack. If access to the data is only ever permitted via stored procedures, then permission for users to access data does not need to be explicitly given on any data tables.

Even if you are using parameterized stored procedures, your application still needs to validate and sanitize all data inputs, whether it is supplied by your users, authenticated customers or read from a cookie. This means checking that the data is of the correct type, length, format and within an expected range. Any data that is not well-formed and correct should be rejected.

Many applications sanitize input by filtering out known unsafe characters such as "<" and "/". However, this is not best practice because malicious users can usually find an alternative means, such as character encoding, to bypass this type of validation. Instead, your code should check for known secure, safe input. This validation needs to take place on a trusted server, not on the client. Only once this validation has taken place should data be passed on to your scripts or database. These checks also apply to data received from internal applications or entered or edited by internal users; you have to assume all data is from an untrusted source.

Web applications should never run with administrative privilege at the server level or at the database level, otherwise an attacker could potentially perform any task available to an administrator. Run the application with the least privileges that are necessary for it to function, and the database with permissions set to only the essential resources. That way, an attacker is confined by a limited set of permissions, should he or she succeed in bypassing your defences.

When configuring the accounts that are used to access your database, it is important to apply the principle of least privilege. So, for example, if your application only reads data from a database, remove the insert, update and delete permissions for the database account used by the application. If it only needs access to a product catalogue database, make sure the account has no access to the order history database. Ideally, any accounts used will only need permission to execute specific stored procedures, and you can delete any stored procedures that are not used, such as xp_cmdshell and xp_sendmail.

Any sensitive data stored in your database should be encrypted, such as personal details or user login passwords. This type of data can be stored as a salted hash. A salt is a random set of characters which is added to data before calculating its hash. While it still may be possible to determine a password by dictionary attack, a unique salt for each hash causes an attacker to recalculate the dictionary for each user's password, severely disrupting the attack.

Another aspect of application design and development that is often poorly executed and allows potential attackers to finesse their SQL injection code is error handling. Many applications display a detailed error message containing information about the structure of the application, network or database; an attacker can use this to stage further attacks.

Error messages are therefore useful to an attacker because they give additional information about the database or system that might not otherwise be available. Hackers typically test for and find SQL injection vulnerabilities by sending the application inappropriate input to try and generate an invalid SQL query. If the server then returns an error message, the attacker can use information gained from these error messages to refine his or her attack. As coding errors are always a possibility, you need to make sure your applications have a safe mode which they can return to if something truly unexpected occurs. By all means, log any errors for your own records, but make sure your developers use a structured exception handler, like try {} catch {}, and that all debug error handlers have been removed from the production code.

SQL injection detection tools
As SQL injection attacks exploit vulnerable Web application and database code, the only way to prevent them is to resolve your code's vulnerabilities. Any place that code dynamically generates a SQL query using data from an external source should be closely checked. On larger projects, you should look at using automatic source code scanning tools and Web vulnerability scanners.

A good Web vulnerability scanner will spot common technical vulnerabilities, such as SQL injection flaws, cross-site scripting vulnerabilities, parameter tampering, hidden field manipulation, backdoors, debug options and buffer overflows. If you use any third-party applications that utilise a database back-end, it's vital that that you follow any vendor updates regarding vulnerabilities and patches to ensure the new code isn't introducing vulnerabilities into your own system.

Even if your database administrators and application developers are following best practice, I would still recommend the deployment of an application-layer firewall or Web application firewall (WAF). WAFs can provide protection beyond that of traditional network firewalls and intrusion detection/prevention systems. Many, like those produced by Imperva Inc. and Barracuda Networks Inc., can help prevent attacks such as SQL injection, cross-site scripting and others that target flaws in application logic or technical vulnerabilities in software.

Best-of-breed WAFs can recognise evasion techniques exploited by attackers using SQL injection, such as obfuscating the attack by encoding portions of the injected command. Your chosen application-layer firewall should also allow you to create filters to intercept, analyze or modify traffic specific to your network.

Filters make it easier to adapt the firewall to protect assets or monitor traffic specific to your network. Even better if it has the capability to "learn" what is and what isn't normal traffic for your specific network and adapt its behavior accordingly. When irregularities are detected, the WAF can shut down potential attacks while they're happening. Also as SQL injection often takes place via the URL query string, you should regularly review your Web server's logs to look for anomalous queries that may be injection attempts.

Having spent time ensuring your Web application is robust, you should also conduct a penetration test. By simulating an attack, you can evaluate whether your application and its defences still have any potential vulnerabilities resulting from poor or improper system configuration, hardware or software flaws or weaknesses in the perimeter defences protecting the site.

Also remember to configure your system to receive and review messages from Google, which automatically sends badware alerts to the following email addresses at domains where its Web crawlers locate a problem:

  • abuse@
  • admin@
  • administrator@
  • contact@
  • info@
  • postmaster@
  • support@
  • webmaster@

And you can, of course, proactively determine if you have a "badware" problem by using Google's free Webmaster Tools.

SQL injection attacks have been shown to be lethal. They can be used to collect or destroy data held in a database as well as be used as a launchpad to delve deeper into an organisation's network and systems. By implementing some basic defences, you make your database a lot harder to crack.

About the author:
Michael Cobb, CISSP-ISSAP is the founder and managing director of Cobweb Applications Ltd., a consultancy that offers IT training and support in data security and analysis. He co-authored the book IIS Security and has written numerous technical articles for leading IT publications.

Email Alerts

Register now to receive ComputerWeekly.com IT-related news, guides and more, delivered to your inbox.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in November 2009

 

COMMENTS powered by Disqus  //  Commenting policy

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.