For October's T-SQL Tuesday, Steve Jones asks us to talk about ways we've used dynamic SQL to solve problems. Dynamic SQL gets a bad rap simply because, like a lot of tools, it can be abused. It doesn't help that a lot of code samples out there show that "good enough" doesn't meet the bar most of us have, especially in terms of security.

In a series I started last year, I talked about ways to do <X> to every <Y> inside a database, focusing on the example of refreshing every view (in a single database or across all databases). I already touched on what I want to dig into today: that it can be dangerous to try to parameterize things that can't be parameterized in the ways people typically try.

I've been dealing with this "do something to all the databases" problem for essentially my entire career. SQL Server has always had an undocumented and unsupported system procedure, sp_MSforeachdb, that promises to run the command you pass against each database. Unfortunately, this procedure has a set of problems; the primary being that it has a bug where it can skip databases without warning. The cursor it uses with default options is very sensitive to modifications to sys.databases (or the underlying system objects that feed it).

A solution I developed several years ago to address this problem (and a few others) is sp_ineachdb. This procedure is part of the open source First Responder Kit, and I've talked about it here, here, and here.

I often see folks struggling to run something in a different database, and I suggest sp_ineachdb, but people are wary of (or forbidden to install) 3rd party scripts. They try things like:

SET @sql = N'USE ' + @dbname + N' SELECT ''' + @dbname + ''', o.name
  FROM sys.objects AS o 
  INNER JOIN sys.columns AS c
  ON o.[object_id] = c.[object_id] 
  WHERE c.name = N''name'';';

Or:

SET @sql = N'SELECT ''' + @dbname + ''', o.name
  FROM ' + @dbname + N'.sys.objects AS o 
  INNER JOIN ' + @dbname + N'.sys.columns AS c
  ON o.[object_id] = c.[object_id] 
  WHERE c.name = N''name'';';

These will both work, in theory, but prefixing every reference is tedious, and it also means you can't rely on local functions like OBJECT_ID() or DB_NAME(). What you want is for the query to not need all of these prefixes, but also not to repeatedly call USE. If you're building the command for multiple databases, and any database is offline or doesn't exist, it will try running the same script again in the previous database context.

This code is also extremely vulnerable to SQL injection – if a user is granted permissions to create a database, and this dynamic SQL runs under an elevated context, they could get access to data they shouldn't. For example, if they name a database:

CREATE DATABASE [tempdb; SELECT Fullname, Salary FROM HR.dbo.Employees;
];

Then this:

SET @sql = N'USE ' + @dbname + N' SELECT ''' + @dbname + ''', o.name
  FROM sys.objects AS o 
  INNER JOIN sys.columns AS c
  ON o.[object_id] = c.[object_id] 
  WHERE c.name = N''name'';';

Will, when it gets to that database, become the following:

USE tempdb; SELECT Fullname, Salary FROM HR.dbo.Employees; 
SELECT 'tempdb; SELECT Fullname, Salary FROM HR.dbo.Employees;', o.name
  FROM sys.objects AS o 
  INNER JOIN sys.columns AS c
  ON o.[object_id] = c.[object_id] 
  WHERE c.name = N'name';

…which will obviously produce data leakage and let this user see all the salaries from another database they wouldn't normally have access to.

Instead, you can take advantage of the fact that EXECUTE can take the name of a procedure to call as a string argument. Think about the goal: to call a query both safely and locally inside the context of each database. So we can say:

DECLARE @context nvarchar(1024);
SET @context = QUOTENAME(@dbname) + N'.sys.sp_executesql';
EXECUTE @context @sql;

Which is the same as hard-coding:

EXECUTE [tempdb; SELECT Fullname, Salary FROM HR.dbo.Employees;].sys.sp_executesql @sql;

The user could still try to exploit @sql, but this is a much safer path than blindly concatenating that name or other user input into executable query text. Especially if @sql is system-generated or otherwise not editable by the end user.

Going back to the example above, we had:

SET @sql = N'SELECT ''' + @dbname + ''', o.name
  FROM ' + @dbname + N'.sys.objects AS o 
  INNER JOIN ' + @dbname + N'.sys.columns AS c
  ON o.[object_id] = c.[object_id] 
  WHERE c.name = N''name'';';

With the ability to use @dbname to execute within a given database context, this can become:

SET @context = QUOTENAME(@dbname) + N'.sys.sp_executesql';
 
SET @sql = N'SELECT DB_NAME(), o.name
  FROM sys.objects AS o 
  INNER JOIN sys.columns AS c
  ON o.[object_id] = c.[object_id] 
  WHERE c.name = N''name'';';
 
EXECUTE @context @sql;

The fewer single quotes and + operators I have to see inside the dynamic SQL, the better. Getting to call functions like DB_NAME() instead of more string concatenation is an added bonus. Note that whenever you are referencing an entity name you should always surround it with QUOTENAME() (not manually appending square brackets), not only to protect yourself from injectable code, but also because people can give things terrible names, as I demonstrated in this post I mentioned earlier. The only time QUOTENAME() around a variable or parameter becomes problematic is when the user is passing in multi-part names, like dbo.whatever, or the names are already surrounded with square brackets. In those cases, you should verify they exist, e.g.

SELECT name 
  FROM sys.tables 
  WHERE [object_id] = OBJECT_ID(@param);

If you are expecting @param to contain a valid name but that query returns nothing, you should halt execution immediately.

Have parameters? No problem! You can pass parameters just as if you'd hard-coded the call to sp_executesql:

DECLARE @column sysname = N'name';
 
SET @context = QUOTENAME(@dbname) + N'.sys.sp_executesql';
 
SET @sql = N'SELECT DB_NAME(), o.name
  FROM sys.objects AS o 
  INNER JOIN sys.columns AS c
  ON o.[object_id] = c.[object_id] 
  WHERE c.name = @column;';
 
EXECUTE @context @sql, N'@column sysname', @column;

And just like other looping dynamic SQL solutions, you can avoid getting a separate result set per database by creating a #temp table and inserting there:

CREATE TABLE #table(db sysname, [table] sysname);
 
DECLARE @column sysname = N'name';
 
-- loop to walk through @dbname 
 
SET @context = QUOTENAME(@dbname) + N'.sys.sp_executesql';
 
SET @sql = N'INSERT #table(db,[table]) SELECT DB_NAME(), o.name
  FROM sys.objects AS o 
  INNER JOIN sys.columns AS c
  ON o.[object_id] = c.[object_id] 
  WHERE c.name = @column;';
 
EXECUTE @context @sql, N'@column sysname', @column;
 
-- end loop
 
SELECT db, [table] FROM #table;

Dynamic SQL will always be difficult to read and maintain because it's one block of code all the same color, and editors can't make it easier to parse. But this technique I've shown can help simplify the other ugly aspects of concatenating strings to make the query run in the right place, safely. I have some other pointers to articles about dynamic SQL here.