INET6_ATON和NTOA函数的Oracle PL/SQL版本? [英] Oracle PL/SQL versions of INET6_ATON and NTOA functions?
问题描述
有什么好的代码可以将IPv6地址字符串转换为整数?使用一种格式，转换IPv4似乎很容易.但是，IPv6有几种不同的格式来显示地址:
Any have any good code for converting a IPv6 address string into an integer? Converting IPv4 seems to be fairly easy, with the one format. However, IPv6 has several different formats to show an address:
- XXXX:XXXX:XXXX:XXXX ::
- XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX
- XXXX:XXX:XXXX:0:0:XXXX:XXX:XXXX
- XXXX:XXX:XXXX :: XXXX:XXX:XXXX
- :: ffff:XXXX:XXX(IPv4格式为v6)
- :: ffff:###.#.#.###(也是v6格式的有效IPv4)
我希望能够使用这些字符串之一并将其转换为用于IP到网络匹配的INTEGER，并允许将这些格式中的任何一种用作输入.
I'd like to be able to take one of these strings and translate it into an INTEGER for IP-to-network matching, and allow for any of these formats as the input.
推荐答案
结束了我自己的滚动.还意识到Oracle的126位INTEGER对于IPv6的128位地址来说还不够.坦白说，考虑到我从未听说过16字节整数，我不知道原始C库的INET6_ATON(或INET_PTON)是如何做到的.
Ended up rolling my own. Also realized that Oracle's 126-bit INTEGER is not enough bits for IPv6's 128-bit addresses. Frankly, I don't know how the original C library's INET6_ATON (or INET_PTON) does it, considering that I've never heard of a 16-byte integer.
我最后得到了一个32字节的十六进制字符串，这意味着我必须对nettohex做一些花哨的半字符串"数学运算，并使用SUBSTR来使FBI正常工作. (被爆破的PL/SQL不允许使用"RETURN CHAR(32)" ...)
I ended up with a 32-byte hex string, which means I have to do some fancy "half-string" math on nettohex and use SUBSTR for the FBIs to work correctly. (Blasted PL/SQL doesn't allow for "RETURN CHAR(32)"...)
总体而言，它可以很好地工作，可以在所有格式下工作，并且允许基于索引的字符比较来确定IP地址是否在IP范围内.
Overall, though, it works well, works in all formats, and allows for index-based character comparisons to find out if an IP address is within an IP range.
这是完整的代码:
CREATE OR REPLACE FUNCTION ipguess(
ip_string IN VARCHAR2
) RETURN NATURAL
DETERMINISTIC
IS
BEGIN
-- Short-circuit the most popular, and also catch the special case of IPv4 addresses in IPv6
IF REGEXP_LIKE(ip_string, '\d{1,3}(\.\d{1,3}){3}') THEN RETURN 4;
ELSIF REGEXP_LIKE(ip_string, '[[:xdigit:]]{0,4}(\:[[:xdigit:]]{0,4}){0,7}') THEN RETURN 6;
ELSE RETURN NULL;
END IF;
END ipguess;
CREATE OR REPLACE FUNCTION iptohex(
ip_string IN VARCHAR2
) RETURN CHAR -- INTEGER only holds 126 binary digits, IPv6 has 128
DETERMINISTIC
IS
iptype NATURAL := ipguess(ip_string);
ip VARCHAR2(32);
ipwork VARCHAR2(64);
d INTEGER;
q VARCHAR2(3);
BEGIN
IF iptype = 4 THEN
-- Sanity check
ipwork := REGEXP_SUBSTR(ip_string, '\d{1,3}(\.\d{1,3}){3}');
IF ipwork IS NULL THEN RETURN NULL; END IF;
-- Starting prefix
-- NOTE: 2^48 - 2^32 = 281470681743360 = ::ffff:0.0.0.0
-- (for compatibility with IPv4 addresses in IPv6)
ip := '00000000000000000000ffff';
-- Parse the input
WHILE LENGTH(ipwork) IS NOT NULL
LOOP
d := INSTR(ipwork, '.'); -- find the dot
IF d > 0 THEN -- isolate the decimal octet
q := SUBSTR(ipwork, 1, d - 1);
ipwork := SUBSTR(ipwork, d + 1);
ELSE
q := ipwork;
ipwork := '';
END IF;
-- convert to a hex string
ip := ip || TO_CHAR(TO_NUMBER(q), 'FM0x');
END LOOP;
ELSIF iptype = 6 THEN
-- Short-circuit "::" = 0
IF ip_string = '::' THEN RETURN LPAD('0', 32, '0'); END IF;
-- Sanity check
ipwork := REGEXP_SUBSTR(ip_string, '[[:xdigit:]]{0,4}(\:[[:xdigit:]]{0,4}){0,7}');
IF ipwork IS NULL THEN RETURN NULL; END IF;
-- Replace leading zeros
-- (add a bunch to all of the pairs, then remove only the required ones)
ipwork := REGEXP_REPLACE(ipwork, '(^|\:)([[:xdigit:]]{1,4})', '\1000\2');
ipwork := REGEXP_REPLACE(ipwork, '(^|\:)0+([[:xdigit:]]{4})', '\1\2');
-- Groups of zeroes
-- (total length should be 32+Z, so the gap would be the zeroes)
ipwork := REPLACE(ipwork, '::', 'Z');
ipwork := REPLACE(ipwork, ':');
ipwork := REPLACE(ipwork, 'Z', LPAD('0', 33 - LENGTH(ipwork), '0'));
ip := LOWER(ipwork);
ELSE
RETURN NULL;
END IF;
RETURN ip;
END iptohex;
CREATE OR REPLACE FUNCTION nettohex(
ip_string IN VARCHAR2,
cidr IN NATURALN,
is_end IN SIGNTYPE DEFAULT 0
) RETURN CHAR
DETERMINISTIC
IS
iptype NATURAL := ipguess(ip_string);
iphex CHAR(32) := iptohex(ip_string);
iphalf1 CHAR(16) := SUBSTR(iphex, 1, 16);
iphalf2 CHAR(16) := SUBSTR(iphex, 17);
ipwork CHAR(16) := iphalf2;
cidr_exp INTEGER := 2 ** (iptype + 1) - cidr;
ipint INTEGER;
subnet INTEGER;
is_big SIGNTYPE := 0;
BEGIN
-- Sanity checks
IF iptype IS NULL THEN RETURN NULL;
ELSIF iphex IS NULL THEN RETURN NULL;
END IF;
IF cidr_exp >= 64 THEN is_big := 1;
ELSIF cidr_exp = 0 THEN RETURN iphex; -- the exact IP, such as /32 on IPv4
ELSIF cidr_exp < 0 THEN RETURN NULL;
ELSIF cidr_exp > 128 THEN RETURN NULL;
END IF;
-- Change some variables around if we are working with the first/largest half
IF is_big = 1 THEN
ipwork := iphalf1;
iphalf2 := TO_CHAR((2 ** 64 - 1) * is_end, 'FM0xxxxxxxxxxxxxxx'); -- either all 0 or all F
cidr_exp := cidr_exp - 64;
END IF;
-- Normalize IP to divisions of CIDR
subnet := 2 ** cidr_exp;
ipint := TO_NUMBER(ipwork, 'FM0xxxxxxxxxxxxxxx');
-- if is_end = 1 then add one net range (then subtract one IP) to get the ending range
ipwork := TO_CHAR(FLOOR(ipint / subnet + is_end) * subnet - is_end, 'FM0xxxxxxxxxxxxxxx');
-- Re-integrate
IF is_big = 0 THEN iphalf2 := ipwork;
ELSE iphalf1 := ipwork;
END IF;
RETURN SUBSTR(iphalf1 || iphalf2, 1, 32);
END nettohex;
-- WHERE clause:
-- 1. BETWEEN compare:
-- iptohex(a.ip_addy) BETWEEN nettohex(b.net_addy, b.cidr, 0) AND nettohex(b.net_addy, b.cidr, 1)
--
-- Requires three function-based indexes, but all of them would work, as they are all inside the tables.
--
-- 2. CIDR match:
-- nettohex(a.ip_addy, b.cidr) = nettohex(b.net_addy, b.cidr)
--
-- Only two functions and uses exact match, but first one requires an outside variable. Last one would be only function-based index.
-- An FBI of iptohex(a.ip_addy) could be implemented, but it's questionable if nettohex would use that index.
--
-- Recommended FBIs:
--
-- (SUBSTR(iptohex(a.ip_addy), 1, 32))
-- (SUBSTR(nettohex(b.ip_addy, b.cidr, 0), 1, 32), SUBSTR(nettohex(b.ip_addy, b.cidr, 1), 1, 32))
--
-- NOTE: Will need to use the SUBSTR form for the above WHERE clauses!
更新:Oracle 11g确实允许将SUBSTR条目放入虚拟列.因此，您可能会有这样的列:
UPDATE: Oracle 11g does allow for the SUBSTR entry to be put a virtual column. So, you could have columns like this:
ip VARCHAR2(39),
cidr NUMBER(2),
ip_hex AS (SUBSTR(iptohex(ip), 1, 32)) VIRTUAL,
ip_nethex_start AS (SUBSTR(nettohex(ip, cidr, 0), 1, 32)) VIRTUAL,
ip_nethex_end AS (SUBSTR(nettohex(ip, cidr, 1), 1, 32)) VIRTUAL,
索引如下:
CREATE INDEX foobar_iphex_idx ON foobar (ip_hex);
CREATE INDEX foobar_ipnet_idx ON foobar (ip_nethex_start, ip_nethex_end);
使用WHERE子句，例如:
Using WHERE clauses like:
a.ip_hex BETWEEN b.ip_nethex_start AND b.ip_nethex_end
nettohex(a.ip, b.cidr) = b.ip_nethex_start -- not as effective
这篇关于INET6_ATON和NTOA函数的Oracle PL/SQL版本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
