From 8f1e72fccdbc8b9ccf034088f4b8175df0ba5b7a Mon Sep 17 00:00:00 2001 From: "/C=EU/ST=EU/CN=Pablo Neira Ayuso/emailAddress=pablo@netfilter.org" Date: Sun, 27 Apr 2008 07:54:05 +0000 Subject: Introduce function to convert binary data to printable strings. Update view_tcp_quad and view_udp_quad. Signed-off-by: Pierre Chifflier --- doc/mysql-ulogd2.sql | 39 +++++++++++++++++++++++++++++++++++++-- 1 file changed, 37 insertions(+), 2 deletions(-) (limited to 'doc') 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; -- cgit v1.2.3