Notes on Securing your Web Application Database Calls (Part 1)

A common part of writing a Web-based application in any programming  language is setting up a SQL query and adding parameters to it. Although most of us have been doing this in one form or another for many years now, there are serious consequences involved that every developer should be aware of.

I’m planning this two-part article as: part 1) a general review of the hazards facing all Web-exposed applications and in part 2) a review of  properly handling hand-coded SQL parameters in ASP.NET.

The Main Problem: Preventing System Compromises

One of the most common concerns with passing parameters to the database is preventing SQL injection attacks. This form of attack has grown increasingly sophisticated and automated over the past few years, but the basic principal remains the same.

Additional attacks that you need to be aware of are buffer overflow and Cross-Site Scripting (XSS) attacks.

Typically an attacker will use an automated tool to probe your system for vulnerabilities. The most basic vulnerability that an attacker will probe for is usually an HTML field that is passed unescaped to your database as part of a query.

Script Kiddie Attacks

The majority of attacks are done by so called script kiddies via automated tools. They typically have little understanding of code or systems and attack any target that seems vulnerable. So if you harden your site to at least deter this type of attacker, then you have already protected yourself against a large source of problems. If you are interested in reading more, here is an interesting essay to profile script kiddies.

The Problem with Third Party Components

Sadly, to complicate things, the area of  attack vulnerability on most Web sites is widening significantly since Web development increasingly involves using third party plug-in components, many of which are not regularly patched. These components allow rapid site creation, but their functionality remains hidden to the site developer. However the component can be fully explored by malware creators when they are developing their systems.

Probably the simplest rule of thumb in avoiding exposing your database to SQL injection attacks is to declare each of your parameters as fully as possible.

Microsoft provides some useful point-form advice for securing your web applications that is worth reading. If you are looking for more in-depth reading on architecting secure Web applications, MSDN has good reading material covering the subject. Also don’t forget to check out the Open Web Application Security Project for pointers and guidelines.

The rules of thumb are to write your Web application securely:

1. Always use parameterized queries. Use parameters when calling stored procedures or free text queries. This is the topic I will look into further in part two of this article.
2. Use stored procedures rather than free-text SQL in your application
3. Limit the permissions of the User/Group that your Web application uses to access the database. This involves making sure to set execute-only permissions for stored procedures the user account that will be used by your Web application.
4. Validate and escape any input that us being provided by a user.
5. Stop buffer overruns. This sounds complicated, but it’s actually quite simple. When you are passing a parameter, make sure you specify both the type of data that you will be passing as well as the maximum length.
6. I would add two further points:
   1. Limit the use of third party components as much as possible.
   2. Keep as much of your data on your own servers as possible: why on earth would you give a virtual stranger all of your data for safe-keeping? The “cloud” can save you money, but be aware that you have just opened up a whole new can of worms as soon as your corporate data is off-site.

Testing Your Web Application

Once you have hardened your Web application as much as possible you should test your changes to make sure you have actually succeeded at preventing attacks. To ignore this step is a mistake many developers and companies in general make.

I continually see developers and managers putting zero emphasis on actual vulnerability testing, which is delusional thinking to say the least. Once the Web application has been written and tested, it is usually rushed to production and then work begins on the next big project.

The typical thought pattern for a  programmer is that they have written the application using best practices so naturally their new application should automatically be invincible. Conversely the company wants to start making money immediately so as soon as the application is functional and tested for bugs, it is considered ready to go to market.

Ignoring vulnerability testing is a type of flawed development life-cycle that certainly explains the success of  Script Kiddie type attacks that are have been increasing in frequency over the past few years.

The fact is that it’s really quite simple to test an application’s vulnerability using automated software. There are a large number of tools available that you can use for this purpose, many of them are even free to use.

The best list of tools that I have been able to find is at the Web Application Security Consortium‘s site.

Closing Thoughts

If you follow the general best practices security guidelines and actually test your Web application for vulnerabilities, then you will significantly harden your Web application against many common attacks. This is especially important in preventing the most common source of attack: a Script Kiddie using automated tools.