个人学习笔记14之--浅谈动态SQL

最新推荐文章于 2024-04-19 21:46:44 发布
最新推荐文章于 2024-04-19 21:46:44 发布
阅读量309 收藏
点赞数
分类专栏： 数据库经典 文章标签： sql insert go table 存储 user

动态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()。这是不允许的
view plaincopy to clipboardprint?
DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128);  
SET @schemaname = N'dbo';  
SET @tablename = N'Order Details';  
EXEC(N'SELECT COUNT(*) FROM ' 
     + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';'); 
  DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128);
  SET @schemaname = N'dbo';
  SET @tablename = N'Order Details';
  EXEC(N'SELECT COUNT(*) FROM '
       + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';');
 --这里是会发生错误的.


 b.批处理内定义的局部变量是不可以在动态语句里面访问的--这个就是所谓的EXEC不提供接口了
 view plaincopy to clipboardprint?
DECLARE @i AS INT;  
 SET @i = 10248;  
   
 DECLARE @sql AS VARCHAR(52);  
 SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @i;';  
 EXEC(@sql); 
 DECLARE @i AS INT;
  SET @i = 10248;
 
  DECLARE @sql AS VARCHAR(52);
  SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @i;';
  EXEC(@sql);
 

 /*
   必须声明标量变量 "@i"。
  */
  ---处理方法就是将这个变量串联到字符串里去
view plaincopy to clipboardprint?
DECLARE @i AS INT;  
SET @i = 10248;  
 
DECLARE @sql AS VARCHAR(52);  
SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = ' 
  + rtrim(@i) + N';';  
EXEC(@sql); 
  DECLARE @i AS INT;
  SET @i = 10248;
 
  DECLARE @sql AS VARCHAR(52);
  SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = '
    + rtrim(@i) + N';';
  EXEC(@sql);
  ------可惜-----这么做不但不太安全,而且串联变量上去后,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.关于会话环境和动态语句的相互影响问题
  我直接上例子进行说明问题：
view plaincopy to clipboardprint?
USE Northwind;  
DECLARE @db AS NVARCHAR(258);  
SET @db = QUOTENAME(N'pubs');  
EXEC(N'USE ' + @db + ';');     
--虽然在动态批处理中改变了数据库名,但是并没有能影响到外面的批处理的db_name()函数  
SELECT DB_NAME();  
GO  
/* 
--------------- 
 Northwind 
*/   
USE Northwind;  
DECLARE @db AS NVARCHAR(258);  
SET @db = QUOTENAME(N'pubs');  
EXEC(N'USE ' + @db + N' SELECT DB_NAME();');   
--这里在动态批处理内部处理调用db_name()函数,当然是有效的.  
GO  
/* 
----------------- 
  pubs 
*/ 
USE Northwind;  
DECLARE @db AS NVARCHAR(258);  
SET @db = QUOTENAME(N'pubs');  
EXEC(N'USE ' + @db + N'; EXEC(''SELECT DB_NAME();'');');  
--这里在动态批处理内部的内部调用db_name(),说明动态处理是能影响其内部级别的.  
/* 
----------------- 
  pubs 
*/ 
  USE Northwind;
  DECLARE @db AS NVARCHAR(258);
  SET @db = QUOTENAME(N'pubs');
  EXEC(N'USE ' + @db + ';');  
  --虽然在动态批处理中改变了数据库名,但是并没有能影响到外面的批处理的db_name()函数
  SELECT DB_NAME();
  GO
  /*
  ---------------
   Northwind
  */
  USE Northwind;
  DECLARE @db AS NVARCHAR(258);
  SET @db = QUOTENAME(N'pubs');
  EXEC(N'USE ' + @db + N' SELECT DB_NAME();');
  --这里在动态批处理内部处理调用db_name()函数,当然是有效的.
  GO
  /*
  -----------------
    pubs
  */
  USE Northwind;
  DECLARE @db AS NVARCHAR(258);
  SET @db = QUOTENAME(N'pubs');
  EXEC(N'USE ' + @db + N'; EXEC(''SELECT DB_NAME();'');');
  --这里在动态批处理内部的内部调用db_name(),说明动态处理是能影响其内部级别的.
  /*
  -----------------
    pubs
  */
  -----总结起来说:动态批处理的处理只在动态处理自己的级别和它内部的级别有用,但是无法影响外面的批处理的---------
 
 4.动态语句和临时表
view plaincopy to clipboardprint?
use tempdb  
go  
exec ('create table #(a int ) insert # select 1 ')  
--在动态语句内部建立的临时表 在外面是不可见的  
select * from #  
/* 
对象名 '#' 无效。 
*/ 
use tempdb  
go  
exec ('create table #(a int ) insert # select 1 ;exec (''select * from #'')')  
--在动态语句内部建立的临时表在动态语句的内部级别包括它自己这个级别是可见的  
/* 
a 
----------- 
1 
*/ 
  use tempdb
  go
  exec ('create table #(a int ) insert # select 1 ')
  --在动态语句内部建立的临时表 在外面是不可见的
  select * from #
  /*
  对象名 '#' 无效。
  */
  use tempdb
  go
  exec ('create table #(a int ) insert # select 1 ;exec (''select * from #'')')
  --在动态语句内部建立的临时表在动态语句的内部级别包括它自己这个级别是可见的
  /*
  a
  -----------
  1
  */
  
 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--
 

 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/feixianxxx/archive/2009/11/09/4791479.aspx

智星
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
动态SQL笔记
JavaDarren的博客
07-22 215
动态SQL if choose (when otherwise) where set trim foreach if 我们直接使用if，的时候，有可能会有多余and or等符号的错误，因为不符合SQL语法。 解决方式以前的可以使用 添加额外的固定条件 1=1 where 使用mybatis提供的where标签来解决 可以去除多余的前缀的and or 如果一个条件没有，它会自动去除where本...
动态SQL
silverneedle的博客
08-07 250
3.1使用动态SQL完成完成多条件查询 ➢if:利用if实现简单的条件选择 ➢choose(when,otherwise):相当于Java中的switch语句,通常与when和otherwise搭配 ➢where:简化SQL语句中where的条件判断 ➢set:解决动态更新语句 ➢trim:可以灵活地去除多余的关键字 ➢foreach:迭代一个集合,通常用于in条件 3.1.1使用if+where...
【个人学习笔记14之--浅谈动态SQL】
飞轩亭
11-09 3237
动态SQL   动态的SQL和游标一样也是一个很有争议的东西,不过我觉得貌似大家对他的排斥不是很高.  不好的地方:1.动态SQL是非常危险的,因为动态SQL一般是根据用户的输入来构造你的整个SQL语句,也就是我们平时看到一些参数参与了SQL语句,一般的黑客也会 利用这个空洞进行注入,这是我对注入最简单理解,利用手写进去的一些代码,改变本来的SQL语句结构.2.动态语句
动态sql
weixin_43931372的博客
06-16 173
动态SQL使用小结
Eric198808的博客
10-26 224
1.什么是动态SQL？ 静态 SQL：静态 SQL 语句一般用于嵌入式 SQL 应用中，在程序运行前，SQL 语句必须是确定的，例如 SQL 语句中涉及的列名和表名必须是存在的。静态 SQL 语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。而后程序运行时，数据库将直接执行编译好的 SQL 语句，降低运行时的开销。 动态 SQL：动态 SQL 语句是在应用程序运行时被...
对使用动态SQL的几点建议
keven2840的博客
09-09 310
NDS建议 对共享程序使用调用者权限 在动态SQL中使用AUTHIDCURRENT_USER子句，来实现调用者模型。 例子： PROCEDUREexec_DDL(ddl_string IN VARCHAR2) AUTHID CURRENT_USER IS BEGIN EXECUTEIMMEDIATE ddl_string; END;   预测并处理动态错误 当我们进行大量的
MyBatis动态SQL的学习心得
LiQiangQiangIT的博客
12-15 656
MyBatis动态SQL的学习心得 1、if元素 相当于JAVA中的if语句。常常和test属性联合使用，有如下查询要求 1>当name不为空时，执行模糊查询。如下代码清单： <select id="selData" parameterType="string" resultType="com.test.Data"> select * from data where 1=1 ...
【个人学习笔记17之--存储过程浅谈】
飞轩亭
11-27 4339
这次讲得是 存储过程(proc) 存储过程分类:1.用户自定义存储过程: 分成 TRANSACT-SQL存储过程和CLR存储过程2.拓展存储过程(后续版本将删除这个,所以避免使用它了,用clr替代)3.系统存储过程物理意义上讲，系统存储过程存储在源数据库中，并且带有 sp_ 前缀。从逻辑意义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的 sys 构架中。 存储过程的优
【个人学习笔记18之--浅谈触发器】
飞轩亭
12-03 7218
这次谈的是触发器Microsoft SQL Server 提供两种主要机制来强制使用业务规则和数据完整性：约束和触发器。关于触发器和约束用谁比较好,这主要看业务的逻辑复杂程度.如果你的处理逻辑比较简单,并且可以用一些简单的约束来处理,则应该尽量使用PRIMARY KEY ,UNIQUE CHECK这些约束.当约束支持的功能无法满足应用程序的功能要求时，DML 触发器非常有用 例如:1.除非
【个人学习笔记16之--浅谈UDF(用户自定义函数)】
飞轩亭
11-25 5004
好久不写了,今天来说说UDF(用户自定义函数)UDF分为标量UDF和表值UDF。ps:每种都有CLR UDF的在05里面 本人不熟悉 所以只写T-SQL的 使用场合——在 Transact-SQL 语句（如 SELECT）中——在调用该函数的应用程序中——在另一个用户定义函数的定义中——用于参数化视图或改进索引视图的功能——用于在表中定义列——用于为列定义 CHECK 约束——用于替换
动态SQL的常见错误
12-14
动态SQL在使用时，有很多需要注意的地方，如动态SQL语句结尾处不能使用分号(;)，而动态PL/SQL结尾处需要使用分号(;)，但不能使用正斜杠结尾(/)，以及shcema对象不能直接作为变量绑定。本文介绍了动态SQL的常见问题。 　　一、演示动态SQL的使用 　　下面的示例中，首先使用动态SQL基于scott.emp创建表tb2，然后里直接使用动态SQL从新表中获取记录数并输出。再接下来是定义了一个动态PL/SQL代码并执行以获取当前的系统时间，后使用动态SQL对新表进行更新。 DECLARE                               –定义变量以及给变量设定初
再谈SQL-to-SQL翻译器
最新发布
2401_83412172的博客
04-19 608
还有更多的Redis、MySQL、JVM、Kafka、微服务、Spring全家桶等学习笔记这里就不一一列举出来。
基于《狂神说Java》SpringCloud --学习笔记
wudidahuanggua的博客
06-21 1004
本笔记仅做学习与复习使用，不存在刻意抄袭。参考于学友菜鸟小杰子。给各位学友强烈推荐《遇见狂神说》他的整套Java学习路线使我获益匪浅！！!
mybatis中动态sql常遇到的问题，以及连表查询时常遇到的问题
LIHUI_A的博客
07-17 599
mybatis中动态sql常遇到的问题，以及连表查询时常遇到的问题 1、mybatis中注意下一使用where 和if标签以及set 和if 标签时，如果有多个if，那么请按下面格式书写（即将连接符号 and写在前面），防止出错 <where> <if test="provider != null and provider.providerCode">provider_code like "%"{provider.providerCode}"%" </if>
Mybatis动态SQL踩坑记
徐小陌的博客
06-24 1508
Mybatis动态SQL的一个踩坑复盘～
动态T-SQL语句常見問題與解決方案
水族杰纶
11-24 3000
--> Title  : 动态T-SQL语句常見問題與解決方案--> Author : wufeng4552--> Date   : 2009-11-24 10:11:10if object_id([tb]) is not null drop table [tb]gocreate table [tb] (dt nvarchar(20),ID int)insert int
mybatis动态sql中，遇到的问题（1）
RussellChampagne的博客
09-15 345
mybatis动态sql中，遇到的问题 报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘and name like ‘%%’ limit 0,20’ at line 3 ...
动态sql遇到的错误及解决
KOBEZANG的博客
05-20 455
java 动态sql编写_Java开发人员在编写SQL时犯的10个常见错误
danpu0978的博客
05-11 223
java 动态sql编写 我最近在我自己的博客和联合伙伴DZone上看到有关Java开发人员在编写SQL时常犯的10个常见错误的清单的受欢迎程度，我感到非常惊讶。 受欢迎程度显示出以下几点： SQL对专业Java世界有多重要。 忘记一些基本SQL东西是多么普遍。 通过拥抱SQL ，以SQL为中心的库（例如jOOQ或MyBatis）对市场需求的响应程度如何。 一个有趣的事实是，用户...
SQLServer2008学习笔记：T-SQL与数据库管理
"Sqlserver2008学习笔记，主要涵盖了SQLServer的基础知识，T-SQL语句的使用，以及查询操作的详解。" 在SQLServer 2008的学习笔记中，我们首先接触的是数据库的基础概念。SQLServer是微软公司推出的关系型数据库管理...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值