Overview of SQL Injection
SQL Injection is a major injection technique, which is used to attack data-driven applications. As a matter of fact, thousands of websites get hacked using SQL injection. However, if an application is architected correctly and DBAs and developers have used best practices, SQL injection is easily avoidable. In SQL injection technique, hackers find a way to execute SQL queries from a website or code against backend database and execute SQL queries to access data or manipulate (update, delete, copy) data from a databse. Sometimes hacker can also change security data such as login and passwords. There are thousands of cases where large corporations have been hacked using SQL injection.
If you are a software developer, DBA, databas developer, architect, or even a tester, you must know what a SQL injection is and how to prevent it while wriitng code and how to test it during testing. In this article, we will learn about best practices to avoid SQL injection.
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
DECLARE @sqlText nvarchar(MAX), @user_id nvarchar(MAX);
SET @sqlText = ‘SELECT * FROM Users WHERE user_id = ’+ @user_id;
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.
- Avoid concatenating the parameter variables with the query.
Example
declare @cmd nvarchar(MAX)
set @cmd = N 'select * from dbo.MyTable where col1 = ' + @arg1;
--The above query needs to be rewritten as:
declare @cmd nvarchar(MAX);
declare @parameters nvarchar(MAX);
set @cmd = N 'select * from dbo.MyTable where col1 = @arg1';
set @parameters = '@arg1 VARCHAR(MAX)';
- 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.
declare @cmd nvarchar(MAX);
set @cmd = N 'select * from dbo.MyTable where col1 = ' + @arg1;
EXEC(@cmd);
--The above command should be rewritten as:
declare @cmd nvarchar(MAX);
declare @parameters nvarchar(MAX);
set @cmd = N 'select * from dbo.MyTable where col1 = @arg1';
set @parameters = '@arg1 VARCHAR(MAX)';
EXEC SP_EXECUTESQL
EXEC SP_EXECUTESQL
@cmd, --dynamic sql command as the first parameter
@parameters, --definition of parameters as the second parameter
@arg1 = @arg1;
--Assign the parameter value to the parameter
Note
- The first 2 parameters of SP_EXECUTESQL (@cmd and @parameters should always be of type nvarchar.
- If the dynamic SQL requires multiple string parameters, the parameters can be written separated by commas.
Example
Declare
@cmd nvarchar(MAX),
@params nvarchar(MAX),
@arg1VARCHAR(MAX) = 'ParamValue1',
@arg2VARCHAR(MAX) = ' ParamValue2';
SET @cmd = 'SELECT * FROM dbo.MyTable WHERE col1=@arg1 and col2=@arg2';
SET @params = '@arg1 VARCHAR(MAX),@arg2 VARCHAR(MAX)';
--Execute the above query
EXEC SP_EXECUTESQL
@cmd,
@params,
@arg1 = @arg1,
@arg2 = @arg2;
Here, is a complete example, which demonstrates the usage of dynamic SQL in a stored procedure in the correct way.
create procedure test_procedure1(@arg1 VARCHAR(MAX))
as
declare @cmd nvarchar(MAX)
declare @parameters nvarchar(MAX)
set @cmd = N 'select * from dbo.MyTable where col1 = @arg1'
set @parameters = '@arg1 VARCHAR(MAX)'
EXEC sp_Executesql @cmd, @parameters, @arg1 = @arg1;
go
--Executing the procedure
declare @argVARCHAR(MAX);
SET @arg = 'Some Text';
EXEC test_procedure1 @arg;
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
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.