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.