使用通用字典将值插入到表中

Question

编码平台：ASP.NET 2.0 WebForms，C#，MySQL 作为后端

背景

我目前正在修复网站的错误。
其中一张表“registrations”有 80 列。

插入/更新是通过简单的 sql 语句完成的，无需任何参数化查询。

问题

在注册时，用户可以改变许多参数，从而导致至少 15 种 INSERT 查询。我的问题是如何确保所有字段都插入正确的值。

所以，我创建了一个

Dictionary<string, string> fields = new Dictionary<string, string>();

fields.Add("LoginEmail", MySQL.SingleQuoteSQL(txtLoginEmail.Text));
fields.Add("Password", MySQL.SingleQuoteSQL(txtLoginPassword.Text));

fields.Add("ContactName", MySQL.SingleQuoteSQL(txtContactName.Text));
fields.Add("City", MySQL.SingleQuoteSQL(txtCity.Text));

我的想法是做一个像这样的简单插入查询

INSERT INTO registrations("all keys as comma separated string") VALUES ("all keys as comma separated string") 

我的问题是

1. 字典是实现这个的最佳数据结构吗？
2. 按通用字典对键进行排序是否会更改查询时的键值索引？
3. 将所有键提取到数组中并将相应值提取到另一个匹配数组的最佳方法。

另外，还有哪些其他更好的方法？

PS：我正在维护代码，并且创建一个将列映射到属性并存储值的实体类，这不是一个选项。

Answer 1

我认为在这种情况下字典结构没问题，但按

原样构建和执行字符串允许 SQL 注入攻击

xkcd.com/327/ Exploits Of A Mom

我正在使用 .NET 3.5，但这个想法也可以应用于 .NET 2.0

如果 MySQL.SingleQuoteSQL 的功能超出了其名称的建议，请告诉我

否则将值添加为参数以防止出现这种情况

var values = new Dictionary<string, string>() {
  {"LoginEmail", "LoginEmail"},
  {"Password", "Password"},
  {"ContactName", "ContactName"},
  {"City", "City"}
};

System.Func<string, string> key = p => String.Concat("?", p);

var statement = string.Format("INSERT INTO registrations ({0}) VALUES ({1})", 
  string.Join(",", values.Values.ToArray()),
  string.Join(",", values.Keys.Select(key).ToArray())
);

//"INSERT INTO registrations (LoginEmail,Password,ContactName,City) VALUES (?LoginEmail,?Password,?ContactName,?City)"  

foreach(var p in values) {
  command.Parameters.Add(key(p.Key), p.Value, SqlDbType.Text);
}

command.Prepare();
command.ExecuteNonQuery();

They may be other better classes for .NET mysql, apologies if so - I haven't used mysql in .NET

Answer 2

我知道描述说你没有使用参数化sql，但恕我直言，使用参数化sql是防止sql注入的更好方法。将一些 SQL 放入 txtLoginEmail 并提交表单对您的情况造成严重破坏根本不需要太多努力。

private static string CreateInsertSql(string table, 
                                      IDictionary<string, string> parameterMap)
{
    var keys = parameterMap.Keys.ToList();
    // ToList() LINQ extension method used because order is NOT
    // guaranteed with every implementation of IDictionary<TKey, TValue>

    var sql = new StringBuilder("INSERT INTO ").Append(table).Append("(");

    for (var i = 0; i < keys.Count; i++)
    {
        sql.Append(keys[i]);
        if (i < keys.Count - 1)
            sql.Append(", ");
    }

    sql.Append(") VALUES(");

    for (var i = 0; i < keys.Count; i++)
    {
        sql.Append('?').Append(keys[i]);
        if (i < keys.Count - 1)
            sql.Append(", ");
    }

    return sql.Append(")").ToString();
}

private static void SqlInsert(string table, IDictionary<string, string> parameterMap)
{
    using (var connection = AcquireConnection())
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.Connection = connection;
            command.CommandText = CreateInsertSql(table, parameterMap);
            foreach (var pair in parameterMap)
                command.Parameters.Add(pair.Key, pair.Value);
            command.ExecuteNonQuery();
        }
    }
}

调用会是这样的：

SqlInsert("registrations", new Dictionary<string, string>()
{
    { "LoginEmail", txtLoginEmail.Text },
    { "Password", txtLoginPassword.Text },
    { "ContactName", txtContactName.Text },
    { City", txtCity.Text }
});

Answer 3

 string list<T>(IEnumerable<T> enumerable)
 {
   List<T> list = new List<T>(enumerable);
   return string.Join(",", list.ToArray());
 } 

//...
string sql= String.Format("INSERT INTO registrations({0}) VALUES({1})",
                list(fields.Keys),
                list(fields.Values));