Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.)

like image 418
core Avatar asked Apr 01 '09 02:04

core


People also ask

What protection could be used to prevent an SQL injection attack?

The only sure way to prevent SQL Injection attacks is input validation and parametrized queries including prepared statements. The application code should never use the input directly. The developer must sanitize all input, not only web form inputs such as login forms.

What is sanitization SQL?

Validation checks if the input meets a set of criteria (such as a string contains no standalone single quotation marks). Sanitization modifies the input to ensure that it is valid (such as doubling single quotes). You would normally combine these two techniques to provide in-depth defense to your application.

Why is it important to sanitize input before inserting it in your database?

Failure to sanitize inputs can lead to attackers including SQL code in form inputs so they can do any number of interesting things, ranging from deleting information from a database to injecting information.


3 Answers

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

like image 80
Mitch Wheat Avatar answered Oct 19 '22 01:10

Mitch Wheat


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 ....) )

like image 28
HardCode Avatar answered Oct 19 '22 02:10

HardCode


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.

like image 2
Scott Whitlock Avatar answered Oct 19 '22 01:10

Scott Whitlock