or
sql-server
evan carroll imported from SE
What is the SQL Server method of safe-quoting identifiers for dynamic sql generation.

* MySQL has [`quote_identifier`](https://dev.mysql.com/doc/refman/8.0/en/sys-quote-identifier.html)
* PostgreSQL has [`quote_ident`](https://www.postgresql.org/docs/current/static/functions-string.html)

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;
Top Answer
Erik Darling
The function that you're looking for is [`QUOTENAME`][1]!

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

  [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql

**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'--'.
What function quotes an identifier in dynamic-sql with SQL Server?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.