Writing secure, injection-proof SQL is an important skill that every developer should practice. In this article, I am going to assume that you, the reader, are already aware of what SQL injection is and how to prevent it in static queries by using parameters. Today's topic is how to apply the same techniques to dynamic SQL.

Technique 1: Parameters

The same technique of parameterizing static queries applies just as much to dynamic ones. Here is a contrived example of the wrong way to write a dynamic query:

create procedure Customer_DynamicSelect
  @where_col sysname,
  @where_val varchar(max)
as
set nocount on

declare @stmt nvarchar(max)
set @stmt = 'select * from Customer where ' + @where_col + ' = ' + @where_val

exec sp_executesql @stmt

If I saw this kind of code in production, my immediate reaction would be "Warning! Danger! Warning! Danger!". Concatenating string values and executing them is a huge security risk. The first step in cleaning up this example is to change the dynamic SQL to use a parameter instead of a literal string. To do that, you do things a little backwards: first you use the variable in the dynamic SQL string, and then you declare the variable later when you actually execute the dynamic SQL. Here's how the above example would look after performing these steps:

alter procedure Customer_DynamicSelect
  @where_col sysname,
  @where_val varchar(max)
as
set nocount on

declare @stmt nvarchar(max)
set @stmt = 'select * from Customer where ' + @where_col + ' = @where_val'

exec sp_executesql
  @stmt,
  '@where_val varchar(max)',
  @where_val = @where_val

That last line might look ambiguous, but it actually isn't. SQL Server knows that the left side of the assignment refers to the variable in the dynamic SQL string, and the right side refers to a variable in the current scope. This version solves one problem, but there is still one more string concatenation, and this one can't be parameterized. That's where the second technique will help.

Technique 2: Validation

You can't parameterize things like column names, but you can ensure that the string exactly matches the name of one of the columns in the target table by using SQL Server Catalog Views, which contain information describing the database schema. Here is a version of the example procedure that checks for the existence of the column in the Customer table:

alter procedure Customer_DynamicSelect
  @where_col sysname,
  @where_val varchar(max)
as
set nocount on
  
if not exists
(
  select *
  from sys.columns c
  join sys.tables t on t.object_id = c.object_id
  where t.name = 'Customer'
  and c.name = @where_col
)
begin
  raiserror( 'Invalid column name', 11, 1 )
  return 1
end

declare @stmt nvarchar(max)
set @stmt = 'select * from Customer where ' + @where_col + ' = @where_val'

exec sp_executesql
  @stmt,
  '@where_val varchar(max)',
  @where_val = @where_val

Note that before the contents of @where_col are validated, the variable is only used to check for equality with the name of an existing column. Only if the test succeeds will the contents be deemed safe to inject into the dynamic SQL string for execution.

With these two techniques of parameterization and validation, it is possible to write completely safe dynamic SQL.