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.)
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.
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.
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.
Use sp_executesql
and the built-in quotename()
. This article, The Curse and Blessings of Dynamic SQL, is pretty much the definitive reference.
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 ....) )
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With