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 ....) )