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