There are times when you need to write TSQL code that creates specific TSQL Code and executes it. When you do this you are creating dynamic TSQL code. The code you use to create dynamic TSQL might be simple, or it might be complex. When writing dynamic TSQL you need to understand how dynamic code opens the possibilities for a SQL injection attack. In this article I explain why you might want to use dynamic TSQL and how to generate dynamic TSQL. I also will explore SQL Injection and discuss how to avoid a SQL Injection attack in your dynamic TSQL code.
What is dynamic TSQL and why would you want to use it?
What is dynamic TSQL anyway? Dynamic TSQL is code that is potentially different every time you run it. It is a batch of TSQL code that is generated and executed on-the-fly. The code generated on-the-fly is created based on some condition or parameters in the batch. When the “conditions or parameters” are different the TSQL code produces different TSQL to be executed.
You typically use dynamic TSQL when you want to programmatically determine what TSQL you need based on parameters and/or data in database tables. The uses for dynamic TSQL are endless. Here are two examples of when you might want to use dynamic TSQL:
- You want a user to select some criteria from a drop down list that might cause the query to run differently, like sort order
- Your application doesn’t know the name of the table to run against until run time.
Because the TSQL language does not allow you to use variables or parameters to specific table or column names, dynamic TSQL can be used instead.
To better understand dynamic TSQL let’s look at a few examples.
Creating Simple Dynamic T SQL
For the first example of how to create dynamic TSQL let’s consider the following situation. Suppose you have an application where the user interface allows the user to select the table they want to read from a drop down list. Therefore each time someone uses the interface they could pick a different table from which they want to return data. For this example let’s assume this user interface displays table information from the AdventureWorks2012 database and the user picks the AdventureWorks2012.Sales.SalesOrderDetail table. The code in Listing 1 shows a method of using dynamic TSQL code to return the TOP 10 records from AdventureWorks.Sales.SalesOrderDetail table.
-- Declare variable to hold dynamic TSQL code DECLARE @CMD nvarchar(1000); -- Declare name of table to read DECLARE @Table nvarchar(125); SET @Table = 'AdventureWorks2012.Sales.SalesOrderDetail'; -- Build dynamic TSQL Statement SET @CMD = 'SELECT TOP 10 * FROM ' + @Table; --Execute dynamic TSQL Statement EXECUTE (@CMD);
Listing 1: Simple Dynamic TSQL example
The code in Listing 1 first declares a variable name @CMD to hold the dynamic SELECT statement that is going to be built and the @Table variable to hold the table name. Then I set the @Table variable to AdventureWorks.Sales.SalesOrderDetail. To build my actual dynamic TSQL statement I use a SET statement. This statement sets the variable @CMD to the concatenated string value that contains a SELECT statement and the @TABLE variable value. I then execute my dynamic TSQL statement contained in the @CMD variable using the EXECUTE statement.
To further test the dynamic TSQL in Listing 1, you can try using a different AdventureWork2012 table in the code by changing the “SET @Table=” statement to use the AdventureWorks2012.Sales.Sales.OrderHeader table.
Dealing with More Complex Dynamic SQL Server Requirements
There are times when you need to write some more complicated dynamic TSQL. As a DBA one of those situations where I might need to do this is when I want to generate code to perform some kind of database maintenance. When I need to build dynamic TSQL for database maintenance purposes I usually read a system view and then generating a script that is displayed and/or executed. Suppose you are a DBA that has taken over maintaining a database and you want to delete several test tables that got created in a database. The tables all have names that start with the prefix “Test”. To demonstrate how you might read the sys.tables view and generate of the appropriate DELETE statements, let’s look at the code in Listing 2.
-- Section 1: Create database and Sample Tables USE master; go CREATE DATABASE DYNA; GO USE DYNA; GO CREATE TABLE MyData1 (Id int, DataDesc varchar(100)); CREATE TABLE MyData2 (Id int, DataDesc varchar(100)); CREATE TABLE TestData1 (Id int, DataDesc varchar(100)); CREATE TABLE TestData2 (Id int, DataDesc varchar(100)); GO -- Section 2: Dynamic TSQL code to generate script to delete Test tables USE DYNA; GO DECLARE @TableName varchar(100); DECLARE @CMD varchar(1000); SELECT TOP 1 @TableName = name FROM sys.tables WHERE name like 'Test%' ORDER BY name; WHILE @@ROWCOUNT > 0 BEGIN SELECT @CMD = 'DROP TABLE ' + @TableName + ';'; PRINT @CMD EXECUTE(@CMD); SELECT TOP 1 @TableName = name FROM sys.tables WHERE name like 'Test%' and name > @TableName ORDER BY name; END -- Section 3: Cleanup USE master; GO DROP DATABASE DYNA;
Listing 2: Dynamic Code to delete test tables
The code in Listing 2 contains three different sections. The first section creates a database called DYNA, and then creates 4 different tables, two of which start with “Test”. These two tables that start with “Test” are the tables I want to delete with dynamic TSQL code. The second section of code is my dynamic TSQL code. The last section of code cleans up by deleting the test database I created.
If you review the code in Section 2 you will find the dynamic TSQL code first prints out the delete statements that it run, then deletes the test tables I created in Section 1. I do this by processing though a WHILE loop while looking for different tables that start with the character string “Test”. For each table I find that starts with “Test” I construct a DELETE command that is stored in the variable @CMD. I then display the DELETE statement by using a PRINT statement, immediately followed by executing the statement by using the EXECUTE statement. The last section, section 3 cleans up by dropping the DNYA database.
To test this code I suggest you run each section independently in order starting from Section 1. After you run Section 1 review the DYNA database and verify that there are four tables in the DYNA database. Next run Section 2. When you run this section you will see two messages displayed in the MESSAGE tab in the Query Analyzer window. The two statements shown are the two DELETE statements that were dynamically generated and executed. Once you are done running the code in Section 2, go back and review the tables in your DYNA database. If you’re using the Object Explorer in SQL Server Management Studio, don’t forget to refresh. Alternatively, you can just select from the sys.tables view. You should now find there are only two table that exist, and the two tables deleted are those that started with “Test”. Once you are done verifying what the code in section 2 performed, I would then run the code in Section 3 to cleanup. This code will drop the DYNA database.
This is a very simple example of how to examine rows of metadata and generate dynamic TSQL. As a DBA there are many times where it will come in handy to understand how to write TSQL code that generates TSQL code.
Avoiding SQL Injection
You might have heard that dynamic TSQL is evil. The evil part of dynamic TSQL is that it opens up the possibility for a SQL Injection attack. SQL Injection is a hacking technique where malicious users try to exploit the use of a free form data entry field. These malicious users try to insert additional TSQL code into a data entry field beyond how the data entry field was originally intended to be used. By inserting TSQL code they can fool the system into returning data that they were originally not supposed to get, or worse yet, run additional TSQL commands against your SQL Server database. Depending on the permissions that your application runs under, a SQL Injection attack could insert data into your database tables, drop a table, or worse yet setup a new login that with sysadmin rights.
To demonstrate how dynamic TSQL can be subject to a SQL Injection attack if not managed properly, let me first create a database and a table with the code in Listing 3. I will use this database and table to demonstrate how dynamic TSQL can be vulnerable to a SQL Injection attack.
USE master;
go
CREATE DATABASE DYNA;
GO
USE DYNA;
GO
CREATE TABLE Product(ID int,
ProductName varchar(100),
Price money);
INSERT INTO Product VALUES (1, 'Red Wagon', 12.99),
(2, 'Red Barn', 23.18),
(2, 'Farm Animals', 7.59),
(2, 'Toy Solders', 17.76);Listing 3: Creating Database and table to demo a SQL Injection attack
The code in Listing 3 creates a database name DYNA, and then creates and populates a table name Product with 4 rows of data.
Suppose my application has a data selection screen where an end user can enter a text string that is contained in a ProductName and then application will return all the Product table records that contain the entered text string. The application does this by passing the text string that a user enters to a stored procedure name GetProducts, and then the data returned from the stored procedure is displayed to the user. The stored procedure GetProducts is coded as shown in Listing 4.
CREATE PROC GetProducts
(@EnteredText varchar (100))
AS
DECLARE @CMD varchar(1000);
SET @CMD = 'SELECT ProductName, Price ' +
'FROM Product ' +
'WHERE ProductName LIKE ''%' +
@EnteredText + '%''';
PRINT @CMD
EXEC (@CMD);Listing 4: Code for stored procedure GetUserName
By reviewing the stored procedure GetProducts in Listing 4 you can see this stored procedure accepts a single parameter @EnteredText This parameter is then used to dynamically create a TSQL statement that is stored in the variable @CMD. That variable is then executed. (Note that this procedure could have been written without using dynamic SQL. I am using dynamic SQL here to illustrate the potential problems.)
To demonstrate how to use this stored procedure let me executed it by running the code in Listing 5.
EXEC GetProducts 'Red';
Listing 5 Executing stored procedure GetUserName normally
The code in Listing 5 calls the GetProducts stored procedure and produces the results found in Report 1.
ProductName Price ------------------------------------------------------------------- ------------- Red Wagon 12.99 Red Barn 23.18
Report 1: Results from call GetUserName using code in Listing 5
Because the code in my stored procedure GetProducts takes a parameter and generates the varchar variable @CMD it leaves the stored procedure open for a SQL Injection attack. I can demonstrate this by executing the GetProducts stored procedure with the code in Listing 6.
EXEC GetProducts 'Red%'' and ID = 1 --';
Listing 6: Code to expose how GetProducts stored procedure is susceptible to SQL Injection
If you review the code in Listing 6 you can see I passed a number of other characters in additional to the string “Red” to my stored procedure GetProducts. These additional characters I passed allows me restrict my query to only return the Products that have “Red” in the ProductName column and have an ID value of 1. By allowing my stored procedure to use the unedited text in the @EnteredText parameter allows me to inject additional characters into that parameter to cause the code to perform other actions that was not originally intended to be used in the GetProducts stored procedure.
In my last example I showed you a non-destructive SQL Injection attack using the dynamic TSQL in myGetProducts stored procedure. Most SQL Injection attacks are trying to get additional data out of your system, or just want to corrupt your database. To explore this a little more let’s look at the code in Listing 7.
EXEC GetProducts 'Red'' ;SELECT * FROM Product;--';
Listing 7: SQL Injection to return additional data
If I run the code in Listing 7 it generates the two result sets. The first result set has zero rows and the second set is the text found in Report 2:
ID ProductName Price ----------- ------------------------------------------------------------ --------------------- 1 Red Wagon 12.99 2 Red Barn 23.18 2 Farm Animals 7.59 2 Toy Solders 17.76
Report 2: Text results when running the code in Listing 7
If you compare the results of the normal execution of GetProduct stored procedure found in Results 1, with the Results found in Results 2, you can see the code in Listing 7 generated some additional output columns that my stored procedures wasn’t originally designed to display, but was displayed due to a SQL Injection attack.
My example in Listing 7 was still not a destructive use of SQL Injection, but it did allow me to exploit the @EnteredText parameter of the GetProduct stored procedure to return data for all columns of the Client table. To accomplish this I added the “' ;SELECT * FROM Product;--“ string to my parameter. Note that I added two dashes (“--“) at the end of my additional string. This allowed me to comment out whatever characters or code that my stored procedure might have included after the parameter.
For my last example let me perform a destructive TSQL Injection attack. Review the code in Listing 8 to see my destructive TSQL Injection command.
EXEC GetProducts 'Red'' ;DROP TABLE Product;--';
Listing 8: Destructive TSQL Injection EXEC command
In Listing 8 I added a DELETE statement to the @EMAIL parameter. In this example I deleted the Client table. If I run the code in Listing 8 it will delete the Client table
How to combat against SQL Injection attacks
No one wants to have their code compromised by a SQL Injection attack. In order to combat against SQL Injection attacks you should consider the following points when developing your TSQL application code:
- The best way to avoid a SQL Injection attack is to not use dynamic SQL
- Edit user entered parameters for special characters like semi-colons and comments
- Make your parameters only as long as needed to support the user entered data
- If you must use dynamic SQL then use parameterized TSQL that uses sp_execute sql to execute your dynamic TSQL, instead of EXEC.
- Tighten up security to only allow minimal rights needed to execute your dynamic TSQL.
If your application specifications require that you need to build some code that contains dynamic TSQL then using parameterized TSQL is a good way to fight against SQL Injection. In Listing 9 I have provide an example of how I modify my GetUserName stored procedure to use parameterized TSQL.
ALTER PROC GetProducts
(@EnteredText varchar (100))
AS
DECLARE @CMD nvarchar(1000);
DECLARE @WildCardParm varchar(102);
SET @CMD = 'SELECT ProductName, Price ' +
'FROM Product ' +
'WHERE ProductName LIKE @EnteredParm';
SET @WildCardParm = '%' + @EnteredText + '%';
EXEC sp_executesql @CMD,N'@EnteredParm varchar(100)',@EnteredParm=@WildCardParm;Listing 9: Using parameterized TSQL
In Listing 9 I altered my GetProducts stored procedure to use sp_executesql to execute my dynamic TSQL. In this modified stored procedure I have made the following changes:
- Changed the string @CMD to no longer include the value of the @EnteredText variable in the command string. Instead I introduced the user entered text in a variable named @EnteredParm.
- Added a SET statement to set the variable @WildCardParm to place the wildcard character (%) at the beginning and end of the @EnteredText parameter.
- Changed how the string @CMD was executed. Instead of using the EXEC statement to execute the string, I used the procedure sp_executesql.
By making these two changes the user entered text will now be executed as a parameter driven query. By doing this, a user can no longer try to inject additional TSQL code into my GetProduct store procedure. To verify this, run the four different commands shown in Listing 5, 6, 7 and 8. But since I already deleted my Product table, I first need to recreate it with data. To do this I need to run the code in Listing 9 first.
CREATE TABLE Product(ID int,
ProductName varchar(100),
Price money);
INSERT INTO Product VALUES (1, 'Red Wagon', 12.99),
(2, 'Red Barn', 23.18),
(2, 'Farm Animals', 7.59),
(2, 'Toy Solders', 17.76);Listing 9: Create and populate Client table
After I have run Listing 9 to recreate my Product table, I then can run listing 5, 6, 7 and 8 to prove I resolved my SQL injection problem. When you run these different commands you will find that only Listing 5 returns data. The reason the others do not return data is the dynamic TSQL generated now is looking for ProductName values that contain the additional user enter injection values, which of course don’t match any of the Product column values in the Product table.
Summary
No one wants a SQL Injection attack on their watch. Of course the best solution to making sure it doesn’t happen is not to have dynamic SQL code in your application. If your application does require dynamic SQL hopefully this article gave you some suggestions on how to minimize the risk associated with SQL Injection. Next time you write dynamic SQL make sure you take steps to avoid the possibility of a SQL Injection attack.
Question and Answer
In this section you can review how well you have understood SQL Injection by answering the following questions.
Question 1:
What is the best approach to avoiding a SQL Injection attack (the best method)?
- Do not deploy TSQL code that uses dynamic TSQL
- Edit user entered data used in dynamic TSQL for special characters that allow for SQL Injection attacks
- Make the user entered parameters for your dynamic TSQL as short as possible
- Use parameterized TSQL code
Question 2:
What kinds of thing can users accomplish with a SQL Injection attach (Pick all that apply)?
- Return data that the application didn’t intend for the users to select
- Insert data into a table that wasn’t intended by the application
- Drop a table
- Provide sysadmin rights to a new account
- All of the above
Question 3:
If you are going to deploy dynamic TSQL code that is contained in a variable, which of these two execution methods is best to use in order to minimize your risk for a SQL Injection attack?
- EXEC
- sp_executesql
Answers:
Question 1:
The correct answer is a. The best way to avoid SQL Injection is to not allow dynamic TSQL code in your applications at all. .
Question 2:
The correct answer is e, all of the above. With SQL Injection a malicious user could perform a number of different SQL operations. The kind of commands they could execute depends on the rights of the account used to run the dynamic TSQL command. If the application account has sysadmin rights, a SQL Injection attack could do anything the user wanted.
Question 3:
The correct answer is b. By using sp_executesql you can pass your user enter data using a parameter to your parameterized TSQL code.
