最近在做一个动态拼接where条件的查询,大概想到了以下几种方法:
1、代码内拼接查询条件,sql也是写在代码内的。
2、代码内拼接查询条件,sql写在存储过程内,将 where条件作为一个字符串传入存储过程。(这种需要在代码内过滤sql注入的问题)
3、查询条件的拼接放到存储过程内,在存储过程内对查询条件值进行参数化。
第一种:
不再书写
第二种:
第1步,先拼接where查询条件
StringBuilder condition = new StringBuilder();
if (param.Province > 0)
{
condition.AppendFormat(" AND Province={0}", param.Province);
}
if (param.City > 0)
{
condition.AppendFormat(" AND City={0}", param.City);
}
if (param.County > 0)
{
condition.AppendFormat(" AND County={0}", param.County);
}
第2步:
注意,Condition参数,将拼接的查询条件作为字符串传入,
DynamicParameters parameters = new DynamicParameters();
parameters.Add("Condition", condition.ToString());
第3步:
条件作为一个字符串传入存储过程,这种应该还是有sql注入的问题,因为第一步拼接的时候并没有对参数值进行参数化
SET @sql = 'SELECT @totalCount = COUNT(1)
FROM dbo.Base_Class cls
WHERE IsValid=1 ' + @condition;
第三种:
第1步:将所有用到的查询参数传给存储过程
var parameters = new DynamicParameters();
parameters.Add("Province", param.Province);
parameters.Add("City", param.City);
parameters.Add("County", param.County);
第2步:存储过程内拼接,并参数化(代码只留了关键部分,并不能保证编译通过)
PROCEDURE [dbo].[p_GetList]@P_Province INT,
@P_City INT,
@P_County INT,AS
BEGINDECLARE @Sql NVARCHAR(MAX) ,@SqlCondition NVARCHAR(MAX) ,
@Parm NVARCHAR(MAX) ,
SET @Sql='';
SET @SqlCondition='';
SET @Parm='';
IF(@P_Province>0)
BEGIN
SET @SqlCondition += ' AND Province=@P_Province'
END
SET @Parm += '@P_Province int,';
IF(@P_City>0)
BEGIN
SET @SqlCondition += ' AND City=@P_City'
END
SET @Parm += '@P_City int,';
IF(@P_County>0)
BEGIN
SET @SqlCondition += ' AND County=@P_County'
END
SET @Parm += '@P_County int,';
--这个参数展示了,怎么拼接like这样的查询条件
IF(@P_SchoolName IS NOT NULL)
BEGIN
SET @SqlCondition += ' AND Name like ''%''+@P_SchoolName+''%'''
END
SET @Parm += '@P_SchoolName NVARCHAR(100),';
IF (@Parm<>'')
BEGIN
SET @Parm= SUBSTRING(@Parm,1,len(@Parm)-1)
END
SET @Sql = 'SELECT COUNT(1)
FROM TableName
WHERE IsValid=1 ' + @SqlCondition;
SET @Sql = '
SELECT
*
FROM TableName
WHERE IsValid=1 ' + @SqlCondition;
EXEC sp_executesql @Sql,@Parm,@P_Province = @P_Province,
@P_City = @P_City,
@P_County = @P_County
END
查询条件只有输入值时才拼接,而@pram 将所有的查询条件中的参数拼接。如果查询条件中没有用到某个参数,但在sp_executesql 的输入参数中过多的指定参数,也是可以的。这样就解决了 查询条件参数个数不固定的情况下,无法解决给 sp_executesql 输入参数赋值的问题,因为不知道要给多少个参数赋值。
一开始也是这么做的,但可能因为其他错误导致报“过多的指定参数”的错误,以为这种方式不可以,最终还是通过了。
第一种的弊端也显而易见,改一次结构就要修改代码,第二种可读性一般但需要解决注入的问题,第三种可读性较差但好像又找不出毛病。不知道有没有其他更好的方式解决这种需要拼接sql才能处理不固定查询条件的方法。
写完了,要去刷牙吃早饭咯。