Post
Ask Question

Dynamic SQL Query Injection And Its Prevention Mechanism

What is Dynamic SQL?

Dynamic SQL is a mechanism that 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 stored procedures. Since we focus on the stored procedures, let's look into them.

Example

An unsafe way of writing stored procedures 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 give 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 the 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. Let's 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; the string does not change between the execution. The query optimizer may match with the 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.


Similar Articles