SQL查询安全性验证.docx

1、SQL查询安全性验证SQL查询安全性验证Stefan Brass Christian GoldbergMartin-Luther-Universitt Halle-Wittenberg, D-06099 Halle (Saale), Germany(brass|goldberg)informatik.uni-halle.de, Phone: +49 345 55 24740, Fax: +49 345 55 27333摘要很多程序需要访问关系型数据库中的数据，这一般通过SQL写的查询来实现的。虽然SQL语言是描述式的语言，但是某些运行时错误还是会发生。由于这些错误的出现往往与数据有关，在测试的

2、阶段发现这些错误并不容易。一个查询是否安全的问题可以归结为一致性检查。众所周知，一致性是一般不可判定的，并且这也同样适用于SQL查询。不过，在这篇论文中，我们倡导一个能处理超大SQL子集的一致性检查（它运用有序Skolem方法来Skolemization，同时还有一些其它技巧）。这种一致性检查也是用来产生其它语义警告的基础。进一步的，它可以用来产生SQL查询的测试数据。主题词：模式构建，信息系统，静态分析，软件质量关键字：SQL，运行时错误，一致性1. 绪论当今我们所开发的软件可能有很大比率是要使用存储于关系型数据库中的数据的。这些数据库通常是通过SQL语句来进行访问的，特别是查询。虽然SQL

3、语言是描述式语言，根据数据不同，会有错误在运行时发生的情况是存在的。一个典型的例子就是在嵌入式SQL（包括SQLJ等）中的“SELECT-INTO”：SELECT S.EMAILINTO :EFROM STUDENTS SWHERE S.FRAME = :FNAND S.LNAME = :LN假设有一个表STUDENTS具有SID（唯一学生编号，主键），FNAME（名字），LNAME（姓氏），和EMAIL等字段。如果这个表有两行满足条件（比如两个学生具有相同的姓名），这个查询在执行的时候一个运行时错误（异常）就会发生。由于这个错误可能是无法预料的，它将会无法正确进行处理，并且显示给用户的错误信

4、息也不会有帮助。如果我们想要开发高品质的软件，我们必须保证上述错误永远不会发生。在例子中，如果“FNAME，LNAME”被声明为表的次要键，上述查询就安全了。当然，此时插入具有相同姓氏和名字的两个同学就变成不可能的了（由于这种情况在现实中确实存在，就必须要使用另一种鉴别方法了）。注意，如果一个程序员错误的认为姓氏和名字是唯一的，至少手工开发出来的测试数据自然会符合条件，同时错误在测试阶段也就无法被检测到了。所有关于数据的假设都必须强制使用约束。一些数据库接口（比如JDBC）是不支持“SELECT-INTO”的：那么，人们必须要获取一个结果集才行，所以这种错误也就不会发生了。但是如果一个程序员认

5、为结果集是一个单条数据的话，他/她一般不会去使用循环。在结果集里边如果可能有多余一个的元组，一个警告肯定会比较有用，但是程序只编写了对第一个元素的访问代码。SQL中不依赖于接口的运行时错误请见第2章。让我们回到“SELECT-INTO”的话题，是否会发生异常的问题可以归结到一个一致性检查。在例子中，我们必须检查是否SELECT *FROM STUDENTS S1, STUDENTS S2WHERE S1.FNAME = :FNAND S1.LNAME = :LNAND S2.FNAME = :FNAND S2.LNAME = :LNAND S1.SID S2.SID这个查询应该会返回一个非空结

6、果。如果是的话，上述语句中数据库实例就会产生应答来提供一个导致原查询异常的例子。然而如果不是，上述查询总是返回一个空结果（例如条件不符），原查询就是安全的。众所周知，一致性公式在先序逻辑中是不可判定的，这也同样适用于数据库查询。例如，人们可以写一个用来检查数据库是否具有解决邮局通信问题的方案的查询（不包括数据类型操作），见1，第6.3节。不仅如此，还有很多SQL子集的解决方案。对于比较的组合（包括密集域），看来方法8是比较先进的。在本文中（第3章），我们展示了很多处理子查询如何扩展的例子。我们同时也处理了NULL值问题（SQL为它们使用了三值逻辑）。通过这种方法，非常大的SQL子集的一致性就可

7、判定了。我们的方法是基于被广泛了解的Skolemization的，但是需要进一步的技巧来尽可能保持Herbrand Universe有限。运行时错误在第2章中简要讨论。除了这些安全问题之外，还有更多例子，它们在应该为陌生或可能没有预计到的SQL查询产生警告的地方，或者至少子最优解，见2。本文提出的一致性检查是很多SQL质量测试的子程序。我们正在开发一个SQL语义检查器（叫做SQLLint），见：http:/dbs.informatik.uni-halle.de/sqllint/当前的原型版本包含了文中描述的一致性检查。最后，为了测试数据库应用程序，必须生成一些有趣的测试数据。这个问题在文献中似

8、乎不经常被讨论到，这个13是个显著的例外。我们的一致性检查也同时可以生成测试数据，比之于13，没有必要事先定义每个表需要多少行。2. SQL中常见的运行时错误正如绪论中分析的，如果INTO语句是用来将查询的结果存储到程序变量，查询绝对不能返回超过一行。假设给定查询SELECT t1, . . ., tkINTO v1, . . ., vkFROM R1 X1, . . ., Rn XnWHERE 为了确保绝对没有两个方案，我们重写元组变量副本并且检查如下查询的一致性。如果是一致的（包括约束条件，第3.3节），一个运行时错误就会发生，构造模型给出例子如下：SELECT *FROM R1 X1,

9、., Rn Xn, R1 X1, ., Rn XnWHERE AND AND (X1 X1 OR . OR Xn Xn)公式是将中的所有Xi替换为Xi得到的。我们用X1 X1作为这两个元组主键值不相等的缩写（我们假设永远不为NULL）。如果关系Ri中的一个没有声明的键，具有多个方案的情况就总是有可能的（如果条件是一致的）。如果给定的查询使用了“SELECT DISTINCT”，就需要加一个结果元组不同的检测：(t1 _= t1 OR . OR tk _= tkOR t1 IS NULL AND t1 IS NOT NULLOR t1 IS NULL AND t1 IS NOT NULL. .

10、.OR tk IS NULL AND tk IS NOT NULLOR tk IS NULL AND tk IS NOT NULL)同样的问题发生在当查询中含有A = (SELECT .)的情况：只要子查询作为标量表达式的时候，它就绝不可以返回多行。如果子查询是不相关的（例如没有从外部查询中访问元组变量），我们就用与上边相同的测试即可。如果查询是相关的，它可能就无法完整的知道需要从外部查询中使用什么信息（作为通常的运行时错误，量化顺序在这里就变得很重要了）。如果要求安全的话，我们假设忽略外部条件，让子查询具有如下形式SELECT tFROM R1 X1, . . ., Rn XnWHERE 如

11、果它从外部查询中访问了元组变量S1 Y1, . . ., Sm Ym，我们就需要如下查询是一致的（在添加约束之后）：SELECT *FROM R1 X1, ., Rn Xn, R1 X1, ., Rn Xn, S1 Y1, ., Sm YmWHERE AND AND (X1 X1 OR . OR Xn Xn)更多的运行时异常如下：1. 使用SELECT-INTO或者在相关结果列可能为空的情况下使用没有指示符变量的FETCH。2. 当字符串不具备数值格式时，字符串转化为数值的类型转换错误。3. 数据类型操作可能导致常见问题（如，被零除）。注意，由于在将来版本的DBMS中的量化序列有可能会改变，在

12、本例中这种依赖于测试的方法是行不通的。在本文中，只涉及到了查询。当然，如果违反约束，更新也可能产生运行时异常。但是，这种语句本身通常就是不安全的，这个例子就更加复杂了，必须要考虑程序代码上下文了（例如在更新前检查完成）。3. 不一致性条件在本章中，我们给出了一个探测SQL查询中不一致条件的算法。有一个很小的不一致查询的例子SELECT * FROM R WHERE A=1 AND A=2。这个的问题是一般不可判定，所以我们只能处理所有查询的一个子集。然而我们的算法充分强大，能够判定很大量级的查询的一致性。精确起见，数据库一致性意味着要有一个有限模型，例如关系数据库实例（有时称作database

13、 instance），使得查询结果不为空。在本文中，我们假设给定的SQL查询不包含数据类型操作，比如说所有的原子公式都是t1 t2的形式，此处是比较操作符（=, , , , =），t1和t2是属性（可能是元组变量条件）或常量（字面量）。NULL值和IS NULL在第3.5节中进行讨论，在此之前，不考虑它们。聚合和LIKE条件在本文中没有涉及到，它们是我们将来的研究课题。3.1 无子查询条件如果查询不包含子查询，一致性就可以通过文献中的已知方法判定，特别是Guo，Sun和Weiss的算法8。此时条件由上述AND、OR和NOT连接的原子公式构成。我们先推导否定的原子公式，也就是简单的反转比较操作符

14、。这样，NOT就从公式中被淘汰出去了。然后，我们把公式用分离范式进行转化：1 . n当且仅当i一致的时候一致。现在一个原子公式的联合就可以测试是否满足8的方法了。他们基本上是要创建一个节点被标记为“Tuplevariable.Attribute”（可能是一个用=标记的等价式的代表）并且边用或标记的有向图。他们为每一个节点计算可能值的间隔。注意像NUMERIC(1)这样的SQL数据类型也会限制可能值的间隔。不幸的是，如果只有有限个数量的能够赋值给节点的数值，节点间的不等条件（t1 t2）就变的很重要，并可被编码为图着色问题。因此如果有很多条件，我们就不能期待有效的算法了。否则，8的方法就很快。（

15、可是，在8之前我们所做的DNF转化工作就可能呈指数增长。）3.2 子查询为简单起见，我们只考虑EXISTS子查询情况。其他的子查询（IN,=ALL,等等）可以被沦为EXISTS中。例如，Oracle中将会在执行查询优化前进行查询的重写。我们假设在没有概念性上的损失的基础上每个出现的元组变量拥有不同的名称。首先我们来将变量划分为任意和存在的，如果查询可以转化为前束范式则可以根据它们元组关系演算进行区分（或）：定义1 对于给定的查询Q，对于一个给定的元组变量，在调用时若在主查询语句或者在一个内嵌于奇数（包含零）个NOT的子查询的FROM语句中指明，否则是通用的。例1 在这些例子中，我们利用一个包含

16、课后练习学生成绩的数据库模型，包含以下三种关系： STUDENTS(SID, FNAME, LNAME, EMAIL) EXERCISES(ENO , TOPIC) GRADES(SIDSTUDENTS,ENO EXERCISES, POINTS)下列的SQL查询列举出在所有考试中或者满分的同学信息：SELECT S.FNAME, S.LNAMEFROM STUDENTS SWHERE NOT EXISTS(SELECT *FROM EXERCISES EWHERE NOT EXISTS(SELECT *FROM GRADES GWHERE G.SID = S.SIDAND G.ENO = E

17、.ENOAND G.POINTS = 10)S和G是存在的元组变量，E是一个任意的元组变量。我们的算法将会寻得一种模型使得EXERCISES表为空。由于这种模型不是十分典型，并且应该自动的添加查询条件，使得在每一种关系中至少存在一个元组。由于例子的缺失，这里假设至少有两个练习，每个都用数字标识，并且添加（在WHERE语句尾部）AND EXISTS (SELECT *FROM EXERCISES E1WHERE E1.ENO = 1)AND EXISTS (SELECT *FROM EXERCISES E2WHERE E2.ENO = 2)现在E1和E2是假定存在的元组变量在自动定理中证明到（参

18、见，例子4）：比较普及的是通过引入Skolem常量和Skolem方法来消除存在量词。也就是说元组中指定的名称必须存在。对于在全称量词范围中没有包括的元组变量（例如本例中的S，E1和E2）来说，数据库实例使用一个元组就足够了。然而对于元组变量G来说，它已经在一个全局的元组变量E中进行了声明，则需要一个不同的元组对于E中的每个值。并且，fG函数作为一个函数为E赋值，并返回值给G。这种方法被称作Skolem方法，正常来说，Skolem方法还有fS，fE1和fE2，但是这些方法是无参的（他们是Skolem常量）。精确地来说，参数Y作为Skolem方法fX元组变量的X所必须具备：定义2 一个既定的元组变

19、量X依赖于一个任意元组变量Y ，当且仅当：1X的声明在Y的作用域中2Y存在X被声明的子查询中（包括可能内嵌的子查询）第二个条件不是必须的，但是它将参数的数量，这将会帮助我们处理更多的查询。相对于自动理论验证的古典例子，我们使用了“排序”逻辑：每一个元组变量只能仅在一个特定关系内有效。而且，我们的Skolem方法具备参数和结果类型。例如，例子中的fG方法，假定EXERCISES关系中的元组是被给定的，GRADES关系将会返回一个元组。定义3 对于给定的查询Q，有序的Skolem常量和函数SQ构造如下：对于每个存在于关系R上的元组变量X，让 Y1，Yn都是任意的元组变量，X依赖于此，并让Yi在关系

20、Si的范围上，那么fX有n个变量，排序为S1，Sn。在本例中，具有三个Skolem常量和一个Skolem方法： fS：STUDENTS， fG:：EXERCISESGRADES， fE1:：EXERCISES ， fE2:：EXERCISES，定义4 对于给定的查询Q和关系R，让TQ(R)作为有序R的集合，可以从常量和SQ的方法标志进行构建。使得TQ是TQ(R)的并集，对于在Q中的关系标志R来说：TQ是一种Herbrand Universe，在例1中， TQ (STUDENTS)=fS， TQ (EXERCISES)=fE1，fE2， TQ (GRADES)=fG(fE1)，fG(fE2)。当

21、然，通常来说，无限多组可以被构建，如果无法确定模型（数据库实例）的大小，我们的方法是不适用的。然而，我们在3.4节中可以证明在不同的Herbrand Universe无限元组元素具有相同的元组。但是首先值得注意的是一个无限的Herbrand Universe，它需要至少一个内嵌的NOT EXISTS子查询（否则只有Skolem常量会产生，不是真正的方法），这种只有一级NOT EXISTS子查询的情况对应量词为前缀，可知的是否满足第一顺序逻辑是可界定的（这在1928年Bernays和Schnfinkel进行了验证）。然而，作为展示，我们的方法有时可以处理在前缀下的子查询结构：由于使用了排序逻辑，

22、Skolem组集合不是必要无限的，尤其是在合适的方法标志的情况下。一旦我们得知每个关系的必须的元组数量，我们可以轻松地为一致性测试减少一般情况（用子查询），就像8中指定的一个简单公式。（看3.1节）：定义5 对于给定的查询Q，TQ是有限的，通常情况下WHERE语句构造如下：1在主查询中，用相应的Skolem常量fX替代每个元组变量X2其次，对于子查询内嵌入一个奇数个NOT：用() AND NE R1 AND . AND NE Rn)替代子查询EXISTS (SELECT .FROM R1 X1 , ., Rn XnWHERE )这里的子条件用fXi(Yi, 1,.,Y i,mi).替换每一个存

23、在的元组变量Xi，这里的Yi, 1,.,Y i,mi都是全局的元组变量。3最后对于具有奇数个否定句的子查询来说：用(1() OR . OR k()AND NE R1 AND . AND NE Rn )代替子查询EXISTS (SELECT .FROM R1 X1 , ., Rn XnWHERE )这里的i是子条件，用于映射变量Xj到TQ(Rj)中的一个组。这里k=0是可能的，在这种情况下，空析取可能被写成1=0（错误的）。假设NE_Ri(“Ri不为空”)只有在异常的情况下需要，如上面的例2所说。一个例1的简单的分析说明在例子中他们是不需要的（不存在元组变量被引进到析取的上下文）。而且，让我们这

24、个例子的简化形式，在NE_Ri未知的情况下。我们首先用fS替换S，fE1替换E1，fE2替换E2，fG(E)替换G。用于E是EXERCISES类型，fE1和fE2是TQ（EXERCISES）的仅有元素，析取由两个被fE1和fE2代替的E组成，分别如下：SELECT fS.FNAME, fS.LNAMEFROM STUDENTS S- 用fS替换WHERENOT(EXISTS(SELECT *FROM EXERCISES E- fE1的版本 WHERE NOT EXISTS(SELECT *FROMGRADES G- fG(fE1)WHERE fG(fE1).SID = fS.SIDAND fG

25、(fE1).ENO = fE1.ENOAND fG(fE1).POINTS = 10) OREXISTS(SELECT *FROM EXERCISES E- version for fE2WHERE NOT EXISTS(SELECT *FROM GRADES G - fG(fE2)WHERE fG(fE2).SID = fS .SIDAND fG(fE2).ENO = fE2.ENOAND fG(fE2).POINTS = 10)AND EXISTS(SELECT *FROM EXERCISES E1- 用fE1替换WHERE fE1.ENO = 1)AND EXISTS(SELECT *F

26、ROM EXERCISES E2- 用fE2替换WHERE fE2.ENO = 2)通常来说，我们直接构建上述查询的正常格式。在这个格式中，SELECT/FROM语句和EXISTS/WHERE关键字被移除：NOT( NOT( fG(fE1).SID = fS .SIDAND fG(fE1).ENO = fE1.ENOAND fG(fE1).POINTS = 10)OR NOT( fG(fE2).SID = fS.SIDAND fG(fE2).ENO = fE2.ENOAND fG(fE2).POINTS = 10)AND (fE1.ENO = 1)AND (fE2.ENO = 2)这在逻辑上等

27、价于fG(fE1).SID = fS.SIDAND fG(fE1).ENO = fE1.ENOAND fG(fE1).POINTS = 10AND fG(fE2).SID = fS .SIDAND fG(fE2).ENO = fE2.ENOAND fG(fE2).POINTS = 10AND fE1.ENO = 1AND fE2.ENO = 2一个模型（数据库实例）如下所示（有任意的SID和开放表空间的任意的值）：如例子中所说，构造一个数据库实例以正常的查询模型中查询出一个结果是可能的。数据库实例为关系R中的每一个TQ(R)组（无其他元组）提供一个元组。两个构造的元组可能是完全相同的（即：TQ

28、(R)中的元组比元素少），在异常例子中，关系R必须为空，尽管TQ(R)是非空的，这种情况也是可能发生的。例2 和标准预测逻辑相反，SQL中的域可能为空。试想一下：SELECT * FROM DUMMY WHEREEXISTS (SELECT * FROM R WHERE 1=2)OR NOT EXISTS (SELECT * FROM R)由于R可以为空，SQL是一致性的。在这种情况下，NE_R是构造需要的（因为第一个子查询，我们应当生成一个R类型的Skolem常量，但是NE_R允许无效或有效的移除它）。原理1 使得给定的查询Q，TQ是有限的，Q是一致的当且仅当Q的平面形式是一致的。更多根据，

29、参考：http:/dbs.informatik.uni-halle.de/sqllint/ccheck05.pdf3.3 完整性约束上述算法构建了任何查询模型，不必须匹配所有约束的数据库实例。但是，向确保所有约束都匹配的查询中添加条件是很容易的。考虑GRADES上的这个约束：CHECK(POINTS = 0)那么如下条件就应该添加到参照GRADES的每个查询中：AND NOT EXISTS (SELECT * FROM GRADES WHERE NOT(POINTS = 0)当且仅当这个扩展查询一致时，原查询关于约束一致。注意，纯“所有”约束如键或CHECK约束不需要嵌套子查询，所以千万不要危

30、害到方法的终结。没有新的Skolem方法被构建，条件仅为每个存在的各自排序（关联）的Skolem条件一致。这也正是人们直观上所期待的。然而外键要求特定的元组存在，因此可能有时在一个无限集合TQ中。这是下一小节的课题。3.4 限制与可行的解决方案我们这个方法的主要限制是集合TQ必须是有限的，例如，没有关联R上的元组变量能直接或间接依赖相同关联R上的元组变量。当只有一级子查询的时候，这刚好能够满足。然而，GRADES有一个关联EXERCISES的外键ENO。这可能会通过添加如下条件到所有查询来强制模型：AND NOT EXISTS (SELECT * FROM GRADES C WHERE NOT EXISTS (SELECT * FROM EXERCISES P WHERE P.ENO = C.ENO)我们现在得到一个Skolem方法fP : GRADES EXERCISES。就其本身而言，这是没有问题的，如果外键不是循环的且查询自身只包含一级NOT EXISTS就确实永远不会有问题。但是在例子1中，查询引入了Skolem方法fG : EXERCISES GRADES。综上，我们现在生成了无穷多条件：fE1，fG(fE1)，fP(fG(fE1)，fG(fP(fG(fE1)，等等。然而，很容易证明fP(fG(fE1) = fE1：fG(fE1).ENO = fE1.ENO