-- $Id: wfwl_functions 577 2014-05-13 03:30:36Z bhockney $
--
-- webfwlog mysql functions for binary field used for ip addresses
--
-- - wfwl_inet_ntop - convert 4 or 16 byte binary string to presentation string 
-- - wfwl_inet_pton - convert IPV4 or IPV6 presentation string to 4 or 16 byte
--		binary string
-- - wfwl_inet_contains - compare 4 or 16 byte binary string with test value,
--		possibly including prefix or netmask
--
--
--
-- wfwl_inet_ntop_ipv4(4 byte binary string)
-- 
-- Converts 4 byte binary string to IPv4 dotted quad notation
-- Used by wfwl_inet_ntop
--
--
DROP FUNCTION IF EXISTS wfwl_inet_ntop_ipv4;
delimiter $$
CREATE FUNCTION wfwl_inet_ntop_ipv4(
	_in_addr binary(4)
) RETURNS varchar(16)
SQL SECURITY INVOKER
DETERMINISTIC
COMMENT 'Convert ipv4 binary ip to dotted quad'
BEGIN
	RETURN concat(
		ascii(substring(_in_addr, 1, 1)), '.',
		ascii(substring(_in_addr, 2, 1)), '.',
		ascii(substring(_in_addr, 3, 1)), '.',
		ascii(substring(_in_addr, 4, 1))
	);
END
$$
delimiter ;

--
-- wfwl_inet_ntop(family af, 4 or 16 byte binary string)
-- 
-- Converts 4 or 16 byte binary string to presentation string
--
-- 	@arg1	address family, AF_INET or AF_INET6 (2 or 10)
--	@arg2	4 or 16 byte binary string
--	@returns presentation string according to family
--
--	@arg1 may be NULL and address family will be inferred
--
--	Examples:
--		SELECT wfwl_inet_ntop(2,    wfwl_inet_pton('1.2.3.4')) = 1.2.3.4
--		SELECT wfwl_inet_ntop(10,   wfwl_inet_pton('1.2.3.4')) = ::ffff:1.2.3.4
--		SELECT wfwl_inet_ntop(NULL, wfwl_inet_pton('1.2.3.4')) = 1.2.3.4
--		SELECT wfwl_inet_ntop(NULL, binary '::ffff:1.2.3.4')   = 1.2.3.4
--
--
DROP FUNCTION IF EXISTS wfwl_inet_ntop;
delimiter $$
CREATE FUNCTION wfwl_inet_ntop(
	_family tinyint,
	_in_addr varbinary(16)
) RETURNS varchar(64)
SQL SECURITY INVOKER
DETERMINISTIC
COMMENT 'Convert binary ip to presentation string'
BEGIN
	DECLARE ret varchar(64);
	DECLARE seghex char(4);
	DECLARE loc_str varchar(16);
	DECLARE pos, seg_pos, segs_cur, segs_max tinyint;

	IF length(_in_addr) != 4 AND length(_in_addr) != 16
	THEN
		RETURN NULL; -- ERROR
	END IF;

	-- IPv4 address 
	IF _family = 2 OR _family IS NULL AND (length(_in_addr) = 4
		OR hex(substring(_in_addr, 1, 12)) = '00000000000000000000FFFF'
		OR hex(substring(_in_addr, 1, 12)) = '000000000000000000000000')
	THEN
		RETURN wfwl_inet_ntop_ipv4(right(_in_addr,4));
	END IF;

	-- IPv6 address
	IF _family = 10 OR _family = 26 OR _family = 28 OR (_family IS NULL AND length(_in_addr) = 16)
	THEN
		IF length(_in_addr) = 4
		THEN
			RETURN concat('::ffff:', wfwl_inet_ntop_ipv4(right(_in_addr,4)));
		END IF;
		SET ret = '';
		SET seg_pos = 1;
		SET segs_cur = 0;
		SET segs_max = 0;
		REPEAT
			SET seghex = trim(LEADING '0' FROM upper(hex(substring(_in_addr, seg_pos, 2))));
			IF length(seghex) = 0
			THEN
				SET seghex = '0';
				SET segs_cur = segs_cur + 1;
				IF segs_cur > segs_max THEN SET segs_max = segs_cur; END IF;
			ELSE
				SET segs_cur = 0;
			END IF;
			IF seg_pos = 1
			THEN
				SET ret = seghex;
			ELSE
				SET ret = lower(concat(ret, ':', seghex));
			END IF;
			SET seg_pos = seg_pos + 2;
		UNTIL seg_pos > 15
		END REPEAT;
	
		-- remove longest consecutive segments of zeros
		IF segs_max <= 1
		THEN
			RETURN ret;
		ELSEIF segs_max = 8
		THEN
			RETURN '::';
		ELSEIF segs_max = 5 AND substring(ret, 1, 14) = '0:0:0:0:0:ffff'
		THEN
			RETURN concat('::ffff:', wfwl_inet_ntop_ipv4(right(_in_addr,4)));
		ELSE
			SET loc_str = concat(repeat('0:', segs_max - 1),'0');
			IF left(ret, segs_max * 2) = concat(loc_str, ':')
			THEN
				RETURN concat(':', substring(ret, segs_max * 2));
			ELSEIF right(ret, segs_max * 2) = concat(':', loc_str)
			THEN
				RETURN concat(left(ret, length(ret) - length(loc_str)), ':');
			ELSE
				SET pos = locate(concat(':', loc_str, ':'), ret);
				RETURN concat(left(ret, pos), substring(ret, pos + length(loc_str) + 1));
			END IF;
		END IF;
	ELSE
		RETURN NULL;
	END IF;
END
$$
delimiter ;
--
--
-- wfwl_inet_pton(af family, Presentation string)
--
-- Converts IPv4 or IPv6 presentation string to 4 or 16 by binary string
--
-- 	@arg1	address family, AF_INET or AF_INET6 (2 or 10)
--	@arg2	human readable IPv4 or IPv6 address
--	@returns 4 or 16 byte binary string according to family
--
--	@arg1 may be NULL and address family will be inferred
--
-- Examples:
--		wfwl_inet_pton(NULL, '1.2.3.4')        =  4 byte string
--		wfwl_inet_pton(NULL, '::ffff:1.2.3.4') =  4 byte string
--		wfwl_inet_pton(10, '::ffff:1.2.3.4')   = 16 byte string
--		wfwl_inet_pton(NULL, 'abc::3:42:7c2')  = 16 byte string
--
--
DROP FUNCTION IF EXISTS wfwl_inet_pton;
delimiter $$
CREATE FUNCTION wfwl_inet_pton(
	_family tinyint,
	_in_addr_str varchar(64)
) RETURNS varbinary(16)
SQL SECURITY INVOKER
DETERMINISTIC
COMMENT 'Convert human-readable IPv4 or IPv6 address to 4 or 16 byte binary string'
BEGIN
	DECLARE in_addr_str varchar(64);
	DECLARE ret varbinary(16);
	DECLARE ins_segs, delims, pos, start_pos, seg_len tinyint;

	SET ret = '';
	SET in_addr_str = _in_addr_str;
	IF locate('.', in_addr_str) > 0
	THEN
		-- IPv4 address
		SET in_addr_str = substring_index(in_addr_str, ':', -1);
		SET delims = 0;
		SET pos = 0;
		REPEAT
			SET pos = locate('.', in_addr_str, pos + 1);
			IF pos THEN SET delims = delims + 1; END IF;
		UNTIL pos = 0
		END REPEAT;
		IF delims != 3 THEN RETURN NULL; END IF; -- ERROR
		SET start_pos = 1;
		REPEAT
			SET pos = locate('.', in_addr_str, start_pos);
			IF pos > 0
			THEN
				SET seg_len = pos - start_pos;
				IF substring(in_addr_str, start_pos, seg_len) > 255 THEN RETURN NULL; END IF; -- ERROR
				IF pos = start_pos THEN RETURN NULL; END IF; -- ERROR
				SET ret = concat(ret, char(substring(in_addr_str, start_pos, seg_len)));
				SET start_pos = start_pos + seg_len + 1;
			ELSE -- last segment
				IF substring(in_addr_str, start_pos) > 255 THEN RETURN NULL; END IF; -- ERROR
				IF pos = start_pos THEN RETURN NULL; END IF; -- ERROR
				SET ret = concat(ret, char(substring(in_addr_str, start_pos)));
			END IF;
		UNTIL pos = 0
		END REPEAT;
		IF _family = 10 OR (_family IS NULL AND concat('::ffff:', in_addr_str) = lower(_in_addr_str))
		THEN RETURN concat(unhex('00000000000000000000ffff'), ret);
		ELSEIF _in_addr_str = in_addr_str
		THEN RETURN ret;
		ELSE RETURN NULL; -- ERROR
		END IF;
	ELSE
		-- IPv6 address
		IF _family = 2 THEN RETURN NULL; END IF; -- ERROR
		SET delims = 0;
		SET pos = 0;
		REPEAT
			SET pos = locate(':', in_addr_str, pos + 1);
			IF pos THEN SET delims = delims + 1; END IF;
		UNTIL pos = 0
		END REPEAT;
		IF delims < 2 OR delims > 7 THEN RETURN NULL; END IF; -- ERROR
		SET ins_segs = 7 - delims;
		SET in_addr_str = replace(in_addr_str, '::', concat(repeat(':0', ins_segs + 1), ':'));
		IF  left(in_addr_str, 1) = ':' THEN SET in_addr_str = concat('0', in_addr_str); END IF;
		IF right(in_addr_str, 1) = ':' THEN SET in_addr_str = concat(in_addr_str, '0'); END IF;
		SET delims = 0;
		SET pos = 0;
		REPEAT
			SET pos = locate(':', in_addr_str, pos + 1);
			IF pos THEN SET delims = delims + 1; END IF;
		UNTIL pos = 0
		END REPEAT;
		IF delims != 7 THEN RETURN NULL; END IF; -- ERROR
		SET start_pos = 1;
		REPEAT
			SET pos = locate(':', in_addr_str, start_pos);
			IF pos > 0
			THEN
				SET seg_len = pos - start_pos;
				IF seg_len > 4 THEN RETURN NULL; END IF; -- ERROR
				SET ret = concat(ret, unhex(lpad(substring(in_addr_str, start_pos, seg_len), 4, '0')));
				SET start_pos = start_pos + seg_len + 1;
			ELSE -- last segment
				SET ret = concat(ret, unhex(lpad(substring(in_addr_str, start_pos), 4, '0')));
			END IF;
		UNTIL pos = 0
		END REPEAT;
		RETURN ret;
	END IF;
END
$$
delimiter ;
--
--
-- wfwl_inet_contains(family af, 4 or 16 byte binary string, presentation address or CIDR)
--
-- Compares IPv4 or IPv6 binary string with test value, possibly including netmask or prefix
--
--	@arg1	address family, AF_INET or AF_INET6 (2 or 10)
--	@arg2	4 or 16 byte binary string
--	@arg3	human readable test address[/netmask|/prefix]
--	@returns boolean
--
--
DROP FUNCTION IF EXISTS wfwl_inet_contains;
delimiter $$
CREATE FUNCTION wfwl_inet_contains(
	_family tinyint,
	_in_addr varbinary(16),
	_in_addr_cmp varchar(64)
) RETURNS boolean 
SQL SECURITY INVOKER
DETERMINISTIC
COMMENT 'Compares human-readable IPv4 or IPv6 address to 4 or 16 byte binary string'
BEGIN
	DECLARE cmp_addr varchar(64);
	DECLARE netmask varchar(16);
	DECLARE prefix, in_addr_byte, in_addr_byte_cmp, cmp_mask smallint;
	DECLARE in_addr, in_addr_cmp, netmask_cmp integer unsigned;
	DECLARE cmp_bytes, cmp_bits, family, addr_len tinyint;

	SET netmask = '';
	SET cmp_addr = substring_index(_in_addr_cmp, '/', 1);
	IF locate('/', _in_addr_cmp)
	THEN
		SET netmask = substring_index(_in_addr_cmp, '/', -1);
	END IF;

	IF _family IS NULL
	THEN
		IF locate(':', cmp_addr)
		THEN
			SET family = 10;
		ELSE
			SET family = 2;
		END IF;
	ELSE
		SET family = _family;
	END IF;
	IF ((family = 10 OR family = 26 OR family = 28) AND (length(_in_addr) != 16)) OR
		(family = 2 AND (length(_in_addr) != 4 OR (length(_in_addr) = 16 AND NOT
		(left(_in_addr, 12) = left(wfwl_inet_pton('::'), 12) OR
		left(_in_addr, 12) = left(wfwl_inet_pton('::ffff:0'), 12))))) OR
		(family != 2 AND family != 10 AND family != 26 AND family != 28)
	THEN RETURN FALSE;
	END IF;

	IF family = 2
	THEN SET addr_len = 4;
	ELSE SET addr_len = 16;
	END IF;

	IF locate('.', netmask)
	THEN
		-- compare with netmask
		SET netmask_cmp = inet_aton(netmask);
		SET in_addr = inet_aton(wfwl_inet_ntop(2, right(_in_addr, 4)));
		SET in_addr_cmp = inet_aton(cmp_addr);
		IF in_addr & netmask_cmp  = in_addr_cmp & netmask_cmp
		THEN RETURN TRUE;
		ELSE RETURN FALSE;
		END IF;
	ELSEIF NOT netmask
	THEN
		-- exact compare
		IF right(_in_addr, addr_len) = wfwl_inet_pton(cmp_addr)
		THEN RETURN TRUE;
		ELSE RETURN FALSE;
		END IF;
	ELSE
		-- compare with prefix
		SET prefix = netmask;
		SET cmp_bytes = floor(prefix / 8);
		SET cmp_bits = prefix % 8;
		IF left(_in_addr, cmp_bytes) = left(wfwl_inet_pton(cmp_addr), cmp_bytes)
		THEN
			IF NOT cmp_bits
			THEN RETURN TRUE;
			ELSE
				SET in_addr_byte = ascii(substring(_in_addr, cmp_bytes + 1, 1));
				SET in_addr_byte_cmp = ascii(substring(wfwl_inet_pton(cmp_addr), cmp_bytes + 1, 1));
				SET cmp_mask = 255 - pow(2, 8 - cmp_bits) + 1;
				IF in_addr_byte & cmp_mask = in_addr_byte_cmp & cmp_mask
				THEN RETURN TRUE;
				ELSE RETURN FALSE;
				END IF;
			END IF;
		ELSE
			RETURN FALSE;
		END IF;
	END IF;
--
END
$$
delimiter ;
--
-- vim:syntax=sql ts=4:
