cdr/cdr_tds.c

   1 /*
   2  * Asterisk -- An open source telephony toolkit.
   3  *
   4  * Copyright (C) 2004 - 2006, Digium, Inc.
   5  *
   6  * See http://www.asterisk.org for more information about
   7  * the Asterisk project. Please do not directly contact
   8  * any of the maintainers of this project for assistance;
   9  * the project provides a web site, mailing lists and IRC
  10  * channels for your use.
  11  *
  12  * This program is free software, distributed under the terms of
  13  * the GNU General Public License Version 2. See the LICENSE file
  14  * at the top of the source tree.
  15  */
  16
  17 /*!
  18  * \file
  19  * \brief FreeTDS CDR logger
  20  *
  21  * See also
  22  * \arg \ref Config_cdr
  23  * \arg http://www.freetds.org/
  24  * \ingroup cdr_drivers
  25  */
  26
  27 /*!
  28  * \verbatim
  29  *
  30  * Table Structure for `cdr`
  31  *
  32  * Created on: 05/20/2004 16:16
  33  * Last changed on: 07/27/2004 20:01
  34
  35 CREATE TABLE [dbo].[cdr] (
  36         [accountcode] [varchar] (20) NULL ,
  37         [src] [varchar] (80) NULL ,
  38         [dst] [varchar] (80) NULL ,
  39         [dcontext] [varchar] (80) NULL ,
  40         [clid] [varchar] (80) NULL ,
  41         [channel] [varchar] (80) NULL ,
  42         [dstchannel] [varchar] (80) NULL ,
  43         [lastapp] [varchar] (80) NULL ,
  44         [lastdata] [varchar] (80) NULL ,
  45         [start] [datetime] NULL ,
  46         [answer] [datetime] NULL ,
  47         [end] [datetime] NULL ,
  48         [duration] [int] NULL ,
  49         [billsec] [int] NULL ,
  50         [disposition] [varchar] (20) NULL ,
  51         [amaflags] [varchar] (16) NULL ,
  52         [uniqueid] [varchar] (32) NULL ,
  53         [userfield] [varchar] (256) NULL
  54 ) ON [PRIMARY]
  55
  56 \endverbatim
  57
  58 */
  59
  60 /*** MODULEINFO
  61         <depend>freetds</depend>
  62         <support_level>extended</support_level>
  63  ***/
  64
  65 #include "asterisk.h"
  66
  67 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
  68
  69 #include "asterisk/config.h"
  70 #include "asterisk/channel.h"
  71 #include "asterisk/cdr.h"
  72 #include "asterisk/module.h"
  73
  74 #include <sqlfront.h>
  75 #include <sybdb.h>
  76
  77 #define DATE_FORMAT "%Y/%m/%d %T"
  78
  79 static const char name[] = "FreeTDS (MSSQL)";
  80 static const char config[] = "cdr_tds.conf";
  81
  82 struct cdr_tds_config {
  83         AST_DECLARE_STRING_FIELDS(
  84                 AST_STRING_FIELD(hostname);
  85                 AST_STRING_FIELD(database);
  86                 AST_STRING_FIELD(username);
  87                 AST_STRING_FIELD(password);
  88                 AST_STRING_FIELD(table);
  89                 AST_STRING_FIELD(charset);
  90                 AST_STRING_FIELD(language);
  91                 AST_STRING_FIELD(hrtime);
  92         );
  93         DBPROCESS *dbproc;
  94         unsigned int connected:1;
  95         unsigned int has_userfield:1;
  96 };
  97
  98 AST_MUTEX_DEFINE_STATIC(tds_lock);
  99
 100 static struct cdr_tds_config *settings;
 101
 102 static char *anti_injection(const char *, int);
 103 static void get_date(char *, size_t len, struct timeval);
 104
 105 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
 106         __attribute__((format(printf, 2, 3)));
 107
 108 static int mssql_connect(void);
 109 static int mssql_disconnect(void);
 110
 111 static int tds_log(struct ast_cdr *cdr)
 112 {
 113         char start[80], answer[80], end[80];
 114         char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid, *userfield = NULL;
 115         RETCODE erc;
 116         int res = -1;
 117         int attempt = 1;
 118
 119         accountcode = anti_injection(cdr->accountcode, 20);
 120         src         = anti_injection(cdr->src, 80);
 121         dst         = anti_injection(cdr->dst, 80);
 122         dcontext    = anti_injection(cdr->dcontext, 80);
 123         clid        = anti_injection(cdr->clid, 80);
 124         channel     = anti_injection(cdr->channel, 80);
 125         dstchannel  = anti_injection(cdr->dstchannel, 80);
 126         lastapp     = anti_injection(cdr->lastapp, 80);
 127         lastdata    = anti_injection(cdr->lastdata, 80);
 128         uniqueid    = anti_injection(cdr->uniqueid, 32);
 129
 130         get_date(start, sizeof(start), cdr->start);
 131         get_date(answer, sizeof(answer), cdr->answer);
 132         get_date(end, sizeof(end), cdr->end);
 133
 134         ast_mutex_lock(&tds_lock);
 135
 136         if (settings->has_userfield) {
 137                 userfield = anti_injection(cdr->userfield, AST_MAX_USER_FIELD);
 138         }
 139
 140 retry:
 141         /* Ensure that we are connected */
 142         if (!settings->connected) {
 143                 ast_log(LOG_NOTICE, "Attempting to reconnect to %s (Attempt %d)\n", settings->hostname, attempt);
 144                 if (mssql_connect()) {
 145                         /* Connect failed */
 146                         if (attempt++ < 3) {
 147                                 goto retry;
 148                         }
 149                         goto done;
 150                 }
 151         }
 152
 153         if (settings->has_userfield) {
 154                 if (settings->hrtime) {
 155                         double hrbillsec = 0.0;
 156                         double hrduration;
 157
 158                         if (!ast_tvzero(cdr->answer)) {
 159                                 hrbillsec = (double)(ast_tvdiff_us(cdr->end, cdr->answer) / 1000000.0);
 160                         }
 161                         hrduration = (double)(ast_tvdiff_us(cdr->end, cdr->start) / 1000000.0);
 162
 163                         erc = dbfcmd(settings->dbproc,
 164                                          "INSERT INTO %s "
 165                                          "("
 166                                          "accountcode, src, dst, dcontext, clid, channel, "
 167                                          "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
 168                                          "billsec, disposition, amaflags, uniqueid, userfield"
 169                                          ") "
 170                                          "VALUES "
 171                                          "("
 172                                          "'%s', '%s', '%s', '%s', '%s', '%s', "
 173                                          "'%s', '%s', '%s', %s, %s, %s, %lf, "
 174                                          "%lf, '%s', '%s', '%s', '%s'"
 175                                          ")",
 176                                          settings->table,
 177                                          accountcode, src, dst, dcontext, clid, channel,
 178                                          dstchannel, lastapp, lastdata, start, answer, end, hrduration,
 179                                          hrbillsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid,
 180                                          userfield
 181                         );
 182                 } else {
 183                         erc = dbfcmd(settings->dbproc,
 184                                          "INSERT INTO %s "
 185                                          "("
 186                                          "accountcode, src, dst, dcontext, clid, channel, "
 187                                          "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
 188                                          "billsec, disposition, amaflags, uniqueid, userfield"
 189                                          ") "
 190                                          "VALUES "
 191                                          "("
 192                                          "'%s', '%s', '%s', '%s', '%s', '%s', "
 193                                          "'%s', '%s', '%s', %s, %s, %s, %ld, "
 194                                          "%ld, '%s', '%s', '%s', '%s'"
 195                                          ")",
 196                                          settings->table,
 197                                          accountcode, src, dst, dcontext, clid, channel,
 198                                          dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
 199                                          cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid,
 200                                          userfield
 201                         );
 202                 }
 203         } else {
 204                 if (settings->hrtime) {
 205                         double hrbillsec = 0.0;
 206                         double hrduration;
 207
 208                         if (!ast_tvzero(cdr->answer)) {
 209                                 hrbillsec = (double)(ast_tvdiff_us(cdr->end, cdr->answer) / 1000000.0);
 210                         }
 211                         hrduration = (double)(ast_tvdiff_us(cdr->end, cdr->start) / 1000000.0);
 212
 213                         erc = dbfcmd(settings->dbproc,
 214                                          "INSERT INTO %s "
 215                                          "("
 216                                          "accountcode, src, dst, dcontext, clid, channel, "
 217                                          "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
 218                                          "billsec, disposition, amaflags, uniqueid"
 219                                          ") "
 220                                          "VALUES "
 221                                          "("
 222                                          "'%s', '%s', '%s', '%s', '%s', '%s', "
 223                                          "'%s', '%s', '%s', %s, %s, %s, %lf, "
 224                                          "%lf, '%s', '%s', '%s'"
 225                                          ")",
 226                                          settings->table,
 227                                          accountcode, src, dst, dcontext, clid, channel,
 228                                          dstchannel, lastapp, lastdata, start, answer, end, hrduration,
 229                                          hrbillsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid
 230                         );
 231                 } else {
 232                         erc = dbfcmd(settings->dbproc,
 233                                          "INSERT INTO %s "
 234                                          "("
 235                                          "accountcode, src, dst, dcontext, clid, channel, "
 236                                          "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
 237                                          "billsec, disposition, amaflags, uniqueid"
 238                                          ") "
 239                                          "VALUES "
 240                                          "("
 241                                          "'%s', '%s', '%s', '%s', '%s', '%s', "
 242                                          "'%s', '%s', '%s', %s, %s, %s, %ld, "
 243                                          "%ld, '%s', '%s', '%s'"
 244                                          ")",
 245                                          settings->table,
 246                                          accountcode, src, dst, dcontext, clid, channel,
 247                                          dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
 248                                          cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid
 249                         );
 250                 }
 251         }
 252
 253         if (erc == FAIL) {
 254                 if (attempt++ < 3) {
 255                         ast_log(LOG_NOTICE, "Failed to build INSERT statement, retrying...\n");
 256                         mssql_disconnect();
 257                         goto retry;
 258                 } else {
 259                         ast_log(LOG_ERROR, "Failed to build INSERT statement, no CDR was logged.\n");
 260                         goto done;
 261                 }
 262         }
 263
 264         if (dbsqlexec(settings->dbproc) == FAIL) {
 265                 if (attempt++ < 3) {
 266                         ast_log(LOG_NOTICE, "Failed to execute INSERT statement, retrying...\n");
 267                         mssql_disconnect();
 268                         goto retry;
 269                 } else {
 270                         ast_log(LOG_ERROR, "Failed to execute INSERT statement, no CDR was logged.\n");
 271                         goto done;
 272                 }
 273         }
 274
 275         /* Consume any results we might get back (this is more of a sanity check than
 276          * anything else, since an INSERT shouldn't return results). */
 277         while (dbresults(settings->dbproc) != NO_MORE_RESULTS) {
 278                 while (dbnextrow(settings->dbproc) != NO_MORE_ROWS);
 279         }
 280
 281         res = 0;
 282
 283 done:
 284         ast_mutex_unlock(&tds_lock);
 285
 286         ast_free(accountcode);
 287         ast_free(src);
 288         ast_free(dst);
 289         ast_free(dcontext);
 290         ast_free(clid);
 291         ast_free(channel);
 292         ast_free(dstchannel);
 293         ast_free(lastapp);
 294         ast_free(lastdata);
 295         ast_free(uniqueid);
 296
 297         if (userfield) {
 298                 ast_free(userfield);
 299         }
 300
 301         return res;
 302 }
 303
 304 static char *anti_injection(const char *str, int len)
 305 {
 306         /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
 307         char *buf;
 308         char *buf_ptr, *srh_ptr;
 309         char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
 310         int idx;
 311
 312         if (!(buf = ast_calloc(1, len + 1))) {
 313                 ast_log(LOG_ERROR, "Out of memory\n");
 314                 return NULL;
 315         }
 316
 317         buf_ptr = buf;
 318
 319         /* Escape single quotes */
 320         for (; *str && strlen(buf) < len; str++) {
 321                 if (*str == '\'') {
 322                         *buf_ptr++ = '\'';
 323                 }
 324                 *buf_ptr++ = *str;
 325         }
 326         *buf_ptr = '\0';
 327
 328         /* Erase known bad input */
 329         for (idx = 0; *known_bad[idx]; idx++) {
 330                 while ((srh_ptr = strcasestr(buf, known_bad[idx]))) {
 331                         memmove(srh_ptr, srh_ptr + strlen(known_bad[idx]), strlen(srh_ptr + strlen(known_bad[idx])) + 1);
 332                 }
 333         }
 334
 335         return buf;
 336 }
 337
 338 static void get_date(char *dateField, size_t len, struct timeval when)
 339 {
 340         /* To make sure we have date variable if not insert null to SQL */
 341         if (!ast_tvzero(when)) {
 342                 struct ast_tm tm;
 343                 ast_localtime(&when, &tm, NULL);
 344                 ast_strftime(dateField, len, "'" DATE_FORMAT "'", &tm);
 345         } else {
 346                 ast_copy_string(dateField, "null", len);
 347         }
 348 }
 349
 350 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
 351 {
 352         va_list ap;
 353         char *buffer;
 354
 355         va_start(ap, fmt);
 356         if (ast_vasprintf(&buffer, fmt, ap) < 0) {
 357                 va_end(ap);
 358                 return 1;
 359         }
 360         va_end(ap);
 361
 362         if (dbfcmd(dbproc, buffer) == FAIL) {
 363                 ast_free(buffer);
 364                 return 1;
 365         }
 366
 367         ast_free(buffer);
 368
 369         if (dbsqlexec(dbproc) == FAIL) {
 370                 return 1;
 371         }
 372
 373         /* Consume the result set (we don't really care about the result, though) */
 374         while (dbresults(dbproc) != NO_MORE_RESULTS) {
 375                 while (dbnextrow(dbproc) != NO_MORE_ROWS);
 376         }
 377
 378         return 0;
 379 }
 380
 381 static int mssql_disconnect(void)
 382 {
 383         if (settings->dbproc) {
 384                 dbclose(settings->dbproc);
 385                 settings->dbproc = NULL;
 386         }
 387
 388         settings->connected = 0;
 389
 390         return 0;
 391 }
 392
 393 static int mssql_connect(void)
 394 {
 395         LOGINREC *login;
 396
 397         if ((login = dblogin()) == NULL) {
 398                 ast_log(LOG_ERROR, "Unable to allocate login structure for db-lib\n");
 399                 return -1;
 400         }
 401
 402         DBSETLAPP(login,     "TSQL");
 403         DBSETLUSER(login,    (char *) settings->username);
 404         DBSETLPWD(login,     (char *) settings->password);
 405         DBSETLCHARSET(login, (char *) settings->charset);
 406         DBSETLNATLANG(login, (char *) settings->language);
 407
 408         if ((settings->dbproc = dbopen(login, (char *) settings->hostname)) == NULL) {
 409                 ast_log(LOG_ERROR, "Unable to connect to %s\n", settings->hostname);
 410                 dbloginfree(login);
 411                 return -1;
 412         }
 413
 414         dbloginfree(login);
 415
 416         if (dbuse(settings->dbproc, (char *) settings->database) == FAIL) {
 417                 ast_log(LOG_ERROR, "Unable to select database %s\n", settings->database);
 418                 goto failed;
 419         }
 420
 421         if (execute_and_consume(settings->dbproc, "SELECT 1 FROM [%s] WHERE 1 = 0", settings->table)) {
 422                 ast_log(LOG_ERROR, "Unable to find table '%s'\n", settings->table);
 423                 goto failed;
 424         }
 425
 426         /* Check to see if we have a userfield column in the table */
 427         if (execute_and_consume(settings->dbproc, "SELECT userfield FROM [%s] WHERE 1 = 0", settings->table)) {
 428                 ast_log(LOG_NOTICE, "Unable to find 'userfield' column in table '%s'\n", settings->table);
 429                 settings->has_userfield = 0;
 430         } else {
 431                 settings->has_userfield = 1;
 432         }
 433
 434         settings->connected = 1;
 435
 436         return 0;
 437
 438 failed:
 439         dbclose(settings->dbproc);
 440         settings->dbproc = NULL;
 441         return -1;
 442 }
 443
 444 static int tds_unload_module(void)
 445 {
 446         if (settings) {
 447                 ast_mutex_lock(&tds_lock);
 448                 mssql_disconnect();
 449                 ast_mutex_unlock(&tds_lock);
 450
 451                 ast_string_field_free_memory(settings);
 452                 ast_free(settings);
 453         }
 454
 455         ast_cdr_unregister(name);
 456
 457         dbexit();
 458
 459         return 0;
 460 }
 461
 462 static int tds_error_handler(DBPROCESS *dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
 463 {
 464         ast_log(LOG_ERROR, "%s (%d)\n", dberrstr, dberr);
 465
 466         if (oserr != DBNOERR) {
 467                 ast_log(LOG_ERROR, "%s (%d)\n", oserrstr, oserr);
 468         }
 469
 470         return INT_CANCEL;
 471 }
 472
 473 static int tds_message_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
 474 {
 475         ast_debug(1, "Msg %d, Level %d, State %d, Line %d\n", msgno, severity, msgstate, line);
 476         ast_log(LOG_NOTICE, "%s\n", msgtext);
 477
 478         return 0;
 479 }
 480
 481 static int tds_load_module(int reload)
 482 {
 483         struct ast_config *cfg;
 484         const char *ptr = NULL;
 485         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
 486
 487         cfg = ast_config_load(config, config_flags);
 488         if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
 489                 ast_log(LOG_NOTICE, "Unable to load TDS config for CDRs: %s\n", config);
 490                 return 0;
 491         } else if (cfg == CONFIG_STATUS_FILEUNCHANGED)
 492                 return 0;
 493
 494         if (!ast_variable_browse(cfg, "global")) {
 495                 /* nothing configured */
 496                 ast_config_destroy(cfg);
 497                 return 0;
 498         }
 499
 500         ast_mutex_lock(&tds_lock);
 501
 502         /* Clear out any existing settings */
 503         ast_string_field_init(settings, 0);
 504
 505         /* 'connection' is the new preferred configuration option */
 506         ptr = ast_variable_retrieve(cfg, "global", "connection");
 507         if (ptr) {
 508                 ast_string_field_set(settings, hostname, ptr);
 509         } else {
 510                 /* But we keep 'hostname' for backwards compatibility */
 511                 ptr = ast_variable_retrieve(cfg, "global", "hostname");
 512                 if (ptr) {
 513                         ast_string_field_set(settings, hostname, ptr);
 514                 } else {
 515                         ast_log(LOG_ERROR, "Failed to connect: Database server connection not specified.\n");
 516                         goto failed;
 517                 }
 518         }
 519
 520         ptr = ast_variable_retrieve(cfg, "global", "dbname");
 521         if (ptr) {
 522                 ast_string_field_set(settings, database, ptr);
 523         } else {
 524                 ast_log(LOG_ERROR, "Failed to connect: Database dbname not specified.\n");
 525                 goto failed;
 526         }
 527
 528         ptr = ast_variable_retrieve(cfg, "global", "user");
 529         if (ptr) {
 530                 ast_string_field_set(settings, username, ptr);
 531         } else {
 532                 ast_log(LOG_ERROR, "Failed to connect: Database dbuser not specified.\n");
 533                 goto failed;
 534         }
 535
 536         ptr = ast_variable_retrieve(cfg, "global", "password");
 537         if (ptr) {
 538                 ast_string_field_set(settings, password, ptr);
 539         } else {
 540                 ast_log(LOG_ERROR, "Failed to connect: Database password not specified.\n");
 541                 goto failed;
 542         }
 543
 544         ptr = ast_variable_retrieve(cfg, "global", "charset");
 545         if (ptr) {
 546                 ast_string_field_set(settings, charset, ptr);
 547         } else {
 548                 ast_string_field_set(settings, charset, "iso_1");
 549         }
 550
 551         ptr = ast_variable_retrieve(cfg, "global", "language");
 552         if (ptr) {
 553                 ast_string_field_set(settings, language, ptr);
 554         } else {
 555                 ast_string_field_set(settings, language, "us_english");
 556         }
 557
 558         ptr = ast_variable_retrieve(cfg, "global", "table");
 559         if (ptr) {
 560                 ast_string_field_set(settings, table, ptr);
 561         } else {
 562                 ast_log(LOG_NOTICE, "Table name not specified, using 'cdr' by default.\n");
 563                 ast_string_field_set(settings, table, "cdr");
 564         }
 565
 566         ptr = ast_variable_retrieve(cfg, "global", "hrtime");
 567         if (ptr && ast_true(ptr)) {
 568                 ast_string_field_set(settings, hrtime, ptr);
 569         } else {
 570                 ast_log(LOG_NOTICE, "High Resolution Time not found, using integers for billsec and duration fields by default.\n");
 571         }
 572
 573         mssql_disconnect();
 574
 575         if (mssql_connect()) {
 576                 /* We failed to connect (mssql_connect takes care of logging it) */
 577                 goto failed;
 578         }
 579
 580         ast_mutex_unlock(&tds_lock);
 581         ast_config_destroy(cfg);
 582
 583         return 1;
 584
 585 failed:
 586         ast_mutex_unlock(&tds_lock);
 587         ast_config_destroy(cfg);
 588
 589         return 0;
 590 }
 591
 592 static int reload(void)
 593 {
 594         return tds_load_module(1);
 595 }
 596
 597 static int load_module(void)
 598 {
 599         if (dbinit() == FAIL) {
 600                 ast_log(LOG_ERROR, "Failed to initialize FreeTDS db-lib\n");
 601                 return AST_MODULE_LOAD_DECLINE;
 602         }
 603
 604         dberrhandle(tds_error_handler);
 605         dbmsghandle(tds_message_handler);
 606
 607         settings = ast_calloc_with_stringfields(1, struct cdr_tds_config, 256);
 608
 609         if (!settings) {
 610                 dbexit();
 611                 return AST_MODULE_LOAD_DECLINE;
 612         }
 613
 614         if (!tds_load_module(0)) {
 615                 ast_string_field_free_memory(settings);
 616                 ast_free(settings);
 617                 settings = NULL;
 618                 dbexit();
 619                 return AST_MODULE_LOAD_DECLINE;
 620         }
 621
 622         ast_cdr_register(name, ast_module_info->description, tds_log);
 623
 624         return AST_MODULE_LOAD_SUCCESS;
 625 }
 626
 627 static int unload_module(void)
 628 {
 629         return tds_unload_module();
 630 }
 631
 632 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "FreeTDS CDR Backend",
 633                 .load = load_module,
 634                 .unload = unload_module,
 635                 .reload = reload,
 636                 .load_pri = AST_MODPRI_CDR_DRIVER,
 637                );