Best Practices To Prevent SQL Injection

DECLARE @sqlText nvarchar(MAX), @user_id nvarchar(MAX);  
SET @sqlText = ‘SELECT * FROM Users WHERE user_id = ’+ @user_id;  
EXEC(@sqlText);  

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

declare  
@cmd nvarchar(MAX),  
    @search_string varchar(100);  
  
SET @search_string = '1234';  
SET @cmd = 'SELECT * FROM dbo.MyServers WHERE server_name LIKE ''%' + @search_string + '%''';  
  
EXEC(@cmd); 

This works fine but if I pass something like this as @search_string, the code will be as follows.

declare  
@cmd nvarchar(MAX),  
    @search_string varchar(100);  
  
SET @search_string = 'u'' OR 1=1 --';  
SET @cmd = 'SELECT * FROM dbo.MyServers WHERE server_name LIKE ''%' + @search_string + '%''';  
  
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.

declare  
@cmd nvarchar(MAX),  
    @params nvarchar(MAX),  
    @search_string varchar(100);  
  
SET @search_string = '1234';  
--SET @search_string = 'u'' OR 1=1 --';  
SET @cmd = 'SELECT * FROM dbo.MyServers WHERE server_name LIKE ''%''+' + '@search_string' + '+''%''';  
SET @params = '@search_string varchar(100)';  
EXEC sp_executesql  
@cmd,  
@params,  
@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.

declare  
    @cmd nvarchar(MAX),  
    @params nvarchar(MAX),  
    @search_string varchar(100);  
  
--SET @search_string = '1234';  
SET @search_string = 'u'' OR 1=1 --';  
SET @cmd = 'SELECT * FROM dbo.MyServers WHERE server_name LIKE ''%''+' + '@search_string' + '+''%''';  
SET @params = '@search_string varchar(100)';  
EXEC sp_executesql  
@cmd,  
@params,  
@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

SELECT QUOTENAME(‘MyServers’);  
Output: [MyServers] 

Please check out the below examples:
  
declare @tabname nvarchar(100), @sql nvarchar(MAX);  
SET @tabname = 'MyServers;DROP TABLE dbo.MyConfigs';  
SET @sql = 'SELECT top 10 * FROM ' + @tabname;  
EXEC(@sql);  
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

declare @tabname nvarchar(100), @sql nvarchar(MAX);  
SET @tabname = 'MyServers;PRINT ''HELLO''';  
SET @sql = 'SELECT top 10 * FROM ' + QUOTENAME(@tabname);  
  
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.

declare @tabname nvarchar(100), @column nvarchar(100), @sql nvarchar(MAX);  
SET @tabname = 'MyServers';  
SET @column = 'server_name FROM dbo.MyServers;PRINT ''HELLO BRO! U R HACKED''--';  
SET @sql = 'SELECT ' + @column + ' FROM ' + QUOTENAME(@tabname);  
EXEC(@sql);  
GO

This will print out all the Server names from your dbo.MyServers table.

This should be rewritten, as stated below.

declare  
    @tabname nvarchar(100),  
    @column nvarchar(100),  
    @sql nvarchar(MAX);  
SET @tabname = 'MyServers';  
SET @column = 'server_name FROM dbo.MyServers;PRINT ''HELLO BRO! U R HACKED''--';  
SET @sql = 'SELECT ' + QUOTENAME(@column) + ' FROM ' + QUOTENAME(@tabname);  
EXECSP_EXECUTESQL @sql;  
GO 

Output

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