1、首先需要填写一个StringBuilder的扩展类
namespace Code.Common
{
/// <summary>
/// 扩展StringBuilder方法
/// 防止Sql注入
/// </summary>
public static class StringBuilderExtend
{
public static StringBuilder AppendFormatWithSafe(this StringBuilder a, string format, object arg0, StringBuilder where)
{
where.AppendFormat(format,
((string)arg0)
.ToLower()
.Replace("update", "")
.Replace("delete", "")
.Replace("select", "")
.Replace("insert", "")
.Replace("from", "")
.Replace("or", "")
.Replace("'", "")
.Replace("@", "")
.Trim()
);
return where;
}
}
}
2、讲这个扩展方法写成公有静态的,然后 每次new StringBuilder 拼接Sql语句的时候就可以调用。下面调用案例(用的petapoco的Page分页列表)
public static Page<UserInfo> GetList(Page<UserInfo> model, int myUserId = 0, int currentPage = 1)
{
Page<UserInfo> u = new Page<UserInfo>();
using (DataAccess.Database db = new DataAccess.Database())
{
StringBuilder sql = new StringBuilder().Append(" select * from UserInfo where 1=1");
if (model.Item != null)
{
if (!string.IsNullOrEmpty(model.Item.RealName))
{
sql.AppendFormatWithSafe(" and RealName like '%{0}%'", model.Item.RealName, sql);
}
}
if (!string.IsNullOrEmpty(model.orderby))
{
sql.AppendFormat(" order by {0}", model.orderby);
}
u = db.Page<UserInfo>(currentPage, CodeConfig.ItemsPerPage, sql.ToString(), myUserId);
}
return u;
}
总结:
这样就不用担心用户输入查询条件的是带有特殊字符,如( @‘ ),可以做到防止Sql注入。