where条件拼接查询

时间:2022-02-10 19:23:21

最近在做一个动态拼接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步:

  接下来看下存储过程。只看下关键的地方,将拼接好的where条件作为一个字符串传入存储过程,这种应该还是有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

 

  有个地方需要注意,使用 sp_executesql  时, 第二个参数@pram的拼接并不是和 查询条件同时进行的,查询条件只有输入值时才拼接,而@pram 将所有的查询条件中的参数拼接。如果查询条件中没有用到某个参数,但在sp_executesql 的输入参数中过多的指定参数,也是可以的。这样就解决了  查询条件参数个数不固定的情况下,无法解决给 sp_executesql 输入参数赋值的问题,因为不知道要给多少个参数赋值。

      一开始也是这么做的,但可能因为其他错误导致报“过多的指定参数”的错误,以为这种方式不可以,最终还是通过了。

 

  第一种的弊端也显而易见,改一次结构就要修改代码,第二种可读性一般但需要解决注入的问题,第三种可读性较差但好像又找不出毛病。不知道有没有其他更好的方式解决这种需要拼接sql才能处理不固定查询条件的方法。

 

 

  写完了,要去刷牙吃早饭咯。