动态SQL
动态的SQL和游标一样也是一个很有争议的东西,不过我觉得貌似大家对他的排斥不是很高.
不好的地方:
1.动态SQL是非常危险的,因为动态SQL一般是根据用户的输入来构造你的整个SQL语句,也就是我们平时看到一些参数参与了SQL语句,一般的黑客也会
利用这个空洞进行注入,这是我对注入最简单理解,利用手写进去的一些代码,改变本来的SQL语句结构.
2.动态语句生涩难搞,经常会出现引号错误,参数转换错误等.
好的地方:
它能处理一些静态的SQL语句不能处理的查询任务.比如平时遇到的行转列的时候,转换成列的时候你不知道你要转哪几个列.这个时候用动态就不需要担心了.
执行动态语句的方式: EXEC(execute)和 sp_executesql
由于后者提供了输入/输出接口;后者更有可能重用执行计划;后者可以编写更加安全的代码;
所以没有特殊的情况下,一般我们就是用sp_executesql。。。切忌
关于这2个口令在动态语法中的应用的基础知识:可以参见roy大大的博客.总记的非常好--http://blog.csdn.net/roy_88/archive/2008/10/06/3020586.aspx
我就在提几点注意的地方:
1.EXEC
a.不要在exec()括号里面用case 或者一些函数 比如rtrim(),quotename()。这是不允许的
--这里是会发生错误的.
b.批处理内定义的局部变量是不可以在动态语句里面访问的--这个就是所谓的EXEC不提供接口了
/*
必须声明标量变量 "@i"。
*/
---处理方法就是将这个变量串联到字符串里去
------可惜-----这么做不但不太安全,而且串联变量上去后,sql会为每个唯一的查询字符串创建新的执行计划.
--比如你这个@i 10348 10349 10897 等 它就会为此产生三个执行计划 多么浪费成本.
c.本来exec的优势
exec以前比sp_executesql 可以支持更长的代码.因为一个是varchar(8000) 一个是nvarchar(4000),而且exec(@s1+@s2+@s3)这里的
括号里的每个变量可以都是varchar(8000)多么庞大啊.可惜到了SQL2005 varchar(max),nvarchar(max)的出现 彻底改变了这个笨拙
繁琐的方法.一个nvarchar(max) 可以包含二十亿的字符....是20亿...即使是nvarchar(max) 也要10来亿..你还需要拼接么?.....- -||
所以用05的朋友尽量使用max来定义吧.
2.sq_executesql
a.提供了很强大的借口--避免了串联变量的问题 还可以输出 LOOK
DECLARE @i AS INT;
SET @i = 10248;
DECLARE @sql AS NVARCHAR(46);
SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @oid;';
-----请注意,上面的@oid就不要在转化成字符串联进去了 直接上 直接上 直接上
EXEC sp_executesql
@stmt = @sql,--这个相当存储过程一个查询主体 ,就是AS后面的东西
@params = N'@oid AS INT',--这个相当于存储过程的传入参数声明,就是AS前面的东西
@oid = @i;--这里相当于你调用存储过程的时候 后面那个参数列表
------这里如果你给@id赋值三个不同的值 10348 10349 10897 它为此产生的执行计划只有一个哦~只有一个 不是三个!!!节约成本
b.它可以通过传入的参数检查,用来防止SQL注入.
我的理解就是通过传入的参数的长度类型之类的进行判断吧.
3.关于会话环境和动态语句的相互影响问题
我直接上例子进行说明问题:
-----总结起来说:动态批处理的处理只在动态处理自己的级别和它内部的级别有用,但是无法影响外面的批处理的---------
4.动态语句和临时表
5.动态语句和临时变量
use tempdb
go
exec ('declare @s int select isnull(@s,1)')
--可以执行
exec ('declare @s int ; exec(''select isnull(@s,1)'')')
--在动态语句内部建立的临时变量在其内部级别是不可见的
/*
必须声明标量变量 "@s"。
*/
6.非常浅得说说SQL注入
通俗说:就是通过输入恶意代码串联到动态语句中 做坏事-- - -||书上例子我说明下
a.客户端输入:
CREATE TABLE dbo.Users
(
username VARCHAR(30) NOT NULL PRIMARY KEY,
pass VARCHAR(16) NOT NULL
);
INSERT INTO Users(username, pass) VALUES('user1', '123');
INSERT INTO Users(username, pass) VALUES('user2', '456');
GO
-- 通常客户端程序里使用下面代码来查询验证用户的身份
/*
sql = "SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = '" _
& InputUserName & "' AND pass = '" & InputPass & "';"
InputUserName = "user1"
InputPass = "123"
*/
--通过上面的输入,产生下面的句子
SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = 'user1' AND pass = '123';
--如果你是黑客 输入下面的用户名 密码
/*
InputUserName = "' OR 1 = 1 --"
InputPass = ""
*/
--它的查询语句就变成这样
SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = '' OR 1 = 1 --' AND pass = '';
--好吧 ,现在你可以不用身份 畅通无阻 可怕~
GO
b.服务端输入:
USE Northwind;
GO
--创建存储过程 通过传入订单号 查询ORDERS表的记录
IF OBJECT_ID('dbo.usp_getorders') IS NOT NULL
DROP PROC dbo.usp_getorders;
GO
CREATE PROC dbo.usp_getorders
@orders AS VARCHAR(1000)
AS
DECLARE @sql AS NVARCHAR(4000);
SET @sql = 'SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID IN('
+ @orders + ');';
EXEC sp_executesql @sql;
GO
--输入这个 OK 普通查询
EXEC dbo.usp_getorders '10248,10249,10250';
--输入这个--
EXEC dbo.usp_getorders ' --';
--会弹出错误
/*
Msg 102, Level 15, State 1, Line 1
'(' 附近有语法错误。
*/
--这里就会发现是动态语句,并且左边括号后面可以输入值,随便输入个,它会返回空值,只有表结构
EXEC dbo.usp_getorders '-1) --';
/*
OrderID CustomerID
----------- ----------
*/
--拼接下面字符串 可以搜索到你的sysobjects理的一些你不想让别人看到的记录
EXEC dbo.usp_getorders '-1) UNION ALL SELECT id, name FROM sysobjects --';
--更加可怕的是:如果你输入一些很坏的语句 进行破坏 那你就悲剧了 如
EXEC usp_getorders '-1) UPDATE dbo.Customers SET Phone = ''9999999'' WHERE CustomerID = ''ALFKI'' --';
--上面这个语句将把你的的Customers表 CustomerID = ''ALFKI''的用户的电话都改掉
------关于SQL注入更多信息,看水哥的整理贴http://topic.csdn.net/u/20081205/09/3dd06076-bcbe-45d4-998c-8999fdbe6fae.html--