Dynamic SQL & SQL injection

  • Article

  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 https://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 (https://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.

 

Comments

  • Anonymous
    January 04, 2007
    Hi, thanks for sharing. Is there anyway which I can prevent to write dynamic sql if i need to dynamic pass the table name? For example, there are 2 values in a dropdownlist - A and B. If I select A, then press search button, it will go back to my database and pass into a dynamic sql - e.g. tb_A_Logistics and perform some query. Same to the B. I cannot find a way to overcome this for a dynamic table name. Any help? Yes, I will like to prevent dynamic sql for my case, but I cannot find a better way. Thanks.

  • Anonymous
    January 04, 2007
      If I understand your scenario, you construct the table name based on user input (from the dropdown list), correct? I am not aware of an alternative from dynamic SQL for your particular scenario. As far as I understand, statements such as SELECT cannot use a variable in the FROM clause to specify the table/view name, and it also makes this scenario one that cannot be easily parameterized.   I will strongly recommend in your case validating the input (i.e. make sure the value from the dropdown list follows your business rules) and make sure you properly delimit and escape the table name (in this case, as table names are sysnames, using QUOTENAME or an equivalent will be quite useful) in order to prevent SQL injection attacks.  I will appreciate if anyone knows an alternative mechanism without using dynamic SQL or a way to use a variable to specify the table name in such statements and wants to share the information.   Thanks a lot, -Raul

  • Anonymous
    January 04, 2007
    Thanks for your feedback. Looking forward on that too if anyone can share some lights here :)

  • Anonymous
    January 04, 2007
    Пару недель назад я написал в своем блоге о Передача параметров в динамический SQL . Где пытался показать,

  • Anonymous
    January 08, 2007
    Hello. Well, I might have an alternative without Dynamic SQL, however it is of limited use, and with a lot of constraints. Basically, what you select from either table must have the same structure (i.e. same datatypes) and there's only a predefined number of different tables you can use the solution on. Moreover, I'm pretty sure that performance-wise, this gives you quite an overhead. Having taken all these into consideration, here goes: Suppose you have two tables, tbl_A and tbl_B, with 3 columns that have the same datatypes (and probably the same meaning data-wise). Suppose now, that you have the dropdown with the two values. This makes for the user input. After proper validation (i.e. that it is actually one of the allowed values) the input is passed as an argument to a function. The function is just a big if-else if: if the argument is, say "A", then do one select (write out the full select statement, with the table name tbl_A hard coded). Else if the argument is, say, "B", do another select (again, hard coded select statement, with tbl_B instead of tbl_A). I guess you can see where this fails: if you want different datatypes in the output, depending on the table you select, this solution won't be of any help, as the return type of the function is a table with statically defined columns. Also, performance-wise, I don't know what overhead this might give, so if you have any feedback, it's most welcome. Anyway, hope it helps.

  • Anonymous
    July 24, 2007
    D-SQLInjection things you need to consider and take care, just caught my eye recently.

  • Anonymous
    January 31, 2008
    I am slowly coming to terms with Oracle again, after a decade or so of using SQL Server exclusively.

  • Anonymous
    June 24, 2008
    <p>This year SQL injection attacks are being stepped up and even automated against SQL Server. While SQL injection attacks can occur against any DBMS, my blog will only address SQL Server.</p ...

  • Anonymous
    November 08, 2010
    Great article, thanks for share. To prevent sql inject attack,I think that we need some prevention system softwares, eg. Ax3soft Sax2,snort and so on.

  • Anonymous
    March 17, 2015
    I don't really understand the point of using params with dynamic SQL. If you're in a situation where you are able to use params, there's no need to use dynamic SQL in the first place. You can just make a regular SQL statement using variables. The whole reason you use dynamic SQL is because the part you want to parameterize can't be parameterized in an ordinary statement: e.g. a table name, a list of values i.e. WHERE x IN (1, 2, 3), or a file name in a bulk insert.