A growing consciousness of security has brought Microsoft SQL Server database security into focus as never before. Planning for database security should begin early in the development process and there are important vulnerabilities that you need to prevent in your application's SQL Server.
There's been an intense focus on all aspects of security in the past year, and SQL Server security is no exception. It's no longer a compartmentalized concern; security is a concern for everyone. As a SQL Server developer you should be aware of the major security vulnerabilities of SQL Server. In this article you'll learn how to address the major vulnerabilities in your code, how Microsoft is adding security patches to SQL Server, and some best practices and tools you can use to assess your SQL Server's security.
SQL Injection
By far the most important vulnerability in SQL Server code is SQL injection.
By far the most important vulnerability in SQL Server code is SQL injection. SQL injection is an attempt to trick the SQL Server query engine into executing additional code not intended by the application. Any SQL-based database that allows dynamic code generation is vulnerable to this technique, not just Microsoft SQL Server.
Vulnerability arises when an application dynamically generates commands and sends them to SQL Server for execution. To exploit this vulnerability the intruder simply appends executable code to a text field that provides input for the query. Under the right conditions, an intruder can experimentally find the right combination of quote delimiters and comment marks to modify the current command or append an unwanted command. The results can range from returning otherwise hidden data to the user to letting the user execute commands that they would normally not be able to do from a form.
To understand how SQL injection works consider this simple query. This query selects the lastname and first name from the authors table using the city as search criteria. For the moment, let's ignore how the query is built.
SELECT au_lname, au_fname
FROM authors
WHERE city = 'Berkeley'
To inject unwanted code the intruder indirectly modifies the query so that it can actually execute something not originally intended, such as:
SELECT au_lname, au_fname
FROM authors
WHERE city = 'Berkeley'
UNION
SELECT name, password
FROM syslogins
The way the intruder accomplishes that is by appending a long string (without carriage returns) that contains the UNION clause.
What the intruder needs to make the injection work is to have either your client-side code or the Transact-SQL in SQL Server dynamically generate the code. Then there's a good chance it might work. Take a look at the following Transact-SQL example:
DECLARE @city NVARCHAR(2000),
@cmd NVARCHAR(2000)
SET @city = 'Berkeley''
UNION SELECT name, password FROM master.dbo.syslogins --'
SET @cmd = 'SELECT au_lname, au_fname
FROM authors WHERE city = '''
+ @city
+ ''' ORDER BY city'
EXEC (@cmd)
The two single quotes are required right after Berkeley so that SQL Server will create one embedded single quotemark in the variable. Alternatively, in your input form you might have this code:
Set oRecordset = oConnection.execute(
"SELECT au_lname, au_fname FROM authors
WHERE city ='" & request.form("txtCity") & "'")
Both are vulnerable. An intruder can change your generated string so that the SQL parser will accept the input as a UNION with a second SELECT.
From the input form, the intruder could enter this string:
Berkeley' UNION SELECT name, password
FROM master.dbo.syslogins --
The resulting string will be sent to SQL Server.
SELECT au_id FROM authors
WHERE city = 'Berkeley'
UNION SELECT name FROM master.dbo.syslogins
--' ORDER BY city
How does the hack work? Since the embedded quote mark comes right after the input value, the parser will see it as the end of the string comparison so the WHERE clause is complete. Then the UNION command tells the parser that the initial SELECT statement has ended and a new SELECT statement must follow. After the second SELECT statement the intruder's two dashes comment out the dynamically appended quote mark that was supposed to terminate the original string.
To make SQL injection work an intruder needs dynamically generated SQL where terminating quotes are added to a string. If the application uses a statically formed SELECT statement and a variable, the string termination is implicit, the entire entered string will become the search argument of the original SELECT statement, and injection will not work. In other words, when the string termination is implicit, the resulting command will cause SQL Server to look for a city named "Berkeley' UNION SELECT name, password FROM master.dbo.syslogins --", and the result is harmless.
Preventing SQL Injection
There are numerous ways you can prevent or deter SQL injection when working with SQL Server. Let's start with the most secure.
Avoid Dynamic SQL
You can easily eliminate the possibility of SQL injection by not dynamically constructing SQL commands in the first place.
You can easily eliminate the possibility of SQL injection by not dynamically constructing SQL commands in the first place. One of the easiest ways to do that with SQL Server is to call stored procedures that contain the SQL string and pass your input string to the stored procedure as a parameter:
-- Not vulnerable to injection
CREATE PROC usp_AuthorGetName
@city nvarchar(2000)
AS
SELECT au_lname, au_fname
FROM authors
WHERE city = @city
RETURN
The advantage is that whatever the user enters is considered the entire comparison value. Instead of being able to inject a SQL command the intruder simply adds characters, most likely meaningless, to the string.
Note that if you use dynamic SQL in a stored procedure you may end up reintroducing injection vulnerability:
-- Vulnerable to injection
CREATE PROC usp_AuthorGetName
@city nvarchar(2000)
AS
DECLARE @cmd nvarchar(2000)
SET @cmd = 'SELECT au_lname, au_fname FROM authors
WHERE city = ''' + @city + ''''
EXEC (@cmd)
RETURN
Use sp_executesql with Parameters
If you cannot change your code to call stored procedures you may be able to use sp_executesql. Suppose your form creates a query with a dynamically generated list of columns and search arguments. If you used a stored procedure and tried to pass in the column names as parameters, you'd still have to use dynamic SQL because you cannot use a variable in a column or table name. However, you can dynamically construct a column list and then send parameter(s) for the search argument(s) using the system stored procedure sp_executesql. The following example parameterizes the search argument and prevents injection:
-- Not vulnerable to injection
DECLARE @OuterVar nvarchar(2000),
@cmd nvarchar(2000)
SET @cmd = 'SELECT au_lname, au_fname FROM authors
WHERE city = @Innervar'
SET @OuterVar = '''
UNION Select login, password
from master..syslogins --'
EXEC sp_executesql @cmd,
N'@Innervar nvarchar(2000)',
@Innervar = @OuterVar
You've got to make sure to send the parameter into sp_executesql. If you send the raw command without any parameters, you reintroduce the possibility of injection:
-- Vulnerable to injection
DECLARE @VAR nvarchar(2000),
@cmd nvarchar(2000)
SET @var = ''' UNION Select name, password
from master..syslogins --'
SET @cmd = 'SELECT au_lname, au_fname FROM authors
WHERE city = ''' + @var + ''''
EXEC sp_executesql @cmd
Limit the Size of Input Strings
In the examples above the strings have deliberately been made much longer than necessary to accommodate the SQL injection example. If the city name were limited to, say, 30 characters that would dramatically reduce if not eliminate the number of SQL injection possibilities.
Replace a Single Quote with Two Single Quotes
If you must dynamically construct your string and you cannot use sp_executesql, you can replace a single quote with two single quotes in your input string. This can be a very effective measure for client-side code and can work well in stored procedures. Here's the previous stored procedure rewritten so that replacing the single quote stops the injection:
-- Replace stops the injection
CREATE PROC usp_AuthorGetName
@City nvarchar(2000)
AS
DECLARE @cmd nvarchar(2000)
SET @city = REPLACE(@city, '''', '''''')
SET @cmd = 'SELECT au_lname, au_fname
FROM authors WHERE city = ''' +
@city + ''''
EXEC (@cmd)
RETURN
The generated command behaves the same as when you parameterize the search argument in a variable; the comparison value becomes the entire string, and the intruder's single quote is taken as an embedded quote mark, not the end of a string.
Scan for Injected Strings
You could scan the entered text looking for SQL keywords such as UNION in a case-insensitive manner, or scan for a single quote mark, or the two dashes that comment the ending string. This may not be possible for all input text because there are streets and cities named 'union', there are names with single quotes in them (O'Shaunessy), and so on.
Security Patches
The Microsoft SQL Server group has been busy producing cumulative patches for SQL Server 7.0 and 2000.
The Microsoft SQL Server group has been busy these past months producing cumulative patches for both SQL Server 7.0 and 2000. Presumably all the patches will be rolled up in future service packs but it's reasonable to expect that there will be subsequent patches after those service packs are released. Let's drill down into what the SQL Server 2000 security patches provide, how you can install them, and how you can test your installation.
Each security patch contains a readme.txt file that instructs you to install the patch on the correct service pack (SQL Server 2000 SP2 has a version level of 8.00.534; SP3 is due out in late 2002.) After you apply the security patch, your SQL Server's version number will be incremented. This incremented build number implies that in addition to getting the security fixes, you are also receiving an executable containing other miscellaneous patches, the ones you would normally have to contact Microsoft PSS to receive.
At the time this article was written the security patches require you to install them manually, unlike service packs. By the end of 2002, it is likely the SQL Server group will have automated the security patch system so that it's more like a service pack install. Until the security patch system is automated you'll have to spend time automating the application of the patch. You may decide that the vulnerabilities fixed in a given patch do not affect your application and choose not to install the patch.
The full instructions for applying a given patch are in the readme.txt. If you have to apply the security patches manually, create a simple command file to apply them. There's just enough file copying involved from different file folder locations to make one-at-a-time copying error-prone. Make sure you create a command file to back up the current binaries, copy the new ones in, and restore the old from the copy. One helpful suggestion is that you include the security patch number in the command filename so that you can clone them for the next patch after that. Be careful about file locations; it's common on database servers to place data and log files for production databases on their own volumes. Test each security patch the same way you would a service pack.
One step may be confusing. The command-line executable, servpriv.exe, restricts registry permissions (obliquely described in the Microsoft Knowledge Base article Q322853 "FIX: SQL Server Grants Unnecessary Permissions or an Encryption Function Contains Unchecked Buffers.") When you run this program you pass the name of the SQL Server instance as a parameter. For the default instance on your machine, it's just
servpriv.exe MSSQLSERVER
For a named SQL Server 2000 instance you just use the name of the instance without a machine prefix. Normally on a server called MyServer, with an instance named Intance1, you specify MyServer\Instance1 whenever you connect to it or refer to it in Transact-SQL. But for this executable, you would actually type
servpriv.exe Instance1
Since the registry keys affected by servpriv.exe are not documented, it's not clear how you would back out of its changes.
By the time you read this, Microsoft should be coordinating the Microsoft Baseline Security Analyzer (MBSA) with the security patch files and you can then use this utility to inspect a SQL Server and determine whether the latest patch has been applied. You can download it from Microsoft TechNet (http:// www.microsoft.com/ technet/treeview/ default.asp?url=/technet/ security/tools/ Tools/MBSAhome.asp.) It uses an XML file to periodically update the proper settings. MBSA can inspect SQL Server, Windows, and IIS security on your system, and it reports on SQL Server security issues not addressed by the patches. You'll learn more about the Microsoft Baseline Security Analyzer later in this article.
Note that there's a risk to applying the security patches. Because these fixes are coming out fairly rapidly they may not have been as well tested as a full service pack. It's possible that they might introduce some regression or new bug. Another risk is that manually applying a patch can be error prone. Your best defense is to use command files that you test first.
SQL Server Security Best Practices
What should you do to make your SQL Server as secure as possible? Here's a strategy that can help.
Do the Basics First
These basic steps are recommended so often now that they approach common sense:
Use Windows authentication whenever possible for users and applicationsIf you are using SQL Server authentication, secure your SA account with a strong password and only let a select few know itAssign users minimal permissionsDeny access to tables and views in the databaseĀhave your application execute stored procedures to get dataDon't expose your SQL Server to the Internet; if you must, change it from port 1433 to some other port number and filter that portGive SQL Server and SQL Agent domain logins that do not require administrative access to the serverApply the latest SQL Server service pack and security patchTest!Test Using the Microsoft Baseline Security Analyzer
You can use a number of utilities to test your SQL Server. You can go to Microsoft's site and download the Microsoft Baseline Security Analyzer, for example. This utility will scan your system for Windows, IIS, and SQL Server vulnerabilities and will present you a number of recommendations. You can see some sample output from the MBSA in Figure 1.
At the time I'm writing this MBSA has some trouble recognizing the SQL Server security patches and named instances. By the time you read this, those problems will most likely be resolved.
Test Using Third-Party Utilities
You can get third-party utilities to help with security analysis. For example, NGS Software has released NGSSquirreL, a tool that will scan a SQL Server and report on vulnerabilities, but delivers a slightly different set of recommendations from the Microsoft Baseline Security Analyzer. It can be quite exhaustive, as shown in Figure 2.
NGSSquirreL connects using Windows authentication, and can scan one or more SQL Servers by name, named instance, IP address, or a range of IP addresses. The NGSSquirreL lets you mark an option for fix or unfix and will then generate a script you can apply to make or undo the fix. Since the number of recommendations is quite large, generating a script to apply those fixes is a welcome feature. However, you should be cautious about applying these fixes because some of them might produce unintended results (see the sidebar, "Should you drop xp_cmdshell?")
Test Your Passwords
Another potential vulnerability is having weak passwords. It is recommended that applications contain passwords with combinations of numbers, letters and symbols. There are a number of utilities available that can attempt to hack your SQL Server passwords, and if you are using SQL Server security, it could prove valuable to make sure they are all strong passwords.
A Moving Target
As a SQL Server developer or administrator you must begin wearing the hat of the security manager. Providing adequate security is an important part of software development and needs to be addressed frequently. Securing SQL Server is turning out to be a moving target. As SQL Servers (and other systems) become more secure, more complex vulnerabilities become exposed. Who knows when this will end, but until then it's wise to keep up with the subject and make sure you can keep your application's data secure.
