在代码中拼接SQL命令与在存储过程中拼接的区别

[newdigitime]

一般说来在代码中拼接SQL命令，容易让别人钻空子玩SQL注入，
我看了有些存储过程的代码，发现在存储过程中也是对各参数进行拼接。
这好象与在代码中拼接没什么区别啊，难道在存储过程拼接就更安全？
譬如下面这个存储过程：

Create PROCEDURE [dbo].[ShowPage]
@tblName   varchar(255),       -- 表名
@strGetFields varchar(1000) = ''*'',  -- 需要返回的列
@strOrder varchar(255)='''',      -- 排序的字段名
@PageSize   int = 10,          -- 页尺寸
@PageIndex  int = 1,           -- 页码
@strWhere  varchar(1500) = ''''  -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL   varchar(5000)

if @strWhere !=''''
  set @strWhere='' where ''+@strWhere

set @strSQL=''Select * FROM (Select ROW_NUMBER() OVER (''+@strOrder+'') AS pos,''+@strGetFields+'' FROM [''+@tblName+'']''+@strWhere+'') AS sp Where pos BETWEEN ''+str((@PageIndex-1)*@PageSize+1)+'' AND ''+str(@PageIndex*@PageSize)
本文转摘自『蓝派网』http://www.lan27.com/Article/200810/8986.htm

[newdigitime]

[Quote=引用 13 楼 mars58 的回复:]
的确是这样的，在存储过程拼接sql的话，那些变量都是通过参数传进来的比如
C# codenew SqlParameter("@Category",SqlDbType.Int)
这样的话这个参数接收的参数就只能是整型了，就能有效的防止部分sql注入了，比如@p1="a' or 1=1"  这样。但不是说所有的存储过程都能防止sql注入。别以为所有查询都是用存储过程写的你的程序就安全了，下面给你看个例子
[/Quote]

如果真是这样,那么存储+参数的意义仅在于"判断实际输入的数据类型是否与指定的类型相符"这一点点了?
既然只有这点作用,那么也可以通过代码给变量指定相关的数据类型,岂不是达到了同样的目的
譬如 int i;
i=int.prase(xxx.text);
问题是这么多人说存储+参数具有很强大的防SQL注入特点(微软的MSDN也经常这样提),我想不应该只是在于"数据类型的判断"吧.

现在真是矛盾吧.感觉很多资料都是互相矛盾,包括微软自己的说法都让人云里雾里,
譬如SQL的参考文档中说:
"SQL Server 中的 Parameters 集合提供了类型检查和长度验证。如果使用 Parameters 集合，则输入将被视为文字值而不是可执行代码"
如果真是这样.楼上朋友的Ids = N'1,2) union all select [name],object_id from sys.objects where type=''u''--)' 将完全被视为一段非执行的字符串而不会执行.按楼上朋友的说法,实际情况又不是这样.

[newdigitime]

另外有些资料说，“如果存储过程中，也是拼接SQL代码，同样不安全"
言外之意，存储过程中的代码，还有“不用拼接的另一种写法”？

如果有，应该怎么写，高手能不能就
select id from table where name=[@p1]
给一个简单的例子？

[SQL77]

http://www.16395.net/shownews.asp?id=386这个里面的东西对楼主没作用????

[SQL77]

要不然楼主去C#版问题看看这个问题?

[pt1314917]

主要是对类型、长度的一个限制吧。其实还是差不多。。。
都是拼接。只不过一个在程序里面拼接、一个是在数据库中。。。
不过最好的是在提交前，将所有危险字符过滤掉。譬如： exec drop insert 等等类似的。。。当然这样可能会有些弊端。。。

[cailee]

关注

[fcuandy]

sp_executesql.
上面手误打错了。
用法可见联机丛书。

如果你在C#中写一段传参的方式执行语句的，在执行的同事，打开sqlserver事件探查器，就会看到，这种执参的不是拼语句而是转为sp_executesql执行。

[fcuandy]

传参和拼语句最本质的是什么？

拼语句用的参数和变量在拼出的语句中可以是数据库对象，也可以是值。

而传参，把参数什么为值用。 这是二者最本质的区别。



'select * from ' + @tb
'select * from tb where name=''' + @name + ''''

@tb，@name都是存放的字串，但在拼出的语句里 @name是做值用，@tb是做表对象用。
后者都可以改为sp_executesql执行。
前者不能，即便改用sp_executesql了，也是伪装的，只是形似. sqlserver还不是对象型数据库，没有什么变量可以是一个引用的概念。

[SQL77]

[Quote=引用 19 楼 fcuandy 的回复:]
对于你发的这个存储过程来说：

与直接在代码中拼SQL命令比较：

1,没有提高安全性，虽然说是用的参数，最终参数又被用来拼字串了，并非是转换为sp_execute执行。
2,没有提高效率，虽然说是存储过程，但是只是编译了拼语句的代码，拼出的语句是动态的，每次不一样，在执行中编译和选择计划的
3,仅有的好处，只能说：
a 一个封装
b 当拼的语句够长时，代码主体如果是存储过程，减少了语句发送到sqlserver的数据量
[/Quote]
在C#版的时候很多人都说参数传递能提高安全性,也许不是跟这个比较,呵呵,学习

[cailee]

c#和sql版都关注下。

[newdigitime]

[Quote=引用 19 楼 fcuandy 的回复:]
对于你发的这个存储过程来说：

与直接在代码中拼SQL命令比较：

1,没有提高安全性，虽然说是用的参数，最终参数又被用来拼字串了，并非是转换为sp_execute执行。
[/Quote]
请教,存储过程用sp_execute执行的基本写法是什么样的
能不能用一个简单的例子说明一下,譬如 查询某表中id为xx的记录

[fcuandy]

对于你发的这个存储过程来说：

与直接在代码中拼SQL命令比较：

1,没有提高安全性，虽然说是用的参数，最终参数又被用来拼字串了，并非是转换为sp_execute执行。
2,没有提高效率，虽然说是存储过程，但是只是编译了拼语句的代码，拼出的语句是动态的，每次不一样，在执行中编译和选择计划的
3,仅有的好处，只能说：
a 一个封装
b 当拼的语句够长时，代码主体如果是存储过程，减少了语句发送到sqlserver的数据量

[newdigitime]

[Quote=引用 25 楼 fcuandy 的回复:]
传参和拼语句最本质的是什么？

拼语句用的参数和变量在拼出的语句中可以是数据库对象，也可以是值。

而传参，把参数什么为值用。 这是二者最本质的区别。


'select * from ' + @tb
'select * from tb where name=''' + @name + ''''

@tb，@name都是存放的字串，但在拼出的语句里 @name是做值用，@tb是做表对象用。
后者都可以改为sp_executesql执行。
前者不能，即便改用sp_executesql了，也是伪装的，只是形似. sqlserver还不是对象型数据库，没有什么变量可以是一个引用的概念。
[/Quote]
有人说不要写成select * from tb where name='" + @name + "'"
直接写成select * from tb where name=@name (这里不加单引号可行么)
另外不要写成下面这种形式:
set sql="select * from tb where name='" + @name + "'"
exec (sql)
而是直接在存储代码中写:
begin
select * from tb where name=@name //即不用exec(sql)形式,同时也没给参数加单引号
end
另外还有人说将exec(sql)换成
exec sp_executesql sql这种形式.

不知上面这些说法有无道理

[fcuandy]

既 然 能写成 select * from tb where name=@name
还有必要用exec或sp_executesql吗？

[mars58]

[Quote=引用 19 楼 fcuandy 的回复:]
对于你发的这个存储过程来说：

与直接在代码中拼SQL命令比较：

1,没有提高安全性，虽然说是用的参数，最终参数又被用来拼字串了，并非是转换为sp_execute执行。
2,没有提高效率，虽然说是存储过程，但是只是编译了拼语句的代码，拼出的语句是动态的，每次不一样，在执行中编译和选择计划的
3,仅有的好处，只能说：
a 一个封装
b 当拼的语句够长时，代码主体如果是存储过程，减少了语句发送到sqlserver的数据量
[/Quote]

我不赞同第二点，虽然是拼接的sql但是整个执行计划同样是被缓存了的，你可以在sys.dm_exec_query_plan中查询得到

[mars58]

[Quote=引用 27 楼 fcuandy 的回复:]
既 然 能写成 select * from tb where name=@name
还有必要用exec或sp_executesql吗？


[/Quote]

这两者是完全有区别的，exec仅仅只是执行这个即席查询，那么他要经历整个过程 编译->algebrizer->运算符平展->名词解析->类型派生 再到优化再生成执行计划这个过程
而sp_executesql则不然，而是直接调用执行计划，也就跳过了优化阶段，你的意思就是说参数不同生产的执行计划是不同的，这个观点我不赞同的，执行计划是是跟表的结构，链接顺序，是否使用索引，是否并行处理等相关的，虽然参数的值不同，但是整个查询的结构是相同的

[fcuandy]

[Quote=引用 28 楼 mars58 的回复:]
引用 19 楼 fcuandy 的回复:
对于你发的这个存储过程来说：

与直接在代码中拼SQL命令比较：

1,没有提高安全性，虽然说是用的参数，最终参数又被用来拼字串了，并非是转换为sp_execute执行。
2,没有提高效率，虽然说是存储过程，但是只是编译了拼语句的代码，拼出的语句是动态的，每次不一样，在执行中编译和选择计划的



我不赞同第二点，虽然是拼接的sql但是整个执行计划同样是被缓存了的，你可以在sys.dm_exec_query_plan中查询得到
[/Quote]

请仔细读第二条的意思。

[fcuandy]

[Quote=引用 29 楼 mars58 的回复:]
引用 27 楼 fcuandy 的回复:
既 然 能写成 select * from tb where name=@name
还有必要用exec或sp_executesql吗？




这两者是完全有区别的，exec仅仅只是执行这个即席查询，那么他要经历整个过程 编译->algebrizer->运算符平展->名词解析->类型派生 再到优化再生成执行计划这个过程
而sp_executesql则不然，而是直接调用执行计划，也就跳过了优化阶段，你的意思就是说参数不同生产的执行计划是不同的，这个观点我不赞同的，执行计划是是跟表的结构，链接顺序，是否使用索引，是否并行处理等相关的，虽然参数的值不同，但是整个查询的结构是相同的
[/Quote]

对于你第一个回贴的反驳我可以理解。

对于这个就完全就不能明白你在反驳我的什么，呵呵

declare @name varchar(100)
set @name ='aaa'
即然能写成
select x from tb where name =@name
我又何必去exec或者sp_executesql N'select x from tb where name=@name',N'......
这样呢？
我有说过exec 和sp_executesql是一样的吗？


先不说你是在反驳什么， 对于你描述的后面半句:
执行计划是是跟表的结构，链接顺序，是否使用索引，是否并行处理等相关的，虽然参数的值不同，但是整个查询的结构是相同的

除了你说的这些，表中数据的分布很影响查询计划，不同的参数值都可能引发计划的变更.

[newdigitime]

感谢大家,下次抽时间来"仔细结贴"

[syw_java]

学习并关注