Export (0) Print
Expand All
8 out of 8 rated this helpful - Rate this topic

Writing Secure Transact-SQL

SQL Server 2000

Bart Duncan
Microsoft Corporation

October 18, 2004

Summary: Guest columnist Bart Duncan of Microsoft Product Support Services reviews best practices you can use to make your Transact-SQL code more bulletproof. (7 printed pages)



There are plenty of good sources of information about how to deploy SQL Server in a secure fashion. However, these resources are often targeted at database administrators tasked with securing already developed applications. In addition, there is a rich body of information that discusses writing secure .NET and ASP.NET code, including .NET code that accesses SQL Server. However, many of these resources focus on the data access code that runs on the application servers rather than the Transact-SQL (T-SQL) code that executes within SQL Server. Developing T-SQL code that runs securely on SQL Server is the primary focus of this column.

Secure Your Development SQL Server

The first step to developing secure T-SQL is to secure your development SQL Server. Why bother locking down a SQL Server instance that doesn't hold real-world data and will never be exposed to end users? Because this will force you to write more secure T-SQL, and it will also make it much easier to secure your application when it is deployed to production. The following are some specific steps you can take to quickly secure your development server:

  • At least one of your development or test SQL Servers should be running with the latest service pack and SQL security patch to ensure that your customers can successfully run your application on this build of SQL Server.
  • By default, SQL Server 2000 Service Pack 3a disables unsafe functionality called "cross database ownership chaining". When you install SP3 on your development servers, allow the service pack to disable cross database ownership chaining to help verify that you aren't writing T-SQL code that depends on an insecure server configuration.
  • An easy way to spot common security configuration problems on your development SQL Server is to run the Microsoft Baseline Security Analyzer against it. In addition to this, take advantage of the resources listed in the References section of this column; they provide additional steps that will help secure your development SQL Servers.

In general, it is a best practice to secure your development server as if it was running in production. The closer you get to this goal, the more confident you can be that the code you develop will function properly in a secure production environment.

Develop with a Minimally Privileged Account

It is tempting to use an account with sysadmin or dbo SQL Server privileges during development, and defer the switch to a less privileged account until just prior to deployment. One problem with this approach is that it is much more difficult to reverse engineer the minimum set of needed permissions than to document these as the application is being developed.

Don't develop T-SQL code using a SQL sysadmin account, thinking that you will figure out what privileges can be taken away before the application is deployed. The likely result will be the application running with a more privileged account than is necessary. Instead, develop using a minimally privileged account.

As you develop using such an account, you will incrementally grant specific permissions to EXEC certain necessary stored procedures, SELECT from certain tables, and so on. Document these GRANT statements so that the same minimum permissions can be easily deployed to the production environment without any guesswork.

This philosophy also applies to testing. Perform ad hoc as well as more structured testing with an account that has the exact same set of permissions and user rights that the account used in production will have.

Another advantage of using a minimally privileged account during development is that you will avoid unintentionally writing code that requires dangerous or excessive rights. For example, suppose you needed to interact with a third-party COM component from T-SQL. One way to do this is to send a SQL batch that directly calls sp_OACreate and sp_OAMethod to manipulate the COM object. This will work fine in a development environment where your application connects to SQL Server using a sysadmin account. However, when you attempt to ready the already developed application for production deployment, you will find that this will not work with a less privileged account. To make the application work in the production environment with a non-sysadmin account, you would have to explicitly grant EXECUTE permissions on sp_OACreate. Consider the security implications if a user managed to find a way to execute arbitrary code using the application login, and took advantage of this permission to instantiate a COM object like Scripting.FileSystemObject on the SQL Server.

Follow Best Practices for Secure T-SQL

It is essential to guard against a class of vulnerability called "SQL injection". In general, you protect yourself from SQL injection attacks by using a multilayered defense:

  • Perform validation of user-supplied input (for example, enforce data types and maximum string lengths).
  • Escape character sequences that may have special meaning to the database engine. In T-SQL, two of the more common strings used in injection attacks are the single quote character (') and the comment character sequence (--).
  • Don't concatenate user-supplied values inline in your T-SQL statement. Use prepared statements and parameterization, instead.

SQL injection attacks are covered in great detail in other places, so I will not spend much time discussing the specifics of the problem other than to emphasize that SQL injection is not a problem that is confined to T-SQL queries built at the application layer. It can happen anywhere that a T-SQL query that was constructed in part from user-supplied values is executed. This means that a stored procedure that internally builds up a query string and executes it through the EXEC() command or sp_executesql stored procedure could also be vulnerable. See the References section for links to resources that provide examples of various types of SQL injection attacks, along with techniques for hardening your code against the attack.

Another best practice is to avoid granting permissions on base tables. You should wrap the queries you want the user to be able to perform within stored procedures, and only grant EXECUTE permissions on these stored procedures. If you follow this guideline, even if a user does manage to bypass your application and log in to the database directly, they will not be able to sidestep any data validation, auditing, business rules, or row-level security restrictions that you have built into your stored procedures.

Be Aware of T-SQL Commands with Unique Security Concerns

There are a few T-SQL commands and extensions that present their own unique security concerns. One of these is sp_OACreate and its related family of system procedures (e.g., sp_OAMethod, sp_OAProperty, etc.). Earlier, we looked at a potential security problem that would be created by granting an application login direct access to these procedures. To avoid this problem, never write application code that directly calls the sp_OA procedures. Instead, wrap all references to these procedures in your own T-SQL stored procedures, and only grant access to these wrapper stored procedures. Also, do not allow the application code to pass in the names of COM objects or methods as strings that are blindly invoked by the wrapper procedure.

A second built-in SQL Server extension that has a unique set of security risks is xp_cmdshell. This system stored procedure can run arbitrary executables or system commands. For obvious reasons, EXEC permissions on xp_cmdshell default to sysadmin users only, and must be explicitly granted to other users. If you have a need for your application to run a specific command or utility on the SQL Server, be careful not to build a dependency on direct access to xp_cmdshell into your application. The risks of this are similar to the risks presented by direct access to sp_OACreate. Once an account is granted EXEC permissions on xp_cmdshell, it will be able to execute hundreds of operating system commands and other executables in addition to the specific command that you intended it to access. Like sp_OACreate, always wrap xp_cmdshell calls within another stored procedure, and avoid granting EXECUTE permissions directly on xp_cmdshell.

You should also avoid concatenating any user- or application-supplied string parameters to the command that will be executed through xp_cmdshell. If this is not possible, then you must be aware of a potential code injection attack that (within SQL Server, at least) is unique to xp_cmdshell. Consider the following stored procedure:

CREATE PROCEDURE usp_DoFileCopy @filename varchar(255) AS
DECLARE @cmd varchar (8000)
SET @cmd = 'copy \\src\share\' + @filename + ' \\dest\share\'
EXEC master.dbo.xp_cmdshell @cmd
GRANT EXEC ON usp_DoFileCopy TO myapplogin

By wrapping the xp_cmdshell call in your own stored procedure and only granting EXEC permissions on the usp_DoFileCopy stored procedure, you have prevented users from directly calling xp_cmdshell to execute arbitrary commands. However, consider the following example of shell command injection:

EXEC usp_DoFileCopy @filename = ' & del /S /Q \\dest\share\ & '

With this @filename parameter, the string that will be executed is copy \\src\share\ & del /S /Q \\dest\share\ & \\dest\share. The ampersand symbol (&) is handled as a command delimiter by the operating system command interpreter, so this string will be treated as three distinct commands by CMD.EXE. The second of these commands (del /S /Q \\dest\share\) will attempt to delete all files in \\dest\share. By taking advantage of a shell command injection vulnerability in the stored procedure, a user could still execute an arbitrary operating system command. One way to guard against such an attack is to wrap command strings within a T-SQL function like the one below. This user-defined function adds the shell escape character (^) to escape any occurrences of the ampersand character or other characters with special meanings.

-- Function: fn_escapecmdshellstring
-- Description: Returns an escaped version of a given string
--              with carets ('^') added in front of all the special 
--              command shell symbols. 
-- Parameter: @command_string nvarchar(4000)
CREATE FUNCTION dbo.fn_escapecmdshellstring (
  @command_string nvarchar(4000)) RETURNS nvarchar(4000) AS
  DECLARE @escaped_command_string nvarchar(4000),
    @curr_char nvarchar(1),
    @curr_char_index int    
  SELECT @escaped_command_string = N'',
    @curr_char = N'', 
    @curr_char_index = 1
  WHILE @curr_char_index <= LEN (@command_string)
    SELECT @curr_char = SUBSTRING (@command_string, @curr_char_index, 1) 
    IF @curr_char IN ('%', '<', '>', '|', '&', '(', ')', '^', '"')
      SELECT @escaped_command_string = @escaped_command_string + N'^'
    SELECT @escaped_command_string = @escaped_command_string + @curr_char
    SELECT @curr_char_index = @curr_char_index + 1 
  RETURN @escaped_command_string

Here is the stored procedure after the command shell injection vulnerability has been eliminated:

CREATE PROCEDURE usp_DoFileCopy @filename varchar(255) AS
DECLARE @cmd varchar (8000)
SET @cmd = 'copy \\src\share\' 
  + dbo.fn_escapecmdshellstring (@filename) 
  + ' \\dest\share\'
EXEC master.dbo.xp_cmdshell @cmd

A third set of T-SQL commands that have unique security considerations are the commands that allow execution of a dynamically constructed query: EXEC() and sp_executesql. The risk of SQL injection attacks is not the only reason to avoid dynamic SQL. Any queries executed dynamically through these commands will run in the security context of the current user, not in the context of the stored procedure owner. This means that the use of dynamic SQL can force you to grant users permission to directly access base tables. Consider the following stored procedure:

CREATE PROC dbo.usp_RetrieveMyUserInfo AS 

This procedure will restrict the current user from viewing other any user's data. However, if the SELECT statement in this procedure was executed via a dynamic EXEC() or via sp_executesql, you would be forced to grant the user direct SELECT permission on the UserInfo table because the dynamically-executed query runs in the security context of the current user. With this permission, if the user were able to log in to the server directly, they would be able to bypass the row-level security that the stored procedure provides, and view data for all users.


To recap, the following suggestions can help you develop T-SQL code that runs securely on SQL Server:

  • Secure your development SQL Server as if it was a production server. This helps ensure you are developing secure code. It will also help you define the minimum set of permissions that your application needs to function properly.
  • Use a minimally privileged SQL Server account for T-SQL development and testing. Do not use a sysadmin or dbo account.
  • Be wary of stored procedures like sp_OACreate and xp_cmdshell that allow T-SQL to execute arbitrary external code. If you must use these extensions, be sure you have accounted for their unique security implications.
  • Follow best practices for secure T-SQL development, including: passing user-supplied data as explicit parameters, coding to avoid SQL injection attacks, avoiding unnecessary use of dynamic SQL, and granting access to stored procedures instead of direct access to base tables.
  • Secure T-SQL is only one part of a secure application. Take advantage of the resources below to ensure that your server is securely configured and that you also have a secure database client application.


SQL Server Security Resource Page

Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication

SQL Server 2000 SP3 Security Features and Best Practices: Secure Multi-tier Deployment


SQL Server for Developers

Bart Duncan has spent the last 6 years working in SQL Server product support, most recently as an Escalation Engineer. He lives in Dallas, Texas with his wife.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
© 2014 Microsoft. All rights reserved.