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