Emil Drkusic
the data model

Learn SQL: Dynamic SQL

March 3, 2021 by

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.

the data model

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.

dynamic SQL in a query

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.

using parameters

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.

using variables

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

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.

dynamic SQL using stored procedure

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.

SQL stored procedures calls

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

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server
Learn SQL: SQL Injection
Learn SQL: Dynamic SQL
Learn SQL: How to prevent SQL Injection attacks
Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. 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

168 Views