In the last article I touched on how you would build a dynamic query which is likely, in the end, accepting an input from a user to produce a dynamic statement. These work well but often, for added security, it’s best to also validate the input value prior to executing the statement. This either means that the dynamic SQL isn’t executed at all, or you can choose to return a custom error message.
When using dynamic objects, then use you use the
INFORMATION_SCHEMA objects to check that the object does intend exist. Personally I prefer the
sys objects, as these include all the specific SQL Server information and there is a long (disputed) claim that the schema reported by
INFORMATION_SCHEMA can be wrong. (I am someone who has disputed this, and I and others have never received any information to validate the documentation’s claim). You can use these system objects to check that the object does indeed exist in the database and then take appropriate action. One method is is use new variables who’s values are assigned by querying the system objects. If no rows are returned then the variables have the value
NULL assigned. This means when you concatenate the value (using
CONCAT()) the dynamic SQL’s value becomes
NULL as well, and so no dynamic SQL is run. Alternatively you can define the value of the dynamic SQL variable using a query against the system objects and concatenate there; this is my preferred method. You could also, however, use
OBJECT_ID() or an
EXISTS to check the object is valid and
THROW a custom error in the event it isn’t.
If no object is found, then
@SQL will be assigned the value
NULL and no dynamic SQL will be run.
DECLARE @SQL nvarchar(MAX); SELECT @SQL = N'SELECT * FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' WHERE ID = @ID;' FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id WHERE s.[name] = @SchemaName AND t.[name] = @TableName; EXEC sys.sp_executesql @SQL, N'@ID int', @ID;
If no object is found, then a custom error message is sent to the application.
IF OBJECT_ID(@SchemaName + N'.' + @TableName) IS NULL THROW 81737, N'An invalid object has been supplied, the dynamic statement has been aborted.', 16; DECLARE @SQL nvarchar(MAX); SET @SQL = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WHERE ID = @ID;'; EXEC sys.sp_executesql @SQL, N'@ID int', @ID;
Although rare (and if you;’re doing this I really suggest you need to rethink your design) you might find yourself injecting values for things other than an object name. I have, for example, written something in the past that injects a dynamic data type with both length, scale and precision. For a scale and precision, this isn’t really a problem from an injection issue if you define then as a
tinyint, however, length is a different matter as it can have a value of up for 8,000 or
MAX. Truthfully, as well, a
varchar(4) isn’t really going to open you up to injection, but that doesn’t mean that you shouldn’t validate it. Data types can actually be validated in the same way as shown in the previous example (using
sys.types), so I won’t cover that here.
For such validation, you can hard code the values in to the procedure and ensure the value meets the requirements expected. If you have to do this work multiple times you’ll either need to repeat the code or use a Function to put the data in, which could return a
bit value (
1 for valid, and
0 for not).
IF NOT (TRY_CONVERT(int,@Length) <= 8000 OR @Length = 'MAX') THROW 91712, N'An Invalid value has been provided for the parameter ''@length''.',16; DECLARE @SQL nvarchar(MAX); SELECT @SQL = N'ALTER TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' ADD COLUMN ' + QUOTENAME(@ColumnName) + N'varchar(' + @Length + N');' FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id WHERE s.[name] = @SchemaName AND t.[name] = @TableName; EXEC sys.sp_executesql @sql;