Click or drag to resize

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

The topic describes the SA0172 analysis rule.

Message

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

Description

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

The rule checks stored procedures, which construct and execute dynamic SQL.

The rule reports any executed dynamic SQL, which is constructed using parameters that are not escaped.

The default parameter escaping functions are PARSENAME, QUOTENAME and REPLACE, but can also be configured using the using ParameterEscapeFunctions rule parameter.

In order to prevent SQL injection and resolve the issue, review the parameters or variables reported by the rule and escape external parameters used for constructing the dynamic SQL.

More about SQL Injection can be found in the see also section.

Scope

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

Parameters
NameDescriptionDefault 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.

Categories

Design Rules, Security Rules, New Rules

Additional Information
Example Test Script
 1CREATE PROCEDURE search_orders 
 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

 MessageLineColumn
1SA0172 : The dynamic SQL was constructed using unsafe variable (@sql3), which is assigned not escaped extenral parameter.170
2SA0172 : The dynamic SQL was constructed using unsafe variable (@sql1), which is assigned not escaped extenral parameter.170
3SA0172 : The dynamic SQL was constructed using unsafe variable (@sql2), which is assigned not escaped extenral parameter.230
4SA0172 : The dynamic SQL was constructed using unsafe variable (@sql1), which is assigned not escaped extenral parameter.230
5SA0172 : The dynamic SQL was constructed using not escaped extenral parameter (@shipname).230
See Also

Other Resources