SQL Server Security – What Function Quotes an Identifier in Dynamic SQL?

Securitysql serversql-injection

What is the SQL Server method of safe-quoting identifiers for dynamic sql generation.

How do I ensure given a dynamically generated column name for a dynamically generated statement that the column itself isn't a SQL-injection attack.

Let's say I have a SQL Statement,

SELECT [$col] FROM table;

which is essentially the same as

'SELECT [' + $col + '] FROM table;'

What stops an injection attack where

$col = "name] FROM sys.objects; \r\n DROP TABLE my.accounts; \r\n\ --";

Resulting in

SELECT [name] FROM sys.objects;
DROP TABLE my.accounts;
-- ] FROM table;

Best Answer

The function that you're looking for is QUOTENAME!

Through the practical use of square bracket technology, you can safely encapsulate strings to aid in the prevention of hot SQL injection attacks.

Note that just sticking square brackets around something does not safely quote it out, though you can avoid your code erroring with invalid characters in object names.

Good code

DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql = 'SELECT ' + QUOTENAME(d.name) + ' FROM your_mom'
FROM sys.databases AS d

Bad code

DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql = 'SELECT [' + d.name + '] FROM your_mom'
FROM sys.databases AS d

To give a specific example...

The following works fine for the initial input

DECLARE @ObjectName SYSNAME = 'sysobjects';

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT COUNT(*) FROM [' + @ObjectName + ']';

EXEC (@dynSql);

But with malicious input it is vulnerable to SQL injection 

DECLARE @ObjectName SYSNAME = 'sysobjects];SELECT ''This is some arbitrary code executed. It might have dropped a table or granted permissions''--'

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT  COUNT(*)  FROM [' + @ObjectName + ']';

EXEC (@dynSql);

Using QUOTENAME correctly escapes the embedded ] and prevents the attempted SQL injection from happening.

DECLARE @ObjectName SYSNAME = 'sysobjects];SELECT ''This is some arbitrary code executed. It might have dropped a table or granted permissions''--'

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT  COUNT(*)  FROM ' + QUOTENAME(@ObjectName);

EXEC (@dynSql);

Invalid object name 'sysobjects];SELECT 'This is some arbitrary code executed. It might have dropped a table or granted permissions'--'.

Related Question