CODEWITHSUNDEEP
sqlsql-serversql-injection
core
core

Reputation: 33059

SQL Server: Sanitizing @param against injection attacks

For the sake of argument, let's just say I have to create a local variable containing a SQL query that has an INSERT:

 DECLARE @insert NVARCHAR(MAX)
 SELECT @insert = 'INSERT INTO [dbo].[' + @table + '] VALUES...
 EXEC (@insert)

This INSERT is also going to contain a column value:

 DECLARE @insert NVARCHAR(MAX)
 SELECT @insert = 
  'INSERT INTO [dbo].[' + @table + '] VALUES (N''' + @message + ''')'
 EXEC (@insert)

Now, I'm obviously concerned about an injection attack, and would like to ensure that @message's value can't make @insert's value malicious or malformed as a query to EXEC.

This brings us to my question: is escaping the ' characters in @message sufficient? Are there any other characters that could appear in @message that could escape out?

Example:

 DECLARE @insert NVARCHAR(MAX)
 SELECT @message = REPLACE(@message,'''','''''')
 SELECT @insert = 
  'INSERT INTO [dbo].[' + @table + '] VALUES (N''' + @message + ''')'
 EXEC (@insert)

(When I say "have to", this is because my query is in a stored procedure, and this stored procedure accepts @table, which is the destination table to INSERT into. I'm not interested in discussing my architecture or why the table to INSERT into is "dynamically" specified via a procedure parameter. Please refrain from commenting on this unless there's another way besides EXEC()ing a query to specify a table to INSERT into when then table name is received as a procedure parameter.)

Upvotes: 7

Views: 4833

Answers (5)

Trent
Trent

Reputation: 1109

When writing dynamic SQL you'll want to parameterise as much as possible, and only resort to character escaping when you absolutely have to. You can't parameterise @table, but you can parameterise @message.

DECLARE @insert NVARCHAR(MAX)
set @insert = 'INSERT INTO [dbo].' + quotename(@table) + ' values(@message)'
exec sys.sp_executesql @insert, N'@message nvarchar(max)', @message = @inMessage;

There are a lot of ways attackers can exploit dynamic SQL, including buffer length attacks and using unicode equivalent characters. I encountered an example once where escaping the single quote char still had a vulnerability where one of the unicode equivalents of the quote char could be passed in. Part of the software stack was doing a unicode to ascii conversion, so it was possible to inject a quote back in after they were escaped. Ouch.

Upvotes: 0

core
core

Reputation: 33059

Apparently there's a 128-length limit to quotename(), even in 2008 according to my test, since it expects a SQL identifier. The reference suggests creating a quotestring() function, which does the same thing as:

REPLACE(@variable,'''','''''')

Therefore I am proposing that the answer is to create a function out of the REPLACE() above, like so:

CREATE FUNCTION quotestring(@string nvarchar(MAX)) 
RETURNS nvarchar(MAX) AS
BEGIN
    RETURN(REPLACE(@string,'''',''''''))
END

...Unless I've misunderstood something.

Upvotes: 0

Scott Whitlock
Scott Whitlock

Reputation: 13839

Rather than calling EXEC(@somesql), I suggest using the sp_executesql stored procedure. Specifically, this allows you to pass parameters, and the system will check that the parameters are valid.

Upvotes: 2

HardCode
HardCode

Reputation: 6756

You could first query the schema information with regular T-SQL and make sure the table name exists first. This way, if it's malformed SQL, it won't execute as code. It will just be a VARCHAR table name.

DECLARE @Table AS VARCHAR(MAX)
DECLARE @Exists AS BIT

SET @Table = 'Vicious malformed dynamic SQL'

SELECT  @Exists = COUNT(TABLE_NAME) 
FROM    INFORMATION_SCHEMA.TABLES 
WHERE   TABLE_NAME = @Table

IF (@Exists = 1)
    BEGIN
    PRINT 'Table exists'
    -- Execute dynamic SQL.
    END
ELSE
    PRINT 'Invalid table'

(Or simply use IF EXISTS (SELECT ....) )

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300529

Use sp_executesql and the built-in quotename(). This article, The Curse and Blessings of Dynamic SQL, is pretty much the definitive reference.

Upvotes: 10

Related Questions