Thu Oct 8 21:57:20 2009

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 ) ON [PRIMARY]
00053 
00054 \endverbatim
00055 
00056 */
00057 
00058 /*** MODULEINFO
00059    <depend>freetds</depend>
00060  ***/
00061 
00062 #include "asterisk.h"
00063 
00064 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 89088 $")
00065 
00066 #include <sys/types.h>
00067 #include <stdio.h>
00068 #include <string.h>
00069 #include <stdlib.h>
00070 #include <unistd.h>
00071 #include <time.h>
00072 #include <math.h>
00073 
00074 #include <tds.h>
00075 #include <tdsconvert.h>
00076 #include <ctype.h>
00077 
00078 #include "asterisk/config.h"
00079 #include "asterisk/options.h"
00080 #include "asterisk/channel.h"
00081 #include "asterisk/cdr.h"
00082 #include "asterisk/module.h"
00083 #include "asterisk/logger.h"
00084 
00085 #ifdef FREETDS_PRE_0_62
00086 #warning "You have older TDS, you should upgrade!"
00087 #endif
00088 
00089 #define DATE_FORMAT "%Y/%m/%d %T"
00090 
00091 static char *name = "mssql";
00092 static char *config = "cdr_tds.conf";
00093 
00094 static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *charset = NULL, *language = NULL;
00095 static char *table = NULL;
00096 
00097 static int connected = 0;
00098 
00099 AST_MUTEX_DEFINE_STATIC(tds_lock);
00100 
00101 static TDSSOCKET *tds;
00102 static TDSLOGIN *login;
00103 static TDSCONTEXT *context;
00104 
00105 static char *anti_injection(const char *, int);
00106 static void get_date(char *, struct timeval);
00107 
00108 static int mssql_connect(void);
00109 static int mssql_disconnect(void);
00110 
00111 static int tds_log(struct ast_cdr *cdr)
00112 {
00113    char sqlcmd[2048], start[80], answer[80], end[80];
00114    char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid;
00115    int res = 0;
00116    int retried = 0;
00117 #ifdef FREETDS_PRE_0_62
00118    TDS_INT result_type;
00119 #endif
00120 
00121    ast_mutex_lock(&tds_lock);
00122 
00123    memset(sqlcmd, 0, 2048);
00124 
00125    accountcode = anti_injection(cdr->accountcode, 20);
00126    src = anti_injection(cdr->src, 80);
00127    dst = anti_injection(cdr->dst, 80);
00128    dcontext = anti_injection(cdr->dcontext, 80);
00129    clid = anti_injection(cdr->clid, 80);
00130    channel = anti_injection(cdr->channel, 80);
00131    dstchannel = anti_injection(cdr->dstchannel, 80);
00132    lastapp = anti_injection(cdr->lastapp, 80);
00133    lastdata = anti_injection(cdr->lastdata, 80);
00134    uniqueid = anti_injection(cdr->uniqueid, 32);
00135 
00136    get_date(start, cdr->start);
00137    get_date(answer, cdr->answer);
00138    get_date(end, cdr->end);
00139 
00140    sprintf(
00141       sqlcmd,
00142       "INSERT INTO %s "
00143       "("
00144          "accountcode, "
00145          "src, "
00146          "dst, "
00147          "dcontext, "
00148          "clid, "
00149          "channel, "
00150          "dstchannel, "
00151          "lastapp, "
00152          "lastdata, "
00153          "start, "
00154          "answer, "
00155          "[end], "
00156          "duration, "
00157          "billsec, "
00158          "disposition, "
00159          "amaflags, "
00160          "uniqueid"
00161       ") "
00162       "VALUES "
00163       "("
00164          "'%s', " /* accountcode */
00165          "'%s', " /* src */
00166          "'%s', " /* dst */
00167          "'%s', " /* dcontext */
00168          "'%s', " /* clid */
00169          "'%s', " /* channel */
00170          "'%s', " /* dstchannel */
00171          "'%s', " /* lastapp */
00172          "'%s', " /* lastdata */
00173          "%s, "      /* start */
00174          "%s, "      /* answer */
00175          "%s, "      /* end */
00176          "%ld, "     /* duration */
00177          "%ld, "     /* billsec */
00178          "'%s', " /* disposition */
00179          "'%s', " /* amaflags */
00180          "'%s'"      /* uniqueid */
00181       ")",
00182       table,
00183       accountcode,
00184       src,
00185       dst,
00186       dcontext,
00187       clid,
00188       channel,
00189       dstchannel,
00190       lastapp,
00191       lastdata,
00192       start,
00193       answer,
00194       end,
00195       cdr->duration,
00196       cdr->billsec,
00197       ast_cdr_disp2str(cdr->disposition),
00198       ast_cdr_flags2str(cdr->amaflags),
00199       uniqueid
00200    );
00201 
00202    do {
00203       if (!connected) {
00204          if (mssql_connect())
00205             ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
00206          else
00207             ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
00208 
00209          retried = 1;   /* note that we have now tried */
00210       }
00211 
00212 #ifdef FREETDS_PRE_0_62
00213       if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00214 #else
00215       if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00216 #endif
00217       {
00218          ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
00219 
00220          mssql_disconnect();  /* this is ok even if we are already disconnected */
00221       }
00222    } while (!connected && !retried);
00223 
00224    free(accountcode);
00225    free(src);
00226    free(dst);
00227    free(dcontext);
00228    free(clid);
00229    free(channel);
00230    free(dstchannel);
00231    free(lastapp);
00232    free(lastdata);
00233    free(uniqueid);
00234 
00235    ast_mutex_unlock(&tds_lock);
00236 
00237    return res;
00238 }
00239 
00240 static char *anti_injection(const char *str, int len)
00241 {
00242    /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
00243 
00244    char *buf;
00245    char *buf_ptr, *srh_ptr;
00246    char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
00247    int idx;
00248 
00249    if ((buf = malloc(len + 1)) == NULL)
00250    {
00251       ast_log(LOG_ERROR, "cdr_tds:  Out of memory error\n");
00252       return NULL;
00253    }
00254    memset(buf, 0, len);
00255 
00256    buf_ptr = buf;
00257 
00258    /* Escape single quotes */
00259    for (; *str && strlen(buf) < len; str++)
00260    {
00261       if (*str == '\'')
00262          *buf_ptr++ = '\'';
00263       *buf_ptr++ = *str;
00264    }
00265    *buf_ptr = '\0';
00266 
00267    /* Erase known bad input */
00268    for (idx=0; *known_bad[idx]; idx++)
00269    {
00270       while((srh_ptr = strcasestr(buf, known_bad[idx])))
00271       {
00272          memmove(srh_ptr, srh_ptr+strlen(known_bad[idx]), strlen(srh_ptr+strlen(known_bad[idx]))+1);
00273       }
00274    }
00275 
00276    return buf;
00277 }
00278 
00279 static void get_date(char *dateField, struct timeval tv)
00280 {
00281    struct tm tm;
00282    time_t t;
00283    char buf[80];
00284 
00285    /* To make sure we have date variable if not insert null to SQL */
00286    if (!ast_tvzero(tv))
00287    {
00288       t = tv.tv_sec;
00289       ast_localtime(&t, &tm, NULL);
00290       strftime(buf, 80, DATE_FORMAT, &tm);
00291       sprintf(dateField, "'%s'", buf);
00292    }
00293    else
00294    {
00295       strcpy(dateField, "null");
00296    }
00297 }
00298 
00299 static int mssql_disconnect(void)
00300 {
00301    if (tds) {
00302       tds_free_socket(tds);
00303       tds = NULL;
00304    }
00305 
00306    if (context) {
00307       tds_free_context(context);
00308       context = NULL;
00309    }
00310 
00311    if (login) {
00312       tds_free_login(login);
00313       login = NULL;
00314    }
00315 
00316    connected = 0;
00317 
00318    return 0;
00319 }
00320 
00321 static int mssql_connect(void)
00322 {
00323 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00324    TDSCONNECTION *connection = NULL;
00325 #else
00326    TDSCONNECTINFO *connection = NULL;
00327 #endif
00328    char query[128];
00329 
00330    /* Connect to M$SQL Server */
00331    if (!(login = tds_alloc_login()))
00332    {
00333       ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
00334       return -1;
00335    }
00336    
00337    tds_set_server(login, hostname);
00338    tds_set_user(login, dbuser);
00339    tds_set_passwd(login, password);
00340    tds_set_app(login, "TSQL");
00341    tds_set_library(login, "TDS-Library");
00342 #ifndef FREETDS_PRE_0_62
00343    tds_set_client_charset(login, charset);
00344 #endif
00345    tds_set_language(login, language);
00346    tds_set_packet(login, 512);
00347    tds_set_version(login, 7, 0);
00348 
00349 #ifdef FREETDS_0_64
00350    if (!(context = tds_alloc_context(NULL)))
00351 #else
00352    if (!(context = tds_alloc_context()))
00353 #endif
00354    {
00355       ast_log(LOG_ERROR, "tds_alloc_context() failed.\n");
00356       goto connect_fail;
00357    }
00358 
00359    if (!(tds = tds_alloc_socket(context, 512))) {
00360       ast_log(LOG_ERROR, "tds_alloc_socket() failed.\n");
00361       goto connect_fail;
00362    }
00363 
00364    tds_set_parent(tds, NULL);
00365    connection = tds_read_config_info(tds, login, context->locale);
00366    if (!connection)
00367    {
00368       ast_log(LOG_ERROR, "tds_read_config() failed.\n");
00369       goto connect_fail;
00370    }
00371 
00372    if (tds_connect(tds, connection) == TDS_FAIL)
00373    {
00374       ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
00375       tds = NULL; /* freed by tds_connect() on error */
00376 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00377       tds_free_connection(connection);
00378 #else
00379       tds_free_connect(connection);
00380 #endif
00381       connection = NULL;
00382       goto connect_fail;
00383    }
00384 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00385    tds_free_connection(connection);
00386 #else
00387    tds_free_connect(connection);
00388 #endif
00389    connection = NULL;
00390 
00391    sprintf(query, "USE %s", dbname);
00392 #ifdef FREETDS_PRE_0_62
00393    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00394 #else
00395    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00396 #endif
00397    {
00398       ast_log(LOG_ERROR, "Could not change database (%s)\n", dbname);
00399       goto connect_fail;
00400    }
00401 
00402    connected = 1;
00403    return 0;
00404 
00405 connect_fail:
00406    mssql_disconnect();
00407    return -1;
00408 }
00409 
00410 static int tds_unload_module(void)
00411 {
00412    mssql_disconnect();
00413 
00414    ast_cdr_unregister(name);
00415 
00416    if (hostname) free(hostname);
00417    if (dbname) free(dbname);
00418    if (dbuser) free(dbuser);
00419    if (password) free(password);
00420    if (charset) free(charset);
00421    if (language) free(language);
00422    if (table) free(table);
00423 
00424    return 0;
00425 }
00426 
00427 static int tds_load_module(void)
00428 {
00429    int res = 0;
00430    struct ast_config *cfg;
00431    struct ast_variable *var;
00432    const char *ptr = NULL;
00433 #ifdef FREETDS_PRE_0_62
00434    TDS_INT result_type;
00435 #endif
00436 
00437    cfg = ast_config_load(config);
00438    if (!cfg) {
00439       ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
00440       return 0;
00441    }
00442 
00443    var = ast_variable_browse(cfg, "global");
00444    if (!var) /* nothing configured */ {
00445       ast_config_destroy(cfg);
00446       return 0;
00447    }
00448    
00449    ptr = ast_variable_retrieve(cfg, "global", "hostname");
00450    if (ptr)
00451       hostname = strdup(ptr);
00452    else
00453       ast_log(LOG_ERROR,"Database server hostname not specified.\n");
00454 
00455    ptr = ast_variable_retrieve(cfg, "global", "dbname");
00456    if (ptr)
00457       dbname = strdup(ptr);
00458    else
00459       ast_log(LOG_ERROR,"Database dbname not specified.\n");
00460 
00461    ptr = ast_variable_retrieve(cfg, "global", "user");
00462    if (ptr)
00463       dbuser = strdup(ptr);
00464    else
00465       ast_log(LOG_ERROR,"Database dbuser not specified.\n");
00466 
00467    ptr = ast_variable_retrieve(cfg, "global", "password");
00468    if (ptr)
00469       password = strdup(ptr);
00470    else
00471       ast_log(LOG_ERROR,"Database password not specified.\n");
00472 
00473    ptr = ast_variable_retrieve(cfg, "global", "charset");
00474    if (ptr)
00475       charset = strdup(ptr);
00476    else
00477       charset = strdup("iso_1");
00478 
00479    ptr = ast_variable_retrieve(cfg, "global", "language");
00480    if (ptr)
00481       language = strdup(ptr);
00482    else
00483       language = strdup("us_english");
00484 
00485    ptr = ast_variable_retrieve(cfg,"global","table");
00486    if (ptr == NULL) {
00487       ast_log(LOG_DEBUG,"cdr_tds: table not specified.  Assuming cdr\n");
00488       ptr = "cdr";
00489    }
00490    table = strdup(ptr);
00491 
00492    ast_config_destroy(cfg);
00493 
00494    mssql_connect();
00495 
00496    /* Register MSSQL CDR handler */
00497    res = ast_cdr_register(name, ast_module_info->description, tds_log);
00498    if (res)
00499    {
00500       ast_log(LOG_ERROR, "Unable to register MSSQL CDR handling\n");
00501    }
00502 
00503    return res;
00504 }
00505 
00506 static int reload(void)
00507 {
00508    tds_unload_module();
00509    return tds_load_module();
00510 }
00511 
00512 static int load_module(void)
00513 {
00514    if(!tds_load_module())
00515       return AST_MODULE_LOAD_DECLINE;
00516    else 
00517       return AST_MODULE_LOAD_SUCCESS;
00518 }
00519 
00520 static int unload_module(void)
00521 {
00522    return tds_unload_module();
00523 }
00524 
00525 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "MSSQL CDR Backend",
00526       .load = load_module,
00527       .unload = unload_module,
00528       .reload = reload,
00529           );

Generated on Thu Oct 8 21:57:20 2009 for Asterisk - the Open Source PBX by  doxygen 1.5.8