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

Question

一般说来在代码中拼接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 ''+@strWhereset @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

Answer 1

只要是拼接，程序和存储过程其实差不多你要让传进来的字符串作为值，而不是作为sql的组成部分就可以提高防注入的可能性

Answer 2

既然调用存储,当然是用参数啦.大家都说这样更安全,防注入.
参数+存储一样是拼接SQL,就其安全性而言,这与在代码中拼接SQL命令字符串 区别在哪里呢?最终得到的SQL命令字符串是一样的.

Answer 3

譬如说前台有一个拼接SQL
string xxx="a' or 1=1'";
sql="select * from table where name='"+xxx;
实际上sql得到提select * from table where name='a' or 1=1如果我们用参数+存储的形式,将变量xxx作为参数@p1 传入存储
存储过程中,最终得到SQL命令是:
sql=sql="select * from table where name='"+[@p1]+"'";
这时SQL和在代码中拼接难道不是一样的吗?
最终存储执行 exec (select * from table where name='a' or 1=1)

Answer 4

这个我也知道,我们在代码中进行变量拼接时,本来的目的也是将变量作为值的,但结果却不是我们想看到的.在存储中:
如果  参数@p1="a' or 1=1"   
set @sql="select * from table where name='"+[@p1]+"'" exec(@sql) 难道不等效于
exec (select * from table where name='a' or 1=1) 吗?

Answer 5

http://www.16395.net/shownews.asp?id=386 参考下,具体也说不上来

Answer 6

exec (select * from table where name='a' or 1=1) 这个也不是这样写的,

Answer 7

的确是这样的，在存储过程拼接sql的话，那些变量都是通过参数传进来的比如
new SqlParameter("@Category",SqlDbType.Int)
这样的话这个参数接收的参数就只能是整型了，就能有效的防止部分sql注入了，比如@p1="a' or 1=1"  这样。但不是说所有的存储过程都能防止sql注入。别以为所有查询都是用存储过程写的你的程序就安全了，下面给你看个例子create table tb1(TName varchar(50),id int)
go
insert into tb1 select 'tom',1 union all select 'jone',2create proc getByIds
@Ids varchar(200)
as
begin
declare @sql varchar(max)
set @sql='select * from tb1 where id in('+@Ids+')'
execute(@sql)
end 
  USE [t1]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[getByIds]
@Ids = N'1,2'
SELECT 'Return Value' = @return_value
GO
--结果
--tom 1
--jone 2--看下面这个参数
USE [t1]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[getByIds]
@Ids = N'1,2) union all select [name],object_id from sys.objects where type=''u''--)'
SELECT 'Return Value' = @return_value
GO
--tom 1
--jone 2
--t3 5575058
--nums 405576483
--TTime 421576540
--tb 437576597
--tb1 453576654
--哦呵，所有的表都暴露出来了，再通过object_id找其它对象，最后找到你数据库的密码也是轻易而举了。drop table tb1

Answer 8

如果真是这样,那么存储+参数的意义仅在于"判断实际输入的数据类型是否与指定的类型相符"这一点点了?
既然只有这点作用,那么也可以通过代码给变量指定相关的数据类型,岂不是达到了同样的目的
譬如 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''--)' 将完全被视为一段非执行的字符串而不会执行.按楼上朋友的说法,实际情况又不是这样.

Answer 9

另外有些资料说，“如果存储过程中，也是拼接SQL代码，同样不安全"
言外之意，存储过程中的代码，还有“不用拼接的另一种写法”？如果有，应该怎么写，高手能不能就
select id from table where name=[@p1]
给一个简单的例子？

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

请教,存储过程用sp_execute执行的基本写法是什么样的
能不能用一个简单的例子说明一下,譬如 查询某表中id为xx的记录

Answer 14

sp_executesql.
上面手误打错了。
用法可见联机丛书。如果你在C#中写一段传参的方式执行语句的，在执行的同事，打开sqlserver事件探查器，就会看到，这种执参的不是拼语句而是转为sp_executesql执行。

Answer 15

传参和拼语句最本质的是什么？拼语句用的参数和变量在拼出的语句中可以是数据库对象，也可以是值。而传参，把参数什么为值用。 这是二者最本质的区别。'select * from ' + @tb
'select * from tb where name=''' + @name + ''''@tb，@name都是存放的字串，但在拼出的语句里 @name是做值用，@tb是做表对象用。
后者都可以改为sp_executesql执行。
前者不能，即便改用sp_executesql了，也是伪装的，只是形似. sqlserver还不是对象型数据库，没有什么变量可以是一个引用的概念。

Answer 16

有人说不要写成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这种形式.不知上面这些说法有无道理 
  

Answer 17

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

Answer 18

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

Answer 19

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

Answer 20

对于你第一个回贴的反驳我可以理解。对于这个就完全就不能明白你在反驳我的什么，呵呵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是一样的吗？
先不说你是在反驳什么， 对于你描述的后面半句:
执行计划是是跟表的结构，链接顺序，是否使用索引，是否并行处理等相关的，虽然参数的值不同，但是整个查询的结构是相同的除了你说的这些，表中数据的分布很影响查询计划，不同的参数值都可能引发计划的变更.