在不使用 QUOTENAME 的情况下正确转义 SQL Server 中的分隔标识符 [英] Correct escaping of delimited identifers in SQL Server without using QUOTENAME

问题描述

除了用双引号将标识符(表、视图、列)括起来和标识符名称中存在的加倍"双引号外，代码是否还必须执行其他操作来清理标识符(表、视图、列)? 引用将不胜感激.

我继承了一个具有自定义对象关系映射 (ORM) 系统的代码库.SQL 不能写在应用程序中，但 ORM 最终仍必须生成 SQL 以发送到 SQL Server.所有标识符都用双引号引起来.

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("\"", "\"\"") + "\"";
}

如果我要在 SQL 中构建这个动态 SQL，我会使用内置的 SQL Server QUOTENAME 函数:

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; --"

我还没有找到任何关于如何在 SQL Server 中转义带引号的标识符的权威文档.我找到了 Delimited Identifiers (Database Engine) 并且我也看到了 this stackoverflow question关于消毒.

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.

如果它必须调用 QUOTENAME 函数来引用那些对 SQL Server 造成大量流量的标识符，而这些标识符是不需要的.

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.

关于 SQL 注入，ORM 似乎经过深思熟虑.它在 C# 中并且早于 nHibernate 端口和实体框架等.所有用户输入都是使用 ADO.NET SqlParameter 对象发送的，这只是我在这个问题中关心的标识符名称.这需要在 SQL Server 2005 和 2008 上运行.

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.

2010-03-31 更新

Update on 2010-03-31

虽然应用程序不应该允许用户在查询中输入标识符名称，但 ORM 通过它具有的 ORM 样式读取和自定义查询的查询语法来实现.我试图最终阻止所有可能的 SQL 注入攻击的 ORM，因为与所有应用程序代码相比，它非常小且易于验证.

While the application is not supposed to allow user-input for identifier names in queries, the ORM does via the query syntax that it has for both ORM-style reads and custom queries. It is the ORM that I am trying to ultimately prevent all possible SQL Injection attacks as that is very small and easy to verify as opposed to all the application code.

查询界面的简单示例:

session.Query(new TableReference("Client")
    .Restrict(new FieldReference("city") == "Springfield")
    .DropAllBut(new FieldReference("first_name"));

ADO.NET 通过此查询发送:

ADO.NET sends over this query:

exec sp_executesql N'SELECT "T1"."first_name" 
FROM "dbo"."Client" AS "T1" 
WHERE "T1"."city" = @p1;', 
N'@p1 nvarchar(30)', 
N'Springfield';

考虑一下在 nHibernate 查询语言 (HQL) 中的类似内容可能会有所帮助:

Perhaps it would help to think about how something similar this might look in nHibernate Query Language (HQL):

using (ISession session = NHibernateHelper.OpenSession())
{
    Client client = session
        .CreateCriteria(typeof(Client))  \\ <-- TableReference in example above
        .Add(Restrictions.Eq("city", "Springfield"))  \\ <-- FieldReference above
        .UniqueResult<Client>();
    return client;
}

也许我应该看看 nHibernate 如何保护输入.

Maybe I should look and see how nHibernate protects the input.

推荐答案

你的 QuoteName 函数需要检查长度，因为 T-SQL QUOTENAME 函数指定了它返回的最大长度.使用您的示例:

Your QuoteName function needs to check the length, because the T-SQL QUOTENAME function specifies the maximum length it returns. Using your example:

String.Format(@"declare @delimitedIdentifier nvarchar(258);
set @delimitedIdentifier = {0};", QuoteName(identifier));

如果 QuoteName(identifier) 超过 258 个字符，当分配给 @delimitedIdentifier 时，它会被静默截断.发生这种情况时，您就有可能不正确地转义 @delimitedIdentifier.

If QuoteName(identifier) is longer than 258 characters, it will be silently truncated when assigned to @delimitedIdentifier. When that happens, you open up the possibility for @delimitedIdentifier to be escaped improperly.

有 Microsoft 安全软件开发人员"Bala Neerumalla 撰写的 MSDN 文章，更深入地解释了该主题.这篇文章还包含我发现的最接近关于如何在 SQL Server 中转义引用标识符的最终文档"的内容:

There is an MSDN article by Bala Neerumalla, a "security software developer at Microsoft", that explains the topic in more depth. The article also contains the closest thing I have found to "definitive documentation about how to escape quoted identifiers in SQL Server":

转义机制只是将右方括号的出现次数加倍.您不需要对其他字符执行任何操作，包括左方括号.

The escaping mechanism is simply doubling up the occurrences of right square brackets. You don't need to do anything with other characters, including left square brackets.

这是我目前使用的 C# 代码:

This is the C# code I am currently using:

/// <summary>
/// Returns a string with the delimiters added to make the input string
/// a valid SQL Server delimited identifier. Brackets are used as the
/// delimiter. Unlike the T-SQL version, an ArgumentException is thrown
/// instead of returning a null for invalid arguments.
/// </summary>
/// <param name="name">sysname, limited to 128 characters.</param>
/// <returns>An escaped identifier, no longer than 258 characters.</returns>
public static string QuoteName(string name) { return QuoteName(name, '['); }

/// <summary>
/// Returns a string with the delimiters added to make the input string
/// a valid SQL Server delimited identifier. Unlike the T-SQL version,
/// an ArgumentException is thrown instead of returning a null for
/// invalid arguments.
/// </summary>
/// <param name="name">sysname, limited to 128 characters.</param>
/// <param name="quoteCharacter">Can be a single quotation mark ( ' ), a
/// left or right bracket ( [] ), or a double quotation mark ( " ).</param>
/// <returns>An escaped identifier, no longer than 258 characters.</returns>
public static string QuoteName(string name, char quoteCharacter) {
    name = name ?? String.Empty;
    const int sysnameLength = 128;
    if (name.Length > sysnameLength) {
        throw new ArgumentException(String.Format(
            "name is longer than {0} characters", sysnameLength));
    }
    switch (quoteCharacter) {
        case '\'':
            return String.Format("'{0}'", name.Replace("'", "''"));
        case '"':
            return String.Format("\"{0}\"", name.Replace("\"", "\"\""));
        case '[':
        case ']':
            return String.Format("[{0}]", name.Replace("]", "]]"));
        default:
            throw new ArgumentException(
                "quoteCharacter must be one of: ', \", [, or ]");
    }
}

这篇关于在不使用 QUOTENAME 的情况下正确转义 SQL Server 中的分隔标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！