Skip to content

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:

-- Example of problematic dynamic SQL
DECLARE @sql VARCHAR(500);
DECLARE @tableName VARCHAR(100);
SET @tableName = 'UserInput'; -- Assuming this variable is populated through user input
SET @sql = 'SELECT * FROM ' + @tableName;
EXEC(@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:

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:

-- Corrected dynamic SQL using sp_executesql
DECLARE @sql NVARCHAR(500);
DECLARE @tableName NVARCHAR(100);
SET @tableName = N'UserInput'; -- Assuming this variable is populated through user input
SET @sql = N'SELECT * FROM ' + QUOTENAME(@tableName);
EXEC 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.

1 hour per issue.

Design Rules, Security Rules

SQL Injection

The Importance of nvarchar

CREATE PROCEDURE TestSA0172.ProcedureWithUnsafeDynamicSql
@custid nchar(5) = NULL,
@shipname nvarchar(40) = NULL ,
@TableName varchar(100)
AS
declare @v_custid nchar(5) = NULL,
@v_shipname nvarchar(40) = NULL,
@v2_custid nchar(7) = 'aa' + @v_custid,
@v_tableName nvarchar(100)
DECLARE @sql nvarchar(4000)
DECLARE @sql1 nvarchar(4000)
DECLARE @sql2 nvarchar(4000)
DECLARE @sql3 nvarchar(4000)
SELECT @sql1 = @tableName+ '1'
SELECT @sql3 = @sql1
exec ( @sql3 + @sql1)
SELECT @sql2 = '' + @sql1
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
' FROM dbo.Orders WHERE 1 = 1 '
exec ( @sql + @sql2 + @sql1 + @shipname)
DECLARE @sqlSafe nvarchar(4000)
set @sql1 = quotename(@sql1) + quotename(@sql2);
set @sqlSafe = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
' FROM dbo.' + @sql1 +' WHERE 1 = 1 '
exec (@sqlSafe)
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);
SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
FROM AdventureWorks2008R2.HumanResources.Employee
WHERE BusinessEntityID = @level' + @sql1;
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
EXECUTE sp_executesql @statement = @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
declare @result int
EXECUTE @result = sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
EXECUTE @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

Analysis Rules