Protecting Yourself from SQL Injection in SQL Server

Problem

Many of us are using dynamic SQL because we have requirements that dictate runtime choice – allowing the user to select the columns, or table name, or even entire where clauses. There are different ways to implement dynamic SQL, and some are more prone to SQL injection than others. SQL injection is a major security threat, likely responsible for just about any data breach you read about in the news these days.

Solution

If you’re using dynamic SQL, you have to understand that anything that can be specified by a user can be used against you. Let’s take the very simple example where a user is allowed to specify a table name in a form field, and you blindly select from it:

SET @sql = N'SELECT * FROM dbo.' + @tablename;
EXEC sp_executesql @sql;

You might go ahead and execute that, but is it safe? Hardly. Imagine if the user typed the following into the form field:

Users; DROP TABLE dbo.Users;

Since dynamic SQL is often executed as the owner (or callers are often given more permissions than necessary), this could be a bad day for you – you just dropped that table and will almost certainly need to recover from a backup. If you’ve heard of the XKCD comic about Little Bobby Tables (explained here), you’ll know that the recommendation is to “sanitize your database inputs.” But that usually isn’t enough.

My mantra is to strongly type and parameterize the things you can, to validate and QUOTENAME() the things you can’t, and to avoid simple string concatenation of user input at all costs. There are various approaches to this, depending on which part(s) of the query are actually being parameterized.

Values

The most glaringly dangerous dynamic SQL that you should avoid is blindly concatenating parameter values. I don’t know if this is people trying to use EXEC() because it’s fewer characters, or trying to avoid nvarchar or the pesky N prefix on SQL strings, but this is both risky and cumbersome:

DECLARE @sql VARCHAR(MAX);
SET @sql = 'SELECT * FROM dbo.Users
  WHERE Name = ''' + REPLACE(@Username, '''', '''''') + ''' 
  OR HireDate < ''' + CONVERT(CHAR(8), @HireDate, 112) + ''';';
EXEC(@sql);

For a statement that simple, dynamic SQL obviously isn’t required, but just imagine that it is for other reasons. It is much safer (and less error-prone) to do the following instead, using strongly-typed parameters and avoiding any messes with escaping single quotes:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM dbo.Users
  WHERE Name = @Username
  OR HireDate <  @HireDate;';
EXEC sys.sp_executesql 
  @stmt = @sql, 
  @params = N'@Username SYSNAME, @HireDate DATE',
  @UserName = @Username, @HireDate = @HireDate;

That is a little more code, but a bit more readable and a lot safer; I’ve blogged about why I prefer sp_executesql, but there are other differences, too.

Note: I do not ever encourage SELECT * but will be using it throughout these tips for brevity.

Table Name

The example above showed that someone could easily append a second command if you just concatenate a table name into your @sql string. However, you can very easily validate that the table actually exists, by first checking sys.tables (and/or sys.views), without doing any of the parsing or replacing that so many people try to do:

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = @tablename)
BEGIN
  SET @sql = N'SELECT * FROM dbo.' + QUOTENAME(@tablename);
END
ELSE
BEGIN
  RAISERROR(N'Invalid table name supplied.', 11, 1);
END

We use QUOTENAME() here anyway, because this will surround the entire parameter value with [square brackets]. This will continue to protect you even if someone has managed to create a table called Users; DROP TABLE dbo.Users;, which is possible:

CREATE TABLE dbo.[Users; DROP TABLE dbo.Users;](id INT);

So, even in the case where someone has been clever enough to bypass your validation, the combination of validating the existence of the table *and* using QUOTENAME() yields the following @sql, which merely provides them data from the wrong table:

SELECT * FROM dbo.[Users;DROP TABLE dbo.Users;]

(And if they haven’t created that table in an attempt to bypass simplistic validation, they’ll get an error message that the “table” they tried to access doesn’t exist.)

Schema Name

For local schema references, you can treat this similar to tables:

IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = @schemaname)
BEGIN
  SET @sql = N'SELECT * FROM ' + QUOTENAME(@schemaname) + N'.Users';
END
ELSE
BEGIN
  RAISERROR(N'Invalid schema name supplied.', 11, 1);
END

Of course, you can combine the two methods shown so far if your code accepts parameters for both schema and table. If you allow for the passing of a single value representing a two-part name (like N'dbo.Users'), then you can validate using OBJECT_ID(). You can’t use QUOTENAME() around a two-part name, but you could instead build the command from the metadata instead of trusting the user input. So, for example:

DECLARE @TwoPartTableName SYSNAME = N'dbo.Users';
SET @TwoPartTableName = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.schema_id = s.schema_id
  WHERE t.[object_id] = OBJECT_ID(@TwoPartTableName);

Now our string has been changed from dbo.Users to [dbo].[Users] (which can also ensure that table names with ill-advised characters, such as spaces or periods, do not cause problems).

Database Name or Linked Server Name

If you need to parameterize the database or server name, you can validate and QUOTENAME() those portions, too.

IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @LinkedServerName)
BEGIN
  SET @sql = N'SELECT * FROM ' + QUOTENAME(@LinkedServerName)
    + N'.dbname.dbo.Users;';
END
-- or
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
  SET @sql = N'SELECT * FROM ' + QUOTENAME(@DatabaseName) + N'.dbo.Users;';
END

In fact, when parameterizing the database and linked server portions, you can do this without concatenating those names into the SQL itself, which allows you greater flexibility in passing strongly-typed parameters through sp_executesql. As simple examples, instead of the concatenation demonstrated above, you could do this instead (after validating the server/database names, of course):

DECLARE @cmd NVARCHAR(MAX), @sql NVARCHAR(MAX), @UserID INT = 1;
SET @sql = N'SELECT * FROM dbo.Users WHERE UserID = @UserId;';
SET @cmd = QUOTENAME(@LinkedServerName) + N'.dbname.sys.sp_executesql';
EXEC @cmd @stmt = @sql, @params = N'@UserID INT', @UserID = @UserID;
SET @cmd = QUOTENAME(@DatabaseName) + N'.sys.sp_executesql';
EXEC @cmd @stmt = @sql, @params = N'@UserID INT', @UserID = @UserID;

Further, imagine that the SQL you want to run on the other server also needs to be dynamic, and the database name is a parameter too, you can still protect yourself with nested levels of validation:

DECLARE 
  @LinkedServerName SYSNAME = N'LinkedServer',
  @DatabaseName SYSNAME = N'msdb',
  @RemoteProcedure SYSNAME = N'sys.sp_spaceused',
  @UpdateUsage BIT = 1;
DECLARE 
  @cmd NVARCHAR(MAX),
  @dbExists BIT, 
  @spExists BIT;
IF EXISTS 
(
  SELECT 1 FROM sys.servers WHERE name = @LinkedServerName
)
BEGIN -- @LinkedServerName is a valid linked server 
  SET @cmd = QUOTENAME(@LinkedServerName) 
      + N'.master.sys.sp_executesql';
  -- this runs dynamic SQL at @LinkedServerName to ensure the db exists:
  EXEC @cmd @stmt = N'SELECT @dbExists = 1 FROM sys.databases 
    WHERE name = @DatabaseName;',
    @params = N'@DatabaseName SYSNAME, @dbExists BIT OUTPUT', 
    @DatabaseName = @DatabaseName, @dbExists = @dbExists OUTPUT;
  IF @dbExists = 1
  BEGIN -- @DatabaseName exists in @LinkedServerName
    SET @cmd = QUOTENAME(@LinkedServerName) 
      + N'.' + QUOTENAME(@DatabaseName)
      + N'.sys.sp_executesql';
    -- this runs dynamic SQL in @DatabaseName to ensure the procedure exists:
    EXEC @cmd @stmt = N'SELECT @spExists = 1 FROM sys.all_sql_modules
      WHERE [object_id] = OBJECT_ID(@RemoteProcedure);',
      @params = N'@RemoteProcedure SYSNAME, @spExists BIT OUTPUT',
      @RemoteProcedure = @RemoteProcedure, @spExists = @spExists OUTPUT;
    IF @spExists = 1
    BEGIN -- stored procedure is valid in @DatabaseName
      -- finally, this runs the remote stored procedure
      -- and passes a strongly-typed parameter:
      SET @RemoteProcedure = N'EXEC ' + @RemoteProcedure;
      EXEC @cmd @stmt = @RemoteProcedure, 
        @params = N'@UpdateUsage BIT', 
        @UpdateUsage = @UpdateUsage;
    END
  END
END

Yes, you’re still concatenating user input here, but it’s relatively safe because it is both validated and quoted. I’ll concede that this gets messy quickly (and you can continue following the rabbit hole, too, to separate the schema and procedure names, to determine if the objects and even parameters and columns exist in the remote database, and even to verify data types). But, in my humble opinion, this more tedious method beats the alternative of just trusting all of that user input, regardless of how much you actually trust your users.

Next Time

In my next tip, I will show some other tricks for validating and trusting the content of column lists, IN(@lists), and even entire WHERE clause expressions.

Next Steps

Make a plan to review your usage of dynamic SQL and identify opportunities to make it safer.
Review the following tips and other resources:

Resources

Community

Subscribe

Protecting Yourself from SQL Injection in SQL Server – Part 1