Securing Dynamic SQL with QUOTENAME

I’m a big fan of dynamic SQL in the right conditions. One key to crafting safe dynamic query of the use of the QUOTENAME function.

The issue

Using dynamic SQL can leave us vulnerable without proper safeguard. Let’s see an example of this:

CREATE OR ALTER PROC dbo.TruncateTable (
	@SchemaName VARCHAR(100),
	@TableName VARCHAR(100)
) AS
BEGIN

	DECLARE @Sql VARCHAR(MAX);

	SELECT @Sql = 'TRUNCATE TABLE ' +
		@SchemaName + '.' +
		@TableName;

	EXEC (@Sql);

END
GO

This is a cut down example of something I’ve seen previously in production. The intended use of this procedure is pretty innocent:

EXEC dbo.TruncateTable
	@SchemaName = 'Staging',
	@TableName = 'Customers';

The issue we have here is that the string being executed is simply joining the bits of text together without any checks. This leaves us open to SQL injection.

What if a user maliciously changed the parameters being passed? Things could be much worse:

EXEC dbo.TruncateTable
	@SchemaName = 'Staging',
	@TableName = 'Customers; DROP TABLE dbo.Customers';

This would have very much unintended consequences. It would have executed the following command(s):

TRUNCATE TABLE Staging.Customers;
DROP TABLE dbo.Customers

This method could be used as an attempt to drop the database, create credentials, or as a way to query and scraping data from the database unknowingly.

Enter QUOTENAME

To prevent this type of vulnerability we have a function to protect our strings: QUOTENAME

We can pass a string to this function and it’ll return the string formatted correctly as an identifier. For a regular string such as Customers above, this will simply add brackets, so return [Customers].

Where this function helps is that if we have any object names which are unusual – containing a space for instance – it can format these correctly and can be used as part of a dynamic SQL string.

More importantly, the same also protects us when we’re provided object names which are maliciously formed. It’ll assume the entire string is a single identifier and format it appropriately. Below is a shortened example and the output:

SELECT QUOTENAME('a"b[]c''d e');

[a”b[]]c’d e]

The entire string has been enclosed by brackets to indicate a single identifier. Also notice the bracket inside the string has been quoted to show it’s still part of the same string.

This is how we can protect ourselves.

In action

We’ll modify a couple of lines in our procedure with this function:

CREATE OR ALTER PROC dbo.TruncateTable (
	@SchemaName VARCHAR(100),
	@TableName VARCHAR(100)
) AS
BEGIN

	DECLARE @Sql VARCHAR(MAX);

	/* Use QUOTENAME for safety */
	SELECT @Sql = 'TRUNCATE TABLE ' +
		QUOTENAME(@SchemaName) + '.' +
		QUOTENAME(@TableName);

	EXEC (@Sql);

END
GO

With that in place the statement we’ll be executing from our typical use case looks fine:

TRUNCATE TABLE [Staging].[Customers]

Our malicious example is now much safter:

TRUNCATE TABLE [Staging].[Customers; DROP TABLE dbo.Customers]

When executing the procedure instead of us removing all our customers we now receive an error:

Cannot find the object “Customers; DROP TABLE dbo.Customers” because it does not exist or you do not have permissions.

Wrap up

In this post we’ve looked at how to use the QUOTENAME function to protect ourselves when constructing dynamic SQL strings.

Even when the strings are constructed and executed by automated processes it’s possible for underlying data to be manipulated accidentally or maliciously. Having tools like this when constructing code, particularly code which is designed to be dynamic, can protect us.

Of course the real benefit here is that we can now use more creative table naming: