SQL Server: Sanitizing @param Against Injection Attacks

Question

For the argument, let's just say that we must create a local variable containing an SQL query with INSERT:

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

This INSERT will also contain the column value:

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

Now I am clearly concerned about the injection attack and would like to make sure that the @message value cannot make the @insert value malicious or distorted as an EXEC request.

This brings us to my question: are there enough "characters in @message"? Are there any other characters that may appear in @message that may fail?

Example:

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

(When I say “ must ”, this is because my request is in a stored procedure, and this stored procedure accepts @table, which includes the INSERT destination table. I'm not interested in discussing my architecture or why the INSERT table is “dynamically” set using the procedure parameter. Please refrain from commenting on this if there is no other way than EXEC () for the query to specify the INSERT table when then the table name is accepted as the procedure parameter.)

Answer 1

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

Answer 2

Instead of calling EXEC (@somesql), I suggest using the sp_executesql stored procedure . In particular, it allows you to pass parameters, and the system checks the correctness of the parameters.

Answer 3

First, you can query for schema information using regular T-SQL and make sure that the table name exists first. Thus, if it has distorted SQL, it will not be executed as code. This will simply be the name of the VARCHAR table.

 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 just use IF EXISTS (SELECT ....))

Answer 4

There seems to be a 128 length limit to quotename (), even in 2008, according to my test, since it expects an SQL identifier. The link suggests creating a quotestring () function that does the same thing:

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

Therefore, I propose that the answer is to create a function from REPLACE () above, for example:

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

... if I didn’t understand something.