diff options
author | /C=EU/ST=EU/CN=Pablo Neira Ayuso/emailAddress=pablo@netfilter.org </C=EU/ST=EU/CN=Pablo Neira Ayuso/emailAddress=pablo@netfilter.org> | 2008-04-27 07:54:05 +0000 |
---|---|---|
committer | /C=EU/ST=EU/CN=Pablo Neira Ayuso/emailAddress=pablo@netfilter.org </C=EU/ST=EU/CN=Pablo Neira Ayuso/emailAddress=pablo@netfilter.org> | 2008-04-27 07:54:05 +0000 |
commit | 8f1e72fccdbc8b9ccf034088f4b8175df0ba5b7a (patch) | |
tree | 5f2da5556b2b2af6855593fe81a833e1bdc2393a | |
parent | 4b73becea6fad4c89beb4442f1b76738b817e83e (diff) |
Introduce function to convert binary data to printable strings.
Update view_tcp_quad and view_udp_quad.
Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
-rw-r--r-- | doc/mysql-ulogd2.sql | 39 |
1 files changed, 37 insertions, 2 deletions
diff --git a/doc/mysql-ulogd2.sql b/doc/mysql-ulogd2.sql index 00017be..05ea9b4 100644 --- a/doc/mysql-ulogd2.sql +++ b/doc/mysql-ulogd2.sql @@ -220,13 +220,48 @@ CREATE SQL SECURITY INVOKER VIEW `ulog` AS -- shortcuts +DROP FUNCTION IF EXISTS BIN_TO_IPV6; +delimiter $$ +CREATE FUNCTION BIN_TO_IPV6( + _in binary(16) + ) RETURNS varchar(64) +SQL SECURITY INVOKER +DETERMINISTIC +COMMENT 'Convert binary ip to printable string' +BEGIN + -- IPv4 address in IPv6 form + IF HEX(SUBSTRING(_in, 1, 12)) = '00000000000000000000FFFF' THEN + RETURN CONCAT( + '::ffff:', + ASCII(SUBSTRING(_in, 13, 1)), '.', + ASCII(SUBSTRING(_in, 14, 1)), '.', + ASCII(SUBSTRING(_in, 15, 1)), '.', + ASCII(SUBSTRING(_in, 16, 1)) + ); + END IF; + -- return the full IPv6 form + RETURN LOWER(CONCAT( + HEX(SUBSTRING(_in, 1, 2)), ':', + HEX(SUBSTRING(_in, 3, 2)), ':', + HEX(SUBSTRING(_in, 5, 2)), ':', + HEX(SUBSTRING(_in, 7, 2)), ':', + HEX(SUBSTRING(_in, 9, 2)), ':', + HEX(SUBSTRING(_in, 11, 2)), ':', + HEX(SUBSTRING(_in, 13, 2)), ':', + HEX(SUBSTRING(_in, 15, 2)) + )); +END +$$ +delimiter ; + + DROP VIEW IF EXISTS `view_tcp_quad`; CREATE SQL SECURITY INVOKER VIEW `view_tcp_quad` AS - SELECT ulog2._id,ulog2.ip_saddr,tcp.tcp_sport,ulog2.ip_daddr,tcp.tcp_dport FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id; + SELECT ulog2._id,BIN_TO_IPV6(ulog2.ip_saddr) AS ip_saddr_str,tcp.tcp_sport,BIN_TO_IPV6(ulog2.ip_daddr) AS ip_daddr_str,tcp.tcp_dport FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id; DROP VIEW IF EXISTS `view_udp_quad`; CREATE SQL SECURITY INVOKER VIEW `view_udp_quad` AS - SELECT ulog2._id,ulog2.ip_saddr,udp.udp_sport,ulog2.ip_daddr,udp.udp_dport FROM ulog2 INNER JOIN udp ON ulog2._id = udp._udp_id; + SELECT ulog2._id,BIN_TO_IPV6(ulog2.ip_saddr) AS ip_saddr_str,udp.udp_sport,BIN_TO_IPV6(ulog2.ip_daddr) AS ip_daddr_str,udp.udp_dport FROM ulog2 INNER JOIN udp ON ulog2._id = udp._udp_id; |