I know there are a lot of papers that talk about dynamic SQL in more depth than what I am going to cover, but as SQL injection is still one of the biggest security problems in the relation databases world, that I decided to include this part as a quick (and hopefully helpful) reminder.
First, allow me to define dynamic SQL as any mechanism used to programmatically generate and execute T-SQL statements, including statements generated in some application (using C#, C++ or any other programming language) and strings executed using the SQL Server sp_executesql stored procedure or the EXECUTE statement.
Being able to interpret any string (or variable) as a SQL statement is a really useful and powerful feature; unfortunately, as happens with most powerful tools, it is really easy to misuse it and leave the door open for somebody to abuse it. In this case, the abuse typically takes form as SQL injection attacks.
SQL injection occurs when the attacker provides malicious data that will change the semantics of the intended SQL you are generating, affecting the way it will be interpreted in the system. For example:
-- An innocent looking SP
CREATE PROC [sp_demo_injection01]( @name sysname )
-- ...with an obvious SQL injection-vulnerable sample
EXEC( 'SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''' )
-- This is how it was intended to be used
declare @var sysname
SET @var = 'Some Name'
EXEC [sp_demo_injection01] @var
-- As you can see, I can easily abuse this module in the following manner
SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'
When the attacker runs this query the system will concatenate the input to the command we defined in the SP:
EXEC ( 'SELECT * FROM sys.database_principals WHERE name = ''' + 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!' + '''' )
The attacker is able to close the quote in the user name (notice the trailing quote in Some Name’) and converted the rest of what should have been a user name into a different SQL statement, causing the following command to be executed:
SELECT * FROM sys.database_principals WHERE name = 'Some Name'; GRANT CONTROL TO [Malicious User]; PRINT 'Game over! This system is no longer yours!'-- Malicious User now can control the database!!!'
As you can see the attacker was able to add extra SQL statements that were not intended by the author of the stored procedure, in this case granting CONTROL on the database to herself and printing a note.
In most of these scenarios there is an alternative to the example used above using parameterization. Using parameterization gives you the advantage that you can clearly specify the data type and avoid pitfalls as well as the final T-SQL statement generated will reference the parameters as variables and not directly use the user defined input to generate the statement.
If you are using T-SQL directly to generate dynamic SQL, you can take advantage of sp_ExecuteSql to execute parameterized queries, for example:
-- An improved version of [sp_demo_injection01]
CREATE PROC [sp_demo_injection02]( @name sysname )
declare @cmd nvarchar(max)
declare @parameters nvarchar(max)
set @cmd = N'SELECT * FROM sys.database_principals WHERE name = @name'
set @parameters = '@name sysname'
EXEC sp_executesql @cmd, @parameters, @name = @name
EXEC [sp_demo_injection02] @var
-- The previous attack no longer has any effect!
CREATE PROC [sp_demo_injection03]( @Value nvarchar(100) )
set @cmd = N'SELECT * FROM sys.database_principals WHERE principal_id = @Value'
set @parameters = '@Value int'
EXEC sp_executesql @cmd, @parameters, @value = @value
-- Should work
SET @var = '1'
EXEC [sp_demo_injection03] @var
-- Expect error 8114
-- Error converting data type nvarchar to int.
SET @var = '1; select * from sys.objects'
But be careful, using sp_executesql is not a guarantee that the SQL statement to be executed is not susceptible to SQL injection; the parameters should be used properly in order to really take advantage of this feature. The following example is a demonstration of a common mistake I have seen a few times: constructing the @cmd parameter using user-defined data instead of using it as a parameter.
-- Incorrect usage of sp_executeSql
CREATE PROC [sp_demo_injection04]( @name sysname )
-- Looks famliar? yep, same injection as [sp_demo_injection01]
set @cmd = N'SELECT * FROM sys.database_principals WHERE name = ''' + @name + N''''
-- No parameters!!! This is typically a sign of misusing sp_execsql.
set @parameters = null
EXEC sp_executesql @cmd, @parameters
-- and now run the same attack we tried before...
EXEC [sp_demo_injection04] @var
-- ... and it is game over!
Be aware that sp_ExecuteSql doesn’t automatically protect against every SQL injection. It helps you to create the parameterized query, but it has to be used properly in order to work. I have seen a common misuse of this stored procedure: using the user-defined input (untrusted data) to generate the @statement parameter.
-- No parameters!!! This is typically a sign of misusing sp_execsql.
If you are using the .Net framework, you can use the SqlParameter class to create parameterized queries in a similar way, and the same warning still applies: Do not use user-defined input directly when constructing the parameterized statement. For further reference on this class, please refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlparameterclasstopic.asp
When parameterization is not an option
Unfortunately in SQL Server 2005 (and previous versions) some statements (i.e. DDL) don’t allow specifying parts of the statement using variables, for example:
-- Will fail:
-- Msg 102 - Incorrect syntax near '@name'.
CREATE PROC [sp_demo]( @name sysname )
CREATE USER @name WITHOUT LOGIN
If your business logic require such operations inside T-SQL modules, and you require using data that cannot be known before hand, you have little options other than creating the whole statement dynamically without parameterization.
While not the best of the situations, it is not a SQL-injection-for-sure situation either. In this case you have two different mechanisms to protect yourself: validate user input and escape the input.
I will start with data validation. The rules on how (and where) to validate the data is completely up to you and your application/business logic with just some general recommendations:
Validate the data in such a way that an attacker cannot bypass or alter the results. This may sound obvious, but sometimes we forget that the data crosses trust boundaries and that we may not control the untrusted application/client at all. For example, if your client application is the one validating the input (i.e. a length restriction in a web form), the attacker can always bypass your client app validation routines and send invalid data directly to the server.
Always look for valid input, not for invalid one. The idea is simple: You know what good data looks like, but you cannot always predict how bad data looks like. New ways to attack data may catch you by surprise if you assume that you know all the possible attacks.
For more information on this topic, I strongly recommend Writing Secure Code, Second Edition by Michael Howard and David C. LeBlanc. (ISBN: 0735617228).
In SQL Server, some characters have special meaning, such as the single quote ( ‘ ) and the braces ( [, ] ), but these TSQL specific constrains don’t always reflect the nature of the business needs, and sometimes it is necessary to accept such characters. After validating the data, it may still be necessary to properly escape (or quote) the data in order to be used in the proper context. You can use SQL Server builtins in order to help you in this task, the most important ones you can use are QUOTENAME and REPLACE.
QUOTENAME is designed for system names (sysnames or its equivalent, nvarchar(128)); it will properly add the proper delimiters ( “[“ and “]” by default) to the input and escaping any occurrence of the closing delimiter by duplicating it. For example:
declare @data sysname
set @data = 'data'
-- Will print [data]
print quotename( @data )
set @data = 'this data needs to be escaped: ] '
-- Will print [this data needs to be escaped: ]] ]
For strings I would recommend using REPLACE instead of QUOTENAME, the reason is that QUOTENAME was designed for sysname data types, and it is limited to 128 characters, REPLACE on the other hand doesn’t have this limitation, but you will have to add the delimiter yourself.
One important thing to consider whenever you are escaping or manipulating the user input in any way is to carefully consider the length of the transformed data and allocate enough space on the variables that will be used. Here is a link to a really well written and useful article that describes this problem is detail: New SQL Truncation Attacks And How To Avoid Them (http://msdn.microsoft.com/msdnmag/issues/06/11/SQLSecurity/ )
When using other languages
In general everything I talked about here is focused on TSQL, but it is pretty much the same when you are using any other language like C++ or C#. If you are constructing the statement directly without parameterizing, validating the user input and/or without properly escaping (and making sure you have enough buffer for the escaped string) you are most likely susceptible to SQL injection.
These references are quite interesting and helpful, but they are only some of my favorites ones. I hope you will find them useful as well.