Tips for Protecting Against SQL Injection in SQL Server 2008

SQL injection attacks are one of the most prevalent security concerns today. In this excerpt from Itzik Ben-Gan, et al.'s Inside Microsoft SQL Server 2008: T-SQL Programming you'll learn a few tips to protect against such attacks.

Following are examples for measures you can take to provide some level of protection (though not complete) for your environment against SQL injection attacks:

To reduce the surface area for attack, do not enable functionality that is not needed, such as xp_cmdshell, the SQL Server Agent service, and so on.
Provide minimal permissions to the executing user. For example, in the login scenario I presented, there’s no reason to connect to the database using a powerful user. Create a user that has access only to the Users table and has no other permissions. This will prevent hackers from modifying data, but they might still be able to read it. In SQL Server 2008, you can impersonate users, so the new credentials will even apply to code invoked dynamically at the server. This opens a whole window of opportunities for hackers. Dynamic SQL can now run under impersonated user credentials and not even require direct permissions from the user executing the stored procedure.
Inspect user input thoroughly and use stored procedures. For example, the input to the GetOrders stored procedure should contain only digits and commas. If you inspect the input and find that it contains other characters, don’t run the code. Instead, send an alert to notify an administrator of a potential SQL injection attempt:
```
IF @orders LIKE '%[^0-9,]%'

BEGIN

 -- Raise an error

 -- Send an alert

 RETURN;

END
```
If other characters are allowed, use pattern matching to check whether common SQL injection constructs—such as a single quote, two dashes, EXEC, sp_, xp_, UNION, and so on—exist in the input. Note, however, that this technique is not bulletproof because so many attacks are possible.
Limit the length of the inputs when possible. For example, a user name or password should not be hundreds or thousands of characters long. Such limitations are an effortless way to prevent many SQL injection attempts. Note, though, that some hacking techniques rely on truncation of the inputs; for example, if you set a variable defined as NVARCHAR(128) with a value that is longer than 128 characters, SQL Server will truncate the input beyond the 128th character. Such techniques and ways to block them are described in the article on SQL injection article in SQL Server Books Online.
Use stored procedures. Stored procedures help by encapsulating input, type-checking it (good for integers and date inputs), allowing permissions settings, and so on.
Avoid using dynamic SQL when possible. Static code is safe, especially if you write it yourself giving attention to security issues. For example, I discussed techniques to split an array of elements into multiple rows using a static query in Chapter 2, User-Defined Functions. You can create a function that accepts an array and invokes a static query that splits it into elements, returning a table with the different elements in separate rows. You can then use this function, joining its result table with the data table to return the order attributes. Such an implementation not only prevents SQL injection attacks, but it also reuses the same execution plan for multiple invocations of the code. The current implementation of the stored procedure will produce a different execution plan for each unique input. Imagine the performance effect of invoking such a stored procedure thousands of times a day. You can use thousands of plans or one plan. I provided the static function implementation in Chapter 2, User-Defined Functions. Also be careful with CLR routines, which could have dynamic SQL hidden in them.
When you need to quote inputs, don’t do it explicitly. Rather, use the QUOTENAME function for this purpose, or to be even safer, replace CHAR(39) with CHAR(39)+CHAR(39). QUOTENAME has some limitations, which you can read about in the SQL Injection article in SQL Server Books Online. The function will double each explicit quote that a hacker specifies, practically ensuring that the input is treated as an input string argument and not as part of your code. To demonstrate this, I’ll use PRINT to return the code string that is generated. In practice, there will be an EXEC or sp_executesql invocation. The following code doesn’t use the QUOTENAME function to quote the input value:
```
DECLARE @lastname AS NVARCHAR(40), @sql AS NVARCHAR(200);

SET @lastname = N'Davis';

SET @sql = N'SELECT * FROM HR.Employees WHERE lastname = N'''

 + @lastname + ''';';

PRINT @sql;
```

With innocent input such as Davis, this code produces the following query:

SELECT * FROM HR.Employees WHERE lastname = N'Davis';

But a hacker can easily inject code, like so:

DECLARE @lastname AS NVARCHAR(40), @sql AS NVARCHAR(200);

SET @lastname = N''' DROP TABLE HR.Employees --';

SET @sql = N'SELECT * FROM HR.Employees WHERE lastname = N'''

 + @lastname + ''';';

PRINT @sql;

And that code injection produces the following code:

SELECT * FROM HR.Employees WHERE lastname = N'' DROP TABLE HR.Employees --';

Now use QUOTENAME instead of explicitly adding single quotes to the last name:

DECLARE @lastname AS NVARCHAR(40), @sql AS NVARCHAR(200);

SET @lastname = N''' DROP TABLE HR.Employees --';

SET @sql = N'SELECT * FROM HR.Employees WHERE lastname = N'

 + QUOTENAME(@lastname, '''') + ';';

PRINT @sql;

By doing this, you get the following harmless query:

SELECT * FROM HR.Employees WHERE lastname = N''' DROP TABLE HR.Employees --';

Here I tried to make a point regarding user input strings you concatenate to your code. Of course your code would be much safer if you do not concatenate the last name at all; rather, use sp_executesql with an input parameter defined for last name:

DECLARE @entered_lastname AS NVARCHAR(40), @sql AS NVARCHAR(200);

-- user input

SET @entered_lastname = N''' DROP TABLE HR.Employees --';



SET @sql = N'SELECT * FROM HR.Employees WHERE lastname = @lastname;'



EXEC sp_executesql

 @stmt = @sql,

 @params = N'@lastname AS NVARCHAR(40)',

 @lastname = @entered_lastname;

Or even better, don’t use dynamic SQL at all in such cases—use static SQL:

DECLARE @lastname AS NVARCHAR(40);

-- user input

SET @lastname = N''' DROP TABLE dbo.Employees --';



SELECT * FROM HR.Employees WHERE lastname = @lastname;

Cover of Inside Microsoft® SQL Server® 2008: T-SQL Programming

Learn more about this topic from Inside Microsoft® SQL Server® 2008: T-SQL Programming.

Get a detailed look at the internal architecture of Transact-SQL (T-SQL)—the database programming language built into SQL Server—with this comprehensive, hands-on programming reference.