cdr/cdr_tds.c

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