(This is the short version (though it includes all the bullets). A longer version, including some text explanations and links is posted on my blog)
Always white-list your identifiers.
This can be easily done using system tables or views such as
sysnameas the data type for identifiers.
sysnameis a built in data type that is equivalent to nvarchar(128) but it’s non-nullable. SQL Server use that data type internally for all objects names.
Always use exec
sp_executeSqlto execute your dynamic SQL (unless it needs no parameters which is a rare case).
set @sql = N'select col from '+ quotename(@table) N'+ where otherCol = @value'; exec sp_executeSql @sql, N'@value varchar(100)', @value
- Always use parameters for data.
set @sql = N'select col from '+ quotename(@table) N'+ where otherCol = @value'; -- @table should already be white-listed before you execute the query, so that's O.K
Always wrap identifiers with
quotename. othereise, your statement will break as soon as it contains an identifier with a white space (or one of many other “special” chars).
- Never pass SQL commands or clauses in parameters.
set @placeholder1 = 'select a, b, c'; set @placeholder2 = 'where x = y'; set @sql = @placeholder1 + 'from tableName '+ @placeholder2;
- Never concatenate parameters into your dynamic SQL string.
set @sql = 'select * from table where x = '+ @x
Did I leave anything out? If you think I did, please leave a comment so I can improve my post. Thanks!