Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Protecting Yourself from SQL Injection in SQL Server - Part 1

By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | More > SQL Injection

Attend this free live MSSQLTips webcast

Leveraging Storage Spaces Direct for SQL Server High Availability

Thursday, July 19, 2018 - click here to learn more


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.


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.


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:


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


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:


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)
  SET @sql = N'SELECT * FROM dbo.' + QUOTENAME(@tablename);
  RAISERROR(N'Invalid table name supplied.', 11, 1);

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)
  SET @sql = N'SELECT * FROM ' + QUOTENAME(@schemaname) + N'.Users';
  RAISERROR(N'Invalid schema name supplied.', 11, 1);

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)
  SET @sql = N'SELECT * FROM ' + QUOTENAME(@LinkedServerName)
    + N'.dbname.dbo.Users;';

-- or

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
  SET @sql = N'SELECT * FROM ' + QUOTENAME(@DatabaseName) + N'.dbo.Users;';

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

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:

  @LinkedServerName SYSNAME = N'LinkedServer',
  @DatabaseName SYSNAME = N'msdb',
  @RemoteProcedure SYSNAME = N'sys.sp_spaceused',
  @UpdateUsage BIT = 1;

  @dbExists BIT, 
  @spExists BIT;

  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;

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

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Wednesday, May 27, 2015 - 5:18:00 PM - TimothyAWiseman Back To Top

This is an excellent and well written article on an important topic, so thank you.  Thank you also for linking to my article on Defense in Depth

But I really wanted to emphasize the importance of Sommarskog's article, The Curse and Blessings of Dynamic SQL.  It goes well beyond providing excellent advice on dealing with SQL injection and goes into detail about the best ways to use (and avoid) dynamic SQL.  You helpful list it at the end among other links, but I think that one is well worth reading both for people concerned about SQL injection but also just by anyone thinking about using Dynamic SQL.

Learn more about SQL Server tools