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 )

AS

  -- ...with an obvious SQL injection-vulnerable sample

  EXEC( 'SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''' )

go

 

-- This is how it was intended to be used

declare @var sysname

SET @var = 'Some Name'

EXEC [sp_demo_injection01] @var

go

 

-- As you can see, I can easily abuse this module in the following manner

declare @var sysname

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!!!'

EXEC [sp_demo_injection01] @var

go

 

  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.

 

Parameterization

 

  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 )

AS

  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

go

 

-- This is how it was intended to be used

declare @var sysname

SET @var = 'Some Name'

EXEC [sp_demo_injection02] @var

go

 

-- The previous attack no longer has any effect!

declare @var sysname

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!!!'

EXEC [sp_demo_injection02] @var

go

 

 

CREATE PROC [sp_demo_injection03]( @Value nvarchar(100) )

AS

  declare @cmd nvarchar(max)

  declare @parameters nvarchar(max)

  set @cmd = N'SELECT * FROM sys.database_principals WHERE principal_id = @Value'

  set @parameters = '@Value int'

  EXEC sp_executesql @cmd, @parameters, @value = @value

go

 

-- Should work

declare @var sysname

SET @var = '1'

EXEC [sp_demo_injection03] @var

go

 

-- Expect error 8114

-- Error converting data type nvarchar to int.

declare @var sysname

SET @var = '1; select * from sys.objects'

EXEC [sp_demo_injection03] @var

go

 

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 )

AS

  declare @cmd nvarchar(max)

  declare @parameters nvarchar(max)

  -- 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

go

 

-- and now run the same attack we tried before...

declare @var sysname

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!!!'

EXEC [sp_demo_injection04] @var

-- ... and it is game over!

go

 

  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.

 

---------------------------------------------------------------------

-- Incorrect usage of sp_executeSql

CREATE PROC [sp_demo_injection04]( @name sysname )

AS

  declare @cmd nvarchar(max)

  declare @parameters nvarchar(max)

  -- 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

go

 

-- and now run the same attack we tried before...

declare @var sysname

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!!!'

EXEC [sp_demo_injection04] @var

-- ... and it is game over!

go

 

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 )

AS

  -- ...

  CREATE USER @name WITHOUT LOGIN

  -- ...

go

 

  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.

 

Data validation

 

   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).

 

Escaping input

 

  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: ]] ]

print quotename( @data )

 

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.

 

 References

 

 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.