SQL Server: Injection Attack

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 DBA/Developer might use the sp_executesql with the tables in this scenario:

-- Selects the names of all shipping methods used for Vendor

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

DBA/Developer 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. DBA/Developer 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. DBA/Developer 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 DBA/Developer 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.