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.
Related: Protecting Your Organization from SQL Injection Attacks
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 above, 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:
- 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:.
- Change the ID of the label control that displays the logon result from Label4 to lblResult.
- Add two text boxes, and clear their default text.
- 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'
Because this query is partially commented out, the application ignores the password. The code will falsely identify the user as whatever user is returned at the top of the list of all users. Thus, as Figure 1 shows, the application considers this attacker authenticated, even though the username and password are invalid. Of course, this type of attack can be more complex and cause more damage than the previous simple example string does. For example, you could enter the following statement:
sadf ' OR 1 = 1 ; DROP TABLE USERINJ —
which does a better job of demonstrating the potentially destructive power of the SQL injection attack. This code not only bypasses the logon statement but also drops a table—in this case, the user table. Other actions an intruder might take include creating a new user in the logon table, querying against other known tables, or using SQL management queries to locate and define other tables. With the right user permissions, an attacker could use SQL injection to access your entire system.
As a DBA, you have only limited power to defend against this kind of attack. SQL injection takes advantage of two vulnerable areas: the data-entry field where the attacker enters and executes the injection command and the database permissions that you set. By securing both of these areas, you can limit the attack's success. Secure only one of these areas, and you run the risk of an intruder accessing, misusing, or destroying your organization's private data. To ensure full protection, you need to add layers of defense that limit the input your application accepts and use proper database permissions. That way, even if one part of your defense fails, another layer will continue to protect your data.
Screening User Input
Let's begin by adding a security layer that prevents an unwanted user from using applications connected to your database to insert manipulative SQL commands. In the example SQL injection attack I created, the intruder's input included more characters (some of them special characters) than a typical username or password would have. Screening this kind of input at the source is probably the easiest way to defend against SQL injection attacks.
In Visual Studio .NET, every text field lets you specify a maximum length. To set a field's length, simply select one of the text boxes in the form and find the Properties window, which is typically in the lower right side of the screen. In the list of properties, find maxlength, and enter a value greater than 0 to limit the number of characters a user can input. Limiting the number of input characters is a simple but important step in screening user input.
Not every form contains values that you can limit to a specific length, but you can also control input by forbidding or limiting special characters. ASP.NET provides a control that lets you screen user input. From Visual Studio's toolbox, you can add a validation control for each of your input fields. .NET provides several different validation controls in the Visual Studio .NET toolbox. When you use a validation control, you can specify that a given field should be numeric, for example. Validation controls are available for identifying required fields, comparing fields, and setting numeric ranges. You can even create custom validation controls. Additionally, Visual Studio .NET provides a control called ValidationSummary that you can use as the error display across several validation controls. Validation controls are great because although they're available for execution on the client, the final screening of user input occurs on the server. Having the server run or rerun every validation control ensures that a savvy user can't bypass the screening by manipulating the data and injecting code in a string after it leaves the browser.
The RegularExpression validation control is a control that lets you specify that an input string's characters fit within the definition of a regular expression. The following is an example of a regular expression:
^\[0-9a-zA-Z_\]$
This expression specifies that an input string can contain only alphanumeric characters and the underscore character. In this article's examples, these limitations would screen out the special characters in the SQL injection string I used. With this definition, the application server would reject the attack string. You can create similar regular expressions by using the information about regular expression language elements that Microsoft provides at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconcharacterclasses.asp or the syntax reference information on Jan Goyvaerts' site, Regular-Expressions.info, at http://www.regular-expressions.info/reference.html.
You don't have to limit your screening to the application's user interface. You can also include regular expressions in code. For example, the following Visual Basic .NET code creates a regular expression that checks for valid characters and for a maximum length of 10 characters:
Dim reg As Regex = New
Regex("^\[A-Za-z0-9_\]\{10\}$")
reg.IsMatch(TextBox1.Text)
The expression this code creates is one that you might use to validate usernames. Keep in mind that to add this code to this article's example, you need to include the full reference of System.Text.RegularExpressions unless you add the statement Imports System.Text.RegularExpressions to the top of the source file. .NET provides built-in support for regular expressions both at the control level and, more importantly, within your code. Even though you might not be able to eliminate special characters from every field, you can screen out especially suspicious characters—such as a double dash.
In some cases, you need to ensure that if a user submits special characters, your application will handle them correctly. As the example attack shows, the single-quote character is of particular interest because it lets an attacker close an open quotation, providing a spot to introduce a new string in your system's code. The solution in this case is to ensure that if a user types a single quote in a string, the new quote will be escaped—instead of treating the character as a typical single quote, the code will treat it as just another character in the string. To escape a single quote, you simply match it with a second single quote. An easy way to do this is to use the Replace() method on a string:
TextBox1.Text.Replace (" ' "," ' ' ")
This command should contain a single quote only in the first set of double quotes and a pair of single-quote marks in the second set of double quotes. This command replaces each instance of a single-quote mark with two single-quote marks, thus escaping them from the SQL execution sequence. You can escape other special characters in a similar fashion.
The accompanying challenge is that you need to place this character-screening command in your code. One option is to add a transform such as the previous command at the points where your application accepts user input—which might mean inserting the command in hundreds of locations in your code. Alternatively, you can execute this screening just before the code calls your SQL Server. Of course, if you haven't centralized your data access, then you might still have to screen at hundreds of locations in a large application's code.
Implementing character screening becomes easy when all your data-access commands go through one set of components, the Data Access Layer Components (DALC). The Microsoft Data Access Application Block is an excellent model for your data-access logic, but plan to customize it for your own situation.
Even if you've been using more than one set of data-access components, you can implement a design that funnels all your database calls through one set of data-access classes. In addition to forcing all your database access logic to go through a custom screening process, you can use the DALC to implement other best practices. For example, you can use the DALC to limit developers to writing code that accesses only stored procedures—not dynamic SQL. One of the primary requirements of SQL injection attacks is the use of dynamic SQL. Stored procedures don't suffer the same risks as dynamic SQL queries, because unlike dynamic queries, which SQL Server interprets on the fly, a stored procedure is partially interpreted in advance. When you submit dynamic SQL, you're submitting a string, which SQL Server then interprets from start to finish. However, a stored procedure works with named variables. Each of these variables holds a place for a value within your stored procedure's code. As a result, SQL Server can isolate the variables from the code and avoid inadvertently executing embedded values. So if a user attempts to submit an embedded command string as one of the parameters in a stored procedure, the stored procedure won't execute the commands.
Ensuring that stored procedures handle all your data access is one of the best methods of protecting your data from SQL injection attacks. Unfortunately, it's not foolproof. If your code uses the EXEC command along with the sp_ExecuteSQL stored procedure, you essentially reopen the security hole because you're permitting the passing of strings, which SQL Server then interprets.
One final way to prevent exposure is to be sure your database doesn't give potential intruders error messages that can help them bypass your default logic. An error message such as Username is correct but password is incorrect provides an intruder with valuable information. A simple Invalid logon message tells users that the credentials are invalid without revealing to an intruder whether the username was valid.
At a more complex level, handling error messages correctly means preventing your system from returning actual SQL Server error-message text. By default, ASP.NET doesn't return such error messages to remote connections. Your application should politely inform users that an error has occurred, but users should never see the original message associated with an invalid query, which might contain the submitted query. Developers working on Web projects sometimes add code that returns a submitted query in the error message so that they can debug why the query didn't work. However, the error message can help a hacker debug how to attack your system.
The solutions I've shown you so far focus on how to design, configure, and implement your application so that it doesn't expose your database to an attack. Now, let's look at solutions you can implement in your database's configuration.
Limiting Database Permissions>
The first step toward database security in most developers' minds is protecting the connection string. None of the solutions I've suggested thus far have mentioned the connection string because the connection string isn't part of this attack. The application already has access to your connection string; the injection attack works by manipulating the data that the application sends across that connection to your database. Although you might hide the connection string under several layers of encryption, you still need to presume that the connection string can fall into enemy hands. What have you done to limit damage if that occurs? You need to set up the accounts users employ to access the database with only the permissions they need to access the stored procedures the application uses.
This is where database roles come into play. Many developers would prefer to simply use the sa account for easy access during development, but I hope you've not allowed the production application to have sa permissions on your system. Some developers create a custom account for development, but then grant that account full permissions or a broad set of permissions such as the built-in SQL Server db_datareader and db_datawriter roles. These roles are better than the alternative of using an account with administrator privileges, but in most cases, they provide only limited protection against the ravages of SQL injection.
When setting up your application's database permissions, start with a custom role. Notice that I didn't say "account." Over time, accounts might change or the type of account (SQL Server or Windows) might change, but if you've tied your application permissions to a role, these details don't matter. Using a role means that when an account needs to change, you just add a new account to the role and remove the old account.
Sometimes, developers try to create one role for reading from and one role for writing to the database. This simple approach might sound good, but a better alternative is to create one role that has permissions to access only the stored procedures for your application. With only one role, if the account is compromised (either explicitly or implicitly), you can ensure that the only objects an intruder can reach are those that the application can legitimately access. Allowing direct access to the tables in your database means that if an account is breached, so is every column in the tables. When you allow access only through a stored procedure, the application might be able to insert information such as a credit card number, for example. But if SQL Server doesn't have a stored procedure to return a credit card number (and it shouldn't), intruders won't get a credit card number.
Keeping Up with the Bad Guys
No security solution is guaranteed, so you have to keep up with events that are occurring on your server. Set up alerts for specific errors that shouldn't be occurring, and if they occur repeatedly, investigate further so that you can see if someone's trying to leverage an application's vulnerability. In general, think of security in layers. I suggest that the next layer below what I covered here is how to protect the data once it's in your database. For example, encrypting important data such as credit card numbers is a good idea so that even if intruders find a path to your data, they don't also acquire the keys for unlocking it.
The SQL injection attack can be devastating when you consider how much data might be compromised. Don't wait for someone to drop your customer table or retrieve all the customer credit card numbers you have stored in your database. Take the steps to ensure that your application is secure from this attack.
