SQL Injection – Are Linked Server Queries Susceptible?

information-schemalinked-serversql serversql-injection

I'm using a stored procedure to generate a create table script on the fly based on parameters passed into it. I am validating that the server exists, and then validating if the table exists in the database on said server.

So, first I check if the server exists:

Exists(SELECT 1 FROM sys.servers WHERE name = @Server)

If it passes this check I go on to build a string that points to Information_schema:

set @InformationSchemaPath = @Server + '.' + @DataBaseName + '.' +  'information_schema'

and then use that string in a dynamic SQL statement that gets put into a string:

set @DynamicSQL = ('SELECT count(*) 
             FROM ' +  @InformationSchemaPath + '.Tables
             WHERE  TABLE_NAME = ''' + @TableName + '''')

The results get inserted into a temp table:

INSERT INTO @receiver Exec (@DynamicSQL )

I am unable to use SP_EXECUTESQL. I am stuck using Exec.

Best Answer

Yes, this is unsafe. Try passing this:

@TableName = 'FakeTableName'';DROP TABLE Foo;PRINT''You have been Pwned'

One way to avoid this is to pass @TableName as a parameter to the dynamic SQL. Like so:

SET @DynamicSQL = '... WHERE TABLE_NAME = @TableName'

And then do this:

INSERT INTO @Receiver EXEC sp_executesql @DynamicSQL, @TableName

From the wording of your question, I am not sure if you are saying that you can't run sp_executesql or that you are not sure about the syntax. If you have to run the statement using only EXEC there is a method that works, but only on a Linked Server. You do do this:

SET @DynamicSQL = '... WHERE TABLE_NAME = ?'

Followed by this:

INSERT INTO @Receiver EXEC (@DynamicSQL, @TableName) AT YourLinkedServer
Related Question