Hello guys,
following up some emails i had recently with Steve Wright and Chris Koutras
i made a small patch, attached to this email: it allows to store IP addresses
into a MySQL database as UNSIGNED INTEGERs instead of CHARs; they could be
then retrieved with a query like:
'SELECT * FROM acct_v2 WHERE ip_src=inet_aton('192.168.0.1');'.
Since the beginnings of pmacct, 'ip_src' and 'ip_dst' fields have been
written into MySQL as CHAR(15), the flexible way but obviously not resource
and space savy. As you know, while PostgreSQL sports its own types to store
IPv4, IPv6 and MAC addresses, MySQL is relegated to the use of its inet_aton()
and inet_ntoa() functions; they solve the problem with IPv4 addresses, though
not in a nice way because you need an UNSIGNED INTEGER field greater than 4
bytes to let it work, leaving totally unsolved IPv6 and MAC address issues
which is because we've never considered it a big deal.
Moreover, i've tested the patch with MySQL 3.23.x and 4.0.x over x86 and Alpha
hardware and surprisingly enough in all cases i've received different results
between the Libc inet_ntoa() function and the MySQL's one: to receive correct
results with the MySQL one, i need to roll the byte ordering of the addresses
from network byte order to host one. Did i miss something, maybe into the
MySQL documentation ?
If the patch is definitely considered useful, i will consider to not include
it into mainstream code but creating patches each new release, if necessary.
I will publish it soon over the homepage.
Cheers,
Paolo
--- pmacct-0.7.9/mysql_plugin.c 2004-12-19 21:40:10.000000000 +0100
+++ pmacct-0.7.9-mysql-int/mysql_plugin.c 2005-01-05 11:23:11.000000000
+0100
@@ -751,10 +751,10 @@
strncat(where[primitive].string, " AND ",
sizeof(where[primitive].string));
}
strncat(insert_clause, "ip_src", SPACELEFT(insert_clause));
- strncat(values[primitive].string, "\'%s\'",
SPACELEFT(values[primitive].string));
- strncat(where[primitive].string, "ip_src=\'%s\'",
SPACELEFT(where[primitive].string));
+ strncat(values[primitive].string, "%u",
SPACELEFT(values[primitive].string));
+ strncat(where[primitive].string, "ip_src=%u",
SPACELEFT(where[primitive].string));
values[primitive].type = where[primitive].type = COUNT_SRC_HOST;
- values[primitive].handler = where[primitive].handler =
count_src_host_handler;
+ values[primitive].handler = where[primitive].handler =
MY_count_src_host_handler;
primitive++;
}
@@ -765,10 +765,10 @@
strncat(where[primitive].string, " AND ",
sizeof(where[primitive].string));
}
strncat(insert_clause, "ip_dst", SPACELEFT(insert_clause));
- strncat(values[primitive].string, "\'%s\'",
SPACELEFT(values[primitive].string));
- strncat(where[primitive].string, "ip_dst=\'%s\'",
SPACELEFT(where[primitive].string));
+ strncat(values[primitive].string, "%u",
SPACELEFT(values[primitive].string));
+ strncat(where[primitive].string, "ip_dst=%u",
SPACELEFT(where[primitive].string));
values[primitive].type = where[primitive].type = COUNT_DST_HOST;
- values[primitive].handler = where[primitive].handler =
count_dst_host_handler;
+ values[primitive].handler = where[primitive].handler =
MY_count_dst_host_handler;
primitive++;
}
--- pmacct-0.7.9/sql_handlers.c 2004-11-04 13:12:59.000000000 +0100
+++ pmacct-0.7.9-mysql-int/sql_handlers.c 2005-01-05 12:10:53.000000000
+0100
@@ -80,6 +80,14 @@
*ptr_values += strlen(*ptr_values);
}
+void MY_count_src_host_handler(const struct db_cache *cache_elem, int num,
char **ptr_values, char **ptr_where)
+{
+ snprintf(*ptr_where, SPACELEFT(where_clause), where[num].string,
ntohl(cache_elem->src_ip.s_addr));
+ snprintf(*ptr_values, SPACELEFT(values_clause), values[num].string,
ntohl(cache_elem->src_ip.s_addr));
+ *ptr_where += strlen(*ptr_where);
+ *ptr_values += strlen(*ptr_values);
+}
+
void count_src_as_handler(const struct db_cache *cache_elem, int num, char
**ptr_values, char **ptr_where)
{
snprintf(*ptr_where, SPACELEFT(where_clause), where[num].string,
ntohl(cache_elem->src_ip.s_addr));
@@ -99,6 +107,14 @@
*ptr_values += strlen(*ptr_values);
}
+void MY_count_dst_host_handler(const struct db_cache *cache_elem, int num,
char **ptr_values, char **ptr_where)
+{
+ snprintf(*ptr_where, SPACELEFT(where_clause), where[num].string,
ntohl(cache_elem->dst_ip.s_addr));
+ snprintf(*ptr_values, SPACELEFT(values_clause), values[num].string,
ntohl(cache_elem->dst_ip.s_addr));
+ *ptr_where += strlen(*ptr_where);
+ *ptr_values += strlen(*ptr_values);
+}
+
void count_dst_as_handler(const struct db_cache *cache_elem, int num, char
**ptr_values, char **ptr_where)
{
snprintf(*ptr_where, SPACELEFT(where_clause), where[num].string,
ntohl(cache_elem->dst_ip.s_addr));
--- pmacct-0.7.9/sql_common.h 2004-12-19 14:06:24.000000000 +0100
+++ pmacct-0.7.9-mysql-int/sql_common.h 2005-01-05 11:09:13.000000000 +0100
@@ -132,6 +132,9 @@
EXT void count_id_handler(const struct db_cache *, int, char **, char **);
EXT void fake_mac_handler(const struct db_cache *, int, char **, char **);
EXT void fake_host_handler(const struct db_cache *, int, char **, char **);
+
+EXT void MY_count_src_host_handler(const struct db_cache *, int, char **, char
**);
+EXT void MY_count_dst_host_handler(const struct db_cache *, int, char **, char
**);
#undef EXT
/* global vars: a simple way of gain precious speed when playing with strings
*/
