ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 老虎SQL问题答案之漏洞

老虎SQL问题答案之漏洞

原创 Linux操作系统 作者:yangtingkun 时间:2009-03-17 17:10:09 0 删除 编辑

老虎者,坛主也,专数学,善计算,众人皆称能。

某日示考题,然观其解,现微隙二三。以文记之。

 

 

模仿古文太累了,还是说普通话吧。

老虎在论坛里面发了一篇文章,原文如下:

数据库课,开始的部分讲sql,讲到create table和约束,在虎吧上给他们留了一道思考题,是从那本著名的SQL Puzzles and Answers里找的一道谜题,在原书上的解答其实是有错误的

请建立一新表,包含三个列,全部都是varchar2(5)数据类型

要求给这个表上加上合适的约束,使

第一个列只能输入不含字母的字符串(就是全部由数字或特殊符号等组成),第二个列只能输入包含字母的字符串,第三个列只能输入全部是字母的字符串

写出相关的create table语句

结果很快有一同学就解答出来了,并且把原书的错误也纠正了

随后给出了问题的答案:

这是他给的原始答案

create table x (a varchar2(5) check (upper(a)=a and lower(a)=a), b varchar2(5) check (not upper(b)=b or not lower(b)=b), c varchar2(5) check (length(translate(c,' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '))=0));

这个答案还存在一些瑕疵
1
每个列上还要给not null约束,他后来自己也很快发现这个问题并且指出纠正
2
第一个check其实可以简化为upper(a)=lower(a),第二个check可以简化为upper(b)<>lower(b)
3
第三个check使用的translate函数是神来之笔,原书上这个地方是错的。但translate的通用性不好,最好有通用性更好的办法

对于老虎的这个答案,显然还存在很多问题。根据第三个问题的答案分析,老虎这里的字母指的是英文字母,否则第三个问题的答案就是不完整的。

那么下面就按照英文字母来理解题中所提到的字母,当然字母代表英文字母也是绝大部分情况下正常的理解。

其实第一题和第二题的答案思路是一样的,通过UPPERLOWER函数是否相等,来判断一个字符是否英文字符。这种方法是很不严谨的:

SQL> SELECT UPPER(CHR(42660)), LOWER(CHR(42660)) FROM DUAL;

UP LO
-- --
Δ δ

这是一个最简单的例子,要想知道不满足这个条件的结果有多少,可以执行下面的PL/SQL代码:

SQL> SET SERVEROUT ON SIZE 1000000
SQL> BEGIN
  2   FOR I IN 1..65535 LOOP
  3    IF UPPER(CHR(I)) != LOWER(CHR(I)) AND LENGTH(CHR(I)) = 1 THEN
  4     DBMS_OUTPUT.PUT_LINE(I || ':' || CHR(I));
  5    END IF;
  6   END LOOP;
  7  END;
  8  /

当然这里面大部分的结果是一个英文字母加上一个无法显示的字符,但是既然可能存在这种现象,作为标准答案就可以考虑。何况,还有上面例子给出的希腊字符在内的多种国家的字母。

凭经验或主管判断利用UPPERLOWER来判断是否是英文字符的方法存在很多的不确定性,除非像上面一样验证过所有的字符UPPERLOWER的输出结果。

其实对于这种问题,可以利用LTRIMREPLACETRANSLATE等函数来判断,当然对于高版本也可以使用正则表达式来进行判断。所有上面这些方法的共同点是在函数中明确指出了所有的可能性,比如利用LTRIM的方法:LTRIM(UPPER(C), ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’) IS NULL

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-571233/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10541839