You are a database developer on an instance of SQL Server 2008. Your Prod database contains tables that contain purchasing-related data. The database contains the following tables:
You need to create queries against the tables that use dynamic SQL. Your IT management has recently implemented security policies mandating that all Transact-SQL code must minimize the likelihood of SQL injection attacks.
What should you do to minimize the likelihood of SQL injection attacks?
A. Use the EXECUTE statement to execute dynamic SQL.
B. Implement all dynamic SQL using CLR functions and procedures.
C. Implement all dynamic SQL within Transact-SQL stored procedures.
D. Use the sp_executesql system stored procedure to execute dynamic SQL.
Correct Answer: D
Explanation/Reference:
SQL injection attacks occur when a user maliciously provides input that is embedded into a dynamic SQL statement. The sp_executesql system stored procedure accepts parameters and constructs the dynamic SQL. This eliminates the need to dynamically construct SQL statements with string concatenation, and minimizes the likelihood of SQL injection attacks. This also is more likely to improve performance because with parameters being used, cached execution plans are more likely to be reused. The sp_executesql system stored procedure accepts an @stmt parameter that contains one or more SQL statements, an optional @params parameter to identify parameters used in the SQL statements, and optional user-defined parameter values. The following Transact-SQL illustrates how you might use the sp_executesql with the tables in this scenario:
-- Selects the names of all shipping methods used for Vendor 84
SELECT @sql =N’SELECT DISTINCT sm.Name ‘ + N’FROM Purchasing.PurchaseOrderHeader p ‘ +N’INNER JOIN Purchasing.ShipMethod sm ‘ +N’ON p.ShipMethodID = sm.ShipMethodID ‘ +N’WHERE p.VendorID = @v’;
SELECT @params = N’@v int’
EXEC sp_executesql @sql, @params, 84
You should not use the EXECUTE statement to execute dynamic SQL. Using the EXECUTE statement to execute dynamic SQL increases the likelihood of SQL injection attacks.
You should not implement all dynamic SQL using CLR functions and procedures. CLR functions and procedures still introduce the possibility of SQL injection attacks if they pass dynamically constructed SQL to the database for execution.
You should not implement all dynamic SQL within Transact-SQL stored procedures. Although using parameterized stored procedures may decrease the likelihood of SQL injection attacks, SQL injection may still occur when dynamic SQL is executed from within a stored procedure. For example, suppose you have the following stored procedure defined:
CREATE PROCEDURE getpos (@sname varchar(50))AS
DECLARE @sql nvarchar(max) =N’SELECT p.PurchaseOrderID, sm.Name ‘ +N’FROM PurchaseOrderHeader p ‘ +N’INNER JOIN
ShipMethod sm ‘ +N’ON p.ShipMethodID = sm.ShipMethodID ‘ +
N’WHERE sm.Name LIKE ”’ + @sname +N’%”;’
With this stored procedure, a user could maliciously pass a parameter of ”’; DROP TABLE ShipMethod; --’ and introduce malicious SQL code.
