Warning: This article contains examples of SQL injection. The example queries can be harmful to data and are run on the AdventureWorks2012 database DO NOT run equivalents on your production database. Backup your AdventureWorks2012 database if you don’t want to lose data. Please note that the techniques are purely for education purposes and we do not condone the use of them for any other purpose.

Introduction

Knowing how to write SQL commands can sometimes be too little for the need at hand. Sometimes one needs to make queries dynamic so that they can change them on the fly and make them work the way they need them too. The way to do this is to use the system stored procedure sp_executesql, for which you can glean more information here. This way of running queries can be useful in making an application’s code more malleable in terms of its coupling with the database. However, there are also some negative points to consider and though dynamic SQL is practical it can be dangerous at times.

sp_executesql Stored Procedure

Take the following SELECT query from the AdventureWorks2012 test database:

This is a simple query and it is also very static and not very interesting to have embedded in one’s application code. Granted, there are other methods of making this more supple, like using an entity framework database provider, but that is outside the scope of this article.

Using sp_executesql you can turn this simple query into a dynamically built one. The following example turns the row restriction (TOP), the list of columns and the UnitPrice predicate into dynamically given entities.

As you can see this example makes a stored procedure that accepts three variables: one for the TOP clause, one for the list of columns and one for the minimum unit price. The column list and unit price are nullable. If the column list is not given the stored procedure replaces it with a * wildcard. If the unit price is not given the code replaces it with a WHERE clause that is always true (WHERE 1 = 1).

The query code is broken down into a string of hardcoded words concatenated with the given variables in a specific order. There are ISNULL and CASE WHEN clause to deal with NULL variables. Go ahead and create the stored procedure and play with the variables to see how it acts. You can run the stored procedure by using this code:

Obviously, this type of query can be further perfected by added a check in the sys.columns/sys.tables tables to see whether the given column list exists for example.

Problems with dynamic SQL

The first major problem that needs to be tackled whenever one uses dynamic SQL commands in one’s application code is SQL injection. The fact that the application accepts NVARCHAR strings and concatenates them with actual SQL queries that are later run opens up the possibility for SQL injection. Without going into the details yet just run this query for yourself (be sure to cancel the query because, as you’ll notice, it loops infinitely):

So, if you’re familiar with SQL injections you’ll notice that this dynamic SQL query is cut into two different ones by adding a “;” breaker and then running a TSQL query and cutting the rest of the query off by add comment markers “–“ The query that is actually being run is the following:

Granted, this SQL injection is pretty bad but it could be much worse. Depending on how wise the solution architect was the query may or may not be running with limited privileges. But even if the SQL user the application is using only has read/write privileges the person doing the SQL injection attack could replace WHILE 1 = 1 SELECT‘You got owned!’; with a “DELETE FROM” SQL Command. You can try it by running this query (please backup your database if you are not ready to lose the data):

This security nightmare can get a whole lot worse if an unscrupulous architect assigned a user with sysadmin privileges to the application. In that case it is not impossible to drop users, table, or worse yet, entire databases from the SQL Server instance via SQL command injection. So, please take note that one should be certain the application user has minimal security rights and try to use different data types than strings and cast them to nvarchar in the stored procedure later. This has be done in the above stored procedure for the @NUMBER_OF_ROWS and @UNIT_PRICE variables and they are therefore not at risk of SQL injection.

SQL injection is the most drastic negative point of using dynamic SQL commands but there is another weak-spot and that has to do with performance. Due to the fact that SQL Server stores optimized query plans in cache when using stored procedures one may think that using dynamic SQL in a stored procedure as above would be optimizing the system as opposed to building ad-hoc queries in the application and generating a new query plan every time they are run. However, using dynamic-SQL breaks down this capability as the original query plan becomes invalidated so the engine has to create a new one whenever a new version of the dynamic SQL command is run. More information about that here and here. You may want to look into parameterization to safely navigate those waters. Bear in mind, however, that this only applies to extreme highly transactional queries that run thousands of times per day. The caching of a new query plan is a quick process (more info about the intricacies of this process can be found here). This can be tested by used the SET STATISTICS TIME ON; command. If you run a query for the first time there is a small amount of time allocated to “parse and compile time”. This time differs between different servers with varying amounts of power. It also varies with the complexity of the query. However, you can try it yourself by running the following query to create test data:

Now when you run this simple query you will see that the first time you run it there is some time allocated to compilation and storing the query plan in cache:

You should see some time allocated to parsing and compilation as follows:

However if you rerun this exact query you will notice that this time is almost non-existent:

If the query test itself was changed then the engine would be forced to recompile and re-cache a new query plan. The times to do this are small but if the query is run extremely often they can add up to lots of wasted resources.

So when can one safely use dynamic SQL?

Dynamic SQL can be used by a developer to create an agile query that allows for dynamic results. However, we have seen that this practice can be very dangerous. There are, however, other uses for this type of dynamic SQL. DBAs may use it to help in maintenance/administrative tasks and indeed it works wonders for saving time. SQL injection and query plan optimization is not a problem in this case because these scripts are not run constantly and repetitively as are application/production queries.

I will leave you with a very handy index maintenance query for any DBA that does the following using a dynamic SQL command:

1. Creates a temporary work table
2. Inserts the index names, table names and database names of all table that needs either a REBUILD or REORGANIZE (this info is included too)
3. Using cursors, the query builds a dynamic T-SQL command for each index (ALTER INDEX) and runs a rebuild on indexes over 30% fragmented and a reorganize for indexes between 10 and 30% fragmented.
4. This followed by an error handing CATCH statement so the query can keep running (this generally happens with older data-types that cannot be build ONLINE for security reasons.
5. Finally the query runs an index statistics update so the engine can use these fresh statistics for future query plans.

Conclusion

Dynamic T-SQL commands are very handy and are fun to use. They can make life easier for developers but lots of thought should go into the subject and this should be discussed with a DBA beforehand. In most cases, it may be better to take a parameterized SQL command approach. However, for maintenance or investigative reasons dynamic SQL commands can be a wonderful tool for DBAs. The article includes a use case for rebuilding and reorganizing indexes but one could also use it for restoring multiple databases.