清理表/列名的动态SQL在.NET？ （prevent SQL注入攻击） [英] Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)

问题描述

我生成了一些动态SQL，并希望确保我的code是 SQL注入安全。

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

有关缘故这里的说法是它是如何产生的一个小例子：

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

在上面， tableName值， COLUMNNAME ，不管势必 @value 来自不受信任的来源。由于占位符使用 @value 是SQL注入攻击安全的，可以忽略不计。 （该命令通过SqlCommand的执行。）

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 的不能的约束占位符，都为此<以注入攻击EM>脆弱的。由于这是一个真正的动态的情况，也没有白名单 tableName值或 COLUMNNAME 可用。

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.

因此​​，现在的问题是：

The question is thus:

有没有的标准，内置的方法来检查和/或消毒 tableName值和 COLUMNNAME ？ （SqlConnection的，或辅助类等）。如果没有，什么是不执行此任务的的好办法的使用第三方库？

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?

注：

• 所有SQL标识符，包括架构，应该由接受：如 [方案]。[我的表] .COLUMN 仅仅是为安全为表1 。
• 可以的的sanitize 的标识符或检测的一个无效的标识符。 （它并不需要，以确保表/列是在上下文中实际上是有效的;将所得的SQL可以是无效的，但必须是安全的）

• All SQL identifiers, including the schema, should by accepted: e.g. [schema].[My Table].column is just as "safe" as 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".)

更新：

刚刚发现这一点，并认为这是有点意思：有一个 SqlFunctions.QuoteName 在.NET4函数（EF4？）。好吧，它没有的真正的帮助我在这里...

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

推荐答案

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

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

由于假设，你可以使用常规的前pression下面的SQL Server的标识规则所定义的在MSDN 。虽然我是个彻头彻尾的新手在正规前pressions，我发现这一个应该接近：

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:

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

然而，一个普通的前pression不会解决SQL Server的关键字，它不保证该表和/或列确实存在（虽然你指出，这不是大问题）。

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).

如果这是我的申请，我会先确保最终用户并没有试图通过拒绝含有分号的任何请求执行注射液（;）。

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 (;).

接下来，我会确认表存在通过删除有效的名称分隔符（，，[，]），用句拆分表名，看是否被指定模式，并执行对INFORMATION_SCHEMA.TABLES查询来确定表是否存在。

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.

例如：

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.

最后，我想验证每个列名的存在通过进行一组类似的步骤，只是使用INFORMATION_SCHEMA.COLUMNS确定列（多个）一旦表已被确定为有效的有效性。

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.

我可能会为获取从SQL Server此表中的有效列的列表中，然后验证每个请求列是在我的code中的列表。这样，你可以告诉到底是哪列是错误的，并提供反馈给用户。

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在.NET？ （prevent SQL注入攻击）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！