你的位置:首页 > 数据库

[数据库]关于存储过程可空条件查询


大家都知道使用存储过程的好处其中有2点

1、参数查询安全(用参数就安全?)

2、存储过程在创建时预编译,执行效率比SQL语句要高

 

基于这两点,看看下面这段以前经常写的存储过程吧(查询数据)

CREATE PROCEDURE [dbo].[usp_TestExec]  @Account VARCHAR(50)='',  @UserName VARCHAR(50)=''ASBEGIN  DECLARE @SQL VARCHAR(8000)  SET @SQL='SELECT * FROM dbo.tb_Test WHERE 1=1';  IF(ISNULL(@Account,'')<>'') SET @SQL=@SQL+' AND Account LIKE ''%'+@Account+'%'''  IF(ISNULL(@UserName,'')<>'') SET @SQL=@SQL+' AND UserName LIKE ''%'+@UserName+'%'''    EXEC(@SQL)   END  

运行,测试没问题。

可是有谁会考虑到注入?

看着存储过程我觉得有两个问题

1.实际还是运行SQL,如果条件不一样,生成的SQL语句不一样,预编译运行成为美丽的谎言

2.不能够预防SQL代码注入,不相信请(测)看(试)

  运行如下代码会出现何种结果?

  

 EXEC usp_TestExec "fdsgdsgsdd' OR '' LIKE '"

 

出问题咯,作为一个有进取心的程序员,此时是不是听到一句话:该重写了

这里是我的方法:

 

ALTER PROCEDURE [dbo].[usp_TestExec]  @Account VARCHAR(50)='',  @UserName VARCHAR(50)=''ASBEGIN   --usp_TestExec "DB' OR '' LIKE '"  --usp_TestExec 'DB'  DECLARE @SQL VARCHAR(8000)  SELECT * FROM dbo.tb_Test   WHERE ((@Account IS NULL AND Account IS NULL) OR @Account='' OR (Account LIKE '%'+@Account+'%'))     AND ((@UserName IS NULL AND UserName IS NULL) OR @UserName='' OR (UserName LIKE '%'+@UserName+'%'))   END  

运行测试一下:

  

exec usp_TestExec "DB' OR '' LIKE '"

exec usp_TestExec 'DB'

 

 

注入问题解决,而且由于没有拼接SQL语句,可以说这样的存储过程才预编译后执行查询的。

 

应该还有其他方法。

附上一段SqlCommandBuilder生成的Update语句,给看得懂的人看吧:

UPDATE [dbo].[tb_TT] SET [FRbigint] = @FRbigint          , [Fbinary] = @Fbinary          , [Fbit] = @Fbit          , [Fchar] = @Fchar          , [Fdate] = @Fdate          , [Fdatetime] = @Fdatetime          , [Fdatetime2] = @Fdatetime2          , [Fdatetimeoffset] = @Fdatetimeoffset          , [Fdecimal] = @Fdecimal          , [Ffloat] = @Ffloat          , [Fgeography] = @Fgeography          , [Fgeometry] = @Fgeometry          , [Fhierarchyid] = @Fhierarchyid          , [Fimage] = @Fimage          , [Fint] = @Fint          , [Fmoney] = @Fmoney          , [Fnchar] = @Fnchar          , [Fntext] = @Fntext          , [Fnumeric] = @Fnumeric          , [Fnvarchar] = @Fnvarchar          , [FnvarcharMax] = @FnvarcharMax          , [Freal] = @Freal          , [Fsmalldatetime] = @Fsmalldatetime          , [Fsmallint] = @Fsmallint          , [Fsmallmoney] = @Fsmallmoney          , [Fsql_variant] = @Fsql_variant          , [Ftext] = @Ftext          , [Ftime] = @Ftime          , [Ftinyint] = @Ftinyint          , [Funiqueidentifier] = @Funiqueidentifier          , [Fvarbinary] = @Fvarbinary          , [Fvarbinary_Max] = @Fvarbinary_Max          , [Fvarchar] = @Fvarchar          , [Fvarchar_Max] = @Fvarchar_Max          , [F] = @F WHERE   (      ([isid] = @Original_isid)     AND ((@IsNull_FRbigint = 1 AND [FRbigint] IS NULL) OR ([FRbigint] = @Original_FRbigint))     AND ((@IsNull_Fbinary = 1 AND [Fbinary] IS NULL) OR ([Fbinary] = @Original_Fbinary))     AND ((@IsNull_Fbit = 1 AND [Fbit] IS NULL) OR ([Fbit] = @Original_Fbit))     AND ((@IsNull_Fchar = 1 AND [Fchar] IS NULL) OR ([Fchar] = @Original_Fchar))     AND ((@IsNull_Fdate = 1 AND [Fdate] IS NULL) OR ([Fdate] = @Original_Fdate))     AND ((@IsNull_Fdatetime = 1 AND [Fdatetime] IS NULL) OR ([Fdatetime] = @Original_Fdatetime))     AND ((@IsNull_Fdatetime2 = 1 AND [Fdatetime2] IS NULL) OR ([Fdatetime2] = @Original_Fdatetime2))     AND ((@IsNull_Fdatetimeoffset = 1 AND [Fdatetimeoffset] IS NULL) OR ([Fdatetimeoffset] = @Original_Fdatetimeoffset))     AND ((@IsNull_Fdecimal = 1 AND [Fdecimal] IS NULL) OR ([Fdecimal] = @Original_Fdecimal))     AND ((@IsNull_Ffloat = 1 AND [Ffloat] IS NULL) OR ([Ffloat] = @Original_Ffloat))     AND ((@IsNull_Fhierarchyid = 1 AND [Fhierarchyid] IS NULL) OR ([Fhierarchyid] = @Original_Fhierarchyid))     AND ((@IsNull_Fint = 1 AND [Fint] IS NULL) OR ([Fint] = @Original_Fint))     AND ((@IsNull_Fmoney = 1 AND [Fmoney] IS NULL) OR ([Fmoney] = @Original_Fmoney))     AND ((@IsNull_Fnchar = 1 AND [Fnchar] IS NULL) OR ([Fnchar] = @Original_Fnchar))     AND ((@IsNull_Fnumeric = 1 AND [Fnumeric] IS NULL) OR ([Fnumeric] = @Original_Fnumeric))     AND ((@IsNull_Fnvarchar = 1 AND [Fnvarchar] IS NULL) OR ([Fnvarchar] = @Original_Fnvarchar))     AND ((@IsNull_Freal = 1 AND [Freal] IS NULL) OR ([Freal] = @Original_Freal))     AND ((@IsNull_Fsmalldatetime = 1 AND [Fsmalldatetime] IS NULL) OR ([Fsmalldatetime] = @Original_Fsmalldatetime))     AND ((@IsNull_Fsmallint = 1 AND [Fsmallint] IS NULL) OR ([Fsmallint] = @Original_Fsmallint))     AND ((@IsNull_Fsmallmoney = 1 AND [Fsmallmoney] IS NULL) OR ([Fsmallmoney] = @Original_Fsmallmoney))     AND ((@IsNull_Fsql_variant = 1 AND [Fsql_variant] IS NULL) OR ([Fsql_variant] = @Original_Fsql_variant))     AND ((@IsNull_Ftime = 1 AND [Ftime] IS NULL) OR ([Ftime] = @Original_Ftime))     AND ((@IsNull_Ftinyint = 1 AND [Ftinyint] IS NULL) OR ([Ftinyint] = @Original_Ftinyint))     AND ((@IsNull_Funiqueidentifier = 1 AND [Funiqueidentifier] IS NULL) OR ([Funiqueidentifier] = @Original_Funiqueidentifier))     AND ((@IsNull_Fvarbinary = 1 AND [Fvarbinary] IS NULL) OR ([Fvarbinary] = @Original_Fvarbinary))     AND ((@IsNull_Fvarchar = 1 AND [Fvarchar] IS NULL) OR ([Fvarchar] = @Original_Fvarchar)))