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