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

Comments

• Anonymous
  September 05, 2007
  PingBack from http://msdnrss.thecoderblogs.com/2007/09/05/sql-injection-dynamic-sql-within-stored-procedures/

• Anonymous
  September 05, 2007
  Hi, I use this technique to select rows with specified in parameters order. Like this: SET @sql = 'SELECT * FROM Products ORDER BY' + @OrderBy How you think, I need to avoid this technique or not? And how can i sort rows by order specified at SP parameters? Thanks, Edy.

• Anonymous
  September 05, 2007
  If @OrderBy is user controllable, this is a SQL injection flaw. Assuming you have this stored procedure create proc [dbo].[GetProducts] (@orderby nvarchar(max)) as declare @sql nvarchar(max) set @sql = 'select * from Products order by ' + @orderby exec sp_executesql @sql a malicious user entering @orderby='ProductID drop table Products' can, given enough privilege, drop the table. You can employ one of the following countermeasures:-

1. Use Case statement avoiding dynamic SQL. create proc [dbo].[GetProducts] (@orderby nvarchar(max)) as select * from products order by case when @orderby = 'ProductID' then ProductID     when @orderby = 'ProductName' then ProductName else ProductID end
2. Validate @orderBy. If you are expecting only column name, e.g. "ProductID", validate against a regular expression for only alphabets. If you are expecting column name, ASC/DESC, e.g. "ProductID, ASC", split on ",", make sure you get two parts, validate each to be a word with only alphabets. This validation should be well thought of. If you simply validate @orderBy to have alphabets and spaces, it will still be vulnerable. (@orderby='ProductID drop table Products' is only alphabets and spaces). The disadvantage in this second approach (validation with dynamic SQL) is that you will still need to grant at least SELECT permissions on the table.