| Downloads |
|---|
| 21268.zip |
Designing secure applications is more about good planning than anything else. So before you begin that new project, think it over carefully. With application-level intrusions becoming increasingly common, you need to keep attackers from exploiting your code and circumventing your expensive firewalls and other defense systems. To write secure SQL Server—based applications, you need to know who is accessing your database, with what privileges, and what type of data they're accessing. You need to be aware of what code is making its way back to the server at every tier. With thorough planning, well-tested code, and a dash of paranoia, you won't find yourself sitting in front of the senior managers answering questions about why your code is to blame for the latest hack attack. And you might just be able to catch a few winks.
Use the Principle of Least Privilege
If someone in your office needed access to the supply closet, would you also give them the keys to the executive offices and the petty cash drawer? I doubt it. So why do so many SQL Server applications use the sa account or the system administrator role for everyday connectivity? I blame laziness. Using sa or a sysadmin-level account means developers need never worry about who has which permissions or rights to perform the tasks inherent in any application. But you don't have to give up; you can give out the right permissions and have good security, too.
When you're developing a new application, think about what rights the application will need at runtime. Using administrative privileges temporarily at installation time is OK because the typical application needs to create the databases, tables, and other objects that the application needs. However, after installation, the application shouldn't require such broad rights to perform operational tasks. You need to take the time during installation to prompt the user for (or create) a runtime account and a password so that the application can run with a lower level of privilege.
After you create the user account, create a script that will let the user access the necessary database objects. Listing 1 contains a sample script that you might execute during installation to set up a low-privilege account and give it minimal rights. This script adds a new login, creates the user account, adds the user to a role, and assigns permissions to some database objects. For demonstration purposes, I added the user to a database role, but the average user probably won't need this level of privilege.
With the integrated security model, you don't need to create a SQL Server login; you just grant SQL Server access to an existing Windows account. By using integrated security, you can avoid using mixed security mode, and you can eliminate the password required for SQL Server authentication and the dangers that come with storing the password. By dangers, I mean that when (notice I didn't say if) the latest Microsoft IIS source-code disclosure vulnerability hits, your SQL Server password won't just be sitting in global.asa (or wherever you put it), waiting to be plundered. If attackers can connect directly to your SQL Server, they can easily use the username-password pair from the connection string to go straight for the database. Integrated security lets the existing IIS user context access SQL Server, thus removing the application's dependency on a SQL Server account to perform necessary operations.
In an integrated model, you could modify the account-creation script to look like this sample to keep from having to store a SQL Server password in a connection string:
/* Create login in master.dbo.syslogins. */ EXEC sp_grantlogin N'SEAHAG\IUSR_SEAHAG' GO EXEC sp_defaultdb N'SEAHAG\IUSR_SEAHAG', N'pubs' GO
Many security-savvy administrators prefer this approach to the SQL Server account approach for several reasons, including centralized account administration, a more robust password system, and account-lockout capabilities.
Remember that the sa account or another login with sysadmin privileges has total control over SQL Server and can also perform OS tasks within the security context of the account under which SQL Server is running. For most installations, these extensive privileges mean that a SQL Server administrator can become LocalSystem, a local administrator, or even a domain administrator.
Also, don't think that making the user a member of the db_owner SQL Server database role is a safe shortcut. Keeping privileges low is like having an insurance policy. Even if you fail to secure other areas of the application, you won't inadvertently give an attacker enough power to destroy all you've built. A malicious user with db_owner privileges can drop tables and read and modify all the data in your database.
Validate Input Thoroughly
One of the most compelling reasons for using the principle of least privilege is that your application might someday have to do things it was never designed to do. For example, consider a typical search page in which a user types a book title as a search criterion. You might be inviting trouble if your data-access code looks something like this:
<%
Set Conn =
Server.CreateObject("ADODB.Connection")
Conn.open application("connection_string")
Set RS = Conn.Execute("SELECT author, notes from titles where title like
'" & request.form("title") & "'" )
%>
This access method is weak for reasons that I cover in forthcoming sections, but for now, let's consider the danger. A malicious user visiting the site might test your input-validation code by inputting a single quote (') into an input field. If the input validation is weak (and the error handling is poor), the attacker might get an error message like the one that Figure 1 shows. The message
Unclosed quotation mark before the character string ''.
tells an attacker that it's possible to input into the SQL command data that invalidates the SQL statement and that outsiders shouldn't be able to inject. A common term for this kind of attack is SQL injection, and the effects are dire. When attackers see the screen that Figure 2 shows, they can smell blood. They know that somewhere, someone isn't validating or filtering input. Here, an attacker found the hole that lets outsiders inject code and now has enumerated the SQL Server and OS version information.
Consider the earlier search-form example, but instead of a book title, type the following:
' UNION SELECT loginame, creditcardno FROM accounts--
With this simulated attack, a malicious user can subvert your search form to gain access to your users' accounts and credit card data. When someone inputs this code in your form, the single quote closes the username string, and the new SQL code is injected into the stream. The double dashes at the end of the injection code comment out any following SQL, so the last quote in the Active Server Pages (ASP) code doesn't cause an error message. Keep in mind that an attacker could inject even more dangerous SQL commands, such as DROP TABLE or the master..xp_cmdshell extended stored procedure, which gives access to the OS if the application is running with SQL Server administrative privileges. Take SQL injection attacks seriously—they're deadly and represent a target-rich environment for intruders.
So, can you do anything to stop such attacks? Of course you can. One simple thing you can do is enforce strong data typing. For example, if the application asks the user to enter a number, you can validate that a number was entered. For alphanumeric data entry, one obvious answer is to replace any single quotes in user input by using the replace command as follows in your application code:
replace(request.form("title"),','')
By replacing one single quote with two single quotes, you can still let users query for single quotes in the data.
In addition to the replace command, you can use the regular expression (regex) object to filter out unwanted data. The following regular expression filters out any character that isn't a-z, A-Z, or 0-9:
Set myregex = new regexp myregex.global = True myregex.pattern = "\W+" cleaninput=myregex.replace
(You can find more information about regular expressions and scripting engine requirements for their use at http://msdn.microsoft.com/scripting.) Rather than simply filtering out the invalid character, a more common implementation is to compare the unfiltered input with the filtered input. If they're different, you can simply short-circuit the process and alert the user that the input characters are invalid. No matter which method you choose, consider all the ways unwanted data could get into your database.
Use ADO to Enforce Strong Typing
Strong data typing is a valuable weapon in the fight against SQL injection attacks. Using the ADO Command object can help you ensure not only that you're passing the right data but that SQL Server can tell which part of a query is SQL and which is data. Let's look at two ways you might invoke a stored procedure. The following procedure is a call directly from an ASP page:
<%
Set Conn =
Server.CreateObject("ADODB.Connection")
Conn.open application("connection_string")
Set RS = Conn.Execute("exec
sp_checkloginrights " & param1 &
"," & param2 )
%>
This code is a poor example of invoking a stored procedure from Visual Basic (VB). The code uses a "string-building" technique that lends itself to mischief because of poor input validation.
The second call, which Listing 2 shows, is a sample of VB code that you can use to invoke the same stored procedure from a COM DLL. How does the second procedure call provide an added level of security? The answer lies in the fact that you're no longer "string building" as you are in the ASP code above. In string-building SQL calls, you construct a text string and pass it to SQL Server in one call. In the COM DLL example, however, you use the Command object to enforce your data types and let ADO build the string for you. The primary advantage of the VB-COM approach is that it catches any mistakes caused by front-end programmers not properly validating input.
Note that you need to handle errors in a way that doesn't give a potential attacker any clues about your table structure. Any database-related errors should return a generic error message and log the details for developers to analyze. Displaying ADO error messages gives the end user too much information and could be dangerous in the wrong hands. Also, never assume the people on the other tier are doing good validation: They're probably assuming the same thing about you. If you apply good validation at every tier, a neglected piece of code is less likely to expose your entire application.
Centralize Data Access
Now that you know the safest way to assemble your ADO queries to SQL Server, why not use a COM data-access layer to centralize the data-access routines? A centralized data-access strategy might not seem like a security concern, but unifying data access through a controlled set of components lets you more strictly control how users access data and helps ensure that you're following best practices. As a side benefit, if you find a more secure data-access method later, a centralized data-access strategy will ease the conversion because your application's other components don't need to be affected by any changes you make.
Figure 3 shows a high-level view of what your overall application architecture should look like. In this architecture, an ASP page calls a COM DLL method such as Basket.AddItem and passes some parameters. Your application should validate those parameters before passing them to a data-access component (another COM DLL) such as OrdersDB.AddLine. This COM DLL uses ADO to execute a stored procedure such as sp_lineitems_table_add on SQL Server. You might think that this process sounds like a lot more work than just calling SQL Server from your ASP page. The first time you write the necessary code, that assumption might be true, but these components are reusable, so you'll soon make up that time when you need to perform the same actions later.
If you've ever examined Microsoft's reference applications, such as Duwamish Books or Fitch and Mather (available at http://msdn.microsoft.com/code), you'll find the centralized data-access theme repeated over and over. Using a COM component is a good design practice for many reasons, one of which is that such a design lets you control access to SQL Server at every tier. And the more you centralize, the easier SQL Server is to secure and maintain. If complexity is the enemy of security, then centralized, simplified data access might be one of security's best friends.
Use Stored Procedures
In many ways, using stored procedures is like getting something for nothing. Stored procedures enforce strong data typing, allow for abstracted access to other SQL Server objects, and improve performance by precompiling your SQL code. I recommend you use stored procedures as much as possible: They give improved security with no performance penalty, a combination that doesn't come along often.
Despite the potential benefits, it's important to note that if you use stored procedures but still use string-building techniques to construct your queries, you're no better off than before. Stored procedures are most effective when you use them in conjunction with the other techniques in this article and employ the ADO Command object. If you simply assemble a string and pass it to the Connection object for execution (as in the first ASP example in this article), your system is just as vulnerable as it would be if you were using raw SQL. So remember to combine these strategies for effective security.
Here's a simple strategy I use when developing a new application. First, record all the ways you predict that users will need to access, update, or delete SQL Server data; then, code stored procedures for all the scenarios. If you're diligent, you can give users execute rights only to the stored procedures and never give them direct rights to any other database objects. But be sure to avoid breaking ownership chains. A broken ownership chain occurs when the creator of a stored procedure or view doesn't own the underlying objects. When SQL Server detects a broken ownership chain, it verifies that the person executing the stored procedure or view has the necessary rights to those objects. Making sure that dbo owns all database objects solves this problem nicely. The benefits of this strategy are obvious. Even a successful SQL injection attack might yield no fruit because the malicious user can't access the tables that contain the data.
Test, Test, Test
So you think you've done everything right, and you're ready to head out the door. Whoa there, my friend! If you're human, you forgot something somewhere. But when you're dealing with a finished application, how do you search for those chinks in the armor? You could approach your system the same way the attackers do: by trial and error. One method I use is creating a unique piece of SQL injection code, then pasting it into all my input fields while I run SQL Server Profiler with a filter containing my unique piece of SQL. By using this technique, I get immediate feedback about what SQL was passed to the server.
The testing phase is your chance to stay one step ahead of the crackers. You have all the code, you can trace all the events, and you have a firm grasp of the entire architecture. Put yourself in the position of the attacker and do your best to break in, using your deep knowledge of the internals of the application. Take advantage of your testing phase—a good quality-assurance staff can be a great asset in making sure an application ships secure.
Rest Secure
To keep your SQL Server—based applications secure, you need to be aware of what code is getting back to the server at every tier. Remember to limit user privileges, validate thoroughly, use ADO to maintain strong data typing, and keep your data access centralized. Use stored procedures as often as you can, and when you think you're through, test and test again. Get serious about securing your existing architectures—and maybe, just maybe, you can catch a few winks.
| Related Reading |
|
ARTICLES RAIN FOREST PUPPY "NT Web Technology Vulnerabilities" Phrack Magazine, December 25, 1998, Vol. 8, Issue 54 BOOKS MICHAEL HOWARD, MARC LEVY, AND RICHARD WAYMIRE Designing Secure Web-Based Applications for Microsoft Windows 2000 (Microsoft Press, 2000) STUART MCCLURE, JOEL SCAMBRAY, AND GEORGE KURTZ Hacking Exposed: Network Security Secrets & Solutions, Second Edition (Osborne, 2000) ONLINE RESOURCES SQL Server Security Web site http://www.sqlsecurity.com PRESENTATION DAVID LITCHFIELD "Web Application Disassembly With ODBC Error Messages" http://www.blackhat.com/presentations/win-usa-01/litchfield/bhwin01litchfield.doc |
