在.NET中的动态SQL中清理表/列名称？ （防止SQL注入攻击）

I am generating some Dynamic SQL and would like to ensure that my code is safe from SQL injection.

我正在生成一些动态SQL，并希望确保我的代码是安全的SQL注入。

For sake of argument here is a minimal example of how it is generated:

为了争论，这里是一个如何生成它的最小例子：

var sql = string.Format("INSERT INTO {0} ({1}) VALUES (@value)",
    tableName, columnName);

In the above, tableName, columnName, and whatever is bound to @value come from an untrusted source. Since placeholders are being used @value is safe from SQL injection attacks, and can be ignored. (The command is executed via SqlCommand.)

在上面，tableName，columnName和绑定到@value的任何内容都来自不受信任的源。由于使用了占位符，因此@value可以免受SQL注入攻击，并且可以忽略。 （该命令通过SqlCommand执行。）

However, tableName and columnName cannot be bound as placeholders and are therefor vulnerable to injection attacks. Since this a "truly dynamic" scenario, there is no whitelist of tableName or columnName available.

但是，tableName和columnName不能绑定为占位符，因此容易受到注入攻击。由于这是一个“真正动态”的场景，因此没有tableName或columnName的白名单可用。

The question is thus:

因此问题是：

Is there a standard, built-in way to check and/or sanitize tableName and columnName? (SqlConnection, or a helper class, etc.) If not, what is a good way to perform this task without using a 3rd party library?

是否有标准的内置方法来检查和/或清理tableName和columnName？ （SqlConnection，或帮助程序类等）如果没有，在不使用第三方库的情况下执行此任务的好方法是什么？

Notes:

笔记：

• All SQL identifiers, including the schema, should by accepted: e.g. [schema].[My Table].column is just as "safe" as table1.
• 所有SQL标识符，包括模式，都应该被接受：例如[架构]。[我的表] .column和table1一样“安全”。
• Can either sanitize the identifiers or detect an invalid identifier. (It does not need to ensure that the table/column is actually valid in context; the resulting SQL can be invalid, but must be "safe".)
• 可以清理标识符或检测无效标识符。 （它不需要确保表/列在上下文中实际有效;生成的SQL可能无效，但必须是“安全的”。）

---

Update:

更新：

Just found this, and thought it was somewhat interesting: There is a SqlFunctions.QuoteName function in .NET4 (EF4?). Okay, it doesn't really help me here...

刚发现这个，并认为它有点有趣：.NET4中有一个SqlFunctions.QuoteName函数（EF4？）。好的，这对我来说真的没有帮助......

3 个解决方案

#1

Since you are using an SqlConnection, the assumption is that this is an SQL Server database.

由于您使用的是SqlConnection，因此假设这是一个SQL Server数据库。

Given that assumption, you could validate the table and field names using a regular expression that follows the SQL Server identifier rules as defined in MSDN. While I am a complete and utter novice at regular expressions, I did find this one that should come close:

根据该假设，您可以使用遵循MSDN中定义的SQL Server标识符规则的正则表达式来验证表和字段名称。虽然我是正则表达式的完全和完全新手，但我确实发现这个应该接近：

[\p{L}{\p{Nd}}$#_][\p{L}{\p{Nd}}@$#_]*

However, a regular expression will not address SQL Server keywords and it does not ensure that the table and/or column actually exists (although you indicated that wasn't much of an issue).

但是，正则表达式不会解决SQL Server关键字，并且不能确保表和/或列实际存在（尽管您指出这不是一个问题）。

If this were my application, I would first ensure the end user was not trying to perform injection by rejecting any request that contained semi-colons (;).

如果这是我的应用程序，我首先要确保最终用户没有尝试通过拒绝任何包含分号（;）的请求来执行注入。

Next, I would validate the table existence by removing the valid name delimiters (", ', [, ]), splitting the table name by a period to see if a schema was specified, and executing a query against INFORMATION_SCHEMA.TABLES to determine the existence of the table.

接下来，我将通过删除有效的名称分隔符（“，'，[，]），将表名拆分一段以查看是否已指定架构，并对INFORMATION_SCHEMA.TABLES执行查询来确定表的存在。

For example:

例如：

SELECT 1 
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_NAME = 'tablename' 
AND    TABLE_SCHEMA = 'tableschema'

If you create this query using parameters, then you should further protect yourself from injection.

如果使用参数创建此查询，则应进一步保护自己免受注入。

Finally, I would validate the existence of each column name by performing a similar set of steps, only using INFORMATION_SCHEMA.COLUMNS to determine the validity of the column(s) once the table has been determined to be valid.

最后，我将通过执行一组类似的步骤来验证每个列名称的存在，只有在确定表有效后才使用INFORMATION_SCHEMA.COLUMNS来确定列的有效性。

I would probably fetch the list of valid columns for this table from SQL Server, then verify that each request column was in the list within my code. That way you could tell exactly which columns were in error and provide that feedback to the user.

我可能会从SQL Server中获取此表的有效列列表，然后验证每个请求列是否在我的代码中的列表中。这样，您可以确切地确定哪些列出错并向用户提供反馈。

#2

I'm not sure if you're still looking into this, but the DbCommandBuilder class provides a method QuoteIdentifier for this purpose. The main benefits of this are that it's database-independent and doesn't involve any RegEx mess.

我不确定你是否还在研究这个，但是DbCommandBuilder类为此提供了一个方法QuoteIdentifier。这样做的主要好处是它与数据库无关，并且不涉及任何RegEx混乱。

As of .NET 4.5, you have everything you need to sanitize table and column names just using your DbConnection object:

从.NET 4.5开始，只需使用DbConnection对象就可以获得清理表名和列名所需的一切：

DbConnection connection = GetMyConnection(); // Could be SqlConnection
DbProviderFactory factory = DbProviderFactories.GetFactory(connection);

// Sanitize the table name
DbCommandBuilder commandBuilder = factory.CreateCommandBuilder();

string tableName = "This Table Name Is Long And Bad";
string sanitizedTableName = commandBuilder.QuoteIdentifier(tableName);

IDbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM " + sanitizedTableName;

// Becomes 'SELECT * FROM [This Table Name Is Long And Bad]' in MS-SQL,
// 'SELECT * FROM "This Table Name Is Long And Bad"' in Oracle, etc.

(Pre-4.5, you'll need some other way to get your DbProviderFactory -- maybe from the data provider name in your application configuration or hard-coded somewhere.)

（4.5之前，您需要一些其他方式来获取您的DbProviderFactory - 可能来自应用程序配置中的数据提供程序名称或在某处进行硬编码。）

#3

For SQL Server, it's pretty simple to sanitize an identifier:

对于SQL Server，清理标识符非常简单：

// To make a string safe to use as an SQL identifier :
// 1. Escape single closing bracket with double closing bracket
// 2. Wrap in square brackets
string.Format("[{0}]", identifier.Replace("]", "]]"));

Once wrapped in brackets and escaped, the only thing that won't work as an identifier is an empty/null string.

一旦被括在括号中并进行转义，唯一不能用作标识符的是空/空字符串。