SQL injection: Dynamic SQL within stored procedures

 

 

Sent to you by Hudong via Google Reader:

 

 

SQL injection: Dynamic SQL within stored procedures

via MSDN Blogs by Varun Sharma on Sep 05, 2007

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

 

 

Things you can do from here:

• Visit the original item
  on MSDN Blogs
• Subscribe to MSDN Blogs using Google Reader
• Get started using Google Reader to easily keep up with all your favorite sites