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

2009-08-08 02:06:47 980 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Oracle OCP 19c 认证1Z0-083考试大纲(Oracle官方版) Oracle Database: Managing Multitenant Architecture Ed 1 course 阅读详情

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

...全文

Prompt Airlines CTF通关实战：从提示词注入到多模态攻击的AI安全攻防演练提示词注入（Prompt Injection）是当前大语言模型（LLM）应用面临的核心安全威胁之一，其原理在于攻击者通过精心构造的用户输入，覆盖或绕过系统预设的指令，从而操控模型行为。这种攻击手法的技术价值在于揭示了AI系统在理解与执行指令时存在的逻辑边界模糊性，可能导致数据泄露、越权访问等严重风险。在应用场景上，从基础的客服对话到结合视觉识别的多模态系统，都可能成为攻击面。本文以一场名为“Prompt Airlines CTF”的实战挑战为例，深入剖析了包括指令遗忘、上下文注入、工具调用滥用及多模态提示注阅读详情

newdigitime

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

syw_java 2009-12-04

打赏
举报

学习并关注

newdigitime 2009-12-04

打赏
举报

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

fcuandy 2009-08-17

打赏
举报

[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是一样的吗？

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

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

fcuandy 2009-08-17

打赏
举报

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

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

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

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

请仔细读第二条的意思。

mars58 2009-08-13

打赏
举报

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

[/Quote]

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

mars58 2009-08-13

打赏
举报

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

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

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

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

fcuandy 2009-08-10

打赏
举报

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

newdigitime 2009-08-10

打赏
举报

[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 2009-08-09

打赏
举报

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

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

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

newdigitime 2009-08-09

打赏
举报

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

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

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

cailee 2009-08-09

打赏
举报

c#和sql版都关注下。

SQL77 2009-08-09

打赏
举报

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

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

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

fcuandy 2009-08-09

打赏
举报

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

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

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

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

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

fcuandy 2009-08-09

打赏
举报

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

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

cailee 2009-08-09

打赏
举报

关注

pt1314917 2009-08-08

打赏
举报

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

SQL77 2009-08-08

打赏
举报

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

SQL77 2009-08-08

打赏
举报

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

newdigitime 2009-08-08

打赏
举报

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

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

newdigitime 2009-08-08

打赏
举报

[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''--)' 将完全被视为一段非执行的字符串而不会执行.按楼上朋友的说法,实际情况又不是这样.

加载更多回复（13）

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

相关推荐