组合查询的实现

create   proc   test(@username   char(10),@usersex   char(2),@useraddress   varchar(100))  
  as  
  declare   @sqlstr   varchar(1000)  
  set   @sqlstr='select   top   100   *   from   datatable   where   1=1'  
  if   @username<>''    
    set   @sqlstr=@sqlstr   +   '   and   username   like   ''%''   +   @username+   ''%'''  
  if   @usersex<>''    
  set   @sqlstr=@sqlstr   +   '   and   usersex=   '''   +   @usersex   +   ''''  
  if   @useraddress<>''    
  set   @sqlstr=@sqlstr   +   '   and   useraddress   like   ''%''   +   @useraddress''%'''  
   
  exec(@sqlstr)  

 

由于是组合条件生成的SQL 语句，所以防注入式攻击也就十分重要了，暂时没有太好的办法，用了一个下面的函数：

   #region
    /// <summary>
    /// 函数功能:检查一下当前页面中所有的TextBox,看看是不是有SQL 注入式的攻击！
    /// 作者：黄海
    /// 日期：2007-3-05
    /// </summary>
    /// <param name="page"> 指定的页面</param>
    /// 用法：ErrorSql(this)
    public  bool ErrorSql(System.Web.UI.Control page)
    {
        string[] ErrorSql ={ "delete ", "insert ", "select ", "update ", "exec ", "declare " ,@"""",@"/", "$", "!", "<", ">", "?", "#", "^", "%", "@", "~", "`", "&", "*", "(", ")", ";", ":", "+", "="};

        int nPageControls = page.Controls.Count;
        for (int i = 0; i < nPageControls; i++)
        {
            foreach (System.Web.UI.Control control in page.Controls[i].Controls)
            {
                if (control is TextBox)
                {
                    string temp=(control as TextBox).Text;

                    for(int j=0;j<ErrorSql.Length;j++)
                    {
                        if(temp.IndexOf(ErrorSql[j].ToString())>=0)
                        {
                            return true;
                        }//end if
                    }//end for
                }//end if
            }//end foreach
        }//end for
        return false;
    }
    #endregion