C# 将查询中的参数替换为定义的值

本文关键字：定义 替换 参数 查询 | 更新日期: 2024-10-20 09:51:18

string[] theParms = ['parm1', 'parm2'];
string theQuery = "SELECT something, somethingAgain " + 
                  "FROM aDBTable " +
                  "WHERE something = '{?}'" +
                  "AND something <> '{?}'";

我需要更换 {？}s 与 Parms 中定义的 parms 一起使用。

C# 中是否有某种类型的循环，我可以使用它来遍历字符串并将找到的每个 {？} 替换为相应的参数值？

像这样：

第一个循环：

SELECT something, somethingAgain 
FROM aDBTable 
WHERE something = 'parm1' AND something <> '{?}'

第二循环：

SELECT something, somethingAgain 
FROM aDBTable 
WHERE something = 'parm1' AND something <> 'parm2'

是否有某种类型的 REGEX 或通用框架函数可以完成上述操作？

SQL 注入检查

bool injectionCheckin = new injectionCheck().inCheck(theFinalQuery);
public class injectionCheck
{
    public bool inCheck(string queryString)
    {
        var badWords = new[] {
            "EXEC", "EXECUTE", ";", "-", "*", "--", "@",
            "UNION", "DROP","DELETE", "UPDATE", "INSERT",
            "MASTER", "TABLE", "XP_CMDSHELL", "CREATE",
            "XP_FIXEDDRIVES", "SYSCOLUMNS", "SYSOBJECTS",
            "BC_HOME_ADDRESS1", "BC_HOME_ADDRESS2", "BC_HOME_CITY", "BC_HOME_COUNTY", "BC_HOME_POSTAL", "BC_MAIL_ADDRESS1",
            "BC_MAIL_ADDRESS2", "BC_MAIL_CITY", "BC_MAIL_COUNTY", "BC_MAIL_POSTAL", "BC_MAIL_STATE", "FLSA_STATUS", "GRADE",
            "GRADE_ENTRY_DT", "HIGHEST_EDUC_LVL", "LAST_INCREASE_DT", "BC_SALP_DESCR", "BC_SALP_DESCRSHORT", "SAL_ADMIN_PLAN"
        };
        string pattern = "(?<!''w)(" + Regex.Escape(badWords[0]);
        foreach (var key in badWords.Skip(1))
        {
            pattern += "|" + Regex.Escape(key);
        }
        pattern += ")(?!''w)";
        dynamic _tmpCount = Regex.Matches(queryString, pattern, RegexOptions.IgnoreCase).Count;
        if (_tmpCount >= 1)
            return true;
        else
            return false;
    }
}

C# 将查询中的参数替换为定义的值

始终通过参数化查询创建 SQL 命令：

using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
using (SqlCommand cmd = conn.CreateCommand())
{
    var @params = new Dictionary<string, object>{
        { "something", myValue },
        { "somethingDifferent", anotherValue },
    };
    cmd.CommandText = "SELECT something, somethingAgain " + 
              "FROM aDBTable " +
              "WHERE something = @something'" +
              "AND something <> @somethingDifferent'";
    foreach (KeyValuePair<string, object> item in values)
    {
        cmd.Parameters.AddWithValue("@" + item.Key, item.Value);
    }
    DataTable table = new DataTable();
    using (var reader = cmd.ExecuteReader())
        {
            table.Load(reader);
            return table;
        }
    }
}

这可以防止所有类型的 SqlInjection，并且您不会像使用坏列表那样进行任何奇怪的检查，这非常混乱并且不会真正阻止您，例如，您可以轻松地绕过列表并进行一些转义。特别是：当 allready 有现成的方法可以完全按照您想要的方式运行时，您为什么要编写自己的验证？

为什么不直接使用 String.Format ？

string[] theParms = new string[] { "parm1", "parm2" };
string theQuery = @"SELECT something, somethingAgain 
                    FROM aDBTable 
                    WHERE something = '{0}'
                    AND something <> '{1}'";
var res = string.Format(theQuery, theParms);

结果：

SELECT something, somethingAgain 
       FROM aDBTable 
       WHERE something = 'parm1'
       AND something <> 'parm2'

如果你想在任何一种情况下这样做，你可以在没有循环的情况下做到这一点，如下所示。

string theQuery = String.Format( "SELECT something, somethingAgain " + 
                                 "FROM aDBTable " +
                                 "WHERE something = '{0}'" +
                                 "AND something <> '{1}'",
                                 theParams[0], theParams[1] );

好吧，为了避免注入和所有这些，你为什么不这样做：

string[] theParms = // initialization
string theQuery = // initialization
SqlCommand cmd = new SqlCommand(/* enter connection string */, theQuery)
for(int i = 0; i < theParams.Length; i++)
{
    int index = cmd.Text.IndexOf("{?}");
    if(index > -1)
    {
        string pName = string.Format("@p{0}", i);
        cmd.Text = cmd.Text.Remove(index, 3).Insert(index, pName);
        cmd.Parameters.Add(new SqlParameter() { Name = pName, Value = theParms[i] });
    }
}

这应该避免任何手动注射检查......至少在无法预编译查询并且必须在运行时加载它的情况下。否则，只需适当地制定 SqlCommand 的文本，您将不需要循环或任何东西。只是一个简单的初始化：

SqlCommand cmd = new SqlCommand(/* enter connection string */, "SELECT something, somethingAgain FROM aDBTable WHERE something = @p0 AND something <> @p1");
cmd.Parameters.Add(new SqlParameter() { Name = "@p0", Value = theParms[0] });
cmd.Parameters.Add(new SqlParameter() { Name = "@p1", Value = theParms[1] });
您可以使用

IndexOf和子字符串来查找每个实例

for(int i = 0; i < theParms.GetLength(0); i++)
{
    string[] tempStrings = new string[]{ theQuery.Substring(0,theQuery.IndexOf("{?}") - 1),
    theQuery.Substring(theQuery.IndexOf("{?}"), 3),
    theQuery.Substring(theQuery.IndexOf("{?}") + 4) }
    tempStrings[1] = tempStrings[1].Replace("{?}", theParms[i]);
    theQuery = String.Join("", tempStrings);
}

虽然看到你之后检查注射，使用String.Format肯定要好得多

您不必自己处理它。相反，ADO.NET允许您定义参数并设置其值。请参阅此处的示例。MSDN

相关文章：