mysql/ulogd_MYSQL.c

   1 /* ulogd_MYSQL.c, Version $Revision: 5875 $
   2  *
   3  * ulogd output plugin for logging to a MySQL database
   4  *
   5  * (C) 2000-2001 by Harald Welte <laforge@gnumonks.org>
   6  *
   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
  10  *
  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.
  15  *
  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
  19  *
  20  * $Id: ulogd_MYSQL.c 5875 2005-08-01 06:49:59Z laforge $
  21  *
  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
  25  *
  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.
  31  *
  32  * 09 Feb 2005, Sven Schuster <schuster.sven@gmx.de>:
  33  *      Added the "port" parameter to specify ports different from 3306
  34  *
  35  * 12 May 2005, Jozsef Kadlecsik <kadlec@blackhole.kfki.hu>
  36  *      Added reconnecting to lost mysql server.
  37  */
  38
  39 #include <stdlib.h>
  40 #include <string.h>
  41 #include <time.h>
  42 #include <arpa/inet.h>
  43 #include <ulogd/ulogd.h>
  44 #include <ulogd/conffile.h>
  45 #include <mysql/mysql.h>
  46
  47 #ifdef DEBUG_MYSQL
  48 #define DEBUGP(x, args...)      fprintf(stderr, x, ## args)
  49 #else
  50 #define DEBUGP(x, args...)
  51 #endif
  52
  53 struct _field {
  54         char name[ULOGD_MAX_KEYLEN];
  55         unsigned int id;
  56         struct _field *next;
  57 };
  58
  59 /* The plugin handler */
  60 static ulog_output_t mysql_plugin;
  61
  62 /* the database handle we are using */
  63 static MYSQL *dbh;
  64
  65 /* a linked list of the fields the table has */
  66 static struct _field *fields;
  67
  68 /* buffer for our insert statement */
  69 static char *stmt;
  70
  71 /* pointer to the beginning of the "VALUES" part */
  72 static char *stmt_val;
  73
  74 /* pointer to current inser position in statement */
  75 static char *stmt_ins;
  76
  77 /* Attempt to reconnect if connection is lost */
  78 time_t reconnect = 0;
  79 #define TIME_ERR                ((time_t)-1)    /* Be paranoid */
  80
  81 /* our configuration directives */
  82 static config_entry_t db_ce = {
  83         .key = "db",
  84         .type = CONFIG_TYPE_STRING,
  85         .options = CONFIG_OPT_MANDATORY,
  86 };
  87
  88 static config_entry_t host_ce = {
  89         .next = &db_ce,
  90         .key = "host",
  91         .type = CONFIG_TYPE_STRING,
  92         .options = CONFIG_OPT_MANDATORY,
  93 };
  94
  95 static config_entry_t user_ce = {
  96         .next = &host_ce,
  97         .key = "user",
  98         .type = CONFIG_TYPE_STRING,
  99         .options = CONFIG_OPT_MANDATORY,
 100 };
 101
 102 static config_entry_t pass_ce = {
 103         .next = &user_ce,
 104         .key = "pass",
 105         .type = CONFIG_TYPE_STRING,
 106         .options = CONFIG_OPT_MANDATORY,
 107 };
 108
 109 static config_entry_t table_ce = {
 110         .next = &pass_ce,
 111         .key = "table",
 112         .type = CONFIG_TYPE_STRING,
 113         .options = CONFIG_OPT_MANDATORY,
 114 };
 115
 116 static config_entry_t port_ce = {
 117         .next = &table_ce,
 118         .key = "port",
 119         .type = CONFIG_TYPE_INT,
 120 };
 121
 122 static config_entry_t reconnect_ce = {
 123         .next = &port_ce,
 124         .key = "reconnect",
 125         .type = CONFIG_TYPE_INT,
 126 };
 127
 128 static config_entry_t connect_timeout_ce = {
 129         .next = &reconnect_ce,
 130         .key = "connect_timeout",
 131         .type = CONFIG_TYPE_INT,
 132 };
 133
 134 static int _mysql_init_db(ulog_iret_t *result);
 135
 136 /* our main output function, called by ulogd */
 137 static int mysql_output(ulog_iret_t *result)
 138 {
 139         struct _field *f;
 140         ulog_iret_t *res;
 141 #ifdef IP_AS_STRING
 142         char *tmpstr;           /* need this for --log-ip-as-string */
 143         struct in_addr addr;
 144 #endif
 145
 146         stmt_ins = stmt_val;
 147
 148         for (f = fields; f; f = f->next) {
 149                 res = keyh_getres(f->id);
 150
 151                 if (!res) {
 152                         ulogd_log(ULOGD_NOTICE,
 153                                 "no result for %s ?!?\n", f->name);
 154                 }
 155
 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);
 160                         continue;
 161                 }
 162
 163                 switch (res->type) {
 164                         case ULOGD_RET_INT8:
 165                                 sprintf(stmt_ins, "%d,", res->value.i8);
 166                                 break;
 167                         case ULOGD_RET_INT16:
 168                                 sprintf(stmt_ins, "%d,", res->value.i16);
 169                                 break;
 170                         case ULOGD_RET_INT32:
 171                                 sprintf(stmt_ins, "%d,", res->value.i32);
 172                                 break;
 173                         case ULOGD_RET_INT64:
 174                                 sprintf(stmt_ins, "%lld,", res->value.i64);
 175                                 break;
 176                         case ULOGD_RET_UINT8:
 177                                 sprintf(stmt_ins, "%u,", res->value.ui8);
 178                                 break;
 179                         case ULOGD_RET_UINT16:
 180                                 sprintf(stmt_ins, "%u,", res->value.ui16);
 181                                 break;
 182                         case ULOGD_RET_IPADDR:
 183 #ifdef IP_AS_STRING
 184                                 memset(&addr, 0, sizeof(addr));
 185                                 addr.s_addr = ntohl(res->value.ui32);
 186                                 *stmt_ins++ = '\'';
 187                                 tmpstr = inet_ntoa(addr);
 188 #ifdef OLD_MYSQL
 189                                 mysql_escape_string(stmt_ins, tmpstr,
 190                                                     strlen(tmpstr));
 191 #else
 192                                 mysql_real_escape_string(dbh, stmt_ins,
 193                                                          tmpstr,
 194                                                          strlen(tmpstr));
 195 #endif /* OLD_MYSQL */
 196                                 stmt_ins = stmt + strlen(stmt);
 197                                 sprintf(stmt_ins, "',");
 198                                 break;
 199 #endif /* IP_AS_STRING */
 200                                 /* EVIL: fallthrough when logging IP as
 201                                  * u_int32_t */
 202                         case ULOGD_RET_UINT32:
 203                                 sprintf(stmt_ins, "%u,", res->value.ui32);
 204                                 break;
 205                         case ULOGD_RET_UINT64:
 206                                 sprintf(stmt_ins, "%llu,", res->value.ui64);
 207                                 break;
 208                         case ULOGD_RET_BOOL:
 209                                 sprintf(stmt_ins, "'%d',", res->value.b);
 210                                 break;
 211                         case ULOGD_RET_STRING:
 212                                 *stmt_ins++ = '\'';
 213 #ifdef OLD_MYSQL
 214                                 mysql_escape_string(stmt_ins, res->value.ptr,
 215                                         strlen(res->value.ptr));
 216 #else
 217                                 mysql_real_escape_string(dbh, stmt_ins,
 218                                         res->value.ptr, strlen(res->value.ptr));
 219 #endif
 220                                 stmt_ins = stmt + strlen(stmt);
 221                                 sprintf(stmt_ins, "',");
 222                         /* sprintf(stmt_ins, "'%s',", res->value.ptr); */
 223                                 break;
 224                         case ULOGD_RET_RAW:
 225                                 ulogd_log(ULOGD_NOTICE,
 226                                         "%s: type RAW not supported by MySQL\n",
 227                                         res->key);
 228                                 break;
 229                         default:
 230                                 ulogd_log(ULOGD_NOTICE,
 231                                         "unknown type %d for %s\n",
 232                                         res->type, res->key);
 233                                 break;
 234                 }
 235                 stmt_ins = stmt + strlen(stmt);
 236         }
 237         *(stmt_ins - 1) = ')';
 238         DEBUGP("stmt=#%s#\n", stmt);
 239
 240         /* now we have created our statement, insert it */
 241
 242         if (mysql_real_query(dbh, stmt, strlen(stmt))) {
 243                 ulogd_log(ULOGD_ERROR, "sql error during insert: %s\n",
 244                                 mysql_error(dbh));
 245                 return _mysql_init_db(result);
 246         }
 247
 248         return 0;
 249 }
 250
 251 /* no connection, plugin disabled */
 252 static int mysql_output_disabled(ulog_iret_t *result)
 253 {
 254         return 0;
 255 }
 256
 257 #define MYSQL_INSERTTEMPL   "insert into X (Y) values (Z)"
 258 #define MYSQL_VALSIZE   100
 259
 260 /* create the static part of our insert statement */
 261 static int mysql_createstmt(void)
 262 {
 263         struct _field *f;
 264         unsigned int size;
 265         char buf[ULOGD_MAX_KEYLEN];
 266         char *underscore;
 267
 268         if (stmt)
 269                 free(stmt);
 270
 271         /* caclulate the size for the insert statement */
 272         size = strlen(MYSQL_INSERTTEMPL) + strlen(table_ce.u.string);
 273
 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;
 278         }
 279
 280         ulogd_log(ULOGD_DEBUG, "allocating %u bytes for statement\n", size);
 281
 282         stmt = (char *) malloc(size);
 283
 284         if (!stmt) {
 285                 ulogd_log(ULOGD_ERROR, "OOM!\n");
 286                 return -1;
 287         }
 288
 289         sprintf(stmt, "insert into %s (", table_ce.u.string);
 290         stmt_val = stmt + strlen(stmt);
 291
 292         for (f = fields; f; f = f->next) {
 293                 strncpy(buf, f->name, ULOGD_MAX_KEYLEN);
 294                 while ((underscore = strchr(buf, '.')))
 295                         *underscore = '_';
 296                 sprintf(stmt_val, "%s,", buf);
 297                 stmt_val = stmt + strlen(stmt);
 298         }
 299         *(stmt_val - 1) = ')';
 300
 301         sprintf(stmt_val, " values (");
 302         stmt_val = stmt + strlen(stmt);
 303
 304         ulogd_log(ULOGD_DEBUG, "stmt='%s'\n", stmt);
 305
 306         return 0;
 307 }
 308
 309 /* find out which columns the table has */
 310 static int mysql_get_columns(const char *table)
 311 {
 312         MYSQL_RES *result;
 313         MYSQL_FIELD *field;
 314         char buf[ULOGD_MAX_KEYLEN];
 315         char *underscore;
 316         struct _field *f;
 317         int id;
 318
 319         if (!dbh)
 320                 return -1;
 321
 322         result = mysql_list_fields(dbh, table, NULL);
 323         if (!result)
 324                 return -1;
 325
 326         /* Cleanup before reconnect */
 327         while (fields) {
 328                 f = fields;
 329                 fields = f->next;
 330                 free(f);
 331         }
 332
 333         while ((field = mysql_fetch_field(result))) {
 334
 335                 /* replace all underscores with dots */
 336                 strncpy(buf, field->name, ULOGD_MAX_KEYLEN);
 337                 while ((underscore = strchr(buf, '_')))
 338                         *underscore = '.';
 339
 340                 DEBUGP("field '%s' found: ", buf);
 341
 342                 if (!(id = keyh_getid(buf))) {
 343                         DEBUGP(" no keyid!\n");
 344                         continue;
 345                 }
 346
 347                 DEBUGP("keyid %u\n", id);
 348
 349                 /* prepend it to the linked list */
 350                 f = (struct _field *) malloc(sizeof *f);
 351                 if (!f) {
 352                         ulogd_log(ULOGD_ERROR, "OOM!\n");
 353                         return -1;
 354                 }
 355                 strncpy(f->name, buf, ULOGD_MAX_KEYLEN);
 356                 f->id = id;
 357                 f->next = fields;
 358                 fields = f;
 359         }
 360
 361         mysql_free_result(result);
 362         return 0;
 363 }
 364
 365 /* make connection and select database */
 366 static int mysql_open_db(char *server, int port, char *user, char *pass,
 367                          char *db)
 368 {
 369         dbh = mysql_init(NULL);
 370         if (!dbh)
 371                 return -1;
 372
 373         if (connect_timeout_ce.u.value)
 374                 mysql_options(dbh, MYSQL_OPT_CONNECT_TIMEOUT, (const char *) &connect_timeout_ce.u.value);
 375
 376         if (!mysql_real_connect(dbh, server, user, pass, db, port, NULL, 0))
 377                 return -1;
 378
 379         return 0;
 380 }
 381
 382 static int init_reconnect(void)
 383 {
 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;
 393                         return -1;
 394                 }
 395         }
 396         /* Disable plugin permanently */
 397         mysql_plugin.output = &mysql_output_disabled;
 398
 399         return 0;
 400 }
 401
 402 static int _mysql_init_db(ulog_iret_t *result)
 403 {
 404         if (reconnect && reconnect > time(NULL))
 405                 return 0;
 406
 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();
 411         }
 412
 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();
 417         }
 418         mysql_createstmt();
 419
 420         /* enable plugin */
 421         mysql_plugin.output = &mysql_output;
 422
 423         reconnect = 0;
 424
 425         if (result)
 426                 return mysql_output(result);
 427
 428         return 0;
 429 }
 430
 431 static int _mysql_init(void)
 432 {
 433         /* have the opts parsed */
 434         config_parse_file("MYSQL", &connect_timeout_ce);
 435
 436         return _mysql_init_db(NULL);
 437 }
 438
 439 static void _mysql_fini(void)
 440 {
 441         mysql_close(dbh);
 442 }
 443
 444 static ulog_output_t mysql_plugin = {
 445         .name = "mysql",
 446         .output = &mysql_output,
 447         .init = &_mysql_init,
 448         .fini = &_mysql_fini,
 449 };
 450
 451 void _init(void)
 452 {
 453         register_output(&mysql_plugin);
 454 }