Dynamic SQL is a mechanism which is used to programmatically generate and execute T-SQL statements. With the help of SQL Server sp_execute, that may be using programming languages like C#, C++ etc., or using the stored procedure to execute the dynamic query.
Dynamic SQL Query from the front end as an input control's value:
Note: I have taken the snapshot, shown below, which highlights the content from my previous
article.
Hex to Varchar
- SELECT cast(cast(0x73656c656374202a2066726f6d20504f5f5452414e53414354494f4e5f44455441494c5320776865726520504f5f4e554d424552203d2031272075706461746520504f5f5452414e53414354494f4e5f44455441494c5320736574204445534352495054494f4e3d202773616e746f73683131312720776865726520504f5f4e554d424552203d2031202d2d as varbinary(max)) as varchar(max))
Output
- SELECT * from TABLE_DETAILS where PO_NUMBER = 1' update TAABLE_DETAILS SET DESCRIPTION= 'santosh111' WHERE PO_NUMBER = 1
A hacker can execute such injected malicious code in many ways, a sample is shown below:
Let's say we have one Webpage with a few input controls. If we enter the code as an input in one of the control's values, the entered malicious code directly maps to one of the parameters in your SQL query, executes it and it displays the result, which results in an injection of malicious code.
- Declare @S VARCHAR(MAX) y
- SET @S =(selectcast(cast(0x73656c656374202a2066726f6d20504f5f5452414e53414354494f4e5f44455441494c5320776865726520504f5f4e554d424552203d2031272075706461746520504f5f5452414e53414354494f4e5f44455441494c5320736574204445534352495054494f4e3d202773616e746f73683131312720776865726520504f5f4e554d424552203d2031202d2d as varbinary(max)) as varchar(max)))
- EXEC(@S)
Dynamic SQL Query in database Stored procedures
In general, to prevent SQL injection, we should use parameterized queries in C# code or the stored procedures. Since we focus on the stored procedures, let's look into them.
Example
An unsafe way of writing stored procedure to execute SQL dynamic queries:
- Create Procedure GetData(@input varchar(350))
- AS
- BEGIN
- declare @sqlDynQuery varchar(400)
- SET @sqlDynQuery = 'Select * from table_name where column_name = ''' + @input + '''''
- EXEC @sqlDynQuery // UNSAFE
- END
If you observe, the SQL code given above is vulnerable to SQL injection. Here, the user inputs are directly passed to the stored procedure's parameters.
Note
The Application is using dynamic SQL statements with EXEC(…) in the stored procedure, as shown above; the above sample code stored procedures gives no protection from SQL injection attacks.
Procedure to prevent Dynamic SQL Injection
How to code the dynamic SQL in a secure way? You should use sp_execute SQL function while executing dynamic queries in the stored procedures.
sp_execute function executes a transact-SQL statement or a block of SQL statements that can be reused many times. The Transact-SQL statement or a block of the statements can contain embedded parameters. Lets modify the procedure to make it resilient to SQL injection attacks:
Example
A safe way of using stored procedure when executing SQL dynamic queries:
- Create Procedure GetData(@input varchar(350))
- AS
- BEGIN
- declare @sqlDynQuery nvarchar(500)
- SET @sqlDynQuery = 'Select * from where table_name where column_name = @input'
- EXECUTE sp_executesql @sqlDynQuery,N' @orderId varchar(250)',@input
- END
Dynamic SQL Query injection prevention mechanism
- The stored procedures offer protection against SQL injection only, if the stored procedure does not contain any dynamic SQL query, otherwise they are as vulnerable as plain dynamic SQL queries, when we use EXEC(…) SQL function to process the SQL query.
- It's better to avoid the use of such dynamic SQL queries; either in direct SQL queries in the programming language code or in the stored procedures, but in some cases, it is not possible and in such scenarios it's better to use QUOTENAME().
- As a good practice, always use sp_execute, while executing the dynamic queries in any stored procedure or when you write any dynamic query in your programming language and execute it.
- Due to the actual text of the Transact-SQL statement in the sp_execute; string does not change between the execution. The query optimizer may match with Transact-SQL statement. Therefore, SQL Server does not have to compile the second statement.
- Using QUOTENAME() to protect against SQL injection. For more details, refer to the link.
- Use the least privileged account to run the stored procedures.