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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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) |
Print Friendly
