Untitled query #47155

SQL

DELIMITER $$

DROP FUNCTION IF EXISTS `find_ip4_in_cidr4` $$
CREATE DEFINER=`mezzell`@`%` FUNCTION `find_ip4_in_cidr4`(
  _address VARCHAR(15), 
  _block VARCHAR(18)
) RETURNS TINYINT
DETERMINISTIC /* for a given input, this function always returns the same output */
CONTAINS SQL /* the function does not read from or write to tables */
BEGIN

-- given an IPv4 address and a cidr spec,
-- return -1 for a valid address inside 0.0.0.0/0
-- return prefix length if the address is within the block,
-- return 0 if the address is outside the block,
-- otherwise return null

DECLARE _ip_aton INT UNSIGNED DEFAULT INET_ATON(_address);
DECLARE _cidr_aton INT UNSIGNED DEFAULT INET_ATON(SUBSTRING_INDEX(_block,'/',1));
DECLARE _prefix TINYINT UNSIGNED DEFAULT SUBSTRING_INDEX(_block,'/',-1);
DECLARE _bitmask INT UNSIGNED DEFAULT (0xFFFFFFFF << (32 - _prefix)) & 0xFFFFFFFF;

RETURN CASE /* the first match, not "best" match is used in a CASE expression */
  WHEN _ip_aton IS NULL OR _cidr_aton IS NULL OR /* sanity checks */
       _prefix  IS NULL OR _bitmask IS NULL OR
       _prefix NOT BETWEEN 0 AND 32 OR
       (_prefix = 0 AND _cidr_aton != 0) THEN NULL
  WHEN _cidr_aton = 0 AND _bitmask = 0 THEN -1
  WHEN _ip_aton & _bitmask = _cidr_aton & _bitmask THEN _prefix /* here's the only actual test needed */
  ELSE 0 END;

END $$
DELIMITER ;

SELECT * FROM ipblocks WHERE ipb_user = 0 LIMIT 1;
SQL

Checking query status...
