Asterisk.org: Developer Documentation (28 Oct 2009)

Wed Oct 28 15:47:49 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 - 2005, 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 #include <sys/types.h>
    00059 #include <stdio.h>
    00060 #include <string.h>
    00061 #include <stdlib.h>
    00062 #include <unistd.h>
    00063 #include <time.h>
    00064 #include <math.h>
    00065
    00066 #include <tds.h>
    00067 #include <tdsconvert.h>
    00068 #include <ctype.h>
    00069
    00070 #include "asterisk.h"
    00071
    00072 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 43409 $")
    00073
    00074 #include "asterisk/config.h"
    00075 #include "asterisk/options.h"
    00076 #include "asterisk/channel.h"
    00077 #include "asterisk/cdr.h"
    00078 #include "asterisk/module.h"
    00079 #include "asterisk/logger.h"
    00080
    00081 #ifdef FREETDS_PRE_0_62
    00082 #warning "You have older TDS, you should upgrade!"
    00083 #endif
    00084
    00085 #define DATE_FORMAT "%Y/%m/%d %T"
    00086
    00087 static char *desc = "MSSQL CDR Backend";
    00088 static char *name = "mssql";
    00089 static char *config = "cdr_tds.conf";
    00090
    00091 static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *charset = NULL, *language = NULL;
    00092
    00093 static int connected = 0;
    00094
    00095 AST_MUTEX_DEFINE_STATIC(tds_lock);
    00096
    00097 static TDSSOCKET *tds;
    00098 static TDSLOGIN *login;
    00099 static TDSCONTEXT *context;
    00100
    00101 static char *anti_injection(const char *, int);
    00102 static void get_date(char *, struct timeval);
    00103
    00104 static int mssql_connect(void);
    00105 static int mssql_disconnect(void);
    00106
    00107 static int tds_log(struct ast_cdr *cdr)
    00108 {
    00109    char sqlcmd[2048], start[80], answer[80], end[80];
    00110    char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid;
    00111    int res = 0;
    00112    int retried = 0;
    00113 #ifdef FREETDS_PRE_0_62
    00114    TDS_INT result_type;
    00115 #endif
    00116
    00117    ast_mutex_lock(&tds_lock);
    00118
    00119    memset(sqlcmd, 0, 2048);
    00120
    00121    accountcode = anti_injection(cdr->accountcode, 20);
    00122    src = anti_injection(cdr->src, 80);
    00123    dst = anti_injection(cdr->dst, 80);
    00124    dcontext = anti_injection(cdr->dcontext, 80);
    00125    clid = anti_injection(cdr->clid, 80);
    00126    channel = anti_injection(cdr->channel, 80);
    00127    dstchannel = anti_injection(cdr->dstchannel, 80);
    00128    lastapp = anti_injection(cdr->lastapp, 80);
    00129    lastdata = anti_injection(cdr->lastdata, 80);
    00130    uniqueid = anti_injection(cdr->uniqueid, 32);
    00131
    00132    get_date(start, cdr->start);
    00133    get_date(answer, cdr->answer);
    00134    get_date(end, cdr->end);
    00135
    00136    sprintf(
    00137       sqlcmd,
    00138       "INSERT INTO cdr "
    00139       "("
    00140          "accountcode, "
    00141          "src, "
    00142          "dst, "
    00143          "dcontext, "
    00144          "clid, "
    00145          "channel, "
    00146          "dstchannel, "
    00147          "lastapp, "
    00148          "lastdata, "
    00149          "start, "
    00150          "answer, "
    00151          "[end], "
    00152          "duration, "
    00153          "billsec, "
    00154          "disposition, "
    00155          "amaflags, "
    00156          "uniqueid"
    00157       ") "
    00158       "VALUES "
    00159       "("
    00160          "'%s', " /* accountcode */
    00161          "'%s', " /* src */
    00162          "'%s', " /* dst */
    00163          "'%s', " /* dcontext */
    00164          "'%s', " /* clid */
    00165          "'%s', " /* channel */
    00166          "'%s', " /* dstchannel */
    00167          "'%s', " /* lastapp */
    00168          "'%s', " /* lastdata */
    00169          "%s, "      /* start */
    00170          "%s, "      /* answer */
    00171          "%s, "      /* end */
    00172          "%ld, "     /* duration */
    00173          "%ld, "     /* billsec */
    00174          "'%s', " /* disposition */
    00175          "'%s', " /* amaflags */
    00176          "'%s'"      /* uniqueid */
    00177       ")",
    00178       accountcode,
    00179       src,
    00180       dst,
    00181       dcontext,
    00182       clid,
    00183       channel,
    00184       dstchannel,
    00185       lastapp,
    00186       lastdata,
    00187       start,
    00188       answer,
    00189       end,
    00190       cdr->duration,
    00191       cdr->billsec,
    00192       ast_cdr_disp2str(cdr->disposition),
    00193       ast_cdr_flags2str(cdr->amaflags),
    00194       uniqueid
    00195    );
    00196
    00197    do {
    00198       if (!connected) {
    00199          if (mssql_connect())
    00200             ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
    00201          else
    00202             ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
    00203
    00204          retried = 1;   /* note that we have now tried */
    00205       }
    00206
    00207 #ifdef FREETDS_PRE_0_62
    00208       if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
    00209 #else
    00210       if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
    00211 #endif
    00212       {
    00213          ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
    00214
    00215          mssql_disconnect();  /* this is ok even if we are already disconnected */
    00216       }
    00217    } while (!connected && !retried);
    00218
    00219    free(accountcode);
    00220    free(src);
    00221    free(dst);
    00222    free(dcontext);
    00223    free(clid);
    00224    free(channel);
    00225    free(dstchannel);
    00226    free(lastapp);
    00227    free(lastdata);
    00228    free(uniqueid);
    00229
    00230    ast_mutex_unlock(&tds_lock);
    00231
    00232    return res;
    00233 }
    00234
    00235 static char *anti_injection(const char *str, int len)
    00236 {
    00237    /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
    00238
    00239    char *buf;
    00240    char *buf_ptr, *srh_ptr;
    00241    char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
    00242    int idx;
    00243
    00244    if ((buf = malloc(len + 1)) == NULL)
    00245    {
    00246       ast_log(LOG_ERROR, "cdr_tds:  Out of memory error\n");
    00247       return NULL;
    00248    }
    00249    memset(buf, 0, len);
    00250
    00251    buf_ptr = buf;
    00252
    00253    /* Escape single quotes */
    00254    for (; *str && strlen(buf) < len; str++)
    00255    {
    00256       if (*str == '\'')
    00257          *buf_ptr++ = '\'';
    00258       *buf_ptr++ = *str;
    00259    }
    00260    *buf_ptr = '\0';
    00261
    00262    /* Erase known bad input */
    00263    for (idx=0; *known_bad[idx]; idx++)
    00264    {
    00265       while((srh_ptr = strcasestr(buf, known_bad[idx])))
    00266       {
    00267          memmove(srh_ptr, srh_ptr+strlen(known_bad[idx]), strlen(srh_ptr+strlen(known_bad[idx]))+1);
    00268       }
    00269    }
    00270
    00271    return buf;
    00272 }
    00273
    00274 static void get_date(char *dateField, struct timeval tv)
    00275 {
    00276    struct tm tm;
    00277    time_t t;
    00278    char buf[80];
    00279
    00280    /* To make sure we have date variable if not insert null to SQL */
    00281    if (!ast_tvzero(tv))
    00282    {
    00283       t = tv.tv_sec;
    00284       localtime_r(&t, &tm);
    00285       strftime(buf, 80, DATE_FORMAT, &tm);
    00286       sprintf(dateField, "'%s'", buf);
    00287    }
    00288    else
    00289    {
    00290       strcpy(dateField, "null");
    00291    }
    00292 }
    00293
    00294 char *description(void)
    00295 {
    00296    return desc;
    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
    00423    return 0;
    00424 }
    00425
    00426 static int tds_load_module(void)
    00427 {
    00428    int res = 0;
    00429    struct ast_config *cfg;
    00430    struct ast_variable *var;
    00431    char *ptr = NULL;
    00432 #ifdef FREETDS_PRE_0_62
    00433    TDS_INT result_type;
    00434 #endif
    00435
    00436    cfg = ast_config_load(config);
    00437    if (!cfg) {
    00438       ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
    00439       return 0;
    00440    }
    00441
    00442    var = ast_variable_browse(cfg, "global");
    00443    if (!var) /* nothing configured */
    00444       return 0;
    00445
    00446    ptr = ast_variable_retrieve(cfg, "global", "hostname");
    00447    if (ptr)
    00448       hostname = strdup(ptr);
    00449    else
    00450       ast_log(LOG_ERROR,"Database server hostname not specified.\n");
    00451
    00452    ptr = ast_variable_retrieve(cfg, "global", "dbname");
    00453    if (ptr)
    00454       dbname = strdup(ptr);
    00455    else
    00456       ast_log(LOG_ERROR,"Database dbname not specified.\n");
    00457
    00458    ptr = ast_variable_retrieve(cfg, "global", "user");
    00459    if (ptr)
    00460       dbuser = strdup(ptr);
    00461    else
    00462       ast_log(LOG_ERROR,"Database dbuser not specified.\n");
    00463
    00464    ptr = ast_variable_retrieve(cfg, "global", "password");
    00465    if (ptr)
    00466       password = strdup(ptr);
    00467    else
    00468       ast_log(LOG_ERROR,"Database password not specified.\n");
    00469
    00470    ptr = ast_variable_retrieve(cfg, "global", "charset");
    00471    if (ptr)
    00472       charset = strdup(ptr);
    00473    else
    00474       charset = strdup("iso_1");
    00475
    00476    ptr = ast_variable_retrieve(cfg, "global", "language");
    00477    if (ptr)
    00478       language = strdup(ptr);
    00479    else
    00480       language = strdup("us_english");
    00481
    00482    ast_config_destroy(cfg);
    00483
    00484    mssql_connect();
    00485
    00486    /* Register MSSQL CDR handler */
    00487    res = ast_cdr_register(name, desc, tds_log);
    00488    if (res)
    00489    {
    00490       ast_log(LOG_ERROR, "Unable to register MSSQL CDR handling\n");
    00491    }
    00492
    00493    return res;
    00494 }
    00495
    00496 int reload(void)
    00497 {
    00498    tds_unload_module();
    00499    return tds_load_module();
    00500 }
    00501
    00502 int load_module(void)
    00503 {
    00504    return tds_load_module();
    00505 }
    00506
    00507 int unload_module(void)
    00508 {
    00509    return tds_unload_module();
    00510 }
    00511
    00512 int usecount(void)
    00513 {
    00514    /* Simplistic use count */
    00515    if (ast_mutex_trylock(&tds_lock)) {
    00516       return 1;
    00517    } else {
    00518       ast_mutex_unlock(&tds_lock);
    00519       return 0;
    00520    }
    00521 }
    00522
    00523 char *key()
    00524 {
    00525    return ASTERISK_GPL_KEY;
    00526 }