怎样防止SQL注入这个功能呢？ [英] How do I protect this function from SQL injection?

问题描述

 公共静态布尔TruncateTable（字符串DBALIAS，串tableName值）
{
    字符串的SQLStatement =的String.Format（TRUNCATE TABLE {0}中，TableName）;
    返回的ExecuteNonQuery（DBALIAS，的SQLStatement）> 0;
}  

解决方案

最常见的建议，拼SQL注入是使用SQL查询参数（几个人在此线程建议吧）。

这是在这种情况下，错误的答案。您不能使用一个SQL查询参数在一个DDL语句中的表名。

SQL查询参数只能在地方在SQL前pression文本值的使用。这是SQL的每一个实施标准。

我对防止SQL注入，当你有一个表名是验证输入字符串与已知的表名的列表建议。

您可以从 INFORMATION_SCHEMA 有效的表名的列表：

  SELECT TABLE_NAME
从INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='基表'
  和TABLE_NAME = @tablename
 

现在您可以将输入变量传递给该查询作为SQL参数。如果查询返回任何行，你知道的输入无效作为一个表来使用。如果查询返回的行，它相匹配，让你有更多的保证，你可以放心地使用它。

您也可以验证表名与定义为好你的应用程序，以截断特定表的列表，如@约翰·布坎南<一个href=\"http://stackoverflow.com/questions/1862036/how-do-i-protect-this-function-from-sql-injection/1862134#1862134\">suggests.

即使验证后 tableName值存在在你的RDBMS中的表名，我也建议划定表名，以防万一您使用空格或特殊的表名字符。在Microsoft SQL Server中，默认标识符分隔符是方括号：

 字符串的SQLStatement =的String.Format（TRUNCATE TABLE [{0}]中，TableName）;
 

现在，你只在对SQL注入的风险是，如果 tableName值匹配一个真正的表，你实际上是在你的表的名称使用方括号！

public static bool TruncateTable(string dbAlias, string tableName)
{
    string sqlStatement = string.Format("TRUNCATE TABLE {0}", tableName);
    return ExecuteNonQuery(dbAlias, sqlStatement) > 0;
}

解决方案

The most common recommendation to fight SQL injection is to use an SQL query parameter (several people on this thread have suggested it).

This is the wrong answer in this case. You can't use an SQL query parameter for a table name in a DDL statement.

SQL query parameters can be used only in place of a literal value in an SQL expression. This is standard in every implementation of SQL.

My recommendation for protecting against SQL injection when you have a table name is to validate the input string against a list of known table names.

You can get a list of valid table names from the INFORMATION_SCHEMA:

SELECT table_name 
FROM INFORMATION_SCHEMA.Tables 
WHERE table_type = 'BASE TABLE'
  AND table_name = @tableName

Now you can pass your input variable to this query as an SQL parameter. If the query returns no rows, you know that the input is not valid to use as a table. If the query returns a row, it matched, so you have more assurance you can use it safely.

You could also validate the table name against a list of specific tables you define as okay for your app to truncate, as @John Buchanan suggests.

Even after validating that tableName exists as a table name in your RDBMS, I would also suggest delimiting the table name, just in case you use table names with spaces or special characters. In Microsoft SQL Server, the default identifier delimiters are square brackets:

string sqlStatement = string.Format("TRUNCATE TABLE [{0}]", tableName);

Now you're only at risk for SQL injection if tableName matches a real table, and you actually use square brackets in the names of your tables!

这篇关于怎样防止SQL注入这个功能呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！