抽象SQL查询:SQL-MAP技术的使用

什么是参数化查询?我们来看百科对此的定义和示例:

一,定义

------------------------------------------------------------------

参数化查询(Parameterized Query 或 Parameterized Statement)是指在设计与数据库链接并访问数据时,在需要填入数值或数据的地方,使用参数 (Parameter) 来给值,这个方法目前已被视为最有效可预防SQL注入攻击 (SQL Injection) 的攻击手法的防御方式。

  有部份的开发人员可能会认为使用参数化查询,会让程序更不好维护,或者在实现部份功能上会非常不便,然而,使用参数化查询造成的额外开发成本,通常都远低于因为SQL注入攻击漏洞被发现而遭受攻击,所造成的重大损失。

原理

  在使用参数化查询的情况下,数据库服务器不会将参数的内容视为SQL指令的一部份来处理,而是在数据库完成 SQL 指令的编译后,才套用参数运行,因此就算参数中含有具有损的指令,也不会被数据库所运行。

SQL 指令撰写方法

  在撰写 SQL 指令时,利用参数来代表需要填入的数值,例如:

Microsoft SQL Server

  Microsoft SQL Server 的参数格式是以 "@" 字符加上参数名称而成,SQL Server 亦支持匿名参数 "?"。

  SELECT * FROM myTable WHERE myID = @myID

  INSERT INTO myTable (c1, c2, c3, c4) VALUES (@c1, @c2, @c3, @c4)

Microsoft Access

  Microsoft Access 不支持具名参数,只支持匿名参数 "?"。

  UPDATE myTable SET c1 = ?, c2 = ?, c3 = ? WHERE c4 = ?

MySQL

  MySQL 的参数格式是以 "?" 字符加上参数名称而成。

  UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

Oracle

  Oracle 的参数格式是以 ":" 字符加上参数名称而成。

  UPDATE myTable SET c1 = :c1, c2 = :c2, c3 = :c3 WHERE c4 = :c4

PostgreSQL

  PostgreSQL 的参数格式是以 "$" 字符加上参数顺序号而成。

  UPDATE myTable SET c1 = $1, c2 = $2, c3 = $3 WHERE c4 = $4

     PostgreSQL也支持Oracle的参数表示形式

--------------------------------------------------------------------------------

总结一下各数据库对于参数符号的定义:

SQLSERVER @

Access,MySQL ?

Oracle :

PostgreSQL $

上面的这些符号是各数据库内部原生支持的方式,但是具体到ADO.NET调用的时候,

采用各数据库原生的.NET驱动程序,发现除了Oracle,各种数据库都可以在SQL语句中用@符号表示参数;

采用各数据库的OleDB或者ODBC驱动程序,都要求使用 ?符号表示参数。

还有其它本文未说到的数据库,他们的SQL语句表示参数的符号可能都是不一样的,怎么样在程序里面统一处理呢?本文主题开始了:

二,抽象SQL参数化查询

PDF.NET(PWMIS数据开发框架)中,对参数的定义统一采用##来处理,具体格式如下:

#参数名字[:参数类型],[数据类型],[参数长度],[参数输出输入类型]#

上面定义当中,中括号里面的内容都是可选的。

详细内容,请参看“SQL-MAP” 或者“PDF.NET(PWMIS数据开发框架)之SQL-MAP目标和规范

对本文第一部分的示例,可以改写成下面的方式:

UPDATE myTable SET 
c1 = #c1#, 
c2 = #c2:String#, 
c3 = #c3:String,Sring,50# 
WHERE c4 = #c4:Int32#

如果不指定参数的类型,默认为String类型,例如c1参数。

程序在运行时,会根据当前具体的数据库访问程序实例,将##内部的参数替换成合适的参数内容。

上面这种参数形式是写在SQL-MAP配置文件里面的,例如下面的一个实际的SQL-MAP查询脚本:

<Select CommandName="GetStatisticsAnalysis_SalerRoleStatistics" CommandType="Text" Method="" Description="" ResultClass="DataSet">
        <![CDATA[
     SELECT a.角色,a.销售金额/10000 销售金额,a.占比 FROM [GetStatisticsAnalysis_SalerRoleStatistics] (
    #manageid:Int32#, #min:String#, #max:String#) a]]>
      </Select>

如果想使用动态SQL语句,即SQL语句中有一个“假参数”,在运行时由另外一个字符串来替换的,例如非常复杂的查询条件拼接过程,请参看:

在SQLMAP中使用动态SQL

通过这种方式,完全屏蔽了不同种类的数据库查询的参数问题,将SQL参数化查询抽象了出来。

看到这里本文似乎该结束了,但本文的标题“参数化”加了一个括号,说明我们抽象的不仅仅是参数,我们还可以抽象整个SQL查询。

三,抽象SQL查询:SQL-MAP技术

在本文第二部分,我们将SQL中的参数“抽象化”了,我们还可以进一步抽象整个SQL,看下面的抽象过程:

  1. 编写任意形式的合法SQL查询语句;
  2. 抽象SQL中的参数;
  3. 将整个SQL语句抽象成一个唯一名字为CommandName;
  4. 将一组CommandName映射到一个DAL类文件;
  5. 将这个CommandName映射到一个DAL类的方法名称;
  6. 将SQL语句中的参数名称映射到该DAL类的当前方法中的参数名称;
  7. 将整个SQL脚本文件映射到一个DAL程序集。

这个思想,就是SQL-MAP,将SQL语句映射为程序!

下面我们介绍一下PDF.NET数据开发框架对于存储过程的操作思路,对于单条SQL也是如此。当然,单条SQL语句的操作我们不必请出SQL-MAP这种“重量级”的方式,还是使用框架中的ORM技术OQL吧,但这不是本文讨论的话题。

首先,在SQL-MAP配置文件里面写下面的脚本:

<Select CommandName="GetProductManage_FundSaleAndAIP" Method="" CommandType="Text" Description="获取XXX列表" ResultClass="DataSet">
          <![CDATA[
           select * from GetProductManage_FundSaleAndAIP(#Type:String#,#Name:String#,#isAIP:String#)
           ]]>
         </Select>

注意脚本中的ResultClass属性,它可以将查询映射成为单值,DataSet,实体类,实体类集合。

有了这个SQL-MAP文件,我们可以使用代码工具自动生成下面的代码(当然你也可以手写): 从上面的过程可以看出,框架采用SQL-MAP技术,将SQL语句(包括各种查询的单条SQL语句和存储过程等)映射成了DAL层代码,整个过程不需要了解.NET开发技术,所以DAL层的代码完全可以由DBA来写,而业务开发人员只要调用DAL代码即可。

01	     /// <summary> 
02	   /// 获取XXXXX列表 
03	   /// </summary> 
04	   /// <param name="Type"></param> 
05	   /// <param name="Name"></param> 
06	   /// <param name="isAIP"></param> 
07	   /// <returns></returns> 
08	   public DataSet GetProductManage_FundSaleAndAIP(String Type  , String Name  , String isAIP   )  
09	   {  
10	           //获取命令信息 
11	           CommandInfo cmdInfo=Mapper.GetCommandInfo("GetProductManage_FundSaleAndAIP"); 
12	           //参数赋值,推荐使用该种方式; 
13	           cmdInfo.DataParameters[0].Value = Type; 
14	           cmdInfo.DataParameters[1].Value = Name; 
15	           cmdInfo.DataParameters[2].Value = isAIP; 
16	           //参数赋值,使用命名方式; 
17	           //cmdInfo.SetParameterValue("@Type", Type); 
18	           //cmdInfo.SetParameterValue("@Name", Name); 
19	           //cmdInfo.SetParameterValue("@isAIP", isAIP); 
20	           //执行查询 
21	           return CurrentDataBase.ExecuteDataSet(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText , cmdInfo.DataParameters); 
22	       // 
23	   }//End Function

采用这种技术,DBA可以写高效的有数据库特性的SQL,如果要换数据库,只需要换一个配置文件即可,不需要重写程序。

题外话:

SQL-MAP思想并非PDF.NET数据开发框架独有,实际上,该思想也是从著名的iBatis框架借鉴而来的,但与iBatis不同的是,PDF.NET的SQL-MAP参数不需要定义专门的“参数类”,也不需要写额外的XML文件指明查询结果如何与实体类映射,所以整个开发过程大大简化,简化到你只需要会写SQL语句,就可以写DAL代码。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏技术碎碎念

Mybatis插入数据后返回主键id

20330
来自专栏测试开发架构之路

C语言之控制台读取上下左右方向键指令

首先,可以检测任何按键键值 // 首先,检测任何按键的代码 #include<stdio.h> #include<conio.h> int main() { ...

64370
来自专栏用户画像

mysql模拟题二

  3) MSSQLServer2005Enterprise Edition是哪一种版本?

11460
来自专栏跟着阿笨一起玩NET

sql server递归查询

-----------------------------------------------

82220
来自专栏JavaWeb

Mysql索引原理深入剖析

37230
来自专栏Java3y

Mybatis【配置文件】

映射文件 配置文件和映射文件还有挺多的属性我还没有讲的,现在就把它们一一补全 在mapper.xml文件中配置很多的sql语句,执行每个sql语句时,封装为Ma...

34450
来自专栏岑玉海

sqoop 常用命令整理(一)

  这些内容是从sqoop的官网整理出来的,是1.4.3版本的Document,如果有错误,希望大家指正。       1.使用sqoop导入数据   sqoo...

43660
来自专栏文渊之博

TSQL--临时表和表变量

1. 临时表适用数据量较大的情况,因为临时表可以建立索引 2. 表变量适用于数据较小的情况,表变量只能在定义时创建约束(PRIMARY KEY/UNIQUE)从...

22490
来自专栏文渊之博

索引初探(三)

由于前一篇写的有点匆忙很多地方不是很简单,这一片再描述一些概念和细节。 首先,我们都知道在数据库中的存储分为两种结构,一是堆;二是B树。堆的数据是没有排序也就没...

19490
来自专栏恰童鞋骚年

《T-SQL查询》读书笔记Part 3.索引的基本知识

索引优化是查询优化中最重要的一部分,索引是一种用于排序和搜索的结构,在查找数据时索引可以减少对I/O的需要;当计划中的某些元素需要或是可以利用经过排序的数据时,...

13430

扫码关注云+社区

领取腾讯云代金券