Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server injection: When firewalls offer no protection

This chapter will detail the basics of SQL injection and best practices for defending SQL Server against such attacks.

This excerpt is from SQL Server Security, written by David Litchfield and published by McGraw-Hill/Osborne Media. Read the entire chapter here.

Most companies that have an online presence these days will have a firewall of some kind. The purpose of a firewall is to filter network traffic that passes into and out of an organization's network, limiting use of the network to permitted, "legitimate" users.

One of the conceptual problems with relying on a firewall for security is that the firewall operates at the level of IP addresses and network "ports"—in the OSI model of network protocols, the firewall is a service at layers 3, 4, and 5 (network, transport, and session layers, respectively). Consequently, a firewall doesn't understand the details of higher-level protocols such as HTTP (Hypertext Transfer Protocol, the protocol that runs the Web).

There is a whole class of attacks that operate at the application layer of the OSI model (OSI layer 7), and that by definition pass straight through firewalls. SQL injection is one of these attacks.

SQL Injection Basics

The basic idea behind SQL injection is that an attacker manipulates data passed into a web application to modify the query that is run in the back-end database. This might seem relatively innocuous at first sight, but it can be extremely damaging. The following sections describe SQL injection in depth and some of the steps that can be taken to defend against it.

One of the most worrying aspects of SQL injection is that the most straightforward way of querying a database from an application almost inevitably results in some form of SQL injection bug. The mistakes that result in SQL injection are very easy to make, even if you are aware of them (and most web developers aren't).

Another difficulty faced by organizations that want to rid their infrastructure of SQL injection bugs is that most of the publicly available advice on fixing the problem is flawed in some way, or omits crucial information. For example, the most common "fix" is to replace each occurrence of a single-quote character with two single-quote characters, effectively "escaping" the single quotes. This doesn't completely solve the problem, for reasons we'll go into later in this chapter. First, we'll look at how several SQL injection bugs were found in a real application.

This chapter will detail the basics of SQL injection, and best practices for defending SQL Server against such attacks. Read the entire chapter here.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, SQL Server, DB2, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

Dig Deeper on SQL Server Security