1 /* ulogd_MYSQL.c, Version $Revision: 5875 $
3 * ulogd output plugin for logging to a MySQL database
5 * (C) 2000-2001 by Harald Welte <laforge@gnumonks.org>
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License version 2
9 * as published by the Free Software Foundation
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with this program; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 * $Id: ulogd_MYSQL.c 5875 2005-08-01 06:49:59Z laforge $
22 * 15 May 2001, Alex Janssen <alex@ynfonatic.de>:
23 * Added a compability option for older MySQL-servers, which
24 * don't support mysql_real_escape_string
26 * 17 May 2001, Alex Janssen <alex@ynfonatic.de>:
27 * Added the --with-mysql-log-ip-as-string feature. This will log
28 * IP's as string rather than an unsigned long integer to the database.
29 * See ulogd/doc/mysql.table.ipaddr-as-string as an example.
30 * BE WARNED: This has _WAY_ less performance during table searches.
32 * 09 Feb 2005, Sven Schuster <schuster.sven@gmx.de>:
33 * Added the "port" parameter to specify ports different from 3306
35 * 12 May 2005, Jozsef Kadlecsik <kadlec@blackhole.kfki.hu>
36 * Added reconnecting to lost mysql server.
42 #include <arpa/inet.h>
43 #include <ulogd/ulogd.h>
44 #include <ulogd/conffile.h>
45 #include <mysql/mysql.h>
48 #define DEBUGP(x, args...) fprintf(stderr, x, ## args)
50 #define DEBUGP(x, args...)
54 char name[ULOGD_MAX_KEYLEN];
59 /* The plugin handler */
60 static ulog_output_t mysql_plugin;
62 /* the database handle we are using */
65 /* a linked list of the fields the table has */
66 static struct _field *fields;
68 /* buffer for our insert statement */
71 /* pointer to the beginning of the "VALUES" part */
72 static char *stmt_val;
74 /* pointer to current inser position in statement */
75 static char *stmt_ins;
77 /* Attempt to reconnect if connection is lost */
79 #define TIME_ERR ((time_t)-1) /* Be paranoid */
81 /* our configuration directives */
82 static config_entry_t db_ce = {
84 .type = CONFIG_TYPE_STRING,
85 .options = CONFIG_OPT_MANDATORY,
88 static config_entry_t host_ce = {
91 .type = CONFIG_TYPE_STRING,
92 .options = CONFIG_OPT_MANDATORY,
95 static config_entry_t user_ce = {
98 .type = CONFIG_TYPE_STRING,
99 .options = CONFIG_OPT_MANDATORY,
102 static config_entry_t pass_ce = {
105 .type = CONFIG_TYPE_STRING,
106 .options = CONFIG_OPT_MANDATORY,
109 static config_entry_t table_ce = {
112 .type = CONFIG_TYPE_STRING,
113 .options = CONFIG_OPT_MANDATORY,
116 static config_entry_t port_ce = {
119 .type = CONFIG_TYPE_INT,
122 static config_entry_t reconnect_ce = {
125 .type = CONFIG_TYPE_INT,
128 static config_entry_t connect_timeout_ce = {
129 .next = &reconnect_ce,
130 .key = "connect_timeout",
131 .type = CONFIG_TYPE_INT,
134 static int _mysql_init_db(ulog_iret_t *result);
136 /* our main output function, called by ulogd */
137 static int mysql_output(ulog_iret_t *result)
142 char *tmpstr; /* need this for --log-ip-as-string */
148 for (f = fields; f; f = f->next) {
149 res = keyh_getres(f->id);
152 ulogd_log(ULOGD_NOTICE,
153 "no result for %s ?!?\n", f->name);
156 if (!res || !IS_VALID((*res))) {
157 /* no result, we have to fake something */
158 sprintf(stmt_ins, "NULL,");
159 stmt_ins = stmt + strlen(stmt);
165 sprintf(stmt_ins, "%d,", res->value.i8);
167 case ULOGD_RET_INT16:
168 sprintf(stmt_ins, "%d,", res->value.i16);
170 case ULOGD_RET_INT32:
171 sprintf(stmt_ins, "%d,", res->value.i32);
173 case ULOGD_RET_INT64:
174 sprintf(stmt_ins, "%lld,", res->value.i64);
176 case ULOGD_RET_UINT8:
177 sprintf(stmt_ins, "%u,", res->value.ui8);
179 case ULOGD_RET_UINT16:
180 sprintf(stmt_ins, "%u,", res->value.ui16);
182 case ULOGD_RET_IPADDR:
184 memset(&addr, 0, sizeof(addr));
185 addr.s_addr = ntohl(res->value.ui32);
187 tmpstr = inet_ntoa(addr);
189 mysql_escape_string(stmt_ins, tmpstr,
192 mysql_real_escape_string(dbh, stmt_ins,
195 #endif /* OLD_MYSQL */
196 stmt_ins = stmt + strlen(stmt);
197 sprintf(stmt_ins, "',");
199 #endif /* IP_AS_STRING */
200 /* EVIL: fallthrough when logging IP as
202 case ULOGD_RET_UINT32:
203 sprintf(stmt_ins, "%u,", res->value.ui32);
205 case ULOGD_RET_UINT64:
206 sprintf(stmt_ins, "%llu,", res->value.ui64);
209 sprintf(stmt_ins, "'%d',", res->value.b);
211 case ULOGD_RET_STRING:
214 mysql_escape_string(stmt_ins, res->value.ptr,
215 strlen(res->value.ptr));
217 mysql_real_escape_string(dbh, stmt_ins,
218 res->value.ptr, strlen(res->value.ptr));
220 stmt_ins = stmt + strlen(stmt);
221 sprintf(stmt_ins, "',");
222 /* sprintf(stmt_ins, "'%s',", res->value.ptr); */
225 ulogd_log(ULOGD_NOTICE,
226 "%s: type RAW not supported by MySQL\n",
230 ulogd_log(ULOGD_NOTICE,
231 "unknown type %d for %s\n",
232 res->type, res->key);
235 stmt_ins = stmt + strlen(stmt);
237 *(stmt_ins - 1) = ')';
238 DEBUGP("stmt=#%s#\n", stmt);
240 /* now we have created our statement, insert it */
242 if (mysql_real_query(dbh, stmt, strlen(stmt))) {
243 ulogd_log(ULOGD_ERROR, "sql error during insert: %s\n",
245 return _mysql_init_db(result);
251 /* no connection, plugin disabled */
252 static int mysql_output_disabled(ulog_iret_t *result)
257 #define MYSQL_INSERTTEMPL "insert into X (Y) values (Z)"
258 #define MYSQL_VALSIZE 100
260 /* create the static part of our insert statement */
261 static int mysql_createstmt(void)
265 char buf[ULOGD_MAX_KEYLEN];
271 /* caclulate the size for the insert statement */
272 size = strlen(MYSQL_INSERTTEMPL) + strlen(table_ce.u.string);
274 for (f = fields; f; f = f->next) {
275 /* we need space for the key and a comma, as well as
276 * enough space for the values */
277 size += strlen(f->name) + 1 + MYSQL_VALSIZE;
280 ulogd_log(ULOGD_DEBUG, "allocating %u bytes for statement\n", size);
282 stmt = (char *) malloc(size);
285 ulogd_log(ULOGD_ERROR, "OOM!\n");
289 sprintf(stmt, "insert into %s (", table_ce.u.string);
290 stmt_val = stmt + strlen(stmt);
292 for (f = fields; f; f = f->next) {
293 strncpy(buf, f->name, ULOGD_MAX_KEYLEN);
294 while ((underscore = strchr(buf, '.')))
296 sprintf(stmt_val, "%s,", buf);
297 stmt_val = stmt + strlen(stmt);
299 *(stmt_val - 1) = ')';
301 sprintf(stmt_val, " values (");
302 stmt_val = stmt + strlen(stmt);
304 ulogd_log(ULOGD_DEBUG, "stmt='%s'\n", stmt);
309 /* find out which columns the table has */
310 static int mysql_get_columns(const char *table)
314 char buf[ULOGD_MAX_KEYLEN];
322 result = mysql_list_fields(dbh, table, NULL);
326 /* Cleanup before reconnect */
333 while ((field = mysql_fetch_field(result))) {
335 /* replace all underscores with dots */
336 strncpy(buf, field->name, ULOGD_MAX_KEYLEN);
337 while ((underscore = strchr(buf, '_')))
340 DEBUGP("field '%s' found: ", buf);
342 if (!(id = keyh_getid(buf))) {
343 DEBUGP(" no keyid!\n");
347 DEBUGP("keyid %u\n", id);
349 /* prepend it to the linked list */
350 f = (struct _field *) malloc(sizeof *f);
352 ulogd_log(ULOGD_ERROR, "OOM!\n");
355 strncpy(f->name, buf, ULOGD_MAX_KEYLEN);
361 mysql_free_result(result);
365 /* make connection and select database */
366 static int mysql_open_db(char *server, int port, char *user, char *pass,
369 dbh = mysql_init(NULL);
373 if (connect_timeout_ce.u.value)
374 mysql_options(dbh, MYSQL_OPT_CONNECT_TIMEOUT, (const char *) &connect_timeout_ce.u.value);
376 if (!mysql_real_connect(dbh, server, user, pass, db, port, NULL, 0))
382 static int init_reconnect(void)
384 if (reconnect_ce.u.value) {
385 reconnect = time(NULL);
386 if (reconnect != TIME_ERR) {
387 ulogd_log(ULOGD_ERROR, "no connection to database, "
388 "attempting to reconnect "
389 "after %u seconds\n",
390 reconnect_ce.u.value);
391 reconnect += reconnect_ce.u.value;
392 mysql_plugin.output = &_mysql_init_db;
396 /* Disable plugin permanently */
397 mysql_plugin.output = &mysql_output_disabled;
402 static int _mysql_init_db(ulog_iret_t *result)
404 if (reconnect && reconnect > time(NULL))
407 if (mysql_open_db(host_ce.u.string, port_ce.u.value, user_ce.u.string,
408 pass_ce.u.string, db_ce.u.string)) {
409 ulogd_log(ULOGD_ERROR, "can't establish database connection\n");
410 return init_reconnect();
413 /* read the fieldnames to know which values to insert */
414 if (mysql_get_columns(table_ce.u.string)) {
415 ulogd_log(ULOGD_ERROR, "unable to get mysql columns\n");
416 return init_reconnect();
421 mysql_plugin.output = &mysql_output;
426 return mysql_output(result);
431 static int _mysql_init(void)
433 /* have the opts parsed */
434 config_parse_file("MYSQL", &connect_timeout_ce);
436 return _mysql_init_db(NULL);
439 static void _mysql_fini(void)
444 static ulog_output_t mysql_plugin = {
446 .output = &mysql_output,
447 .init = &_mysql_init,
448 .fini = &_mysql_fini,
453 register_output(&mysql_plugin);