Best Practices To Prevent SQL Injection

Overview of SQL Injection

SQL Injection is a major injection technique, which is used to attack data-driven Applications.

Procedures and functions that use dynamic SQL queries by concatenating the text inputs to the dynamic SQL are prone to SQL Injection attack as someone can provide extra commands/malicious text through the input parameter and when executed can result in the unexpected results.

Example

  1. DECLARE @sqlTextnvarchar(MAX), @user_idnvarchar(MAX);
  2. SET @sqlText = ‘SELECT * FROM Users WHERE user_id = ’+@user_id;
  3. EXEC(@sqlText);
Here, if the user will provide @user_id = ‘105; DROP TABLE SomeTable’, an unexpected DROP table will happen.

Best Practices to prevent SQL Injection

Execute Dynamic SQL queries, using SP_EXECUTESQL procedure with the parameters.

While writing dynamic SQL queries and executing them, one needs to be cautious in regards to the following.
  1. Avoid concatenating the parameter variables with the query.

    Example
    1. declare @cmd nvarchar(MAX)
    2. set @cmd = N 'select * from dbo.MyTable where col1 = ' + @arg1;
    3. The above query needs to be rewritten as:
    4. declare @cmd nvarchar(MAX)
    5. declare @parameters nvarchar(MAX)
    6. set @cmd = N 'select * from dbo.MyTable where col1 = @arg1';
    7. set @parameters = '@arg1 VARCHAR(MAX)';
  2. Avoid executing dynamic SQL queries, using EXEC stored procedure. This approach does not support passing of parameters.

    Always use SP_EXECUTESQL procedure with the parameters to execute dynamic SQL queries.

    Example

    Let @arg1 be the parameter supplied to the procedure, which contains the script, mentioned below.
    1. declare @cmd nvarchar(MAX);
    2. set @cmd = N 'select * from dbo.MyTable where col1 = ' + @arg1;
    3. EXEC(@cmd);
    4. The above command should be rewritten as:
    5. declare @cmd nvarchar(MAX);
    6. declare @parameters nvarchar(MAX);
    7. set @cmd = N 'select * from dbo.MyTable where col1 = @arg1';
    8. set @parameters = '@arg1 VARCHAR(MAX)';
    EXECsp_Executesql
    1. @cmd, --dynamic sql command as the first parameter
    2. @parameters, --definition of parameters as the second parameter
    3. @arg1 = @arg1;
    4. --Assign the parameter value to the parameter

Note

  1. The first 2 parameters of SP_EXECUTESQL (@cmd and @parameters should always be of type nvarchar.
  2. If the dynamic SQL requires multiple string parameters, the parameters can be written separated by commas.

    Example
    1. Declare
    2. @cmd nvarchar(MAX),
    3. @params nvarchar(MAX),
    4. @arg1VARCHAR(MAX) = 'ParamValue1',
    5. @arg2VARCHAR(MAX) = ' ParamValue2';
    6. SET @cmd = 'SELECT * FROM dbo.MyTable WHERE col1=@arg1 and col2=@arg2';
    7. SET @params = '@arg1 VARCHAR(MAX),@arg2 VARCHAR(MAX)';
    EXECSP_EXECUTESQL
    1. @cmd,
    2. @params,
    3. @arg1 = @arg1,
    4. @arg2 = @arg2;
    Here, is a complete example, which demonstrates the usage of dynamic SQL in a stored procedure in the correct way:

    createprocedure test_procedure1(@arg1VARCHAR(MAX))

    as,
    1. declare @cmd nvarchar(MAX)
    2. declare @parameters nvarchar(MAX)
    3. set @cmd = N 'select * from dbo.MyTable where col1 = @arg1'
    4. set @parameters = '@arg1 VARCHAR(MAX)'
    5. EXECsp_Executesql @cmd, @parameters, @arg1 = @arg1;
    6. go
    7. declare @argVARCHAR(MAX);
    8. SET @arg = 'Some Text';
    9. EXEC test_procedure1 @arg;
    10. Go

Guidelines to follow while using parameter in like clause in dynamic SQL

When we use the parameters supplied to a procedure in a dynamic SQL command and execute it, using EXEC procedure, there is a chance the input parameter can be used to hack into the database object.

Example

  1. declare
  2. @cmd nvarchar(MAX),
  3. @search_string varchar(100);
  4. SET @search_string = '1234';
  5. SET @cmd = 'SELECT * FROM dbo.MyServers WHERE server_name LIKE '
  6. '%' + @search_string + '%'
  7. '';
  8. EXEC(@cmd);
This works fine but if I pass something like this as @search_string, the code will be as follows.
  1. declare
  2. @cmd nvarchar(MAX),
  3. @search_string varchar(100);
  4. SET @search_string = 'u'
  5. ' OR 1=1 --';
  6. SET @cmd = 'SELECT * FROM dbo.MyServers WHERE server_name LIKE '
  7. '%' + @search_string + '%'
  8. '';
  9. EXEC(@cmd);
This will list out every record from the dbo.MyServers table as the command, which will go to the db will be.

SELECT * FROM dbo.MyServers WHERE server_name LIKE '%u' OR 1=1 --%'

Here, the best practice is to embed the parameters (search string) in the dynamic SQL command and execute it, using SP_EXECUTESQL with the parameters, as shown below.
  1. declare
  2. @cmd nvarchar(MAX),
  3. @params nvarchar(MAX),
  4. @search_string varchar(100);
  5. SET @search_string = '1234';
  6. --SET @search_string = 'u'
  7. ' OR 1=1 --';
  8. SET @cmd = 'SELECT * FROM dbo.MyServers WHERE server_name LIKE '
  9. '%'
  10. '+' + '@search_string' + '+'
  11. '%'
  12. '';
  13. SET @params = '@search_string varchar(100)';
  14. EXECsp_executesql
  15. @cmd,
  16. @params,
  17. @search_string = @search_string;
If the supplied pattern matches, the query upon execution will generate the appropriate records.

If a malicious pattern is supplied, the execution will result in an empty result set . Please follow the example, stated below.
  1. declare
  2. @cmd nvarchar(MAX),
  3. @params nvarchar(MAX),
  4. @search_string varchar(100);
  5. --SET @search_string = '1234';
  6. SET @search_string = 'u'
  7. ' OR 1=1 --';
  8. SET @cmd = 'SELECT * FROM dbo.MyServers WHERE server_name LIKE '
  9. '%'
  10. '+' + '@search_string' + '+'
  11. '%'
  12. '';
  13. SET @params = '@search_string varchar(100)';
  14. EXECsp_executesql
  15. @cmd,
  16. @params,
  17. @search_string = @search_string;
This will result in an empty resultset and our data will not show up.

Guidelines to use table/column names in dynamic SQL:

While using the table/column names as the parameters in a dynamic SQL command, the system defined function QUOTENAME should be used to enclose the table/column name within [ and ].

Example
  1. SELECTQUOTENAME(‘MyServers’);
  2. Output: [MyServers]
  3. Please check out the below examples:
  4. declare @tabname nvarchar(100), @sql nvarchar(MAX);
  5. SET @tabname = 'MyServers;DROP TABLE dbo.MyConfigs';
  6. SET @sql = 'SELECT top 10 * FROM ' + @tabname;
  7. EXEC(@sql);
  8. GO
Here, the @tabname variable can be used to manipulate the database in a wrong way. To prevent it, @tabname should be enclosed within [ and ] as in this case [MyServers;DROP TABLE dbo.MyConfigs] will not be considered as a valid table name.

Here is the script
  1. declare @tabname nvarchar(100), @sql nvarchar(MAX);
  2. SET @tabname = 'MyServers;PRINT '
  3. 'HELLO'
  4. '';
  5. SET @sql = 'SELECT top 10 * FROM ' + QUOTENAME(@tabname);
  6. EXECSP_EXECUTESQL @sql;
Output

Invalid Object name ‘MyServers;PRINT ‘HELLO’’.

Here is another example, where both column and table names are used in a dynamic SQL query.

Someone can push something dangerous through the column name.
  1. declare @tabname nvarchar(100), @column nvarchar(100), @sql nvarchar(MAX);
  2. SET @tabname = 'MyServers';
  3. SET @column = 'server_name FROM dbo.MyServers;PRINT '
  4. 'HELLO BRO! U R HACKED'
  5. '--';
  6. SET @sql = 'SELECT ' + @column + ' FROM ' + QUOTENAME(@tabname);
  7. EXEC(@sql);
  8. GO
This will print out all the Server names from your dbo.MyServers table.

This should be rewritten, as stated below.
  1. declare
  2. @tabname nvarchar(100),
  3. @column nvarchar(100),
  4. @sql nvarchar(MAX);
  5. SET @tabname = 'MyServers';
  6. SET @column = 'server_name FROM dbo.MyServers;PRINT '
  7. 'HELLO BRO! U R HACKED'
  8. '--';
  9. SET @sql = 'SELECT ' + QUOTENAME(@column) + ' FROM ' + QUOTENAME(@tabname);
  10. EXECSP_EXECUTESQL @sql;
  11. GO
Output

Invalid column name 'server_name FROM dbo.MyServers;PRINT 'HELLO BRO! U R HACKED.