Sat Aug 6 00:39:23 2011

Asterisk developer's documentation


cdr_tds.c

Go to the documentation of this file.
00001 /*
00002  * Asterisk -- An open source telephony toolkit.
00003  *
00004  * Copyright (C) 2004 - 2006, Digium, Inc.
00005  *
00006  * See http://www.asterisk.org for more information about
00007  * the Asterisk project. Please do not directly contact
00008  * any of the maintainers of this project for assistance;
00009  * the project provides a web site, mailing lists and IRC
00010  * channels for your use.
00011  *
00012  * This program is free software, distributed under the terms of
00013  * the GNU General Public License Version 2. See the LICENSE file
00014  * at the top of the source tree.
00015  */
00016 
00017 /*! \file
00018  *
00019  * \brief FreeTDS CDR logger
00020  *
00021  * See also
00022  * \arg \ref Config_cdr
00023  * \arg http://www.freetds.org/
00024  * \ingroup cdr_drivers
00025  */
00026 
00027 /*! \verbatim
00028  *
00029  * Table Structure for `cdr`
00030  *
00031  * Created on: 05/20/2004 16:16
00032  * Last changed on: 07/27/2004 20:01
00033 
00034 CREATE TABLE [dbo].[cdr] (
00035    [accountcode] [varchar] (20) NULL ,
00036    [src] [varchar] (80) NULL ,
00037    [dst] [varchar] (80) NULL ,
00038    [dcontext] [varchar] (80) NULL ,
00039    [clid] [varchar] (80) NULL ,
00040    [channel] [varchar] (80) NULL ,
00041    [dstchannel] [varchar] (80) NULL ,
00042    [lastapp] [varchar] (80) NULL ,
00043    [lastdata] [varchar] (80) NULL ,
00044    [start] [datetime] NULL ,
00045    [answer] [datetime] NULL ,
00046    [end] [datetime] NULL ,
00047    [duration] [int] NULL ,
00048    [billsec] [int] NULL ,
00049    [disposition] [varchar] (20) NULL ,
00050    [amaflags] [varchar] (16) NULL ,
00051    [uniqueid] [varchar] (32) NULL ,
00052    [userfield] [varchar] (256) NULL
00053 ) ON [PRIMARY]
00054 
00055 \endverbatim
00056 
00057 */
00058 
00059 /*** MODULEINFO
00060    <depend>freetds</depend>
00061  ***/
00062 
00063 #include "asterisk.h"
00064 
00065 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 269006 $")
00066 
00067 #include <sys/types.h>
00068 #include <stdio.h>
00069 #include <string.h>
00070 #include <stdlib.h>
00071 #include <unistd.h>
00072 #include <time.h>
00073 #include <math.h>
00074 
00075 #include <tds.h>
00076 #include <tdsconvert.h>
00077 #include <ctype.h>
00078 
00079 #include "asterisk/config.h"
00080 #include "asterisk/options.h"
00081 #include "asterisk/channel.h"
00082 #include "asterisk/cdr.h"
00083 #include "asterisk/module.h"
00084 #include "asterisk/logger.h"
00085 
00086 #ifdef FREETDS_PRE_0_62
00087 #warning "You have older TDS, you should upgrade!"
00088 #endif
00089 
00090 #define DATE_FORMAT "%Y/%m/%d %T"
00091 
00092 static char *name = "mssql";
00093 static char *config = "cdr_tds.conf";
00094 
00095 static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *charset = NULL, *language = NULL;
00096 static char *table = NULL;
00097 
00098 static int connected = 0;
00099 static int has_userfield = 0;
00100 
00101 AST_MUTEX_DEFINE_STATIC(tds_lock);
00102 
00103 static TDSSOCKET *tds;
00104 static TDSLOGIN *login;
00105 static TDSCONTEXT *context;
00106 
00107 static char *anti_injection(const char *, int);
00108 static void get_date(char *, size_t, struct timeval);
00109 
00110 static int mssql_connect(void);
00111 static int mssql_disconnect(void);
00112 
00113 static int tds_log(struct ast_cdr *cdr)
00114 {
00115    char sqlcmd[2048], start[80], answer[80], end[80];
00116    char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid, *userfield = NULL;
00117    int res = 0;
00118    int retried = 0;
00119 #ifdef FREETDS_PRE_0_62
00120    TDS_INT result_type;
00121 #endif
00122 
00123    ast_mutex_lock(&tds_lock);
00124 
00125    memset(sqlcmd, 0, 2048);
00126 
00127    accountcode = anti_injection(cdr->accountcode, 20);
00128    src = anti_injection(cdr->src, 80);
00129    dst = anti_injection(cdr->dst, 80);
00130    dcontext = anti_injection(cdr->dcontext, 80);
00131    clid = anti_injection(cdr->clid, 80);
00132    channel = anti_injection(cdr->channel, 80);
00133    dstchannel = anti_injection(cdr->dstchannel, 80);
00134    lastapp = anti_injection(cdr->lastapp, 80);
00135    lastdata = anti_injection(cdr->lastdata, 80);
00136    uniqueid = anti_injection(cdr->uniqueid, 32);
00137 
00138    if (has_userfield) {
00139       userfield = anti_injection(cdr->userfield, AST_MAX_USER_FIELD);
00140    }
00141 
00142    get_date(start, sizeof(start), cdr->start);
00143    get_date(answer, sizeof(answer), cdr->answer);
00144    get_date(end, sizeof(end), cdr->end);
00145 
00146    if (has_userfield) {
00147       snprintf(
00148          sqlcmd,
00149          sizeof(sqlcmd),
00150          "INSERT INTO %s "
00151          "("
00152             "accountcode, "
00153             "src, "
00154             "dst, "
00155             "dcontext, "
00156             "clid, "
00157             "channel, "
00158             "dstchannel, "
00159             "lastapp, "
00160             "lastdata, "
00161             "start, "
00162             "answer, "
00163             "[end], "
00164             "duration, "
00165             "billsec, "
00166             "disposition, "
00167             "amaflags, "
00168             "uniqueid, "
00169             "userfield"
00170          ") "
00171          "VALUES "
00172          "("
00173             "'%s', " /* accountcode */
00174             "'%s', " /* src */
00175             "'%s', " /* dst */
00176             "'%s', " /* dcontext */
00177             "'%s', " /* clid */
00178             "'%s', " /* channel */
00179             "'%s', " /* dstchannel */
00180             "'%s', " /* lastapp */
00181             "'%s', " /* lastdata */
00182             "%s, "      /* start */
00183             "%s, "      /* answer */
00184             "%s, "      /* end */
00185             "%ld, "     /* duration */
00186             "%ld, "     /* billsec */
00187             "'%s', " /* disposition */
00188             "'%s', " /* amaflags */
00189             "'%s', " /* uniqueid */
00190             "'%s'"      /* userfield */
00191          ")",
00192          table,
00193          accountcode,
00194          src,
00195          dst,
00196          dcontext,
00197          clid,
00198          channel,
00199          dstchannel,
00200          lastapp,
00201          lastdata,
00202          start,
00203          answer,
00204          end,
00205          cdr->duration,
00206          cdr->billsec,
00207          ast_cdr_disp2str(cdr->disposition),
00208          ast_cdr_flags2str(cdr->amaflags),
00209          uniqueid,
00210          userfield
00211          );
00212    } else {
00213       snprintf(
00214          sqlcmd,
00215          sizeof(sqlcmd),
00216          "INSERT INTO %s "
00217          "("
00218             "accountcode, "
00219             "src, "
00220             "dst, "
00221             "dcontext, "
00222             "clid, "
00223             "channel, "
00224             "dstchannel, "
00225             "lastapp, "
00226             "lastdata, "
00227             "start, "
00228             "answer, "
00229             "[end], "
00230             "duration, "
00231             "billsec, "
00232             "disposition, "
00233             "amaflags, "
00234             "uniqueid"
00235          ") "
00236          "VALUES "
00237          "("
00238             "'%s', " /* accountcode */
00239             "'%s', " /* src */
00240             "'%s', " /* dst */
00241             "'%s', " /* dcontext */
00242             "'%s', " /* clid */
00243             "'%s', " /* channel */
00244             "'%s', " /* dstchannel */
00245             "'%s', " /* lastapp */
00246             "'%s', " /* lastdata */
00247             "%s, "      /* start */
00248             "%s, "      /* answer */
00249             "%s, "      /* end */
00250             "%ld, "     /* duration */
00251             "%ld, "     /* billsec */
00252             "'%s', " /* disposition */
00253             "'%s', " /* amaflags */
00254             "'%s'"      /* uniqueid */
00255          ")",
00256          table,
00257          accountcode,
00258          src,
00259          dst,
00260          dcontext,
00261          clid,
00262          channel,
00263          dstchannel,
00264          lastapp,
00265          lastdata,
00266          start,
00267          answer,
00268          end,
00269          cdr->duration,
00270          cdr->billsec,
00271          ast_cdr_disp2str(cdr->disposition),
00272          ast_cdr_flags2str(cdr->amaflags),
00273          uniqueid
00274          );
00275    }
00276 
00277    do {
00278       if (!connected) {
00279          if (mssql_connect())
00280             ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
00281          else
00282             ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
00283 
00284          retried = 1;   /* note that we have now tried */
00285       }
00286 
00287 #ifdef FREETDS_PRE_0_62
00288       if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00289 #else
00290       if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00291 #endif
00292       {
00293          ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
00294 
00295          mssql_disconnect();  /* this is ok even if we are already disconnected */
00296       }
00297    } while (!connected && !retried);
00298 
00299    free(accountcode);
00300    free(src);
00301    free(dst);
00302    free(dcontext);
00303    free(clid);
00304    free(channel);
00305    free(dstchannel);
00306    free(lastapp);
00307    free(lastdata);
00308    free(uniqueid);
00309    if (userfield) {
00310       free(userfield);
00311    }
00312 
00313    ast_mutex_unlock(&tds_lock);
00314 
00315    return res;
00316 }
00317 
00318 static char *anti_injection(const char *str, int len)
00319 {
00320    /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
00321 
00322    char *buf;
00323    char *buf_ptr, *srh_ptr;
00324    char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
00325    int idx;
00326 
00327    if ((buf = malloc(len + 1)) == NULL)
00328    {
00329       ast_log(LOG_ERROR, "cdr_tds:  Out of memory error\n");
00330       return NULL;
00331    }
00332    memset(buf, 0, len);
00333 
00334    buf_ptr = buf;
00335 
00336    /* Escape single quotes */
00337    for (; *str && strlen(buf) < len; str++)
00338    {
00339       if (*str == '\'')
00340          *buf_ptr++ = '\'';
00341       *buf_ptr++ = *str;
00342    }
00343    *buf_ptr = '\0';
00344 
00345    /* Erase known bad input */
00346    for (idx=0; *known_bad[idx]; idx++)
00347    {
00348       while((srh_ptr = strcasestr(buf, known_bad[idx])))
00349       {
00350          memmove(srh_ptr, srh_ptr+strlen(known_bad[idx]), strlen(srh_ptr+strlen(known_bad[idx]))+1);
00351       }
00352    }
00353 
00354    return buf;
00355 }
00356 
00357 static void get_date(char *dateField, size_t length, struct timeval tv)
00358 {
00359    struct tm tm;
00360    time_t t;
00361    char buf[80];
00362 
00363    /* To make sure we have date variable if not insert null to SQL */
00364    if (!ast_tvzero(tv))
00365    {
00366       t = tv.tv_sec;
00367       ast_localtime(&t, &tm, NULL);
00368       strftime(buf, sizeof(buf), DATE_FORMAT, &tm);
00369       snprintf(dateField, length, "'%s'", buf);
00370    }
00371    else
00372    {
00373       ast_copy_string(dateField, "null", length);
00374    }
00375 }
00376 
00377 static int mssql_disconnect(void)
00378 {
00379    if (tds) {
00380       tds_free_socket(tds);
00381       tds = NULL;
00382    }
00383 
00384    if (context) {
00385       tds_free_context(context);
00386       context = NULL;
00387    }
00388 
00389    if (login) {
00390       tds_free_login(login);
00391       login = NULL;
00392    }
00393 
00394    connected = 0;
00395 
00396    return 0;
00397 }
00398 
00399 static int mssql_connect(void)
00400 {
00401 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00402    TDSCONNECTION *connection = NULL;
00403 #else
00404    TDSCONNECTINFO *connection = NULL;
00405 #endif
00406    char query[512];
00407 
00408    /* Connect to M$SQL Server */
00409    if (!(login = tds_alloc_login()))
00410    {
00411       ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
00412       return -1;
00413    }
00414    
00415    tds_set_server(login, hostname);
00416    tds_set_user(login, dbuser);
00417    tds_set_passwd(login, password);
00418    tds_set_app(login, "TSQL");
00419    tds_set_library(login, "TDS-Library");
00420 #ifndef FREETDS_PRE_0_62
00421    tds_set_client_charset(login, charset);
00422 #endif
00423    tds_set_language(login, language);
00424    tds_set_packet(login, 512);
00425    tds_set_version(login, 7, 0);
00426 
00427 #ifdef FREETDS_0_64
00428    if (!(context = tds_alloc_context(NULL)))
00429 #else
00430    if (!(context = tds_alloc_context()))
00431 #endif
00432    {
00433       ast_log(LOG_ERROR, "tds_alloc_context() failed.\n");
00434       goto connect_fail;
00435    }
00436 
00437    if (!(tds = tds_alloc_socket(context, 512))) {
00438       ast_log(LOG_ERROR, "tds_alloc_socket() failed.\n");
00439       goto connect_fail;
00440    }
00441 
00442    tds_set_parent(tds, NULL);
00443    connection = tds_read_config_info(tds, login, context->locale);
00444    if (!connection)
00445    {
00446       ast_log(LOG_ERROR, "tds_read_config() failed.\n");
00447       goto connect_fail;
00448    }
00449 
00450    if (tds_connect(tds, connection) == TDS_FAIL)
00451    {
00452       ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
00453       tds = NULL; /* freed by tds_connect() on error */
00454 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00455       tds_free_connection(connection);
00456 #else
00457       tds_free_connect(connection);
00458 #endif
00459       connection = NULL;
00460       goto connect_fail;
00461    }
00462 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00463    tds_free_connection(connection);
00464 #else
00465    tds_free_connect(connection);
00466 #endif
00467    connection = NULL;
00468 
00469    snprintf(query, sizeof(query), "USE %s", dbname);
00470 #ifdef FREETDS_PRE_0_62
00471    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00472 #else
00473    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00474 #endif
00475    {
00476       ast_log(LOG_ERROR, "Could not change database (%s)\n", dbname);
00477       goto connect_fail;
00478    }
00479 
00480    snprintf(query, sizeof(query), "SELECT 1 FROM %s WHERE 1 = 0", table);
00481 #ifdef FREETDS_PRE_0_62
00482    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00483 #else
00484    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00485 #endif
00486    {
00487       ast_log(LOG_ERROR, "Could not find table '%s' in database '%s'\n", table, dbname);
00488       goto connect_fail;
00489    }
00490 
00491    has_userfield = 1;
00492    snprintf(query, sizeof(query), "SELECT userfield FROM %s WHERE 1 = 0", table);
00493 #ifdef FREETDS_PRE_0_62
00494    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00495 #else
00496    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00497 #endif
00498    {
00499       ast_log(LOG_NOTICE, "Unable to find 'userfield' column in table '%s'\n", table);
00500       has_userfield = 0;
00501    }
00502 
00503    connected = 1;
00504    return 0;
00505 
00506 connect_fail:
00507    mssql_disconnect();
00508    return -1;
00509 }
00510 
00511 static int tds_unload_module(void)
00512 {
00513    mssql_disconnect();
00514 
00515    ast_cdr_unregister(name);
00516 
00517    if (hostname) free(hostname);
00518    if (dbname) free(dbname);
00519    if (dbuser) free(dbuser);
00520    if (password) free(password);
00521    if (charset) free(charset);
00522    if (language) free(language);
00523    if (table) free(table);
00524 
00525    return 0;
00526 }
00527 
00528 static int tds_load_module(void)
00529 {
00530    int res = 0;
00531    struct ast_config *cfg;
00532    struct ast_variable *var;
00533    const char *ptr = NULL;
00534 #ifdef FREETDS_PRE_0_62
00535    TDS_INT result_type;
00536 #endif
00537 
00538    cfg = ast_config_load(config);
00539    if (!cfg) {
00540       ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
00541       return 0;
00542    }
00543 
00544    var = ast_variable_browse(cfg, "global");
00545    if (!var) /* nothing configured */ {
00546       ast_config_destroy(cfg);
00547       return 0;
00548    }
00549    
00550    ptr = ast_variable_retrieve(cfg, "global", "hostname");
00551    if (ptr)
00552       hostname = strdup(ptr);
00553    else
00554       ast_log(LOG_ERROR,"Database server hostname not specified.\n");
00555 
00556    ptr = ast_variable_retrieve(cfg, "global", "dbname");
00557    if (ptr)
00558       dbname = strdup(ptr);
00559    else
00560       ast_log(LOG_ERROR,"Database dbname not specified.\n");
00561 
00562    ptr = ast_variable_retrieve(cfg, "global", "user");
00563    if (ptr)
00564       dbuser = strdup(ptr);
00565    else
00566       ast_log(LOG_ERROR,"Database dbuser not specified.\n");
00567 
00568    ptr = ast_variable_retrieve(cfg, "global", "password");
00569    if (ptr)
00570       password = strdup(ptr);
00571    else
00572       ast_log(LOG_ERROR,"Database password not specified.\n");
00573 
00574    ptr = ast_variable_retrieve(cfg, "global", "charset");
00575    if (ptr)
00576       charset = strdup(ptr);
00577    else
00578       charset = strdup("iso_1");
00579 
00580    ptr = ast_variable_retrieve(cfg, "global", "language");
00581    if (ptr)
00582       language = strdup(ptr);
00583    else
00584       language = strdup("us_english");
00585 
00586    ptr = ast_variable_retrieve(cfg,"global","table");
00587    if (ptr == NULL) {
00588       ast_log(LOG_DEBUG,"cdr_tds: table not specified.  Assuming cdr\n");
00589       ptr = "cdr";
00590    }
00591    table = strdup(ptr);
00592 
00593    ast_config_destroy(cfg);
00594 
00595    mssql_connect();
00596 
00597    /* Register MSSQL CDR handler */
00598    res = ast_cdr_register(name, ast_module_info->description, tds_log);
00599    if (res)
00600    {
00601       ast_log(LOG_ERROR, "Unable to register MSSQL CDR handling\n");
00602    }
00603 
00604    return res;
00605 }
00606 
00607 static int reload(void)
00608 {
00609    tds_unload_module();
00610    return tds_load_module();
00611 }
00612 
00613 static int load_module(void)
00614 {
00615    if(!tds_load_module())
00616       return AST_MODULE_LOAD_DECLINE;
00617    else 
00618       return AST_MODULE_LOAD_SUCCESS;
00619 }
00620 
00621 static int unload_module(void)
00622 {
00623    return tds_unload_module();
00624 }
00625 
00626 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "MSSQL CDR Backend",
00627       .load = load_module,
00628       .unload = unload_module,
00629       .reload = reload,
00630           );

Generated on Sat Aug 6 00:39:23 2011 for Asterisk - the Open Source PBX by  doxygen 1.4.7