SQL Server Magazine

When defining your security implementation, your primary goal is to protect your organization's data. SQL Server, as the data store, becomes the center of your security universe. To secure your data, you've run the Microsoft Baseline Security Analyzer (MBSA), limited database communications, used Internet Protocol Security (IPSec) to encrypt communications, and possibly even added a separate firewall. But intruders still have one way to attack your database—through the applications it supports. Every security policy has a weak link, and by design, the weakest link in your security policy should be the Web application because it's the primary point of entry for outsiders; every other possible access path should be locked down. However, if you don't manage this entry point correctly, you can leave your data vulnerable to potentially devastating SQL injection attack. This type of attack can be extremely damaging because it lets intruders submit commands directly against your database.

You can minimize your vulnerability to SQL injection attacks by setting up layers of protection in your database. In this article, I explain how to set up two of these layers: screening user input and limiting user permissions. But before you can effectively defend against SQL injection, you need to understand how such an attack occurs. Let's start by creating an example injection scenario so that you understand what you're up against.

Anatomy of an Injection Attack
In SQL injection, an unauthorized user "injects" SQL commands in fields where your application expects data and uses the application's built-in database connection to access your data. Injection attack can happen anywhere a user submits data for use in your application, from a text box that's part of a Web form to what you might have thought was an unmodifiable browser query string. An attacker can begin with a logon page, a search page, or most any page that directly or indirectly accepts user input. A potential intruder can then initiate SQL injection from any application input field that doesn't properly screen user input before sending that input to SQL Server. I think the best way to illustrate this kind of attack is to use an example. For this example, I've chosen a simple logon page as the point of attack.

You can download the sample ASP.NET project at http://www.sqlmag.com, Instant-Doc ID 43012, copy the files to your development machine's default Web site, and follow along. Note that when you copy an ASP.NET project to a Web server after placing the application directory under your default Web site, you need to open the Microsoft IIS Manager and change the directory status to Application. Alternatively, instead of downloading code from the Internet, you can create a new project from scratch. To create the logon page that Figure 1 shows, use Visual Studio .NET to create a new ASP.NET project. (I used Visual Basic .NET as my implementation language.) Then, complete the following steps:

1. Drag four label controls onto the page, and change the default text for each as follows:
• Change the text property of the first label to Enter Your User Name and Password and adjust the font to 14-point bold.
• Change the text property of the second label to Name:.
• Change the text property of the third label to Password:.
• Change the text property of the fourth label to Result:.
2. Change the ID of the label control that displays the logon result from Label4 to lblResult.
3. Add two text boxes, and clear their default text.
4. Add a button, change its text to
   Login,
   and double-click the display to open the code-behind file for your default page. For this example, I use the ASP.NET code-behind that Listing 1 shows.

The connection string in Listing 1's code connects a local instance of SQL Server, which contains the sample Northwind database, to the sa account. You need to change the sa password to match the one you use on your local system. Next, you need to create this example's table in your database. For this demonstration, you can add a table called UserInj to your sample Northwind database by using the following T-SQL statement:

CREATE TABLE UserInj(UserId 
   varchar(50), password 
   varchar(50))

Be certain to enter at least one user into this table; you can use any name and password. The key to successful SQL injection is being able to run the application and log on with-out using valid credentials. For this example, I launch the attack from a Web form. As Figure 1 shows, although I've entered an invalid set of credentials, the page indicates a successful logon by welcoming the user named Bill.

I've purposely kept the password text box in Figure 1 set to echo the password to the screen so that you can see I've entered a string that obviously isn't an actual password. This simple SQL injection attack demonstrates that once intruders inject commands, they can cause your application to ignore whole sections of logic—such as password validation. I launch the attack from the string I've entered as my username:

sadf ' OR 1 = 1 —

This string uses two characters to manipulate the dynamic SQL in the logon process: a single quote and a double dash. The string starts with the garbage characters sadf to ensure I have something to use in the application's SQL statement so that the statement doesn't throw an error. The next character initiates the attack on the SQL query at callout A in Listing 1. The single quote mark, when embedded in a dynamic query, closes the string value that's part of the WHERE clause defined in the SELECT statement at callout A. The inserted string then adds a conditional statement (OR) to the SQL command, which is always a true statement (1=1). Finally, the string appends a double dash to comment out the remainder of the original command. The result is the following hacked SQL query:

SELECT * FROM UserInj WHERE 
   userid='sadf' OR 1 = 1 — ' AND 
   password='password'

   Prev. page   [1] 2 3 4     next page