Wednesday, February 4, 2009

MySQL Stored Procedures And Functions for Subnet Check

写了几个mysql 的sp和func,检测IP段
好久没写过sp了,上次写的时候,还是两年前了
远程进了学校,上服务器拿了库里的sp代码,看了看,便开始写了
function是第一次写,是写完sp之后,改写的,只是有个sp实在没辙,两个返回值,没找到合适的手段用function实现,只能用sp了


版本1 - sp: ====================================================
DELIMITER $$
-- ip_parser: split ip/cidr with /
-- param: IN ip_with_cidr VARCHAR(18) 输入的IP/CIDR数据
-- param: OUT ip VARCHAR(15) 输出的IP
-- param: OUT cidr VARCHAR(2) 输出的cidr
DROP PROCEDURE IF EXISTS ip_parser;
CREATE PROCEDURE ip_parser(IN ip_with_cidr VARCHAR(18), OUT ip VARCHAR(15), OUT cidr VARCHAR(2))
BEGIN
-- 正则匹配有cidr的数据
IF (SELECT ip_with_cidr REGEXP '^([0-9]{1,3}\.){3}[0-9]{1,3}\/[0-9]{1,2}$')
THEN
SELECT substring_index(ip_with_cidr, '/', 1) INTO ip;
SELECT substring_index(ip_with_cidr, '/', -1) INTO cidr;
-- 如果没有cidr则直接赋值32
ELSEIF (SELECT ip_with_cidr REGEXP '^([0-9]{1,3}\.){3}[0-9]{1,3}$')
THEN
SELECT ip_with_cidr INTO ip;
SELECT '32' INTO cidr;
-- 否则全部置0即允许全部
ELSE
SELECT 0 INTO ip;
SELECT 0 INTO cidr;
END IF;
END; $$


-- 判断提交的IP是否属于提交的ip段
-- param: IN ip VARCHAR(15) 目标IP
-- param: IN ip_with_cidr VARCHAR(18) 目标IP段
-- param: OUT ret TINYINT(1) 返回值
DROP PROCEDURE IF EXISTS in_subnet;
CREATE PROCEDURE in_subnet(IN ip VARCHAR(15), IN ip_with_cidr VARCHAR(18), OUT ret TINYINT(1))
BEGIN
CALL ip_parser(ip_with_cidr, @t_ip, @t_cidr);

CALL get_subnet(@t_ip, @t_cidr, @subnet);
CALL get_subnet(ip, @t_cidr, @test_subnet);
-- 分别计算两个子网号,然后比较
SELECT @subnet = @test_subnet INTO ret;
END; $$

-- 根据提交的IP和CIDR返回子网号
-- param: IN ip VARCHAR(15) 目标IP
-- param: IN cidr VARCHAR(2) 目标CIDR
-- param: OUT subnet_addr VARCHAR(15) 返回的子网号
DROP PROCEDURE IF EXISTS get_subnet;
CREATE PROCEDURE get_subnet(IN ip VARCHAR(15), IN cidr VARCHAR(2), OUT subnet_addr VARCHAR(15))
BEGIN
-- 将cidr转换成子网掩码
CALL cidr2mask(cidr, @mask);

CALL ip2long(ip, @ip_ints);
CALL ip2long(@mask, @mask_int);
-- 用IP和子网掩码按位与,生成子网号
CALL long2ip(@ip_ints & @mask_int, subnet_addr);
END; $$

-- 将cidr转换成mask,懒得用字符串处理,就直接case when了,看着还清楚些
-- param: IN cidr VARCHAR(2) 输入的cidr
-- param: OUT mask_addr VARCHAR(15) 输出的子网掩码
DROP PROCEDURE IF EXISTS cidr2mask;
CREATE PROCEDURE cidr2mask(IN cidr VARCHAR(2), OUT mask_addr VARCHAR(15))
BEGIN
CASE cidr
WHEN '0' THEN SELECT '0.0.0.0' INTO mask_addr;
WHEN '1' THEN SELECT '128.0.0.0' INTO mask_addr;
WHEN '2' THEN SELECT '192.0.0.0' INTO mask_addr;
WHEN '3' THEN SELECT '224.0.0.0' INTO mask_addr;
WHEN '4' THEN SELECT '240.0.0.0' INTO mask_addr;
WHEN '5' THEN SELECT '248.0.0.0' INTO mask_addr;
WHEN '6' THEN SELECT '252.0.0.0' INTO mask_addr;
WHEN '7' THEN SELECT '254.0.0.0' INTO mask_addr;
WHEN '8' THEN SELECT '255.0.0.0' INTO mask_addr;
WHEN '9' THEN SELECT '255.128.0.0' INTO mask_addr;
WHEN '10' THEN SELECT '255.192.0.0' INTO mask_addr;
WHEN '11' THEN SELECT '255.224.0.0' INTO mask_addr;
WHEN '12' THEN SELECT '255.240.0.0' INTO mask_addr;
WHEN '13' THEN SELECT '255.248.0.0' INTO mask_addr;
WHEN '14' THEN SELECT '255.252.0.0' INTO mask_addr;
WHEN '15' THEN SELECT '255.254.0.0' INTO mask_addr;
WHEN '16' THEN SELECT '255.255.0.0' INTO mask_addr;
WHEN '17' THEN SELECT '255.255.128.0' INTO mask_addr;
WHEN '18' THEN SELECT '255.255.192.0' INTO mask_addr;
WHEN '19' THEN SELECT '255.255.224.0' INTO mask_addr;
WHEN '20' THEN SELECT '255.255.240.0' INTO mask_addr;
WHEN '21' THEN SELECT '255.255.248.0' INTO mask_addr;
WHEN '22' THEN SELECT '255.255.252.0' INTO mask_addr;
WHEN '23' THEN SELECT '255.255.254.0' INTO mask_addr;
WHEN '24' THEN SELECT '255.255.255.0' INTO mask_addr;
WHEN '25' THEN SELECT '255.255.255.128' INTO mask_addr;
WHEN '26' THEN SELECT '255.255.255.192' INTO mask_addr;
WHEN '27' THEN SELECT '255.255.255.224' INTO mask_addr;
WHEN '28' THEN SELECT '255.255.255.240' INTO mask_addr;
WHEN '29' THEN SELECT '255.255.255.248' INTO mask_addr;
WHEN '30' THEN SELECT '255.255.255.252' INTO mask_addr;
WHEN '31' THEN SELECT '255.255.255.254' INTO mask_addr;
WHEN '32' THEN SELECT '255.255.255.255' INTO mask_addr;
-- 不合法的全都用 255.255.255.255
ELSE SELECT '255.255.255.255' INTO mask_addr;
END CASE;
END; $$

-- 将IP转换为long int
-- param: IN ip VARCHAR(15) 输入IP
-- param: OUT ip_int INTEGER(12) UNSIGNED 输出整数
DROP PROCEDURE IF EXISTS ip2long;
CREATE PROCEDURE ip2long(IN ip VARCHAR(15), OUT ip_int INTEGER(12) UNSIGNED)
BEGIN
-- 定位点分的位置
SET @int_pos_dot_1 = LOCATE('.', ip, 1);
SET @int_pos_dot_2 = LOCATE('.', ip, @int_pos_dot_1+1);
SET @int_pos_dot_3 = LOCATE('.', ip, @int_pos_dot_2+1);
-- 切割
SET @part_1 = SUBSTRING(ip, 0+1, @int_pos_dot_1-1);
SET @part_2 = SUBSTRING(ip, @int_pos_dot_1+1, @int_pos_dot_2-@int_pos_dot_1-1);
SET @part_3 = SUBSTRING(ip, @int_pos_dot_2+1, @int_pos_dot_3-@int_pos_dot_2-1);
SET @part_4 = SUBSTRING(ip, @int_pos_dot_3+1);
-- 计算
SELECT @part_1 * 16777216 + @part_2 * 65536 + @part_3 * 256 + @part_4 INTO ip_int;
END; $$

-- 将long int转换为点分IP格式
-- param: IN ip_int INTEGER(12) UNSIGNED 输入整数
-- param: OUT ip VARCHAR(15) 输出IP
DROP PROCEDURE IF EXISTS long2ip;
CREATE PROCEDURE long2ip(IN ip_int INT(12) UNSIGNED, OUT ip VARCHAR(15))
BEGIN
-- 计算
SET @part_1 = ip_int DIV 16777216;
SET @modulo = ip_int % 16777216;
SET @part_2 = @modulo DIV 65536;
SET @modulo = @modulo % 65536;
SET @part_3 = @modulo DIV 256;
SET @part_4 = @modulo % 256;
-- 合并
SELECT CONCAT_WS('.', @part_1, @part_2, @part_3, @part_4) INTO ip;
END; $$

DELIMITER ;


=====================================================================



版本 2 - func: ip_parser还是sp的版本,因为返回值的原因===============================


-- ip_parser: split ip/cidr with /
-- param: IN ip_with_cidr VARCHAR(18) 输入的IP/CIDR数据
-- param: OUT ip VARCHAR(15) 输出的IP
-- param: OUT cidr VARCHAR(2) 输出的cidr
DROP PROCEDURE IF EXISTS ip_parser;
CREATE PROCEDURE ip_parser(IN ip_with_cidr VARCHAR(18), OUT ip VARCHAR(15), OUT cidr VARCHAR(2))
BEGIN
-- 正则匹配有cidr的数据
IF (SELECT ip_with_cidr REGEXP '^([0-9]{1,3}\.){3}[0-9]{1,3}\/[0-9]{1,2}$')
THEN
SELECT substring_index(ip_with_cidr, '/', 1) INTO ip;
SELECT substring_index(ip_with_cidr, '/', -1) INTO cidr;
-- 如果没有cidr则直接赋值32
ELSEIF (SELECT ip_with_cidr REGEXP '^([0-9]{1,3}\.){3}[0-9]{1,3}$')
THEN
SELECT ip_with_cidr INTO ip;
SELECT '32' INTO cidr;
-- 否则全部置0即允许全部
ELSE
SELECT 0 INTO ip;
SELECT 0 INTO cidr;
END IF;
END; $$

-- 判断提交的IP是否属于提交的ip段
-- param: ip VARCHAR(15) 目标IP
-- param: ip_with_cidr VARCHAR(18) 目标IP段
-- return: TINYINT(1) 返回值
DROP FUNCTION IF EXISTS in_subnet;
CREATE FUNCTION in_subnet(ip VARCHAR(15), ip_with_cidr VARCHAR(18))
RETURNS TINYINT(1) DETERMINISTIC
BEGIN
CALL ip_parser(ip_with_cidr, @t_ip, @t_cidr);
-- 分别计算两个子网号,然后比较
RETURN get_subnet(@t_ip, @t_cidr) = get_subnet(ip, @t_cidr);
END; $$

-- 根据提交的IP和CIDR返回子网号
-- param: ip VARCHAR(15) 目标IP
-- param: cidr VARCHAR(2) 目标CIDR
-- return: subnet_addr VARCHAR(15) 返回的子网号
DROP FUNCTION IF EXISTS get_subnet;
CREATE FUNCTION get_subnet(ip VARCHAR(15), cidr VARCHAR(2))
RETURNS VARCHAR(15) DETERMINISTIC
BEGIN
-- 用IP和子网掩码按位与,生成子网号
RETURN long2ip(ip2long(ip) & ip2long(cidr2mask(cidr)));
END; $$

-- 将cidr转换成mask,懒得用字符串处理,就直接case when了,看着还清楚些
-- param: cidr VARCHAR(2) 输入的cidr
-- return: mask_addr VARCHAR(15) 输出的子网掩码
DROP FUNCTION IF EXISTS cidr2mask;
CREATE FUNCTION cidr2mask(cidr VARCHAR(2))
RETURNS VARCHAR(15) DETERMINISTIC
BEGIN
CASE cidr
WHEN '0' THEN RETURN '0.0.0.0';
WHEN '1' THEN RETURN '128.0.0.0';
WHEN '2' THEN RETURN '192.0.0.0';
WHEN '3' THEN RETURN '224.0.0.0';
WHEN '4' THEN RETURN '240.0.0.0';
WHEN '5' THEN RETURN '248.0.0.0';
WHEN '6' THEN RETURN '252.0.0.0';
WHEN '7' THEN RETURN '254.0.0.0';
WHEN '8' THEN RETURN '255.0.0.0';
WHEN '9' THEN RETURN '255.128.0.0';
WHEN '10' THEN RETURN '255.192.0.0';
WHEN '11' THEN RETURN '255.224.0.0';
WHEN '12' THEN RETURN '255.240.0.0';
WHEN '13' THEN RETURN '255.248.0.0';
WHEN '14' THEN RETURN '255.252.0.0';
WHEN '15' THEN RETURN '255.254.0.0';
WHEN '16' THEN RETURN '255.255.0.0';
WHEN '17' THEN RETURN '255.255.128.0';
WHEN '18' THEN RETURN '255.255.192.0';
WHEN '19' THEN RETURN '255.255.224.0';
WHEN '20' THEN RETURN '255.255.240.0';
WHEN '21' THEN RETURN '255.255.248.0';
WHEN '22' THEN RETURN '255.255.252.0';
WHEN '23' THEN RETURN '255.255.254.0';
WHEN '24' THEN RETURN '255.255.255.0';
WHEN '25' THEN RETURN '255.255.255.128';
WHEN '26' THEN RETURN '255.255.255.192';
WHEN '27' THEN RETURN '255.255.255.224';
WHEN '28' THEN RETURN '255.255.255.240';
WHEN '29' THEN RETURN '255.255.255.248';
WHEN '30' THEN RETURN '255.255.255.252';
WHEN '31' THEN RETURN '255.255.255.254';
WHEN '32' THEN RETURN '255.255.255.255';
-- 不合法的全都用 255.255.255.255
ELSE RETURN '255.255.255.255';
END CASE;
END; $$

-- 将long int转换为点分IP格式
-- param: ip_int INTEGER(12) UNSIGNED 输入整数
-- return: ip VARCHAR(15) 输出IP
DROP FUNCTION IF EXISTS long2ip;
CREATE FUNCTION long2ip(ip_int INT(12) UNSIGNED)
RETURNS VARCHAR(15) DETERMINISTIC
BEGIN
-- 计算
SET @part_1 = ip_int DIV 16777216;
SET @modulo = ip_int % 16777216;
SET @part_2 = @modulo DIV 65536;
SET @modulo = @modulo % 65536;
SET @part_3 = @modulo DIV 256;
SET @part_4 = @modulo % 256;
-- 合并
RETURN CONCAT_WS('.', @part_1, @part_2, @part_3, @part_4);
END; $$

-- 将IP转换为long int
-- param: ip VARCHAR(15) 输入IP
-- return: ip_int INTEGER(12) UNSIGNED 输出整数
DROP FUNCTION IF EXISTS ip2long;
CREATE FUNCTION ip2long(ip VARCHAR(15))
RETURNS INTEGER(12) UNSIGNED DETERMINISTIC
BEGIN
-- 定位点分的位置
SET @int_pos_dot_1 = LOCATE('.', ip, 1);
SET @int_pos_dot_2 = LOCATE('.', ip, @int_pos_dot_1+1);
SET @int_pos_dot_3 = LOCATE('.', ip, @int_pos_dot_2+1);
-- 切割
SET @part_1 = SUBSTRING(ip, 0+1, @int_pos_dot_1-1);
SET @part_2 = SUBSTRING(ip, @int_pos_dot_1+1, @int_pos_dot_2-@int_pos_dot_1-1);
SET @part_3 = SUBSTRING(ip, @int_pos_dot_2+1, @int_pos_dot_3-@int_pos_dot_2-1);
SET @part_4 = SUBSTRING(ip, @int_pos_dot_3+1);
-- 计算
RETURN @part_1 * 16777216 + @part_2 * 65536 + @part_3 * 256 + @part_4;
END; $$

No comments:

Post a Comment