SQL注入漏洞veracode c#

Question

Veracode报告显示以下查询存在SQL注入缺陷。

private const string DropDatabaseTemplate = @"DROP DATABASE [{0}]";
ExecuteNonQuery(connection, string.Format(DropDatabaseTemplate, databaseName));
private static int ExecuteNonQuery(SqlConnection connection, string commandText)
{
using (var command = new SqlCommand(commandText, connection))
{
return command.ExecuteNonQuery();
}
}

他们建议使用参数化的准备语句。我的方法是什么来删除这个安全漏洞

提前谢谢。

答案：您可以使用此简单地避免安全漏洞

private static void ExecuteNonQuery(SqlConnection connection, string commandText)
{
using (var command = new SqlCommand("exec sp_executesql @sqlCommandText", connection))
{
command.Prepare();
command.Parameters.Add("@sqlCommandText", SqlDbType.NVarChar);
command.Parameters["@sqlCommandText"].Value = commandText;
command.ExecuteNonQuery();
}
}

Answer 1

我从未尝试过，但我怀疑它会起作用：

private static void DropDbNamed(SqlConnection connection, string name)
{
using (var command = new SqlCommand("EXEC @q", connection))
{
command.Parameters.AddWithValue("@q", $"DROP DATABASE [{name}]"); 
var command.ExecuteScalar();
}
}

_{注：乔尔的标准"停止使用AddWithValue"；此处不适用}

Answer 2

它会是什么样子。

private const string DropDatabaseTemplate = @"DROP DATABASE [{0}]";

private static int ExecuteNonQuery(SqlConnection connection, string commandText)
{
string dbNamesQuery_ = @"SELECT [name]
FROM sys.databases d
WHERE d.database_id > 4";
DataTable tableNames = new DataTable();
using (var command = new SqlCommand(dbNamesQuery_, connection))
{
SqlDataReader dataReader_ = command.ExecuteReader();
tableNames.Load(dataReader_);        //allow you dynamically load actual list DB, but you can fill table manually.
//find exactly same name of DB that user requared.
var rowsData_ = tableNames.Select(String.Format("name = '{0}'", commandText));        
if (rowsData_.Length == 1)        //it will be prevent any kind of injection.
{
command.CommandText = String.Format(DropDatabaseTemplate, commandText);
return command.ExecuteNonQuery();
}
else
{
return -1;
}
}
}