Getting to Grips with Dynamic SQL: Validating inputs

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.

Validating Objects

When using dynamic objects, then use you use the sys or 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 + not 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.

Example 1: Using System Objects to set the dynamic SQL variable's value

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;

Example 2: Using System Objects to THROW a custom error

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;

Validating Other Values

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

Example 3: Using an IF to validate the value

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;