How to Safely Parameterize Table Names

Published on: 2017-09-05

Protecting against SQL Injection Part 2

Photo by Igor Ovsyannykov on Unsplash

Watch this content on YouTube.

Last week we talked about building dynamic SQL queries and how doing so might leave you open to SQL injection attacks.

In that post we examined how using sp_executesql to parameterize our dynamic SQL statements protects us.

Today, we are going to look at where sp_executesql falls short.

The problem with sp_executesql

Let’s modify last week’s final query to make our table name dynamic:

CREATE PROCEDURE dbo.sp_GetFullNameFromTable
		@ParmTableName varchar(100),
		@ParmUserName varchar(100)
AS
BEGIN
	DECLARE @FullQuery nvarchar(1000)
	SET @FullQuery = N'SELECT FullName FROM dbo.@TableName WHERE UserName = @UserName'

	DECLARE @ParmDefinition nvarchar(100) = N'@TableName varchar(100), @UserName varchar(100)';  

	EXEC sp_executesql @FullQuery, @ParmDefinition,  
					  @UserName = @ParmUserName,
					  @TableName = @ParmTableName;  

END

(there’s an entirely different discussion to be had about whether you should allow table and column names to be dynamic, but we’ll assume we need a dynamic table name for the rest of this post)

If we pass in a table name parameter value and execute this query, we’ll be greeted with this error:

Yeah, sp_executesql doesn’t like parameterizing a table names.

So how do we make table name dynamic and safe?

Unfortunately we have to fall back on SQL’s EXEC command.

However, like we discussed last week, we need to be vigilant about what kind of user input we allow to be built as part of our query.

Assuming our app layer is already sanitizing as much of the user input as possible, here are some precautions we can take on the SQL side of the equation:

QUOTENAME()

If we wrap our user input parameters with QUOTENAME(), we’ll achieve safety:

CREATE PROCEDURE dbo.sp_GetFullNameFromTableSanitized
	@ParmTableName varchar(100),
	@ParmUserName varchar(100)
AS
BEGIN
	DECLARE @FullQuery nvarchar(1000)
	SET @FullQuery = N'SELECT FullName FROM dbo.' + QUOTENAME(@ParmTableName) + ' WHERE UserName = @UserName'

	DECLARE @ParmDefinition nvarchar(100) = N'@UserName varchar(100)';  

	EXEC sp_executesql @FullQuery, @ParmDefinition,  
					  @UserName = @ParmUserName

END

This results in:

Although QUOTENAME() works well by adding brackets (by default) to sanitize quotes, it’s downside is that it will only output strings up to 128 characters long. If you are expecting parameters with values longer than that, you will have to use something like REPLACE(@TableName,'''','''''') instead to delimit single quotes (however, rolling your own logic like this is really hard to do securely and not recommended).

EXECUTE AS

The account running any dynamic SQL queries should be locked down so that it won’t be able to perform any operations you don’t want it to do.

Taking this idea one step further, you can create another account with very limited permissions, and add EXECUTE AS to your stored procedure to run that code under the very limited account.

CREATE PROCEDURE dbo.sp_GetFullNameFromTableSanitized
  @ParmTableName varchar(100),
  @ParmUserName varchar(100)
  WITH EXECUTE AS 'LimitedUser'
AS
BEGIN
...

This won’t prevent injection, but it will limit what the malicious user is able to do.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

4 thoughts on “How to Safely Parameterize Table Names”

  1. Regarding dynamic table names, I am developping kind of a mass importer/exporter tool add-in for Excel, which targets only a couple of tables in my Database. Would it be a good idea to validate if the table name received by the stored procedure is part of an authorized list of tables ? or it’s completely useless if I apply the concepts in this post ?

    Btw I just discovered your blog and your work, it’s amazing man ! You are an outstanding teacher, keep going !

    Ben

    1. Thanks for reading and commenting Ben!

      If you have a relatively short whitelist of allowable table names that you are checking for exact equality, then that’s probably the safest way to go. If done right, there’s no room for any unexpected code – either the table is in your whitelist or its not.

      If there’s any possibility of someone injecting some different value however, then a whitelist check is insufficient and you’ll need to implement some other technique.

  2. Hi i am in a situation where I have created a multi tenant SAAS solution. The db is similar to a ledger and there are 3 core tables per tenant. I realise one option is to move to 1 db per tenant however at this point in time it is quicker and easier to maintain/develop against 1 db (whils i am the sole dev)… So as you can imagine for any access to these 3 tenant specific tables i am having to use dynamic sql. I use Dapper.net and all access is via SPROCS called by dapper with params.. I then use sp_execute with params passed in to run them. Do you have any reccomendations/thoughts on this setup ? am i still prone to SQL injection or is the fact that i am using params in dapper protecting me from injection ?

    1. If you are truly parameterizing your queries, you should be safe. The only time you can run into problems is when you are building queries by concatenating strings with variables. So. As long as you don’t have anything that looks like the following you should be ok:

      ‘WHERE Col1=‘+@Value

      Also make sure you aren’t vulnerable to any second order injection attacks: https://bertwagner.com/2018/03/20/how-to-steal-data-using-a-second-order-sql-injection-attack/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.