SQL审核工具介绍

d3fw 9年前

1.背景

现阶段的业务变更过程是由运维提单,再由DBA对这些 SQL逐句进行审核,审核通过后再由运维执行。由于单据的多样性与复杂性(部分单据大于1M),不止消耗DBA的大量时间精力,也让审单无法面面俱到,从 而可能执行了非法的SQL导致变更时间延长,影响业务正常上线造成了损失。

SQL审核工具(tmysqlparse)用于对MySQL的SQL语句进行语法分析,判定语法正确性及检测高危告警。通过将tmysqlparse集成到凯丽/GCS系统中,可以降低人工审单的难度及减少其工作量,从而实现审单的自动化。

在tmysqlparse集成凯丽/GCS系统中后,运维的提单就可由SQL审核工具自动进行语法检查及高危告警处理,以保证提交语法正确的单据到现网服务器中。

2.功能

tmysqlparse工具能够正确解析基于MySQL 5.5的SQL语法,并尽量兼容MySQL 5.0、MySQL 5.1。当前已解决不同版本间因保留字用法不同导致的语法差异。关于保留字的差异性详见KM文章http://km.oa.com/group/mysql_internals/articles/show/131384

2.1 主要功能

   tmysqlparse工具主要用于检测SQL(针对MySQL)语句是否存在语法问题,SQL语法标准参照MySQL 5.5,tmysqlparse能够正确的分析基于MySQL 5.5中的语法。并在保留字的差异上兼容MySQL 5.0,MySQL 5.1的语法。下面语句是现网部分出错单据示例,都能够通过tmysqlparse检测出语法错误。

  1. delete * from t1;
  2. alter table tbTipStrats Add column sPicture char(256) NOT NULL default '';
  3. create database d_rose_dailyglobal_limit if not exists;
  4. create table tbRestoreInfo(

    iID bigint(12) unsigned NOT NULL AUTO_INCREMENT primary key,

    iWorldId int(6) null default 0,

    iUin bigint(12) not null,

dtBanTime datetime null default now(),

    dtCreateTime datetime null default now(),

       dtUpdateTime datetime null default now(),

        iRsFlg int(2) datetime not null default 0,

        iSource int(2) not null default 0,

    vMemo varchar2(100),

    key 'iWorldId'(iWorldId),

    key 'iUin'(iUin),

);

  1. select read_only, read_write, `general`, slow_ from t1 where maxvalue>10 and linear<'a' and "resignal" = 'a';

  其中示例语句(5)在MySQL 5.0的版本中是没有语法错误的。上述都是一些普通的语法错误,还有些比较极端错误比如编码问题。

  tmysqlparse工具还提供对一些高危操作的告警。在现网中,比如删表、删库操作都可能造成数据损失,造成重大事故。为此,对于这类操作更好是检测出来并进一步确认。初步定义的高危操作如下,后续可以进一步补充。对于下列的操作都可以正确测试出。

STMT_DROP_DB  删除数据库操作

STMT _DROP_TABLE  删除表操作

STMT _DROP_VIEW 删除视图操作

STMT _TRUNCATE 清空表操作

STMT _DELETE 删除操作不带where条件

STMT _UPDATE 更新操作不带where条件

STMT _CREATE_TABLE 创建表时blob/text字段数大于10

STMT _ALTER_TABLE 更改表增加的blob/text字段数大于10

2.2 输入/输出

  tmysqlparse提供两种输入方式:从终端输入或者文本输入。文本输入可以通过./tmysqlparse < xxx.sql 。xxx.sql即为输入的文件。tmysqlparse还提供一些参数,详见如下:

  ./tmysqlparse  -f xxx.xml; 指定输出结果到xxx.xml中

  ./tmysqlparse  -v version;version是MySQL版本号,如”5.0”

  ./tmysqlparse  -V/--version; 输出tmysqlparse的版本信息

  ./tmysqlparse –help;提供帮助信息

  ./tmysqlparse test; 指定数据库名为test

示例命令: ./tmysqlparse –f xxx.xml test –v “5.1” < xxx.sql

tmysqlparse以xml的形式输出SQL检查的结果,通过如下定义

<result></result>中包含tmysqlparse分析后的所有结果;

<syntax_failed></syntax_failed>包含所有语法出错的信息;

<failed_info></failed_info>包含一条出错语句,里面再 分<sql>、<error_code>、<error_msg>和<line>四部分来输出出错 SQL语句的信息。

<risk_warnings></risk_warnings>包含所有的高危告警信息,产生告警的前提是语法正确,与<syntax_failed></syntax_failed>互相独立,不存在交集。

<warning_info></warning_info>包含一条产生高危告警的SQL语句信息;<type>、<name>、<text>和<line>四部分给出告警SQL语句信息。

<info></info>则存储额外的信息

<type>中包含是产生告警的类型,分为如下几种:

STMT_DROP_DB  删除数据库操作

STMT _DROP_TABLE  删除表操作

STMT _DROP_VIEW 删除视图操作

STMT _TRUNCATE 清空表操作

STMT _DELETE 删除操作不带where条件

STMT _UPDATE 更新操作不带where条件

STMT _CREATE_TABLE 创建表时blob/text字段数大于10

STMT _ALTER_TABLE 更改表增加的blob/text字段数大于10

下图为输出结果的截图,以xml格式输出有助于对结果进行解析。

      SQL审核工具介绍

 

2.3 C/C++ API

tmysqlparse不仅仅是一个SQL审核工具,也提供C/C++调用的 API函数。选取几个函数作为示例:

parse_global_init();     全局初始化函数

parse_result_init();     查检结果初始化函数

query_parse();         语法分析函数

parse_result_destroy();  结果销毁函数

parse_global_destroy();  全局释放函数

下面示例一个实际程序,展示如何调用组件提供的API

int main(int argc, char **argv)

{

    parse_result_t pr;

    char buf[1024];

    int i;

    /* 初始化全局数据 */

    parse_global_init();

    /* 初始化parse_result结构 */

    parse_result_init(&pr);

    while(1)

    {

        fprintf(stdout, "please input a query:\n");

        if (fgets(buf, MAX_BUF_SIZE, stdin) == NULL)

        {

            fprintf(stderr, "fgets error\n");

            break;

        }

        if (buf[strlen(buf) -1] == '\n')

            buf[strlen(buf) - 1] = '\0';

        if (strcmp(buf, "exit") == 0)

            break;

        /* 语法分析 */

        if (query_parse(buf, &pr))

            printf("query_parse error: %s\n", pr.err_msg);

        else

        {

            printf("%s :\n", parse_result_get_stmt_type_str(&pr));

            for ( i = 0; i < pr.n_tables; ++i)

                     printf("dbname:%s,tablename:%s\n",pr.table_arr[i].dbname,pr.table_arr[i].

tablename);

            printf("\n");

        }

    }

    parse_result_destroy(&pr);

    parse_global_destroy();

    return 0;

}

通过上述程序,即可完成对SQL语句的语法分析。

3.原理

tmysqlparse工具主要是通过修改MySQL 5.5的源代码实现。基本思路是分离出MySQL源代码中的语法分析模块与client处理模块。MySQL源码的各代码模块间比较耦合,所以会有大量的细节要处理。

对于终端部分,主要是对SQL文本的处理,即如何将字符串分隔成完整的SQL语句。其中主要考虑的是字符串的处理及如何断句,比如如何处理分号,处理 delimiter, 如何处理注释,处理分号的问题。对于同时执行多条SQL语句,client应该如何与server进行交互。

语法处理部分,也是从MySQL 5.5 源码中抽离出来的。主要抽取sql_parse这一部分涉及的代码,更需要注意的其中的涉及的诸多变量的初始化定义,还有各种内存的释放等等。

对于保留字的问题,如果某SQL语句测试出是保留字出错,而当前处理的版本是5.0,则可以对该SQL语句进行封装处理,将保留加个反引号,再次执行该SQL来解决MySQL 5.5对5.0及5.1在保留字上的语法差别。

另外,通过对SQL语句的完整分析,可以准备得到SQL语句的类型及涉及的表、库及其它关系。就可以从中获取高危语句的的信息,从而输出以示告警。

4.测试

测试结果详见《SQL审核工具测试结果》

5.应用示例

  凯丽/GCS系统当前已集成了tmysqlparse工具,下图为应用tmysqlparse工具了,实际应用效果。     SQL审核工具介绍

     图 1 凯丽系统中应用tmysqlparse检测出语法错误

 

SQL审核工具介绍

 

 

 

 

 

     图表 2 点击语法错误信息后所示

来自: http://tencentdba.com/blog/sqlparse/