MySQL审核存储过程脚本时SQL片段切分不正确

[sjjian]

版本信息（Version）

main

问题描述（Describe）

MySQL审核存储过程脚本时SQL片段切分不正确

截图或日志（Log）

输入的存储过程脚本如下：

DELIMITER $$

USE `fwdb`$$

DROP PROCEDURE IF EXISTS `_GS_GM_Check`$$

CREATE DEFINER=`root`@`%` PROCEDURE `_GS_GM_Check`(vi_uid INT,vi_pwd VARCHAR(32),vi_ip VARCHAR(100),OUT vo_level INT,OUT vo_code INT)
BEGIN
SET vo_level = 0;
	-- JinSQ 2016-04-08 CHECK IP ADD START
	IF IFNULL(vi_ip,' ')=' ' THEN
		BEGIN
	-- JinSQ 2016-04-08 CHECK IP ADD END
			-- if exists(select 1 from T_GM_Info where f_userid = vi_uid and f_pwd = vi_pwd and F_ip = vi_ip and F_Active = 1) 
			IF EXISTS(SELECT 1 FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active = 1) 
			THEN
				-- select f_level into vo_level from T_GM_Info where f_userid = vi_uid AND f_pwd = vi_pwd AND F_ip = vi_ip AND F_Active = 1;
				SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active = 1;
				SET vo_code = 1000;  -- success
			ELSE
				SET vo_code = 1001;  --  fail
			END IF;	
	-- JinSQ 2016-04-08 CHECK IP ADD START
		END;
	ELSE 
		BEGIN
			IF EXISTS(SELECT 1 FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active = 1) 
			THEN
				SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active = 1;
				SET vo_code = 1000;  -- success
			ELSE
				SET vo_code = 1001;  --  fail
			END IF;	
		END;
	END IF;
	-- JinSQ 2016-04-08 CHECK IP ADD END
    END$$

DELIMITER ;

审核后切分的片段如下：

DELIMITER $$

USE `fwdb`$$

DROP PROCEDURE IF EXISTS `_GS_GM_Check`$$

CREATE DEFINER=`root`@`%` PROCEDURE `_GS_GM_Check`(vi_uid INT,vi_pwd VARCHAR(32),vi_ip VARCHAR(100),OUT vo_level INT,OUT vo_code INT)
BEGIN
SET vo_level = 0;
	-- JinSQ 2016-04-08 CHECK IP ADD START
	IF IFNULL(vi_ip,' ')=' ' THEN
		BEGIN
	-- JinSQ 2016-04-08 CHECK IP ADD END
			-- if exists(select 1 from T_GM_Info where f_userid = vi_uid and f_pwd = vi_pwd and F_ip = vi_ip and F_Active = 1) 
			IF EXISTS(SELECT 1 FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active = 1) 
			THEN
				-- select f_level into vo_level from T_GM_Info where f_userid = vi_uid AND f_pwd = vi_pwd AND F_ip = vi_ip AND F_Active = 1;
				SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active = 1;
				SET vo_code = 1000;  -- success
			ELSE
				SET vo_code = 1001;  --  fail
			END IF;	
	-- JinSQ 2016-04-08 CHECK IP ADD START
		END;

	ELSE 
		BEGIN
			IF EXISTS(SELECT 1 FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active = 1) 
			THEN
				SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active = 1;
				SET vo_code = 1000;  -- success
			ELSE
				SET vo_code = 1001;  --  fail
			END IF;	
		END;

	END IF;

	-- JinSQ 2016-04-08 CHECK IP ADD END
    END$$

DELIMITER ;

如何复现（To Reproduce）

通过快捷审核随意创建一个MySQL的静态审核，输入上面的SQL脚步即可复现

实现方案

无法正确切分存储过程原因：
1、解析器对于一条存储过程语句的匹配规则是根据"BEGIN"和"END"关键字，但是复杂存储过程可以嵌套，解析器无法处理嵌套的情况（https://dev.mysql.com/doc/refman/5.7/en/begin-end.html）
2、如果一条存储过程定义中本身包含默认分隔符";"，所以必须重新定义分隔符，需用到关键字delimiter，但是解析器无法解析该关键字。（https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html）
如：

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

解决方案：
由于嵌套begin end结构的特殊性，嵌套结构时必须使用DELIMITER重新定义分隔符，所以嵌套结构不需要单独处理。
修改解析器方案：
识别DELIMITER关键字，获取自定义的sql语句分隔符，以自定义的分隔符重新分割sql文本

变更影响面

受影响的模块或功能

外部引用的潜在问题或风险

版本兼容性

测试建议