One of the requirement for an application I’m currently working on is for the end user of a web application/site to be able to create objects in the database.

There are a number of objects that must be created or manipulated but for the sake of simplicity let’s take for example creating a table with one column. The SQL statement would look like:

CREATE TABLE [MyTable] ( [MyColumn] INT NOT NULL )

To minimize SQL injection, I was hoping I could use the sp_executesql stored procedure to come up with a parameterized query (in addition to other preventive measures like validating input). Something like:

DECLARE @SQLString NVARCHAR(MAX);
DECLARE @ParamDefinition NVARCHAR(256);

SET @SQLString =
  N’CREATE TABLE @TableName (@ColumnName INT NOT NULL)’;
SET @ParamDefinition =
  N’@TableName VARCHAR(128), @ColumnName VARCHAR(128)';

EXECUTE sp_executesql
  @SQLString,
  @ParamDefinition,
  @TableName = ‘MyTable’,
  @ColumnName = ‘MyColumn’;

When I executed the statement it returned an error “Incorrect syntax near ‘@TableName’". Though I haven’t worked with sp_executesql with Data Definition Language (DDL) statements I have been using it for Data Manipulation Language (DML) statements such as conditional selects and others so I’m quite sure there is nothing wrong with syntax near @TableName by itself. What could be wrong?[more]

It turns out that using sp_executesql with DDL statements are not supported.

So in this particular case, to minimize SQL injection you have to use concatenation to dynamically build the SQL statement.

DECLARE @SQLString NVARCHAR(MAX)
DECLARE @TableName VARCHAR(128)
DECLARE @ColumnName VARCHAR(128)
SET @TableName = ‘MyTable’
SET @ColumnName = ‘MyColumn’
SET @SQLString =
 ‘CREATE TABLE ’ + @TableName + ‘( ’ + @ColumnName + ’ INT NOT NULL)’
EXEC (@SQLString)

But wait, there is something else you can do to prevent SQL injection so all is not really hopeless. Meet QUOTENAME. As you might have noticed before, one way to create a table with spaces in the name is using quotes or brackets like CREATE TABLE [hello world how are you]. So anything between the delimiter (eg. quote, brackets) are considered part of the TableName rather than a command/statement.

When you experiment more you will notice that the following statement will create a table named as indicated instead throwing an error or doing something messy: 

CREATE TABLE [Hello; Drop Master; ] ( [MyColumn] INT NOT NULL)

Yes I know that’s a very clever sql injection attempt but here’s another example:

DECLARE @SQLString NVARCHAR(MAX)
DECLARE @TableName VARCHAR(128)
DECLARE @ColumnName VARCHAR(128)
SET @TableName = ‘MyTable (ColA INT NULL); PRINT ‘‘HELLO’’; – ’
SET @ColumnName = ‘MyColumn’
SET @SQLString =
 ‘CREATE TABLE ’ + @TableName + ‘( ’ + @ColumnName + ’ INT NOT NULL)’
PRINT (@SQLString)

Try executing that in your table and along with creating the table named MyTable you will see HELLO printed in the output/messages window. If you replace the print statement with a more maliciously statement such as  DROP DATABASE master and you happen to be running a highly privileged account then you’re in trouble.

But try the statement again but this time using this @SQLString:

SET @SQLString =
 ‘CREATE TABLE ’ + QUOTENAME(@TableName) + ‘( ’ + QUOTENAME(@ColumnName) + ’ INT NOT NULL)’

As you might have expected you should get a table named [MyTable (ColA INT NULL); PRINT ‘HELLO’; – ] instead of HELLO being printed. Should there be a ‘[’ or ‘]’ in your variable value, it will be changed to ‘[[’ and ‘]]’ respectively just like how single quotes are changed to two single quotes to avoid issues.

Now that’s seems a lot better.

So in addition to other ways of protecting your database such as (but not limited to) validating input (very well), running least privileged SQL server account, using stored procedures (along with determining proper parameter data type and length) when faced with the need to perform dynamic queries where you cannot use sp_executesql like in this case with DDL statements, see if QUOTENAME would be applicable and if it would help.

** note that QUOTENAME(dbo.Employee) will not result to [dbo].[Employee] but rather [dbo.Employee] so for fully qualified names, quote the server/database/schema and table names respectively.