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 /*! \file
  18  *
  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 /*! \verbatim
  28  *
  29  * Table Structure for `cdr`
  30  *
  31  * Created on: 05/20/2004 16:16
  32  * Last changed on: 07/27/2004 20:01
  33
  34 CREATE TABLE [dbo].[cdr] (
  35         [accountcode] [varchar] (20) NULL ,
  36         [src] [varchar] (80) NULL ,
  37         [dst] [varchar] (80) NULL ,
  38         [dcontext] [varchar] (80) NULL ,
  39         [clid] [varchar] (80) NULL ,
  40         [channel] [varchar] (80) NULL ,
  41         [dstchannel] [varchar] (80) NULL ,
  42         [lastapp] [varchar] (80) NULL ,
  43         [lastdata] [varchar] (80) NULL ,
  44         [start] [datetime] NULL ,
  45         [answer] [datetime] NULL ,
  46         [end] [datetime] NULL ,
  47         [duration] [int] NULL ,
  48         [billsec] [int] NULL ,
  49         [disposition] [varchar] (20) NULL ,
  50         [amaflags] [varchar] (16) NULL ,
  51         [uniqueid] [varchar] (32) NULL
  52 ) ON [PRIMARY]
  53
  54 \endverbatim
  55
  56 */
  57
  58 /*** MODULEINFO
  59         <depend>freetds</depend>
  60  ***/
  61
  62 #include "asterisk.h"
  63
  64 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
  65
  66 #include <time.h>
  67 #include <math.h>
  68
  69 #include <tds.h>
  70 #include <tdsconvert.h>
  71 #include <ctype.h>
  72
  73 #include "asterisk/config.h"
  74 #include "asterisk/channel.h"
  75 #include "asterisk/cdr.h"
  76 #include "asterisk/module.h"
  77
  78 #ifdef FREETDS_PRE_0_62
  79 #warning "You have older TDS, you should upgrade!"
  80 #endif
  81
  82 #define DATE_FORMAT "%Y/%m/%d %T"
  83
  84 static char *name = "mssql";
  85 static char *config = "cdr_tds.conf";
  86
  87 struct cdr_tds_config {
  88         AST_DECLARE_STRING_FIELDS(
  89                 AST_STRING_FIELD(hostname);
  90                 AST_STRING_FIELD(dbname);
  91                 AST_STRING_FIELD(dbuser);
  92                 AST_STRING_FIELD(password);
  93                 AST_STRING_FIELD(table);
  94                 AST_STRING_FIELD(charset);
  95                 AST_STRING_FIELD(language);
  96         );
  97         TDSSOCKET *tds;
  98         TDSLOGIN *login;
  99         TDSCONTEXT *context;
 100         unsigned int connected:1;
 101         ast_mutex_t lock;
 102 };
 103
 104 static struct cdr_tds_config *settings;
 105
 106 static char *anti_injection(const char *, int);
 107 static void get_date(char *, struct timeval);
 108
 109 static int mssql_connect(void);
 110 static int mssql_disconnect(void);
 111
 112 static void cdr_tds_config_destroy(void);
 113
 114 static int tds_log(struct ast_cdr *cdr)
 115 {
 116         char sqlcmd[2048], start[80], answer[80], end[80];
 117         char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid;
 118         int res = 0;
 119         int retried = 0;
 120 #ifdef FREETDS_PRE_0_62
 121         TDS_INT result_type;
 122 #endif
 123
 124         ast_mutex_lock(&settings->lock);
 125
 126         memset(sqlcmd, 0, 2048);
 127
 128         accountcode = anti_injection(cdr->accountcode, 20);
 129         src = anti_injection(cdr->src, 80);
 130         dst = anti_injection(cdr->dst, 80);
 131         dcontext = anti_injection(cdr->dcontext, 80);
 132         clid = anti_injection(cdr->clid, 80);
 133         channel = anti_injection(cdr->channel, 80);
 134         dstchannel = anti_injection(cdr->dstchannel, 80);
 135         lastapp = anti_injection(cdr->lastapp, 80);
 136         lastdata = anti_injection(cdr->lastdata, 80);
 137         uniqueid = anti_injection(cdr->uniqueid, 32);
 138
 139         get_date(start, cdr->start);
 140         get_date(answer, cdr->answer);
 141         get_date(end, cdr->end);
 142
 143         sprintf(
 144                 sqlcmd,
 145                 "INSERT INTO %s "
 146                 "("
 147                         "accountcode, "
 148                         "src, "
 149                         "dst, "
 150                         "dcontext, "
 151                         "clid, "
 152                         "channel, "
 153                         "dstchannel, "
 154                         "lastapp, "
 155                         "lastdata, "
 156                         "start, "
 157                         "answer, "
 158                         "[end], "
 159                         "duration, "
 160                         "billsec, "
 161                         "disposition, "
 162                         "amaflags, "
 163                         "uniqueid"
 164                 ") "
 165                 "VALUES "
 166                 "("
 167                         "'%s', "        /* accountcode */
 168                         "'%s', "        /* src */
 169                         "'%s', "        /* dst */
 170                         "'%s', "        /* dcontext */
 171                         "'%s', "        /* clid */
 172                         "'%s', "        /* channel */
 173                         "'%s', "        /* dstchannel */
 174                         "'%s', "        /* lastapp */
 175                         "'%s', "        /* lastdata */
 176                         "%s, "          /* start */
 177                         "%s, "          /* answer */
 178                         "%s, "          /* end */
 179                         "%ld, "         /* duration */
 180                         "%ld, "         /* billsec */
 181                         "'%s', "        /* disposition */
 182                         "'%s', "        /* amaflags */
 183                         "'%s'"          /* uniqueid */
 184                 ")",
 185                 settings->table,
 186                 accountcode,
 187                 src,
 188                 dst,
 189                 dcontext,
 190                 clid,
 191                 channel,
 192                 dstchannel,
 193                 lastapp,
 194                 lastdata,
 195                 start,
 196                 answer,
 197                 end,
 198                 cdr->duration,
 199                 cdr->billsec,
 200                 ast_cdr_disp2str(cdr->disposition),
 201                 ast_cdr_flags2str(cdr->amaflags),
 202                 uniqueid
 203         );
 204
 205         do {
 206                 if (!settings->connected) {
 207                         if (mssql_connect())
 208                                 ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
 209                         else
 210                                 ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
 211
 212                         retried = 1;    /* note that we have now tried */
 213                 }
 214
 215 #ifdef FREETDS_PRE_0_62
 216                 if (!settings->connected || (tds_submit_query(settings->tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(settings->tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
 217 #else
 218                 if (!settings->connected || (tds_submit_query(settings->tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(settings->tds) != TDS_SUCCEED))
 219 #endif
 220                 {
 221                         ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
 222
 223                         mssql_disconnect();     /* this is ok even if we are already disconnected */
 224                 }
 225         } while (!settings->connected && !retried);
 226
 227         ast_free(accountcode);
 228         ast_free(src);
 229         ast_free(dst);
 230         ast_free(dcontext);
 231         ast_free(clid);
 232         ast_free(channel);
 233         ast_free(dstchannel);
 234         ast_free(lastapp);
 235         ast_free(lastdata);
 236         ast_free(uniqueid);
 237
 238         ast_mutex_unlock(&settings->lock);
 239
 240         return res;
 241 }
 242
 243 static char *anti_injection(const char *str, int len)
 244 {
 245         /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
 246         char *buf;
 247         char *buf_ptr, *srh_ptr;
 248         char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
 249         int idx;
 250
 251         if (!(buf = ast_calloc(1, len + 1))) {
 252                 ast_log(LOG_ERROR, "Out of memory\n");
 253                 return NULL;
 254         }
 255
 256         buf_ptr = buf;
 257
 258         /* Escape single quotes */
 259         for (; *str && strlen(buf) < len; str++) {
 260                 if (*str == '\'') {
 261                         *buf_ptr++ = '\'';
 262                 }
 263                 *buf_ptr++ = *str;
 264         }
 265         *buf_ptr = '\0';
 266
 267         /* Erase known bad input */
 268         for (idx = 0; *known_bad[idx]; idx++) {
 269                 while ((srh_ptr = strcasestr(buf, known_bad[idx]))) {
 270                         memmove(srh_ptr, srh_ptr + strlen(known_bad[idx]), strlen(srh_ptr + strlen(known_bad[idx])) + 1);
 271                 }
 272         }
 273
 274         return buf;
 275 }
 276
 277 static void get_date(char *dateField, struct timeval tv)
 278 {
 279         struct ast_tm tm;
 280         char buf[80];
 281
 282         /* To make sure we have date variable if not insert null to SQL */
 283         if (!ast_tvzero(tv)) {
 284                 ast_localtime(&tv, &tm, NULL);
 285                 ast_strftime(buf, 80, DATE_FORMAT, &tm);
 286                 sprintf(dateField, "'%s'", buf);
 287         } else {
 288                 strcpy(dateField, "null");
 289         }
 290 }
 291
 292 static int mssql_disconnect(void)
 293 {
 294         if (settings->tds) {
 295                 tds_free_socket(settings->tds);
 296                 settings->tds = NULL;
 297         }
 298
 299         if (settings->context) {
 300                 tds_free_context(settings->context);
 301                 settings->context = NULL;
 302         }
 303
 304         if (settings->login) {
 305                 tds_free_login(settings->login);
 306                 settings->login = NULL;
 307         }
 308
 309         settings->connected = 0;
 310
 311         return 0;
 312 }
 313
 314 static int mssql_connect(void)
 315 {
 316 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
 317         TDSCONNECTION *connection = NULL;
 318 #else
 319         TDSCONNECTINFO *connection = NULL;
 320 #endif
 321         char query[128];
 322
 323         /* Connect to M$SQL Server */
 324         if (!(settings->login = tds_alloc_login())) {
 325                 ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
 326                 return -1;
 327         }
 328
 329         tds_set_server(settings->login, settings->hostname);
 330         tds_set_user(settings->login, settings->dbuser);
 331         tds_set_passwd(settings->login, settings->password);
 332         tds_set_app(settings->login, "TSQL");
 333         tds_set_library(settings->login, "TDS-Library");
 334 #ifndef FREETDS_PRE_0_62
 335         tds_set_client_charset(settings->login, settings->charset);
 336 #endif
 337         tds_set_language(settings->login, settings->language);
 338         tds_set_packet(settings->login, 512);
 339         tds_set_version(settings->login, 7, 0);
 340
 341 #ifdef FREETDS_0_64
 342         if (!(settings->context = tds_alloc_context(NULL)))
 343 #else
 344         if (!(settings->context = tds_alloc_context()))
 345 #endif
 346         {
 347                 ast_log(LOG_ERROR, "tds_alloc_context() failed.\n");
 348                 goto connect_fail;
 349         }
 350
 351         if (!(settings->tds = tds_alloc_socket(settings->context, 512))) {
 352                 ast_log(LOG_ERROR, "tds_alloc_socket() failed.\n");
 353                 goto connect_fail;
 354         }
 355
 356         tds_set_parent(settings->tds, NULL);
 357         connection = tds_read_config_info(settings->tds, settings->login, settings->context->locale);
 358         if (!connection) {
 359                 ast_log(LOG_ERROR, "tds_read_config() failed.\n");
 360                 goto connect_fail;
 361         }
 362
 363         if (tds_connect(settings->tds, connection) == TDS_FAIL) {
 364                 ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
 365                 settings->tds = NULL;   /* freed by tds_connect() on error */
 366 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
 367                 tds_free_connection(connection);
 368 #else
 369                 tds_free_connect(connection);
 370 #endif
 371                 connection = NULL;
 372                 goto connect_fail;
 373         }
 374 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
 375         tds_free_connection(connection);
 376 #else
 377         tds_free_connect(connection);
 378 #endif
 379         connection = NULL;
 380
 381         sprintf(query, "USE %s", settings->dbname);
 382 #ifdef FREETDS_PRE_0_62
 383         if ((tds_submit_query(settings->tds, query) != TDS_SUCCEED) || (tds_process_simple_query(settings->tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
 384 #else
 385         if ((tds_submit_query(settings->tds, query) != TDS_SUCCEED) || (tds_process_simple_query(settings->tds) != TDS_SUCCEED))
 386 #endif
 387         {
 388                 ast_log(LOG_ERROR, "Could not change database (%s)\n", settings->dbname);
 389                 goto connect_fail;
 390         }
 391
 392         settings->connected = 1;
 393         return 0;
 394
 395 connect_fail:
 396         mssql_disconnect();
 397         return -1;
 398 }
 399
 400 static void cdr_tds_config_destroy(void)
 401 {
 402         ast_mutex_destroy(&settings->lock);
 403         ast_string_field_free_memory(settings);
 404         ast_free(settings);
 405 }
 406
 407 static int tds_unload_module(void)
 408 {
 409         mssql_disconnect();
 410
 411         ast_cdr_unregister(name);
 412
 413         cdr_tds_config_destroy();
 414
 415         return 0;
 416 }
 417
 418 static int tds_load_module(int reload)
 419 {
 420         struct ast_config *cfg;
 421         struct ast_variable *var;
 422         const char *ptr = NULL;
 423         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
 424
 425         cfg = ast_config_load(config, config_flags);
 426         if (!cfg) {
 427                 ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDRs: %s\n", config);
 428                 return 0;
 429         } else if (cfg == CONFIG_STATUS_FILEUNCHANGED)
 430                 return 0;
 431
 432         var = ast_variable_browse(cfg, "global");
 433         if (!var) /* nothing configured */ {
 434                 ast_config_destroy(cfg);
 435                 return 0;
 436         }
 437
 438         if (reload) {
 439                 ast_string_field_init(settings, 0);
 440         } else {
 441                 settings = ast_calloc(1, sizeof(*settings));
 442
 443                 if (!settings || ast_string_field_init(settings, 256)) {
 444                         if (settings) {
 445                                 ast_free(settings);
 446                                 settings = NULL;
 447                         }
 448                         ast_config_destroy(cfg);
 449                         return 0;
 450                 }
 451
 452                 ast_mutex_init(&settings->lock);
 453         }
 454
 455         ptr = ast_variable_retrieve(cfg, "global", "hostname");
 456         if (ptr) {
 457                 ast_string_field_set(settings, hostname, ptr);
 458         } else {
 459                 ast_log(LOG_ERROR, "Database server hostname not specified.\n");
 460         }
 461
 462         ptr = ast_variable_retrieve(cfg, "global", "dbname");
 463         if (ptr) {
 464                 ast_string_field_set(settings, dbname, ptr);
 465         } else {
 466                 ast_log(LOG_ERROR, "Database dbname not specified.\n");
 467         }
 468
 469         ptr = ast_variable_retrieve(cfg, "global", "user");
 470         if (ptr) {
 471                 ast_string_field_set(settings, dbuser, ptr);
 472         } else {
 473                 ast_log(LOG_ERROR, "Database dbuser not specified.\n");
 474         }
 475
 476         ptr = ast_variable_retrieve(cfg, "global", "password");
 477         if (ptr) {
 478                 ast_string_field_set(settings, password, ptr);
 479         } else {
 480                 ast_log(LOG_ERROR, "Database password not specified.\n");
 481         }
 482
 483         ptr = ast_variable_retrieve(cfg, "global", "charset");
 484         if (ptr) {
 485                 ast_string_field_set(settings, charset, ptr);
 486         } else {
 487                 ast_string_field_set(settings, charset, "iso_1");
 488         }
 489
 490         ptr = ast_variable_retrieve(cfg, "global", "language");
 491         if (ptr) {
 492                 ast_string_field_set(settings, language, ptr);
 493         } else {
 494                 ast_string_field_set(settings, language, "us_english");
 495         }
 496
 497         ptr = ast_variable_retrieve(cfg, "global", "table");
 498         if (ptr) {
 499                 ast_string_field_set(settings, table, ptr);
 500         } else {
 501                 ast_debug(1, "Table not specified.  Assuming 'cdr'\n");
 502                 ast_string_field_set(settings, table, "cdr");
 503         }
 504
 505         ast_config_destroy(cfg);
 506
 507         ast_mutex_lock(&settings->lock);
 508         mssql_disconnect();
 509         mssql_connect();
 510         ast_mutex_unlock(&settings->lock);
 511
 512         return 1;
 513 }
 514
 515 static int reload(void)
 516 {
 517         return tds_load_module(1);
 518 }
 519
 520 static int load_module(void)
 521 {
 522         if (!tds_load_module(0))
 523                 return AST_MODULE_LOAD_DECLINE;
 524         ast_cdr_register(name, ast_module_info->description, tds_log);
 525         return AST_MODULE_LOAD_SUCCESS;
 526 }
 527
 528 static int unload_module(void)
 529 {
 530         return tds_unload_module();
 531 }
 532
 533 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "MSSQL CDR Backend",
 534                 .load = load_module,
 535                 .unload = unload_module,
 536                 .reload = reload,
 537                );