SA0077 : Avoid executing dynamic code using EXECUTE statement
Introduction
Section titled “Introduction”The unnecessary use of EXECUTE for dynamic SQL can lead to inefficiencies and security risks.
Description
Section titled “Description”Dynamic SQL execution using the EXECUTE statement in SQL Server is a common approach but can lead to several issues. It lacks parameterization, which increases the risk of SQL injection attacks and can diminish query performance by reducing the likelihood of reusing execution plans.
For example:
-- Problematic dynamic SQL executionEXECUTE('SELECT * FROM Employees WHERE EmployeeID = ' + @EmployeeID);This approach is problematic because:
-
It exposes the query to SQL injection attacks as the parameters are concatenated directly into the SQL string.
-
It is less efficient because SQL Server has limited ability to reuse execution plans, impacting query performance.
How to fix
Section titled “How to fix”Use sp_executesql to execute dynamic SQL statements securely and efficiently, minimizing the risks associated with SQL injection and improving query performance through parameterization.
Follow these steps to address the issue:
1.Rewrite dynamic SQL queries to use sp_executesql instead of EXECUTE . This allows for parameterized queries that are safer and more efficient.
2.Define the query parameters explicitly in a @Params variable to provide data type information and enable plan reuse.
3.Call sp_executesql with the query string, parameter definition, and actual parameter values.
For example:
-- Example using sp_executesql for dynamic SQL executionDECLARE @sql NVARCHAR(MAX);DECLARE @Params NVARCHAR(MAX);DECLARE @EmployeeID INT;
SET @sql = N'SELECT * FROM Employees WHERE EmployeeID = @EmployeeID';SET @Params = N'@EmployeeID INT';
-- Safe and efficient executionEXEC sp_executesql @sql, @Params, @EmployeeID = @EmployeeID;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”3 hours per issue.
Categories
Section titled “Categories”Design Rules, Performance Rules, Security Rules
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”DECLARE @TableName AS VARCHAR( 100 )DECLARE @SQL AS VARCHAR( 1000 )
SET @TableName = 'MyTable'
SELECT @SQL = 'CREATE TABLE ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL PRIMARY KEY, FIELDNAME varchar(10))'
EXEC( @SQL )
EXECUTE( @SQL )Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0077 : Avoid executing dynamic code using EXECUTE statement. | 10 | 0 |
| 2 | SA0077 : Avoid executing dynamic code using EXECUTE statement. | 12 | 0 |