Introduction

Sooner or later everyone who works with SQL Server hears that it is better to avoid dynamic SQL at all cost. Dynamic SQL will force you to give out more permissions than static SQL. There is even a danger that someone will successfully execute their own code on your server because you use dynamic SQL. Dynamic SQL often requires less thinking, less code, and seems to require less maintenance time than the static SQL alternatives.

This is part one of a two-part series that will describe how SQL Server handles static SQL and dynamic SQL. In part 1, I will discuss and demonstrate issues related to security. Part 2 will be dedicated to cases where you will be tempted to use dynamic SQL and the static SQL alternatives.

In this series I plan to look at each of the above reasons for not using or for using dynamic SQL and present reasons to avoid it. I'll show you how static SQL will work as well if not better. Many static SQL solutions will require more coding than dynamic SQL. This adds to the temptation to use it when in a hurry. Resist the temptation. The use of static SQL will cause you less problems in the future.

This article in no way is meant to say you should never use dynamic SQL. It is just meant to show you should think more than once before deciding to use it and consider if you really can't use static SQL. In a previous article, called When To Use Dynamic SQL, I looked at a few times when I believe the only solution is to use dynamic SQL.

Throughout this series I'll use the Pubs database for my examples.

Dynamic SQL Requires More Permissions

For those who plan to try the examples I give here, please open your Query Analyzer and connect to the Pubs database using a login that only has access to the database but has no read nor write permissions (I created one called PubsUser); this will be our test user account. Now open a second connection with sa privileges; we'll use this connection to create a stored procedure.

Paste the following code in the connection that has sa privileges and execute it:

CREATE PROCEDURE TestPermissions
(
@ExecSection int
)
AS
DECLARE @Query varchar(200)
IF @ExecSection = 1
            BEGIN
                        SELECT
*
                        FROM
Authors
            END
ELSE
            BEGIN
                        SET
@Query = 'SELECT * FROM Authors'
                        EXEC
(@Query)
            END
GO
GRANT EXECUTE ON [dbo].[TestPermissions]  TO PubsUser
GO

Now execute the following code using the window connected with your test user account:

exec TestPermissions
@ExecSection = 1

You should see all the records in the authors table. Now change the 1 in the above code to a 2 and execute again. Now you will see the following error:

Server: Msg 229, Level 14, State
5, Line 0
SELECT permission denied on object 'authors', database
'pubs', owner 'dbo'.

Let's analyze what happened with the two executions of the same stored procedure using the same permissions. The first time we executed TestPermissions the static SQL was executed. Since the code is static SQL and the owner of the stored procedure and objects referenced in the stored procedure are the same, SQL Server assumed the creator of the stored procedure intended that anyone with execute permissions to the stored procedure should have permissions to execute the code on the objects in the stored procedure. It did not verify the permissions. If you execute the SELECT statement outside the stored procedure you will get the same error as you got when executing the dynamic portion of the stored procedure.

The second time we executed TestPermissions the dynamic SQL was executed. When SQL Server encounters dynamic SQL it automatically verifies that the person executing the stored procedure has permissions to execute the code found in the dynamic SQL. SQL Server found that our test user doesn't have SELECT permissions to the authors table and so issued the error.

To fix this problem you can give the test user account SELECT permissions on the table authors. Paste and execute the following code into the window with sa privileges:

GRANT SELECT ON [dbo].[authors]  TO PubsUser
GO

Now switch to your other window and execute the stored procedure with @ExecSection = 2. The execution is now successful. However, you had to specifically grant SELECT permissions before the dynamic SQL would work. The same would happen if we had used a DELETE , INSERT, or UPDATE query in the stored procedures.

The advantage here for the static SQL is that you can give a user permissions to do what the user needs to do in a controlled environment (the stored procedure) by giving the user execute permissions to the stored procedure and nothing more. When using dynamic SQL you must give the user appropriate permissions in the database or the stored procedure execution will fail. These added permissions will allow any user to manipulate data outside the controlled environment (again the stored procedure) and this can cause a serious security concern. You won't be able to control what kind of UPDATE is executed on the authors table if the user has UPDATE permissions. A user without UPDATE permissions won't be able to update anything unless you create an UPDATE statement in a stored procedure that the user has permissions to execute.

Executing Unauthorized Code

When using dynamic SQL combined with values from a user it is possible for a user to execute code other than what is in a stored procedure.

Paste and execute the following code into the Query Analyzer window that has sa privileges:

CREATE PROCEDURE TestDynamic
(
@LastName varchar(50) = ''
)
AS
DECLARE @Query varchar(500)
SET @Query = '
SELECT *
FROM authors'
IF @LastName <> ''
            SET @Query
= @Query + CHAR(13) + 'WHERE au_lname
like ''' + @LastName + '%'''
SET @Query = @Query + CHAR(13) +
'ORDER BY au_lname'
PRINT @Query
EXEC (@Query)
GO
GRANT EXECUTE ON [dbo].[TestDynamic]  TO PubsUser
GO

For this example we'll say that we use dynamic SQL often for viewing and modifying data. The simplest way to give users permissions is to make them part of the fixed database roles db_datareader and db_datawriter. As mentioned in the previous section, when using dynamic SQL you often have to give more permissions than with static. Modify the security for the test user you created so they can read and write in the pubs database.

Before executing the following code, be aware that it will modify the authors table. This is an example of how a user can exploit your use of dynamic SQL in a stored procedure with your need to give more permissions than you would need to give for static SQL:

EXEC TestDynamic @LastName = ''' update authors set phone = ''''--'

Safest way to avoid this possibility is to not use dynamic SQL. However, if you must use dynamic SQL there are a few ways you can minimize if not eliminate the possibility that someone executes unauthorized code.

First make sure the variables that accept text characters from a user are only long enough to contain possible valid entries. In our example, it is doubtful a person's last name will reach 50 characters. Also, since the user would be doing a search limited by whats in your database you can check for the max length you have right now and make the variable one or two longer than that.

Next, you can use the REPLACE function to find a single quote and add another single quote. This will ensure that the single quote entered by the user doesn't complete the like comparison and will keep all the characters they enter within the quotes that make it the character string to search for in table authors.

Last, is that if none of your dynamic SQL requires changes to be made in the database then don't give any permissions that allow UPDATE's, INSERT's, and DELETE's. That way if a user manages to find a way to execute unauthorized code SQL Server will reject it because they lack the permissions to execute it.

The above mentioned changes will make our test stored procedure look like this:

ALTER PROCEDURE TestDynamic
(
@LastName varchar(30)
= ''
)
AS
DECLARE @Query varchar(500)
SET @LastName = REPLACE (@LastName,'''','''''')
SET @Query = '
SELECT *
FROM authors'
IF @LastName <> ''
            SET @Query
= @Query + CHAR(13) + 'WHERE au_lname
like ''' + @LastName + '%'''
SET @Query = @Query + CHAR(13) +
'ORDER BY au_lname'
PRINT @Query
EXEC (@Query)
GO

After altering the stored procedure run the same code as before to execute the stored procedure:

EXEC TestDynamic @LastName = ''' update authors set phone = ''''--'

In this example you will notice this has done two things that defeat the unauthorized code from successfully executing. First the update statement got cut off at the = sign. If it had run we would have gotten an error. The second, and I think most important, is that the single quote intended to complete the like comparison simply became part of the characters used as the search criteria for the authors table.

Conclusions

Plan to avoid dynamic SQL. It always requires more permissions than static SQL. If forced to use dynamic SQL, plan carefully how you assign permissions; only assign permissions that are absolutely needed. Make sure you have enough safeguards in place so that a user can only execute what you want them to execute and no more. If you can't guarantee anyone can execute unauthorized code through your dynamic SQL then don't use it.