MySQL, Checking the IP address in a range

fn_ipv4_match( needle, haystack )

Returns 1 if haystack contains needle.
needle : IPv4 address
haystack : IPv4 address, IPv4 address with subnet mask, IPv4 address with netmask bits

DELIMITER $$

DROP FUNCTION IF EXISTS `fn_ipv4_match`$$

CREATE FUNCTION `fn_ipv4_match`(
  needle    VARCHAR(15),
  haystack  VARCHAR(31)
) RETURNS TINYINT
BEGIN
/*
 * Checking the IP address in the range
 * 2014-09-30
 * Aiden Kim
 */
	
	DECLARE start_ip VARCHAR(15) DEFAULT NULL;
	DECLARE netmask VARCHAR(15) DEFAULT NULL;
	DECLARE netmask_bits TINYINT DEFAULT NULL;
	DECLARE search_ip_val INT UNSIGNED DEFAULT NULL;
	DECLARE start_ip_val INT UNSIGNED DEFAULT NULL;
	DECLARE end_ip_val INT UNSIGNED DEFAULT NULL;
	DECLARE result TINYINT DEFAULT 0;
	
	-- Split Network & Netmask
	SET start_ip = SUBSTRING_INDEX(haystack, '/', 1);
	SET netmask = SUBSTRING_INDEX(haystack, '/', -1);
	SET start_ip_val = INET_ATON(start_ip);
	
	-- Calculate the range
	IF (netmask = '') OR (start_ip = netmask) THEN
		SET end_ip_val = start_ip_val;
	ELSE
		IF (INSTR(netmask,'.') > 0) THEN
			-- Subnet mask
			SET netmask_bits = 32-LOG2((4294967296-INET_ATON(netmask)));
			SET start_ip_val = (-1 << (32-netmask_bits)) & start_ip_val;
			SET end_ip_val = start_ip_val + 4294967295 - INET_ATON(netmask);
		ELSE
			-- Subnet bits
			SET netmask_bits = CONVERT(netmask, UNSIGNED);
			SET start_ip_val = (-1 << (32-netmask_bits)) & start_ip_val;
			SET end_ip_val = start_ip_val + 4294967295 - (((POWER(2,32)-1)<<(32-netmask_bits)) & (POWER(2,32)-1));
		END IF;
	END IF;
	
	-- Compare 
	IF (INET_ATON(needle) BETWEEN start_ip_val AND end_ip_val) THEN
		SET result = 1;
	END IF;
	
	RETURN result;
	
END$$

DELIMITER ;

Results

mysql> SELECT fn_ipv4_match('1.2.3.88','1.2.3.88');
+--------------------------------------+
| fn_ipv4_match('1.2.3.88','1.2.3.88') |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.21','1.2.3.22');
+--------------------------------------+
| fn_ipv4_match('1.2.3.21','1.2.3.22') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.139','1.2.3.160/255.255.255.192');
+--------------------------------------------------------+
| fn_ipv4_match('1.2.3.139','1.2.3.130/255.255.255.192') |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.127','1.2.3.160/25');
+-------------------------------------------+
| fn_ipv4_match('1.2.3.127','1.2.3.130/25') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('2.3.4.5','1.2.3.0/8');
+--------------------------------------+
| fn_ipv4_match('2.3.4.5','1.2.3.0/8') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.