Dynamic SQL is a programming technique you can use to build SQL statements as textual strings and execute them later. This technique could prove to be useful in some cases and therefore it’s good to know we have it as an option. In today’s article, we’ll show how to create and execute dynamic SQL statements.
Data Model and a Brief Introduction
The data model we’ll use in this article is the same one we’re using throughout this series. Nothing changed since the last article, so we won’t lose time explaining it.
In the previous article, Learn SQL: SQL Injection, we’ve talked about SQL injection and showed a few examples of how dynamic SQL can be used, but still, we missed explaining what this technique is. In this article, we’ll fix that.
What is Dynamic SQL?
You could think of dynamic SQL as prepared statements in any programming language. With that said, using this technique, we can write down any query, from the simplest possible to the most complex ones. You could use this technique outside SQL to build queries and send them for execution, or inside a stored procedure to build queries based on given parameters. The biggest advantage we have here is flexibility because you can control everything – what shall be in the SELECT and WHERE parts of the query, as well conditions – test variables to include or exclude conditions, adjust values of input variables based on certain criteria, etc.
Dynamic SQL – Simple Examples
Let’s start with the simplest possible example.
|
1 |
EXEC sp_executesql N'SELECT * FROM customer'; |
It’s pretty obvious that this query is exactly the same as the simple SELECT * FROM customer; query. The sp_executesql procedure takes the SQL string as a parameter and executes it. Since it’s the Unicode string we use the N prefix.
The next thing we’ll do is to build a query using variables (parameters). An example of such a query is given below. We’ll use the PRINT command here to show what this query looks like after concatenation.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @sql NVARCHAR(MAX), @id NVARCHAR(MAX); -- run query using parameters(s) SET @id = N'2'; SET @sql = N'SELECT id, customer_name FROM customer WHERE id = ' + @id; PRINT @sql; EXEC sp_executesql @sql; |
If you write down a prepared statement in PHP (or any other language) the overall idea and syntax would be close to this example. This leads us to the conclusion that we could prepare statements in a programming language or at the database level.
Since we’re concatenating strings, we can do whatever we want and the only limitation to execute that string is that SQL syntax before execution is OK. Therefore, we can do the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @sql NVARCHAR(MAX), @top NVARCHAR(MAX), @attributes NVARCHAR(MAX), @table NVARCHAR(MAX), @id NVARCHAR(MAX); -- run query using parameters(s) SET @top = ' TOP 3 '; SET @attributes = ' * '; SET @table = ' customer ' SET @id = N'0'; SET @sql = N'SELECT ' + @top + @attributes + N'FROM ' + @table + N' WHERE id > ' + @id; SELECT @sql AS query; EXEC sp_executesql @sql; |
You can notice that we’ve used parameters for different parts of our query – limit/top, list of attributes, table name, and id. Since the variable (@sql) we pass to the sp_executesql procedure is the textual variable we can do all standard sting operations with it.
This time, instead of the PRINT command, I’ve used the SELECT @sql AS query; statement to test the contents of the @sql variable. This is a good practice when you’re working with dynamic SQL and you want to be sure that your SQL syntax is correct before executing it.
Dynamic SQL – More Complex Examples
If we want to go one step further, we can pass variables to stored procedures and build queries based on the values. That stands for using these variables as parameters in the query, but we can also test variables and modify the query based on their value (e.g. omit part of the query if the variable value IS NULL).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE PROCEDURE p_dynamic_sql (@employee_id INT, @customer_id INT) AS BEGIN DECLARE @sql NVARCHAR(MAX); SET @sql = N'SELECT * FROM call'; IF (@employee_id IS NOT NULL OR @customer_id IS NOT NULL) SET @sql = @sql + N' WHERE'; IF @employee_id IS NOT NULL BEGIN SET @sql = @sql + N' employee_id = ' + CAST(@employee_id AS CHAR(10)); IF @customer_id IS NOT NULL SET @sql = @sql + N' AND '; END; IF @customer_id IS NOT NULL SET @sql = @sql + N' customer_id = ' + CAST(@customer_id AS CHAR(10)); SELECT @sql AS query; EXEC sp_executesql @sql; END; |
As you can see, we’ve tested if parameters contain NULL values. If they do, we’ve omitted their part in the WHERE clause of the query. That results in a query that will test only these values that are passed as NOT NULL values. Please notice that we’ve also tested if variables are NULL or not in order to put WHERE and AND in the query exactly where they should be so we don’t have syntax errors. This could be also done in a simpler manner using ” WHERE 1 = 1 ” as a default part of the query and then adding ” AND <condition> ” for each NOT NULL variable. I decided to go this way because it results in a “cleaner” final query.
We’ve also added the SELECT statement to print a query before executing so we can monitor what happened. Let’s now call the procedure. We’ll do it in the same manner as with any other stored procedure because the stored procedure for the user is a black box – the user is interested in only how to pass parameters and what shall be the format of the result. For the first call, we’ll pass NULL as values for both variables.
|
1 |
EXECUTE p_dynamic_sql NULL, NULL; |
Since both variables were NULL the query created inside the procedure omitted both conditions and the final query is just selecting all records from the call table. We can clearly see that because the result returned by the query is the list of all our calls.
Let’s now run the same procedure with passing NOT NULL values for one or both variables.
|
1 2 3 4 5 |
EXECUTE p_dynamic_sql 1, NULL; EXECUTE p_dynamic_sql NULL, 1; EXECUTE p_dynamic_sql 1, 1; |
We had three query calls and three results they’ve returned. The first query returned only calls related to the employee with the id 1, while not testing anything related to the customer.
The second query did the same, but for the customer – we’ve returned all calls related to the customer with the id 1, no matter which employee made the call.
Finally, in the third procedure call, we’ve passed both parameters, so the query took both values into account. The result is the list of all calls employee with id 1 made to the customer with id 1.
There are a few things I would like to point here:
- While calling stored procedures, the user doesn’t have any insight into the structure of the query and therefore doesn’t see tables used in the query. This could prove to be very useful if you want to hide a database structure from a potential attacker
- In this example, we had only 2 parameters and both were numerical (integer) values. Things would become a bit more complicated if these values would be dates or texts
- While you handle texts, you maybe want to show rows where the condition is not only equal to the value passed but the text contains the substring passed as a parameter
- For dates, you could be interested in date ranges
- We could also decide to set some default values if the parameter passed IS NULL. This could be very useful while working with dates – if e.g., start date IS NULL, you’ll select some date as a default start date
- If you want to add some more logic to procedures, you would need to make adjustments. One such example could be this – in our example, we’ve tested if both conditions hold at the same time, so the query used AND. But what if we want to test if any of these conditions hold and we want to use OR, or a combination of AND and OR in the query? While it’s obvious you’ll build the query in a similar manner you would normally write it, still creating it dynamically based on which parameters were passed and which were not would require some time to set up everything correctly
All of this leads us to the conclusion that dynamic SQL allows us to manipulate queries and other database objects as we like, and that is great. Still, this comes with the cost too. If we can do whatever we want, the potential attacker also has this as an option. Also, this way of building queries and passing parameters makes systems much more vulnerable to SQL injection attacks.
Conclusion
Today we gave a brief introduction to dynamic SQL statements. They can be very helpful and sometimes they could be the best solution to your problem. Still, as this is usually the case with most non-standard solutions, do not overuse it and be sure to use it when it’s needed (or is the best possible solution at that moment). In the next article, we’ll continue where we stopped with the previous one and use knowledge from that article and today’s article to show how to create a dynamic SQL solution protected against SQL injection.
Table of contents
His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...
You can find him on LinkedIn
View all posts by Emil Drkusic
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020