Ebay - Advertisement

Wednesday, June 9, 2010

Avoiding SQL Injection attacks in stored procedures that must be dynamic

No doubt that stored procedures which use dynamic construction of queries by string concatenation technique are vulnerable to SQL injection attacks. In order to prevent the attack, stored procedures should not use such technique and the sql statements should be written as a part of the T-SQL syntax with sql parameters.

However, under certain circumstances the use of dynamic construction is not avoidable.For example in Microsoft SQL Server you cannot write a T-SQL stored procedure which contains parameters such as table or a column name. In this case you must interpolate it into the SQL string using the string concatenation technique.

So what is the solution for this issue?

Very simple, actually there are two ways to do it as follows:
1. Using the quotename() function which was added in sql 7. The function takes two parameters: the first is a string, and the second is a pair of delimiters to wrap the string in. The default for the second parameter is []. Thus, quotename('table_name') returns [table_name]. quotename() takes care of nested delimiters, so if table name like table]_name, quotename() will return [table]]_name]
The following example demonstrates how treat the table_name in order to avoid the SQL Injection attack:

 
ALTER PROCEDURE [DBO].[EmptyTables]
(
@table_name NVARCHAR(30)
)
AS DECLARE @sql NVARCHAR(100)
BEGIN
IF CHARINDEX (']',@table_name,1 ) > 0
                        BEGIN
                                SET @error1='Error'
                                RAISERROR( @error1 ,11,1 );       
                        END
                SET @sql='TRUNCATE TABLE ' + quotename(@table_name)
                EXEC sp_executesql @sql
END

2. Using the [] characters in order to indicate that the table or the column name is an object. In such case if the attacker injects the following query in the table_name paramater as follows: sometable';drop table users --, the database will raise the following error :Cannot find the object "';drop table users --'" because it does not exist or you do not have permissions.
The following example demonstrates how treat the table_name in order to avoid the SQL Injection attack:
 
ALTER PROCEDURE [DBO].[EmptyTables]
(
@table_name NVARCHAR(30)
)
AS DECLARE @sql NVARCHAR(100)
BEGIN
IF CHARINDEX (']',@table_name,1 ) > 0
                        BEGIN
                                SET @error1='Error'
                                RAISERROR( @error1 ,11,1 );       
                        END
                SET @sql='TRUNCATE TABLE [' + @table_name + ']'
                EXEC sp_executesql @sql
END