Mon Mar 20 08:20:08 2006

Asterisk developer's documentation


Main Page | Modules | Alphabetical List | Data Structures | Directories | File List | Data Fields | Globals | Related Pages

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: 11503 $")
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 #ifdef FREETDS_0_63
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    if (!(context = tds_alloc_context()))
00350    {
00351       ast_log(LOG_ERROR, "tds_alloc_context() failed.\n");
00352       goto connect_fail;
00353    }
00354 
00355    if (!(tds = tds_alloc_socket(context, 512))) {
00356       ast_log(LOG_ERROR, "tds_alloc_socket() failed.\n");
00357       goto connect_fail;
00358    }
00359 
00360    tds_set_parent(tds, NULL);
00361    connection = tds_read_config_info(tds, login, context->locale);
00362    if (!connection)
00363    {
00364       ast_log(LOG_ERROR, "tds_read_config() failed.\n");
00365       goto connect_fail;
00366    }
00367 
00368    if (tds_connect(tds, connection) == TDS_FAIL)
00369    {
00370       ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
00371       tds = NULL; /* freed by tds_connect() on error */
00372 #ifdef FREETDS_0_63
00373       tds_free_connection(connection);
00374 #else
00375       tds_free_connect(connection);
00376 #endif
00377       connection = NULL;
00378       goto connect_fail;
00379    }
00380 #ifdef FREETDS_0_63
00381    tds_free_connection(connection);
00382 #else
00383    tds_free_connect(connection);
00384 #endif
00385    connection = NULL;
00386 
00387    sprintf(query, "USE %s", dbname);
00388 #ifdef FREETDS_PRE_0_62
00389    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00390 #else
00391    if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00392 #endif
00393    {
00394       ast_log(LOG_ERROR, "Could not change database (%s)\n", dbname);
00395       goto connect_fail;
00396    }
00397 
00398    connected = 1;
00399    return 0;
00400 
00401 connect_fail:
00402    mssql_disconnect();
00403    return -1;
00404 }
00405 
00406 static int tds_unload_module(void)
00407 {
00408    mssql_disconnect();
00409 
00410    ast_cdr_unregister(name);
00411 
00412    if (hostname) free(hostname);
00413    if (dbname) free(dbname);
00414    if (dbuser) free(dbuser);
00415    if (password) free(password);
00416    if (charset) free(charset);
00417    if (language) free(language);
00418 
00419    return 0;
00420 }
00421 
00422 static int tds_load_module(void)
00423 {
00424    int res = 0;
00425    struct ast_config *cfg;
00426    struct ast_variable *var;
00427    char *ptr = NULL;
00428 #ifdef FREETDS_PRE_0_62
00429    TDS_INT result_type;
00430 #endif
00431 
00432    cfg = ast_config_load(config);
00433    if (!cfg) {
00434       ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
00435       return 0;
00436    }
00437 
00438    var = ast_variable_browse(cfg, "global");
00439    if (!var) /* nothing configured */
00440       return 0;
00441 
00442    ptr = ast_variable_retrieve(cfg, "global", "hostname");
00443    if (ptr)
00444       hostname = strdup(ptr);
00445    else
00446       ast_log(LOG_ERROR,"Database server hostname not specified.\n");
00447 
00448    ptr = ast_variable_retrieve(cfg, "global", "dbname");
00449    if (ptr)
00450       dbname = strdup(ptr);
00451    else
00452       ast_log(LOG_ERROR,"Database dbname not specified.\n");
00453 
00454    ptr = ast_variable_retrieve(cfg, "global", "user");
00455    if (ptr)
00456       dbuser = strdup(ptr);
00457    else
00458       ast_log(LOG_ERROR,"Database dbuser not specified.\n");
00459 
00460    ptr = ast_variable_retrieve(cfg, "global", "password");
00461    if (ptr)
00462       password = strdup(ptr);
00463    else
00464       ast_log(LOG_ERROR,"Database password not specified.\n");
00465 
00466    ptr = ast_variable_retrieve(cfg, "global", "charset");
00467    if (ptr)
00468       charset = strdup(ptr);
00469    else
00470       charset = strdup("iso_1");
00471 
00472    ptr = ast_variable_retrieve(cfg, "global", "language");
00473    if (ptr)
00474       language = strdup(ptr);
00475    else
00476       language = strdup("us_english");
00477 
00478    ast_config_destroy(cfg);
00479 
00480    mssql_connect();
00481 
00482    /* Register MSSQL CDR handler */
00483    res = ast_cdr_register(name, desc, tds_log);
00484    if (res)
00485    {
00486       ast_log(LOG_ERROR, "Unable to register MSSQL CDR handling\n");
00487    }
00488 
00489    return res;
00490 }
00491 
00492 int reload(void)
00493 {
00494    tds_unload_module();
00495    return tds_load_module();
00496 }
00497 
00498 int load_module(void)
00499 {
00500    return tds_load_module();
00501 }
00502 
00503 int unload_module(void)
00504 {
00505    return tds_unload_module();
00506 }
00507 
00508 int usecount(void)
00509 {
00510    /* Simplistic use count */
00511    if (ast_mutex_trylock(&tds_lock)) {
00512       return 1;
00513    } else {
00514       ast_mutex_unlock(&tds_lock);
00515       return 0;
00516    }
00517 }
00518 
00519 char *key()
00520 {
00521    return ASTERISK_GPL_KEY;
00522 }

Generated on Mon Mar 20 08:20:08 2006 for Asterisk - the Open Source PBX by  doxygen 1.3.9.1