(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)
The Do's
Always white-list your identifiers.
This can be easily done using system tables or views such assys.Tables
+sys.Column
orinformation_Schema.Column
.Always use
sysname
as the data type for identifiers.
sysname
is 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_executeSql
to 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).
The Don'ts
- 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!
Top comments (0)