RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Securing Your SQL Server

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.

lanning for database security means a number of things. First, you need to take care that the code you send to SQL Server is not vulnerable to SQL injection. Second, you need to keep up with and apply the latest Microsoft SQL Server security updates. Third, after you take care of some of the basic SQL Server security measures, you should test your servers using the Microsoft Baseline Security Analyzer, and also pay attention to other third party utilities.

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.

By far the most important vulnerability in SQL Server code is SQL injection.
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. (See Sidebar 1: Detecting Dynamic SQL)

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'
   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-quote mark 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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date