使用StringBuilder与SqlParameter-优快云博客

本文介绍了使用SQL参数化查询来防止SQL注入攻击的方法，并通过一个具体的.NET示例展示了如何构建参数化查询并安全地传递变量。此外还讨论了参数化查询在减少内存占用方面的优势。

好处：

防止sql注入；占用内存更少

例子：

传参有业务查询条件startDate,endDate,A,每页数据个数pageSize,当前查询页码pageIndex

string sql = @"select A,B,C
               FROM sc with(nolock) 
               WHERE IsDelete!=1 {0}
               ORDER BY A DESC
           OFFSET @pageSize*(@pageIndex-1) ROWS FETCH NEXT @pageSize ROWS ONLY";
StringBuilder sb = new StringBuilder();
List<SqlParameter> parameters = new List<SqlParameter>();
DateTime now = DateTime.Now;
parameters.Add(new SqlParameter("@startDate", startDate==null?new DateTime(now.Year,now.Month,now.Day):startDate));
parameters.Add(new SqlParameter("@endDate", endDate == null ? new DateTime(now.Year, now.Month, now.Day).AddDays(1) : DateTime.Parse(endDate.ToString()).AddDays(1)));
parameters.Add(new SqlParameter("@A", A));
parameters.Add(new SqlParameter("@pageSize", pageSize));
parameters.Add(new SqlParameter("@pageIndex", pageIndex));
if (startDate != null)
    sb.Append(" and sc.Date>=@startDate ");
if (endDate != null)
    sb.Append(" and sc.Date<@endDate ");
if (!string.IsNullOrEmpty(A))
    sb.Append(" and sc.A=@A ");
sql = string.Format(sql, sb);
//查询数据库

转载于:https://www.cnblogs.com/Lulus/p/7874190.html