|zyciis| 做了一个新网站,才上线.大家来打渣,找到一个安全漏洞给200分,前面大多安全出错在SQL拼接上，现在改为传参大家来测试一下

原贴：
|zyciis| 做了一个新网站,才上线.大家来打渣,找到一个安全漏洞给200分,谢谢
http://topic.csdn.net/u/20090711/09/13c4a586-7c7c-49a2-82bf-a37a88265d2a.html?79679
-----------------------------------
经大家的测试多数出错是拼接SQL语句的问题
现在全都改为参数的方式，大家再来测试一下
同样 安全建议一个100分，谢谢测试地址为
http://admin.zingmall.com.cn/Default.aspx

如原：/// <summary> 
        /// 帮助测试表[HelpTest] 分页查询 
        /// </summary> 
        /// <param name="ID">ID</param>
        /// <param name="Text">内容</param>
        /// <param name="AddTime"></param>
        /// <param name="Status"></param>
        /// <param name="SearchOther">其他特殊查询条件</param>
        /// <param name="PageIndex">指定当前为第几页</param>
        /// <param name="PageSize">每页多少条记录</param>
        /// <param name="RecordCount">返回总行数</param>
        public static DataTable Search(Int32 ID, String Text, DateTime AddTimeBegin, DateTime AddTimeEnd, Int32 Status, String SearchOther, Int32 PageIndex, Int32 PageSize, out Int32 RecordCount)
        {
            String TableName = "[HelpTest]";           //表名
            String FieldList = "*";                                //显示列名，如果是全部字段则为*
            String PrimaryKey = "[ID]";                                //显示列名，如果是全部字段则为*
            System.Text.StringBuilder iWhere = new System.Text.StringBuilder();         //查询条件 不含'WHERE'字符，如ID > 10 AND LEN(USER) > 9
            String Order = "";                                //排序 不含'ORDER BY'字符，如ID ASC,USERID DESC, 必须指定ASC或DESC
            Int32 SortType = 1;                                //排序规则 1:主键ASC 2:主键DESC 3:多列排序方法, 记住一定要在最后加上主键排序方式
            iWhere.Append("(1=1)");            if (ID != -1)
            {
                iWhere.Append(" AND [ID] = " + ID);
            }
            if (Text != "")
            {
                iWhere.Append(" AND [Text] LIKE '%" + Text + "%'");
            }
            if (AddTimeBegin > new DateTime(1900, 1, 1))
            {
                iWhere.Append(" And (DateDiff(d,'" + AddTimeBegin + "', [AddTime]) >= 0)");
            }
            if (AddTimeEnd > new DateTime(1900, 1, 1))
            {
                iWhere.Append(" And (DateDiff(d,'" + AddTimeEnd + "', [AddTime]) <= 0)");
            }
            if (Status != -1)
            {
                iWhere.Append(" AND [Status] = " + Status);
            }
            if (SearchOther != "")
            {
                iWhere.Append(" AND (" + SearchOther + ")");
            }
            DeRecord Record = new DeRecord(TableName, FieldList, PrimaryKey, iWhere.ToString(), Order, SortType, PageIndex, PageSize);
            DataSet ds = Record.ExecuteDataSet();
            RecordCount = Record.RecordCount;
            return ds.Tables[0];
        }改为/// <summary> 
        /// 帮助测试表[HelpTest] 分页查询 
        /// </summary> 
        /// <param name="ID">ID</param>
        /// <param name="Text">内容</param>
        /// <param name="AddTime"></param>
        /// <param name="Status"></param>
        /// <param name="SearchOther">其他特殊查询条件</param>
        /// <param name="PageIndex">指定当前为第几页</param>
        /// <param name="PageSize">每页多少条记录</param>
        /// <param name="RecordCount">返回总行数</param>
        public static DataTable Search(Int32 ID, String Text, DateTime AddTimeBegin, DateTime AddTimeEnd, Int32 Status, String SearchOther, Int32 PageIndex, Int32 PageSize, out Int32 RecordCount)
        {
            EDbOperator oo = new EDbOperator();
            try
            {
                String TableName = "[HelpTest]";           //表名
                String FieldList = "*";                                //显示列名，如果是全部字段则为*
                String PrimaryKey = "[ID]";                                //显示列名，如果是全部字段则为*
                System.Text.StringBuilder iWhere = new System.Text.StringBuilder();         //查询条件 不含'WHERE'字符，如ID > 10 AND LEN(USER) > 9
                String Order = "";                                //排序 不含'ORDER BY'字符，如ID ASC,USERID DESC, 必须指定ASC或DESC
                Int32 SortType = 1;                                //排序规则 1:主键ASC 2:主键DESC 3:多列排序方法, 记住一定要在最后加上主键排序方式
                iWhere.Append("(1=1)");                if (ID != -1)
                {
                    iWhere.Append(" AND [ID] = @ID");
                    oo.DbOperator.Parameters.Add(MyParameter.Add("@ID", ParameterDbType.Int, 4, ParameterDirection.Input, ID));
                }
                if (Text != "")
                {
                    iWhere.Append(" AND [Text] LIKE @Text");
                    oo.DbOperator.Parameters.Add(MyParameter.Add("@Text", ParameterDbType.VarChar, 50, ParameterDirection.Input, "%" + Text + "%"));
                }
                if (AddTimeBegin > new DateTime(1900, 1, 1))
                {
                    iWhere.Append(" And (DateDiff(d,@AddTimeBegin, [AddTime]) >= 0)");
                    oo.DbOperator.Parameters.Add(MyParameter.Add("@AddTimeBegin", ParameterDbType.DateTime, 8, ParameterDirection.Input, AddTimeBegin));
                }
                if (AddTimeEnd > new DateTime(1900, 1, 1))
                {
                    iWhere.Append(" And (DateDiff(d,@AddTimeEnd, [AddTime]) <= 0)");
                    oo.DbOperator.Parameters.Add(MyParameter.Add("@AddTimeEnd", ParameterDbType.DateTime, 8, ParameterDirection.Input, AddTimeEnd));
                }
                if (Status != -1)
                {
                    iWhere.Append(" AND [Status] = @Status");
                    oo.DbOperator.Parameters.Add(MyParameter.Add("@Status", ParameterDbType.Int, 4, ParameterDirection.Input, Status));
                }
                if (SearchOther != "")
                {
                    iWhere.Append(" AND (" + SearchOther + ")");
                }
                DeRecordParams Record = new DeRecordParams(TableName, FieldList, PrimaryKey, iWhere.ToString(), Order, SortType, PageIndex, PageSize);
                DataSet ds = Record.ExecuteDataSet(oo);
                RecordCount = Record.RecordCount;
                return ds.Tables[0];
            }
            finally
            {
                oo.Dispose();
            }
        }

解决方案 »

  1.   

    你的表格在firefox下没分页，6行，每行都很长很长
      

  2.   

      DeRecord 是什么？替换字符的类？
      

  3.   

    RE:
    这一贴为SQL安全贴，不算网站的其他如DIV+CSS 规范这些谢谢
      

  4.   


    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Text;
    using System.Data.SqlClient;namespace com.Basic
    {
        /// <summary>
        /// 分页生成
        /// </summary>
        public class DeRecordParams
        {
            public int IfQueryCount = 0;
            public String TableName;        //表名
            public String FieldList;            //显示列名，如果是全部字段则为*
            public String PrimaryKey;          //单一或唯一主键
            public String Where;             //查询条件 不含'WHERE'字符，如ID > 10 AND LEN(USER) > 9
            public String Order;             //排序 不含'ORDER BY'字符，如ID ASC,USERID DESC, 必须指定ASC或DESC
            public Int32 SortType;            //排序规则 1:主键ASC 2:主键DESC 3:多列排序方法, 记住一定要在最后加上主键排序方式
            public int PageIndex;        //当前页数
            public int PageSize;    //每页输出的记录数
            /// <summary>
            /// 执行存储过程分页
            /// </summary>
            /// <param name="_TableName">表名</param>
            /// <param name="_FieldList">显示列名，如果是全部字段则为*</param>
            /// <param name="_PrimaryKey">单一或唯一主键</param>
            /// <param name="_Where">查询条件 不含'WHERE'字符，如ID > 10 AND LEN(USER) > 9</param>
            /// <param name="_Order">排序 不含'ORDER BY'字符，如ID ASC,USERID DESC, 必须指定ASC或DESC</param>
            /// <param name="_SortType">排序规则 3时用 1:主键ASC 2:主键DESC 3:多列排序方法, 记住一定要在最后加上主键排序方式</param>
            /// <param name="_PageIndex">当前页数</param>
            /// <param name="_PageSize">每页输出的记录数</param>
            public DeRecordParams(String _TableName, String _FieldList, String _PrimaryKey, String _Where, String _Order, Int32 _SortType, Int32 _PageIndex, Int32 _PageSize)
            {
                this.TableName = _TableName;
                this.FieldList = _FieldList;
                this.PrimaryKey = _PrimaryKey;
                this.Where = _Where;
                this.Order = _Order;
                this.SortType = _SortType;
                this.PageIndex = _PageIndex;
                this.PageSize = _PageSize;
            }        #region 执行结果值
            private int _PageCount;
            private int _RecordCount;
            //private string _Sql;        public int PageCount
            {
                get
                {
                    return _PageCount;
                }
            }
            public int RecordCount
            {
                get
                {
                    return _RecordCount;
                }
            }
            #endregion        public DataSet ExecuteDataSet(EDbOperator oo)
            {
                return ExecuteDataSet(oo, Jerry.AppSettingManager.DBConnString);
            }        
      

  5.   


    public DataSet ExecuteDataSet(EDbOperator oo, String cnnString)
            {
                String cmdText = "";
                String cmdTextCount = "";
                Order = Order.Trim();
                PrimaryKey = PrimaryKey.Trim();
                FieldList = FieldList.Trim();
                Order = Order.Replace(", ", ",");
                Order = Order.Replace(" ,", ",");
                if (String.IsNullOrEmpty(TableName) || String.IsNullOrEmpty(FieldList) || String.IsNullOrEmpty(PrimaryKey) || SortType < 1 || PageSize < 0 || PageIndex < 0)
                {
                    throw new Exception("分页查询数据不全");
                }
                if (SortType == 3)
                {
                    if (Order.Substring(Order.Length - 4, 4).ToUpper() != " ASC" && Order.Substring(Order.Length - 5, 5).ToUpper() != " DESC")
                    {
                        throw new Exception("查询未指定排序方式");
                    }
                }
                String new_where1 = "";
                String new_where2 = "";
                String new_order1 = "";
                String new_order2 = "";
                String new_order3 = "";
                if (Where == "")
                {
                    new_where1 = " ";
                    new_where2 = " WHERE ";
                }
                else
                {
                    new_where1 = " WHERE " + Where;
                    new_where2 = " WHERE " + Where + " AND ";
                }
                if (Order == "" || SortType == 1 || SortType == 2)
                {
                    if (SortType == 1)
                    {
                        new_order1 = " ORDER BY " + PrimaryKey + " ASC";
                        new_order2 = " ORDER BY " + PrimaryKey + " DESC";
                    }
                    if (SortType == 2)
                    {
                        new_order1 = " ORDER BY " + PrimaryKey + " DESC";
                        new_order2 = " ORDER BY " + PrimaryKey + " ASC";
                    }
                }
                else
                {
                    new_order1 = " ORDER BY " + Order;
                }
                if (SortType == 3 && Order.IndexOf(PrimaryKey) > 0)
                {
                    new_order1 = " ORDER BY " + Order;
                    new_order2 = Order + ",";
                    new_order2 = new_order2.Replace("ASC,", "{ASC},").Replace("DESC", "{DESC},");
                    new_order2 = new_order2.Replace("{ASC},", "DESC,").Replace("{DESC},", "ASC,");
                    new_order2 = " ORDER BY " + new_order2.Substring(0, new_order2.Length - 1);
                    if (FieldList != "*")
                    {
                        new_order3 = (Order + ",").Replace("ASC,", ",").Replace("DESC,", ",");
                        FieldList = "," + FieldList;
                        while (new_order3.IndexOf(",") > -1)
                        {
                            int ss = ("," + FieldList).IndexOf(("," + new_order3).Substring(1, new_order3.IndexOf(",")));
                            if (("," + FieldList).IndexOf(("," + new_order3).Substring(1, new_order3.IndexOf(","))) > -1)
                            {
                                FieldList = FieldList + "," + new_order3.Substring(1, new_order3.IndexOf(","));
                            }
                            new_order3 = new_order3.Substring(new_order3.IndexOf(",") + 1, new_order3.Length - (new_order3.IndexOf(",") + 1));
                        }
                        FieldList = FieldList.Substring(1, FieldList.Length - 1);
                    }
                }
                cmdTextCount = "SELECT COUNT(*) AS RecordCount,CEILING((COUNT(*)+0.0)/" + PageSize.ToString() + ") AS PageCount FROM " + TableName + new_where1;
                DataSet dsRecord = new DataSet();
                //这里取出页数和记录数            SqlDataReader sdr = (SqlDataReader)oo.DbOperator.ExecDataReader(cmdTextCount);
                sdr.Read();
                _RecordCount = Convert.ToInt32(sdr[0]);
                _PageCount = Convert.ToInt32(sdr[1]);
                sdr.Close();
                sdr.Dispose();            if (PageIndex > Convert.ToInt32(Math.Ceiling(Convert.ToDouble(_RecordCount) / Convert.ToDouble(PageSize))))
                {
                    PageIndex = Convert.ToInt32((Math.Ceiling(Convert.ToDouble(_RecordCount) / Convert.ToDouble(PageSize))));
                }
                if (PageIndex == 1 || PageIndex >= Convert.ToInt32(Math.Ceiling(Convert.ToDouble(_RecordCount) / Convert.ToDouble(PageSize))))
                {
                    if (PageIndex == 1)
                    {
                        cmdText = "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM " + TableName + new_where1 + new_order1;
                    }
                    else if (PageIndex >= Convert.ToInt32((Math.Ceiling(Convert.ToDouble(_RecordCount) / Convert.ToDouble(PageSize)))))
                    {
                        cmdText = "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM (" + "SELECT TOP " + Math.Abs(PageSize * PageIndex - _RecordCount - PageSize).ToString() + " " + FieldList + " FROM " + TableName + new_where1 + new_order2 + " ) AS TMP " + new_order1;
                    }
                }
                else
                {
                    if (SortType == 1)
                    {
                        if (PageIndex <= Convert.ToInt32((Math.Ceiling(Convert.ToDouble(_RecordCount) / Convert.ToDouble(PageSize)))) / 2)
                        {
                            cmdText = "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM " + TableName + new_where2 + PrimaryKey + " > " + "(SELECT MAX(" + PrimaryKey + ") FROM (SELECT TOP " + (PageSize * (PageIndex - 1)).ToString() + " " + PrimaryKey + " FROM " + TableName + new_where1 + new_order1 + " ) AS TMP) " + new_order1;
                        }
                        else
                        {
                            cmdText = "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM (" + "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM " + TableName + new_where2 + PrimaryKey + " < " + "(SELECT MIN(" + PrimaryKey + ") FROM (SELECT TOP " + (_RecordCount - PageSize * PageIndex).ToString() + " " + PrimaryKey + " FROM " + TableName + new_where1 + new_order2 + " ) AS TMP) " + new_order2 + " ) AS TMP " + new_order1;
                        }
                    }
                    else if (SortType == 2)
                    {
                        if (PageIndex <= Convert.ToInt32((Math.Ceiling(Convert.ToDouble(_RecordCount) / Convert.ToDouble(PageSize)))) / 2)
                        {
                            cmdText = "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM " + TableName + new_where2 + PrimaryKey + " < " + "(SELECT MIN(" + PrimaryKey + ") FROM (SELECT TOP " + (PageSize * (PageIndex - 1)).ToString() + " " + PrimaryKey + " FROM " + TableName + new_where1 + new_order1 + ") AS TMP) " + new_order1;
                        }
                        else
                        {
                            cmdText = "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM (" + "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM " + TableName + new_where2 + PrimaryKey + " > " + "(SELECT MAX(" + PrimaryKey + ") FROM (SELECT TOP " + (_RecordCount - PageSize * PageIndex).ToString() + " " + PrimaryKey + " FROM " + TableName + new_where1 + new_order2 + " ) AS TMP) " + new_order2 + " ) AS TMP " + new_order1;
                        }
                    }
                    else if (SortType == 3)
                    {
                        if (("," + Order).IndexOf("," + PrimaryKey) == -1)
                        {
                            throw new Exception("多列排序，必须包含主键");
                        }
                        if (PageIndex <= Convert.ToInt32((Math.Ceiling(Convert.ToDouble(_RecordCount) / Convert.ToDouble(PageSize)))) / 2)
                        {
                            cmdText = "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM ( " + "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM ( " + " SELECT TOP " + (PageSize * PageIndex).ToString() + FieldList + " FROM " + TableName + new_where1 + " " + new_order1 + " ) AS TMP " + new_order2 + " ) AS TMP " + new_order1;
                        }
                        else
                        {
                            cmdText = "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM ( " + "SELECT TOP " + PageSize.ToString() + " " + FieldList + " FROM ( " + " SELECT TOP " + (_RecordCount - PageSize * PageIndex + PageSize).ToString() + " " + FieldList + " FROM " + TableName + new_where1 + " " + new_order2 + " ) AS TMP " + new_order1 + " ) AS TMP " + new_order1;
                        }
                    }
                }
                oo.DbOperator.ExecDataSet(cmdText, dsRecord);            return dsRecord;
            }
        }
    }
      

  6.   

    为动态生成SQL语句的类
    我这里不对字符进行替换
    而是每一个查询条件都用传参数的方法谢谢
      

  7.   

     oo.DbOperator.Parameters.Add(MyParameter.Add("@Text", ParameterDbType.VarChar, 50, ParameterDirection.Input, "%" + Text + "%"));
    这一句要改为
    "%" + Text.Repalce("%","%%") + "%"));
    这样吗？
      

  8.   

    s = s.Replace("[", "[[]");
    s = s.Replace("%", "[%]");
    s = s.Replace("_", "[_]");
      

  9.   

    就上面三个吗？
    传参的还要替换这些，哈哈
    SQL我不太熟还有哪些注意的