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;
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'--'.
