In this post I’d like to share my views about Dynamic SQL
This post is part of the T-SQL Tuesday, which is a monthly blog party on the second Tuesday of each month. Everyone is welcome and have the chance to write about SQL Server.
This month’s host is Steve Jones (B|T) and the topic is “The Dynamic Code Invitation“.
Background
Dynamic SQL is a technique to create SQL code based on things that change due to external factors.
Although there are concerns about dynamic SQL, I have to say that if used safely it can be one of the most powerful tools at our service as data engineers.
Security Concerns
One of the most common topics we hear about dynamic SQL is related to SQL Injection, where some malicious code is pushed into our SQL string to be executed altogether and may result in gaining unauthorized access or data being altered.
Thankfully SQL Server provide different ways to execute SQL string and there are ways to prevent it completely by using parameterized strings instead of concatenating user inputs.
For instance, this code is vulnerable to SQL injection, if we allow direct user input for the value of @dbname, a malicious user can try until get it right and execute whatever the execution user is allowed
DECLARE @sql nvarchar(MAX); DECLARE @dbname sysname; SET @dbname = ''' OR ''1'' = ''1'; SET @sql = N'SELECT * FROM sys.databases WHERE name = ''' + @dbname + ''';' EXEC (@sql);
But this code can be easily protected from SQL injection like this
DECLARE @sql nvarchar(MAX); DECLARE @dbname sysname; SET @dbname = ''' OR ''1'' = ''1'; SET @sql = N'SELECT * FROM sys.databases WHERE name = @dbname;' EXECUTE sys.sp_executesql @stmt = @sql, @params = N'@dbname sysname', @dbname = @dbname;
That’s so much better and as I mentioned earlier, it is quite simple to prevent SQL injection.
My personal experience
But this post is not about SQL injection or the risk that implies, but I want to explain how much dynamic SQL has helped me through the years and keeps doing it in daily basis.
SQL Server keeps lots of information in metadata and writing the correct queries against those tables, can help us to perform DBA maintenance tasks.
In all these years I have written multiple scripts and put them available in my Github repo.
Restoring databases, for instance, how nice it is when you run a query and this return the correct sequence to restore all transaction log backups just for you to copy /paste and execute?
Configure tempdb dynamically based on the number of cores? possible with dynamic SQL
Recreate logins/database users? There you go.
Indexes? Of course
And many other that I have posted in the past, like doing binary search to find certain dates, or reading Extended Events file target.
Conclusion
Writing queries that create new queries is something that has helped me quite a lot in my career by saving me lots of time and allowing me to spend that time in more interesting things or learning new skills.
Using dynamic SQL in a way to interact with users, I wouldn’t recommend it though, there are better ways, but still can be done safely.
If you have never done it, give it a go, you might be surprised how much you can get done with all the free time you will get.
Thanks Steve Jones for hosting this month and hope you enjoyed reading this post!