Correct escaping of delimited identifers in SQL Server without using QUOTENAME
- by Ross Bradbury
Is there anything else that the code must do to sanitize identifiers (table, view, column) other than to wrap them in double quotation marks and "double up" and double quotation marks present in the identifier name? References would be appreciated.
I have inherited a code base that has a custom object-relational mapping (ORM) system. SQL cannot be written in the application but the ORM must still eventually generate the SQL to send to the SQL Server. All identifiers are quoted with double quotation marks.
string QuoteName(string identifier)
{
return "\" + identifier.Replace("\"", "\"\"") + "\"";
}
If I were building this dynamic SQL in SQL, I would use the built-in SQL Server QUOTENAME function:
declare @identifier nvarchar(128);
set @identifier = N'Client"; DROP TABLE [dbo].Client; --';
declare @delimitedIdentifier nvarchar(258);
set @delimitedIdentifier = QUOTENAME(@identifier, '"');
print @delimitedIdentifier;
-- "Client""; DROP TABLE [dbo].Client; --"
I have not found any definitive documentation about how to escape quoted identifiers in SQL Server. I have found Delimited Identifiers (Database Engine) and I also saw this stackoverflow question about sanitizing.
If it were to have to call the QUOTENAME function just to quote the identifiers that is a lot of traffic to SQL Server that should not be needed.
The ORM seems to be pretty well thought out with regards to SQL Injection. It is in C# and predates the nHibernate port and Entity Framework etc. All user input is sent using ADO.NET SqlParameter objects, it is just the identifier names that I am concerned about in this question. This needs to work on SQL Server 2005 and 2008.
