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>

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;