SQL Server: Sanitizing @param against injection attacks

Use sp_executesql and the built-in quotename(). This article, The Curse and Blessings of Dynamic SQL, is pretty much the definitive reference.


Rather than calling EXEC(@somesql), I suggest using the sp_executesql stored procedure. Specifically, this allows you to pass parameters, and the system will check that the parameters are valid.


You could first query the schema information with regular T-SQL and make sure the table name exists first. This way, if it's malformed SQL, it won't execute as code. It will just be a VARCHAR table name.

DECLARE @Table AS VARCHAR(MAX)
DECLARE @Exists AS BIT

SET @Table = 'Vicious malformed dynamic SQL'

SELECT  @Exists = COUNT(TABLE_NAME) 
FROM    INFORMATION_SCHEMA.TABLES 
WHERE   TABLE_NAME = @Table

IF (@Exists = 1)
    BEGIN
    PRINT 'Table exists'
    -- Execute dynamic SQL.
    END
ELSE
    PRINT 'Invalid table'

(Or simply use IF EXISTS (SELECT ....) )