SQL Server 2005 : Dynamic T-SQL - Dynamic SQL Security Considerations

Permissions to Referenced Objects

Dynamic SQL is invoked in a different scope than static SQL. This is extremely important from an authorization perspective, because upon execution, permissions for all objects referenced in the dynamic SQL will be checked. Therefore, in order for the dynamic SQL to run without throwing an authorization exception, the user executing the dynamic SQL must either have access directly to the referenced objects or be impersonating a user with access to the objects.

This creates a slightly different set of challenges from those you get when working with static SQL stored procedures, due to the fact that the change of context that occurs when invoking dynamic SQL breaks any ownership chain that has been established. If you need to manage a permissions hierarchy such that users should have access to stored procedures that use dynamic SQL, but not to the base tables they reference, make sure to become intimately familiar with certificate signing and the EXECUTE AS clause.

Interface Rules

There are other types of optional parameters that developers often try to use with dynamic SQL. These parameters involve passing table names, column lists, ORDER BY lists, and other modifications to the query itself into a stored procedure for concatenation.

As a general rule, you should never, ever pass any database object name from an application into a stored procedure (and the application should not know the object names anyway). If you absolutely must modify a table or some other object name in a stored procedure, try to encapsulate the name via a set of parameters instead of allowing the application to dictate.

For instance, assume you were working with the following stored procedure:

CREATE PROC SelectDataFromTable
    @TableName VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @sql VARCHAR(MAX)

    SET @sql = '' +
        'SELECT ' +
            'ColumnA, ' +
            'ColumnB, ' +
            'ColumnC ' +
        'FROM ' + @TableName

    EXEC(@sql)
END

Table names cannot be parameterized, meaning that using sp_executesql in this case would not help in any way. However, in virtually all cases, there is a limited subset of table names that can (or will) realistically be passed into the stored procedure. If you know in advance that this stored procedure will only ever use tables TableA, TableB, and TableC, you can rewrite the stored procedure to keep those table names out of the application while still providing the same functionality.

The following stored procedure is an example of how you might provide dynamic table functionality, while abstracting the names somewhat to avoid coupling issues:

CREATE PROC SelectDataFromTable
    @UseTableA BIT = 0,
    @UseTableB BIT = 0,
    @UseTableC BIT = 0
AS
BEGIN
    SET NOCOUNT ON

    IF (
        CONVERT(TINYINT, COALESCE(@UseTableA, 0)) +
        CONVERT(TINYINT, COALESCE(@UseTableB, 0)) +
        CONVERT(TINYINT, COALESCE(@UseTableC, 0))
        ) <> 1
    BEGIN
        RAISERROR('Must specify exactly one table', 16, 1)
        RETURN
    END

    DECLARE @sql VARCHAR(MAX)

    SET @sql = '' +
        'SELECT ' +
            'ColumnA, ' +
            'ColumnB, ' +
            'ColumnC ' +
        'FROM ' +
            CASE
                 WHEN @UseTableA = 1 THEN 'TableA'
                 WHEN @UseTableB = 1 THEN 'TableB'
                 WHEN @UseTableC = 1 THEN 'TableC'
            END

    EXEC(@sql)
END

					  

This version of the stored procedure is obviously quite a bit more complex, but it is still relatively easy to understand. The IF block validates that exactly one table is selected (i.e., the value of the parameter corresponding to the table is set to 1), and the CASE expression handles the actual dynamic selection of the table name.

If you find yourself in a situation in which even this technique is not possible, and you absolutely must support the application passing in object names dynamically, you can at least do a bit to protect from the possibility of SQL injection problems. SQL Server includes a function called QUOTENAME, which bracket-delimits any input string such that it will be treated as an identifier if concatenated with a SQL statement. For instance, QUOTENAME('123') returns the value [123].

By using QUOTENAME, the original version of the dynamic table name stored procedure can be modified such that there will be no risk of SQL injection:

CREATE PROC SelectDataFromTable
    @TableName VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @sql VARCHAR(MAX)

    SET @sql = '' +
        'SELECT ' +
            'ColumnA, ' +
            'ColumnB, ' +
            'ColumnC ' +
        'FROM ' + QUOTENAME(@TableName)

    EXEC(@sql)
END

Unfortunately, this does nothing to fix the interface issues, and modifying the database schema may still necessitate a modification to the application code.