C# 动态组合SQL脚本LIKE语句及查询参数防SQL注入攻击

  C# 动态组合SQL脚本LIKE语句及查询参数防SQL注入攻击

动态组合SQL脚本的多个LIKE条件，并要求传入SQL参数防止SQL注入攻击。

动态组合的SQL脚本：

参考代码1：

C# Code:

public DataTable Search(String DocType, String DeptID, String FileCaption, String DocKind)
{
  //构建CommandHelper实例，用于动态创建参数对象
   CommandHelper cmd = _Database.CreateCommand("");
  
  StringBuilder sb = new StringBuilder();
  sb.Append("SELECT * FROM dt_doc WHERE 1=1 ");
  
  if (!String.IsNullOrEmpty(DocType))
  {
    sb.Append($" AND DocType LIKE {_Database.ParamSymboName}DocType");//拼接SQL参数
     cmd.AddParam("DocType", "%" + DocType + "%");// 添加SQL参数对象，组合LIKE条件的值
     }
    
    if (!String.IsNullOrEmpty(DeptID))
    {
      sb.Append($" AND DeptID LIKE {_Database.ParamSymboName}DeptID");
      cmd.AddParam("DeptID", "%" + DeptID + "%");
    }
    
    if (!String.IsNullOrEmpty(FileCaption))
    {
      sb.Append($" AND FileCaption LIKE {_Database.ParamSymboName}FileCaption");
      cmd.AddParam("FileCaption", "%" + FileCaption + "%");
    }
    
    //数字类型
     if (!String.IsNullOrEmpty(DocKind))
    {
      sb.Append($" AND DocKind = {_Database.ParamSymboName}DocKind");
      cmd.AddParam("DocKind", DocKind);
    }
    
    sb.Append(" ORDER BY " + dt_Doc.__KeyName);
    
    //给DbCommand绑定SQL脚本
     DbCommand command = cmd.Command;
    command.CommandText = sb.ToString();
    
    //调用IDatabase底层组件的方法
     return _Database.GetTable(command, dt_Doc.__TableName);
  }
  
  //来源:C/S框架网 | www.csframework.com | QQ:23404761

参考代码2：

C# Code:

public DataTable Search(string content)
{
  string sql = "SELECT * FROM tb_MyUser ";
  
  CommandHelper cmd = _Database.CreateCommand("");
  if (!string.IsNullOrEmpty(content))
  {
    sql = sql + $" WHERE Account LIKE {_Database.ParamSymboName}Account OR UserName LIKE {_Database.ParamSymboName}UserName ";
    cmd.AddParam("Account", "%" + content + "%");
    cmd.AddParam("UserName", "%" + content + "%");
  }
  
  cmd.Command.CommandText = sql;
  
  DataTable dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
  return dt;
}

//来源:C/S框架网 | www.csframework.com | QQ:23404761

---

扫一扫加作者微信