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