sqlserver where Like传参正确写法

sqlserver where Like传参正确写法

EventClass:RPC:Completed

TextData:exec sp_executesql N’

CardIDWhere = " and CardID like '%'+ @CardID + '%'";    

else if (BeginDate != null && EndDate == null)
            {
                CreateDateWhere = " and CreateDate between '" + @BeginDate + "' and '5000-01-01'";
            }

这个也是正确的，不过是括号的参数直接匹配的，会有注入漏洞。

----------------------------------------------------------------------------------------------------------------------------

exec sp_executesql N'
            select * from (
             select Row_number() over(order by CreateDate) as rownum,* 
             from ValueCardSaveTradeLog
             )as t1 where t1.rownum>=(@PageIndex-1)*@PageSize+1 and t1.rownum<=(@PageIndex*@PageSize)
                       and CardID like ''%''+ @CardID + ''%''',
N'@CardID nvarchar(4000),@PageIndex int,@PageSize int',@CardID=N'912',@PageIndex=1,@PageSize=10

正确的。

----------------------------------------------------------------------------------------------------------------------------

以下语句，参数里面有单引号，仍然没有解决问题。虽然没有报错，但是没有查询出来数据。

C#参数OpenIdWhereIn字符串："'ollRst0knVF9Jc7nRp25AEu5edP8','opWpbt2xr1UF1lWm_hOltliUGh20','osxmGwf_0hZfG6JmPFLjzpb-5kB0'"

OpendIdWhereInWhere = "and OpenID in (@OpenIdWhereIn)";

exec sp_executesql N'
            select * from (
             select Row_number() over(order by CreateDate) as rownum,* 
             from ValueCardSaveTradeLog
             )as t1 where t1.rownum>=(@PageIndex-1)*@PageSize+1 and t1.rownum<=(@PageIndex*@PageSize)
                       and OpenID in (@OpenIdWhereIn)',N'@OpenIdWhereIn nvarchar(4000),@PageIndex int,@PageSize 
int',@OpenIdWhereIn=N'''opWpbt2xr1UF1lWm_hOltliUGh20'',''ollRst0knVF9Jc7nRp25AEu5edP8'',''osxmGwf_0hZfG6JmPFLjzpb-5kB0''',
@PageIndex=1,@PageSize=10

搜索

复制