SQL injection: Dynamic SQL within stored procedures

Most resources on the internet concentrate on dynamic SQL in the data access code as the cause of SQL injection. Although lesser known, SQL injection is also possible if the stored procedure itself constructs dynamic SQL and executes it with the “exec” or “sp_executesql” statements. This is in spite of using parameterized input in data access code to call the stored procedure.

This code is exploitable if the @CategoryID parameter is user controllable and has not been validated.

CREATE Procedure GetProductsByCategory

(

    @CategoryID VARCHAR(4000)

)

AS

DECLARE @sql AS VARCHAR(4000)

SET @sql = 'SELECT ProductID, ModelName, UnitCost, ProductImage

FROM CMRC_Products WHERE CategoryID = ' +

@CategoryID + ' ORDER BY ModelName, ModelNumber'

SP_EXECUTESQL(@sql)

This is how to fix it by avoiding dynamic SQL. White list Input validation and using least privileged accounts are the other countermeasures.

CREATE Procedure GetProductsByCategory

(

    @CategoryID VARCHAR(4000)

)

AS

DECLARE @sql AS VARCHAR(4000)

SET @sql = 'SELECT ProductID, ModelName, UnitCost, ProductImage

FROM CMRC_Products WHERE CategoryID = @CategoryID ORDER BY ModelName, ModelNumber'

SP_EXECUTESQL @sql, N'@CategoryID VARCHAR(4000)', @CategoryID