对传入where条件的sql语句进行参数化处理

一般对sql参数化处理都是在sql语句执行的地方对所要输入的参数进行参数化已防止注入。但有时候会碰到一些特殊情况。一些老的项目可能会在逻辑处理的地方执行sql语句

而sql语句的where条件来自web层调用此方法的地方。
针对这种情况需要对where条件进行处理。我采用的是对where条件进行拆分处理组装List和where语句。
首先通过递归对条件进行拆分。

 public void GetArrow(int start, string str, ref List<SubIndex> list, string chars)
        {
            if (start == str.LastIndexOf(chars))
            {
                list.Add(new SubIndex { Index = start, Tag = chars.Trim() });
                return;
            }

            int i = str.LastIndexOf(chars);
            list.Add(new SubIndex { Index = i, Tag = chars.Trim() });
            str = str.Substring(0, i);

            GetArrow(start, str, ref list, chars);
        }

得到一个List
SubIndex为一个辅助处理的类

    public class SubIndex
    {
        public int Index { get; set; }
        public string Tag { get; set; }
    }

主处理函数为

        public Dictionary<string, List<SqlParameter>> ConvertSqlWhere(string str)
        {
            str = str.Replace("'", "");
            int start = 0;
            List<SubIndex> list = new List<SubIndex>();
            if (str.Contains("and"))
            {
                start = str.IndexOf("and");
                GetArrow(start, str, ref list, "and");
            }
            if (str.Contains("or"))
            {
                start = str.IndexOf("or");
                GetArrow(start, str, ref list, "or");
            }

            list = list.OrderBy(ex => ex.Index).ToList();
            List<string> ls = new List<string>();
            for (int i = 0; i < list.Count; i++)
            {
                if (i == 0)
                {
                    ls.Add((str.Substring(0, list[i].Index)).Trim());
                }
                else
                {
                    ls.Add((str.Substring(list[i - 1].Index, list[i].Index - list[i - 1].Index)).Trim());
                    if (i == list.Count - 1)
                    {
                        ls.Add((str.Substring(list[list.Count - 1].Index)).Trim());
                    }

                }

            }
            return GetSQLAndParameters(ls);
        }

GetSQLAndParameters方法是对list进行处理并放回字典的方法

        public Dictionary<string,List<SqlParameter>> GetSQLAndParameters(List<String> ls )
        {
            string sql = "";
            Dictionary<string,List<SqlParameter>> dic=new Dictionary<string,List<SqlParameter>>();
            List<SqlParameter> cmdParas=new List<SqlParameter>();
            for (int i = 0; i < ls.Count; i++)
            {
                if (ls[i].Contains("and"))
                {
                    #region and
                    if (ls[i].Contains("in"))
                    {
                        string sqlin="";
                        string[] temp = ls[i].Substring(ls[i].IndexOf('(') + 1, ls[i].IndexOf(')') - ls[i].IndexOf('(')-1).Split(',');
                        sqlin += (ls[i].Substring(0, ls[i].IndexOf('(')+1));
                        for (int j = 0; j < temp.Length; j++)
                        {
                            cmdParas.Add(new SqlParameter("@"+ls[i].Substring(3,ls[i].IndexOf("in")-3).Trim() +i.ToString() +j.ToString(), temp[j]));
                            sqlin += "@" + ls[i].Substring(3, ls[i].IndexOf("in") - 3).Trim() + i.ToString() + j.ToString() + ",";
                        }
                        sql += (sqlin.Substring(0, sqlin.Length - 1) + ")");
                    }
                    else
                    {

                        if (ls[i].Contains("<>"))
                        {
                            sql += GetStr(ls[i], "<>", i,"and");
                            cmdParas.Add(GetPara(ls[i],"<>",i,"and"));
                        }
                        else if(ls[i].Contains('<'))
                        {
                            sql += GetStr(ls[i], "<", i,"and");
                            cmdParas.Add(GetPara(ls[i], "<", i,"and"));
                        }
                        else if (ls[i].Contains('='))
                        {
                            sql += GetStr(ls[i], "=", i,"and");
                            cmdParas.Add(GetPara(ls[i], "=", i,"and"));
                        }
                        else if (ls[i].Contains('<'))
                        {
                            sql += GetStr(ls[i], "<", i, "and");
                            cmdParas.Add(GetPara(ls[i], ">", i, "and"));
                        }
                        else
                        {
                            sql += ls[i];
                        }
                    }
                    #endregion
                }
                else if (ls[i].Contains("or"))
                {
                    #region or
                    if (ls[i].Contains("in"))
                    {
                        string sqlin = "";
                        string[] temp = ls[i].Substring(ls[i].IndexOf('(') + 1, ls[i].IndexOf(')') - ls[i].IndexOf('(')-1).Split(',');
                        sqlin += (ls[i].Substring(0, ls[i].IndexOf('(')));
                        for (int j = 0; j < temp.Length; j++)
                        {
                            cmdParas.Add(new SqlParameter("@" + ls[i].Substring(2, ls[i].IndexOf("in") - 2).Trim() + i.ToString() + j.ToString() , temp[j]));
                            sqlin += "@" + ls[i].Substring(2, ls[i].IndexOf("in") - 2).Trim() + i.ToString() + j.ToString() + ",";
                        }
                        sql += (sqlin.Substring(0, sqlin.Length - 1) + ")");
                    }
                    else
                    {
                        if (ls[i].Contains("<>"))
                        {
                            sql += GetStr(ls[i], "<>", i, "or");
                            cmdParas.Add(GetPara(ls[i], "<>", i, "or"));
                        }
                        else if (ls[i].Contains('<'))
                        {
                            sql += GetStr(ls[i], "<", i, "or");
                            cmdParas.Add(GetPara(ls[i], "<", i, "or"));
                        }
                        else if (ls[i].Contains('='))
                        {
                            sql += GetStr(ls[i], "=", i, "or");
                            cmdParas.Add(GetPara(ls[i], "=", i, "or"));
                        }
                        else if (ls[i].Contains('<'))
                        {
                            sql += GetStr(ls[i], "<", i, "or");
                            cmdParas.Add(GetPara(ls[i], ">", i, "or"));
                        }
                        else
                        {
                            sql += ls[i];
                        }
                    }
                    #endregion

                }
                else
                {
                    #region 第一个条件
                    if (ls[i].Contains("in"))
                    {
                        string sqlin = "";
                        string[] temp = ls[i].Substring(ls[i].IndexOf('(') + 1, ls[i].IndexOf(')') - ls[i].IndexOf('(')-1).Split(',');
                        sqlin += (ls[i].Substring(0, ls[i].IndexOf('(')));
                        for (int j = 0; j < temp.Length; j++)
                        {
                            cmdParas.Add(new SqlParameter("@" + ls[i].Substring(0, ls[i].IndexOf("in")) + i.ToString() + j.ToString() , temp[j]));
                            sqlin += "@" + ls[i].Substring(0, ls[i].IndexOf("in") ).Trim() + i.ToString() + j.ToString() + ",";
                        }
                        sql += (sqlin.Substring(0, sqlin.Length - 1) + ")");
                    }
                    else
                    {
                        if (ls[i].Contains("<>"))
                        {
                            sql += GetStr(ls[i], "<>", i, "");
                            cmdParas.Add(GetPara(ls[i], "<>", i, ""));
                        }
                        else if (ls[i].Contains('<'))
                        {
                            sql += GetStr(ls[i], "<", i, "");
                            cmdParas.Add(GetPara(ls[i], "<", i, ""));
                        }
                        else if (ls[i].Contains('='))
                        {
                            sql += GetStr(ls[i], "=", i, "");
                            cmdParas.Add(GetPara(ls[i], "=", i, ""));
                        }
                        else if (ls[i].Contains('<'))
                        {
                            sql += GetStr(ls[i], "<", i, "");
                            cmdParas.Add(GetPara(ls[i], ">", i, ""));
                        }
                        else
                        {
                            sql += ls[i];
                        }
                    }
                    #endregion
                }
            }
            dic.Add(sql, cmdParas);
            return dic;
        }

另外重构的对字符进行处理得到sql和SQLParameter的方法

        public string GetStr(string str, string type, int tag,string andor)
        {
            if (type == "<>")
            {
                if (andor=="and")
                {
                    return " " + str.Substring(0, str.IndexOf('<'))+type + ("@" + (str.Substring(3, str.IndexOf('<') - 3)).Trim() + tag.ToString()) + " ";
                }
                else if (andor == "or")
                {
                    return " " + str.Substring(0, str.IndexOf('>')) +type+ ("@" + (str.Substring(2, str.IndexOf('<') - 2)).Trim() + tag.ToString()) + " ";
                }
                else
                {
                    return " " + str.Substring(0, str.IndexOf('>')) +type+ ("@" + (str.Substring(0, str.IndexOf('<'))).Trim() + tag.ToString()) + " ";
                }
            }
            else
            {
                if (andor=="and")
                {
                    return " " + str.Substring(0, str.IndexOf(type)) +type+ ("@" + (str.Substring(3, str.IndexOf(type) - 3)).Trim() + tag.ToString()) + " ";
                }
                else if (andor == "or")
                {
                    return " " + str.Substring(0, str.IndexOf(type)) +type+ ("@" + (str.Substring(2, str.IndexOf(type) - 2)).Trim() + tag.ToString()) + " ";
                }
                else
                {
                    return " " + str.Substring(0, str.IndexOf(type)) +type+ ("@" + (str.Substring(0, str.IndexOf(type))).Trim() + tag.ToString()) + " ";
                }
            }
        }
        public SqlParameter GetPara(string str, string type, int tag, string andor)
        {
            if (type == "<>")
            {
                if (andor == "and")
                {
                    return new SqlParameter("@" + (str.Substring(3, str.IndexOf('<') - 3)).Trim() + tag.ToString(), (str.Substring(str.IndexOf('>') + 1)).Trim());
                }
                else if (andor == "or")
                {
                    return new SqlParameter("@" + (str.Substring(2, str.IndexOf('<') - 2)).Trim() + tag.ToString(), (str.Substring(str.IndexOf('>') + 1)).Trim());
                }
                else
                {
                    return new SqlParameter("@" + (str.Substring(0, str.IndexOf('<'))).Trim() + tag.ToString(), (str.Substring(str.IndexOf('>') + 1)).Trim());
                }
            }
            else
            {
                if (andor == "and")
                {
                    return new SqlParameter("@" + (str.Substring(3, str.IndexOf(type) - 3)).Trim() + tag.ToString(), (str.Substring(str.IndexOf(type) + 1)).Trim());
                }
                else if (andor == "or")
                {
                    return new SqlParameter("@" + (str.Substring(2, str.IndexOf(type) - 2)).Trim() + tag.ToString(), (str.Substring(str.IndexOf(type) + 1)).Trim());
                }
                else
                {
                    return new SqlParameter("@" + (str.Substring(0, str.IndexOf(type))).Trim() + tag.ToString(), (str.Substring(str.IndexOf(type) + 1)).Trim());
                }
            }
        }