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.

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:
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.

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:
-
Identify all parameters or variables used in constructing dynamic SQL. Ensure that these are the ones reported by the sa0172 rule.
-
Use parameterized queries or the sp_executesql procedure to execute dynamic SQL safely. This not only binds parameters but also handles escaping effectively.
-
Switch from VARCHAR to NVARCHAR to ensure proper handling of Unicode data, which is especially important for international applications.
For example:
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;

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

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, |

The rule does not need Analysis Context or SQL Connection.


Design Rules, Security Rules


SQL
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; |

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 |
