大家都知道使用存储过程的好处其中有2点
?
1、参数查询安全(用参数就安全？)
?
2、存储过程在创建时预编译，执行效率比SQL语句要高
?
基于这两点，看看下面这段以前经常写的存储过程吧(查询数据)
?
CREATE PROCEDURE [dbo].[usp_TestExec]
@Account VARCHAR(50)='',
@UserName VARCHAR(50)=''
AS
BEGIN
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)=''
AS
BEGIN
--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
, [Fxml] = @Fxml
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_Ffl