SA0172 : The dynamic SQL is constructed using external parameters, which is not ensured to be safe

Dynamic SQL construction without proper escaping and data type considerations can lead to significant vulnerabilities and performance issues in SQL Server.

Description

In T-SQL and SQL Server environments, dynamic SQL is often used for flexibility and real-time query generation. However, building and executing dynamic SQL without properly escaping parameters exposes SQL Server databases to SQL injection attacks.

For example:

SQL
Copy
1-- Example of problematic dynamic SQL
2DECLARE @sql VARCHAR(500);
3DECLARE @tableName VARCHAR(100);
4SET @tableName = 'UserInput'; -- Assuming this variable is populated through user input
5SET @sql = 'SELECT * FROM ' + @tableName;
6EXEC(@sql);

This code can be problematic because it concatenates user input directly into the SQL statement, risking SQL injection attacks. Additionally, using a VARCHAR for dynamic SQL can lead to loss of information when dealing with multilingual data. It is a common SQL Server best practice to use NVARCHAR for better handling of Unicode data.

  • Leaving user input unescaped allows attackers to manipulate the SQL logic, potentially leading to data breaches.

  • Using VARCHAR instead of NVARCHAR can cause issues with character data, especially for international applications.

How to fix

This section outlines steps to mitigate the risk of SQL injection when using dynamic SQL by properly escaping parameters and using appropriate data types.

Follow these steps to address the issue:

  1. Identify all parameters or variables used in constructing dynamic SQL. Ensure that these are the ones reported by the sa0172 rule.

  2. Use parameterized queries or the sp_executesql procedure to execute dynamic SQL safely. This not only binds parameters but also handles escaping effectively.

  3. Switch from VARCHAR to NVARCHAR to ensure proper handling of Unicode data, which is especially important for international applications.

For example:

SQL
Copy
1-- Corrected dynamic SQL using sp_executesql
2DECLARE @sql NVARCHAR(500);
3DECLARE @tableName NVARCHAR(100);
4SET @tableName = N'UserInput'; -- Assuming this variable is populated through user input
5SET @sql = N'SELECT * FROM ' + QUOTENAME(@tableName);
6EXEC sp_executesql @sql;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
ParameterEscapeFunctions

Comma separated list of functions that can escape the procedure parameters in order to make them safe for use in dynamic SQL.

,parsename,quotename,replace,
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Security Rules

Additional Information
Example Test SQL
SQL
Copy
 1CREATE PROCEDURE TestSA0172.ProcedureWithUnsafeDynamicSql 
 2@custid   nchar(5)     = NULL,
 3@shipname nvarchar(40) = NULL ,
 4@TableName varchar(100)
 5AS
 6declare @v_custid   nchar(5)     = NULL,
 7        @v_shipname nvarchar(40) = NULL,
 8        @v2_custid  nchar(7)     = 'aa' + @v_custid,
 9        @v_tableName nvarchar(100)
10
11DECLARE @sql nvarchar(4000)
12DECLARE @sql1 nvarchar(4000)
13DECLARE @sql2 nvarchar(4000)
14DECLARE @sql3 nvarchar(4000)
15SELECT @sql1 =  @tableName+ '1'
16SELECT @sql3 =  @sql1
17exec ( @sql3 + @sql1)
18SELECT @sql2 = '' + @sql1
19
20SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
21              ' FROM dbo.Orders WHERE 1 = 1 '  
22
23exec ( @sql + @sql2 + @sql1 + @shipname)
24DECLARE @sqlSafe nvarchar(4000)
25set @sql1 = quotename(@sql1) + quotename(@sql2);
26set @sqlSafe  = ' SELECT OrderID, OrderDate, CustomerID,  ShipName ' +
27              ' FROM dbo.' +  @sql1  +' WHERE 1 = 1 '
28
29exec (@sqlSafe)
30
31
32DECLARE @IntVariable int;
33DECLARE @SQLString nvarchar(500);
34DECLARE @ParmDefinition nvarchar(500);
35DECLARE @max_title varchar(30);
36
37SET @IntVariable = 197;
38SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) 
39   FROM AdventureWorks2008R2.HumanResources.Employee
40   WHERE BusinessEntityID = @level'  + @sql1;
41SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';
42
43EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
44
45EXECUTE sp_executesql @statement = @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
46
47declare @result int
48
49EXECUTE @result = sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
50
51EXECUTE @result = sp_executesql @statement = @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
Analysis Results
  Message Line Column
1 SA0172 : The dynamic SQL was constructed using unsafe variable (@sql3), which is assigned not escaped extenral parameter. 17 0
2 SA0172 : The dynamic SQL was constructed using unsafe variable (@sql1), which is assigned not escaped extenral parameter. 17 0
3 SA0172 : The dynamic SQL was constructed using unsafe variable (@sql2), which is assigned not escaped extenral parameter. 23 0
4 SA0172 : The dynamic SQL was constructed using unsafe variable (@sql1), which is assigned not escaped extenral parameter. 23 0
5 SA0172 : The dynamic SQL was constructed using not escaped extenral parameter (@shipname). 23 0
See Also

Other Resources

Analysis Rules