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:

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

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.

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!

2 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.

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.