February 2011 Archives

Microsoft's Books Online article on SQL Injection does a great job of reviewing the possible attacks against dynamic SQL statements (using EXEC or sp_executesql). I won't re-hash their discussion and suggestions. What I offer below is a sample remediation effort for this set of statements (the @Fields and @Values variables are actually stored procedure parameters):

DECLARE @Fields VARCHAR(1000), @VALUES VARCHAR(1000), @SQL NVARCHAR(2500);
SELECT @SQL = 'INSERT INTO MyTable (' + @Fields + ') VALUES (' + @Values + ')';
EXEC(@SQL);