Sat Nov 25 00:45:31 2006

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 }

Generated on Sat Nov 25 00:45:31 2006 for Asterisk - the Open Source PBX by  doxygen 1.4.6