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

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

提前谢谢。

Ans :您可以简单地避免使用此的安全漏洞。

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