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 /*!
00018  * \file
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 /*!
00028  * \verbatim
00029  *
00030  * Table Structure for `cdr`
00031  *
00032  * Created on: 05/20/2004 16:16
00033  * Last changed on: 07/27/2004 20:01
00034 
00035 CREATE TABLE [dbo].[cdr] (
00036    [accountcode] [varchar] (20) NULL ,
00037    [src] [varchar] (80) NULL ,
00038    [dst] [varchar] (80) NULL ,
00039    [dcontext] [varchar] (80) NULL ,
00040    [clid] [varchar] (80) NULL ,
00041    [channel] [varchar] (80) NULL ,
00042    [dstchannel] [varchar] (80) NULL ,
00043    [lastapp] [varchar] (80) NULL ,
00044    [lastdata] [varchar] (80) NULL ,
00045    [start] [datetime] NULL ,
00046    [answer] [datetime] NULL ,
00047    [end] [datetime] NULL ,
00048    [duration] [int] NULL ,
00049    [billsec] [int] NULL ,
00050    [disposition] [varchar] (20) NULL ,
00051    [amaflags] [varchar] (16) NULL ,
00052    [uniqueid] [varchar] (32) NULL ,
00053    [userfield] [varchar] (256) NULL
00054 ) ON [PRIMARY]
00055 
00056 \endverbatim
00057 
00058 */
00059 
00060 /*** MODULEINFO
00061    <depend>freetds</depend>
00062    <support_level>extended</support_level>
00063  ***/
00064 
00065 #include "asterisk.h"
00066 
00067 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 419592 $")
00068 
00069 #include "asterisk/config.h"
00070 #include "asterisk/channel.h"
00071 #include "asterisk/cdr.h"
00072 #include "asterisk/module.h"
00073 
00074 #include <sqlfront.h>
00075 #include <sybdb.h>
00076 
00077 #define DATE_FORMAT "%Y/%m/%d %T"
00078 
00079 static const char name[] = "FreeTDS (MSSQL)";
00080 static const char config[] = "cdr_tds.conf";
00081 
00082 struct cdr_tds_config {
00083    AST_DECLARE_STRING_FIELDS(
00084       AST_STRING_FIELD(hostname);
00085       AST_STRING_FIELD(database);
00086       AST_STRING_FIELD(username);
00087       AST_STRING_FIELD(password);
00088       AST_STRING_FIELD(table);
00089       AST_STRING_FIELD(charset);
00090       AST_STRING_FIELD(language);
00091       AST_STRING_FIELD(hrtime);
00092    );
00093    DBPROCESS *dbproc;
00094    unsigned int connected:1;
00095    unsigned int has_userfield:1;
00096 };
00097 
00098 AST_MUTEX_DEFINE_STATIC(tds_lock);
00099 
00100 static struct cdr_tds_config *settings;
00101 
00102 static char *anti_injection(const char *, int);
00103 static void get_date(char *, size_t len, struct timeval);
00104 
00105 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
00106    __attribute__((format(printf, 2, 3)));
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 start[80], answer[80], end[80];
00114    char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid, *userfield = NULL;
00115    RETCODE erc;
00116    int res = -1;
00117    int attempt = 1;
00118 
00119    accountcode = anti_injection(cdr->accountcode, 20);
00120    src         = anti_injection(cdr->src, 80);
00121    dst         = anti_injection(cdr->dst, 80);
00122    dcontext    = anti_injection(cdr->dcontext, 80);
00123    clid        = anti_injection(cdr->clid, 80);
00124    channel     = anti_injection(cdr->channel, 80);
00125    dstchannel  = anti_injection(cdr->dstchannel, 80);
00126    lastapp     = anti_injection(cdr->lastapp, 80);
00127    lastdata    = anti_injection(cdr->lastdata, 80);
00128    uniqueid    = anti_injection(cdr->uniqueid, 32);
00129 
00130    get_date(start, sizeof(start), cdr->start);
00131    get_date(answer, sizeof(answer), cdr->answer);
00132    get_date(end, sizeof(end), cdr->end);
00133 
00134    ast_mutex_lock(&tds_lock);
00135 
00136    if (settings->has_userfield) {
00137       userfield = anti_injection(cdr->userfield, AST_MAX_USER_FIELD);
00138    }
00139 
00140 retry:
00141    /* Ensure that we are connected */
00142    if (!settings->connected) {
00143       ast_log(LOG_NOTICE, "Attempting to reconnect to %s (Attempt %d)\n", settings->hostname, attempt);
00144       if (mssql_connect()) {
00145          /* Connect failed */
00146          if (attempt++ < 3) {
00147             goto retry;
00148          }
00149          goto done;
00150       }
00151    }
00152 
00153    if (settings->has_userfield) {
00154       if (settings->hrtime) {
00155          double hrbillsec = 0.0;
00156          double hrduration;
00157 
00158          if (!ast_tvzero(cdr->answer)) {
00159             hrbillsec = (double)(ast_tvdiff_us(cdr->end, cdr->answer) / 1000000.0);
00160          }
00161          hrduration = (double)(ast_tvdiff_us(cdr->end, cdr->start) / 1000000.0);
00162 
00163          erc = dbfcmd(settings->dbproc,
00164                 "INSERT INTO %s "
00165                 "("
00166                 "accountcode, src, dst, dcontext, clid, channel, "
00167                 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
00168                 "billsec, disposition, amaflags, uniqueid, userfield"
00169                 ") "
00170                 "VALUES "
00171                 "("
00172                 "'%s', '%s', '%s', '%s', '%s', '%s', "
00173                 "'%s', '%s', '%s', %s, %s, %s, %lf, "
00174                 "%lf, '%s', '%s', '%s', '%s'"
00175                 ")",
00176                 settings->table,
00177                 accountcode, src, dst, dcontext, clid, channel,
00178                 dstchannel, lastapp, lastdata, start, answer, end, hrduration,
00179                 hrbillsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid,
00180                 userfield
00181          );
00182       } else {
00183          erc = dbfcmd(settings->dbproc,
00184                 "INSERT INTO %s "
00185                 "("
00186                 "accountcode, src, dst, dcontext, clid, channel, "
00187                 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
00188                 "billsec, disposition, amaflags, uniqueid, userfield"
00189                 ") "
00190                 "VALUES "
00191                 "("
00192                 "'%s', '%s', '%s', '%s', '%s', '%s', "
00193                 "'%s', '%s', '%s', %s, %s, %s, %ld, "
00194                 "%ld, '%s', '%s', '%s', '%s'"
00195                 ")",
00196                 settings->table,
00197                 accountcode, src, dst, dcontext, clid, channel,
00198                 dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
00199                 cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid,
00200                 userfield
00201          );
00202       }
00203    } else {
00204       if (settings->hrtime) {
00205          double hrbillsec = 0.0;
00206          double hrduration;
00207 
00208          if (!ast_tvzero(cdr->answer)) {
00209             hrbillsec = (double)(ast_tvdiff_us(cdr->end, cdr->answer) / 1000000.0);
00210          }
00211          hrduration = (double)(ast_tvdiff_us(cdr->end, cdr->start) / 1000000.0);
00212 
00213          erc = dbfcmd(settings->dbproc,
00214                 "INSERT INTO %s "
00215                 "("
00216                 "accountcode, src, dst, dcontext, clid, channel, "
00217                 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
00218                 "billsec, disposition, amaflags, uniqueid"
00219                 ") "
00220                 "VALUES "
00221                 "("
00222                 "'%s', '%s', '%s', '%s', '%s', '%s', "
00223                 "'%s', '%s', '%s', %s, %s, %s, %lf, "
00224                 "%lf, '%s', '%s', '%s'"
00225                 ")",
00226                 settings->table,
00227                 accountcode, src, dst, dcontext, clid, channel,
00228                 dstchannel, lastapp, lastdata, start, answer, end, hrduration,
00229                 hrbillsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid
00230          );
00231       } else {
00232          erc = dbfcmd(settings->dbproc,
00233                 "INSERT INTO %s "
00234                 "("
00235                 "accountcode, src, dst, dcontext, clid, channel, "
00236                 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
00237                 "billsec, disposition, amaflags, uniqueid"
00238                 ") "
00239                 "VALUES "
00240                 "("
00241                 "'%s', '%s', '%s', '%s', '%s', '%s', "
00242                 "'%s', '%s', '%s', %s, %s, %s, %ld, "
00243                 "%ld, '%s', '%s', '%s'"
00244                 ")",
00245                 settings->table,
00246                 accountcode, src, dst, dcontext, clid, channel,
00247                 dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
00248                 cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid
00249          );
00250       }
00251    }
00252 
00253    if (erc == FAIL) {
00254       if (attempt++ < 3) {
00255          ast_log(LOG_NOTICE, "Failed to build INSERT statement, retrying...\n");
00256          mssql_disconnect();
00257          goto retry;
00258       } else {
00259          ast_log(LOG_ERROR, "Failed to build INSERT statement, no CDR was logged.\n");
00260          goto done;
00261       }
00262    }
00263 
00264    if (dbsqlexec(settings->dbproc) == FAIL) {
00265       if (attempt++ < 3) {
00266          ast_log(LOG_NOTICE, "Failed to execute INSERT statement, retrying...\n");
00267          mssql_disconnect();
00268          goto retry;
00269       } else {
00270          ast_log(LOG_ERROR, "Failed to execute INSERT statement, no CDR was logged.\n");
00271          goto done;
00272       }
00273    }
00274 
00275    /* Consume any results we might get back (this is more of a sanity check than
00276     * anything else, since an INSERT shouldn't return results). */
00277    while (dbresults(settings->dbproc) != NO_MORE_RESULTS) {
00278       while (dbnextrow(settings->dbproc) != NO_MORE_ROWS);
00279    }
00280 
00281    res = 0;
00282 
00283 done:
00284    ast_mutex_unlock(&tds_lock);
00285 
00286    ast_free(accountcode);
00287    ast_free(src);
00288    ast_free(dst);
00289    ast_free(dcontext);
00290    ast_free(clid);
00291    ast_free(channel);
00292    ast_free(dstchannel);
00293    ast_free(lastapp);
00294    ast_free(lastdata);
00295    ast_free(uniqueid);
00296 
00297    if (userfield) {
00298       ast_free(userfield);
00299    }
00300 
00301    return res;
00302 }
00303 
00304 static char *anti_injection(const char *str, int len)
00305 {
00306    /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
00307    char *buf;
00308    char *buf_ptr, *srh_ptr;
00309    char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
00310    int idx;
00311 
00312    if (!(buf = ast_calloc(1, len + 1))) {
00313       ast_log(LOG_ERROR, "Out of memory\n");
00314       return NULL;
00315    }
00316 
00317    buf_ptr = buf;
00318 
00319    /* Escape single quotes */
00320    for (; *str && strlen(buf) < len; str++) {
00321       if (*str == '\'') {
00322          *buf_ptr++ = '\'';
00323       }
00324       *buf_ptr++ = *str;
00325    }
00326    *buf_ptr = '\0';
00327 
00328    /* Erase known bad input */
00329    for (idx = 0; *known_bad[idx]; idx++) {
00330       while ((srh_ptr = strcasestr(buf, known_bad[idx]))) {
00331          memmove(srh_ptr, srh_ptr + strlen(known_bad[idx]), strlen(srh_ptr + strlen(known_bad[idx])) + 1);
00332       }
00333    }
00334 
00335    return buf;
00336 }
00337 
00338 static void get_date(char *dateField, size_t len, struct timeval when)
00339 {
00340    /* To make sure we have date variable if not insert null to SQL */
00341    if (!ast_tvzero(when)) {
00342       struct ast_tm tm;
00343       ast_localtime(&when, &tm, NULL);
00344       ast_strftime(dateField, len, "'" DATE_FORMAT "'", &tm);
00345    } else {
00346       ast_copy_string(dateField, "null", len);
00347    }
00348 }
00349 
00350 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
00351 {
00352    va_list ap;
00353    char *buffer;
00354 
00355    va_start(ap, fmt);
00356    if (ast_vasprintf(&buffer, fmt, ap) < 0) {
00357       va_end(ap);
00358       return 1;
00359    }
00360    va_end(ap);
00361 
00362    if (dbfcmd(dbproc, buffer) == FAIL) {
00363       ast_free(buffer);
00364       return 1;
00365    }
00366 
00367    ast_free(buffer);
00368 
00369    if (dbsqlexec(dbproc) == FAIL) {
00370       return 1;
00371    }
00372 
00373    /* Consume the result set (we don't really care about the result, though) */
00374    while (dbresults(dbproc) != NO_MORE_RESULTS) {
00375       while (dbnextrow(dbproc) != NO_MORE_ROWS);
00376    }
00377 
00378    return 0;
00379 }
00380 
00381 static int mssql_disconnect(void)
00382 {
00383    if (settings->dbproc) {
00384       dbclose(settings->dbproc);
00385       settings->dbproc = NULL;
00386    }
00387 
00388    settings->connected = 0;
00389 
00390    return 0;
00391 }
00392 
00393 static int mssql_connect(void)
00394 {
00395    LOGINREC *login;
00396 
00397    if ((login = dblogin()) == NULL) {
00398       ast_log(LOG_ERROR, "Unable to allocate login structure for db-lib\n");
00399       return -1;
00400    }
00401 
00402    DBSETLAPP(login,     "TSQL");
00403    DBSETLUSER(login,    (char *) settings->username);
00404    DBSETLPWD(login,     (char *) settings->password);
00405    DBSETLCHARSET(login, (char *) settings->charset);
00406    DBSETLNATLANG(login, (char *) settings->language);
00407 
00408    if ((settings->dbproc = dbopen(login, (char *) settings->hostname)) == NULL) {
00409       ast_log(LOG_ERROR, "Unable to connect to %s\n", settings->hostname);
00410       dbloginfree(login);
00411       return -1;
00412    }
00413 
00414    dbloginfree(login);
00415 
00416    if (dbuse(settings->dbproc, (char *) settings->database) == FAIL) {
00417       ast_log(LOG_ERROR, "Unable to select database %s\n", settings->database);
00418       goto failed;
00419    }
00420 
00421    if (execute_and_consume(settings->dbproc, "SELECT 1 FROM [%s] WHERE 1 = 0", settings->table)) {
00422       ast_log(LOG_ERROR, "Unable to find table '%s'\n", settings->table);
00423       goto failed;
00424    }
00425 
00426    /* Check to see if we have a userfield column in the table */
00427    if (execute_and_consume(settings->dbproc, "SELECT userfield FROM [%s] WHERE 1 = 0", settings->table)) {
00428       ast_log(LOG_NOTICE, "Unable to find 'userfield' column in table '%s'\n", settings->table);
00429       settings->has_userfield = 0;
00430    } else {
00431       settings->has_userfield = 1;
00432    }
00433 
00434    settings->connected = 1;
00435 
00436    return 0;
00437 
00438 failed:
00439    dbclose(settings->dbproc);
00440    settings->dbproc = NULL;
00441    return -1;
00442 }
00443 
00444 static int tds_unload_module(void)
00445 {
00446    if (ast_cdr_unregister(name)) {
00447       return -1;
00448    }
00449 
00450    if (settings) {
00451       ast_mutex_lock(&tds_lock);
00452       mssql_disconnect();
00453       ast_mutex_unlock(&tds_lock);
00454 
00455       ast_string_field_free_memory(settings);
00456       ast_free(settings);
00457    }
00458 
00459    dbexit();
00460 
00461    return 0;
00462 }
00463 
00464 static int tds_error_handler(DBPROCESS *dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
00465 {
00466    ast_log(LOG_ERROR, "%s (%d)\n", dberrstr, dberr);
00467 
00468    if (oserr != DBNOERR) {
00469       ast_log(LOG_ERROR, "%s (%d)\n", oserrstr, oserr);
00470    }
00471 
00472    return INT_CANCEL;
00473 }
00474 
00475 static int tds_message_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
00476 {
00477    ast_debug(1, "Msg %d, Level %d, State %d, Line %d\n", msgno, severity, msgstate, line);
00478    ast_log(LOG_NOTICE, "%s\n", msgtext);
00479 
00480    return 0;
00481 }
00482 
00483 static int tds_load_module(int reload)
00484 {
00485    struct ast_config *cfg;
00486    const char *ptr = NULL;
00487    struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
00488 
00489    cfg = ast_config_load(config, config_flags);
00490    if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
00491       ast_log(LOG_NOTICE, "Unable to load TDS config for CDRs: %s\n", config);
00492       return 0;
00493    } else if (cfg == CONFIG_STATUS_FILEUNCHANGED)
00494       return 0;
00495 
00496    if (!ast_variable_browse(cfg, "global")) {
00497       /* nothing configured */
00498       ast_config_destroy(cfg);
00499       return 0;
00500    }
00501 
00502    ast_mutex_lock(&tds_lock);
00503 
00504    /* Clear out any existing settings */
00505    ast_string_field_init(settings, 0);
00506 
00507    /* 'connection' is the new preferred configuration option */
00508    ptr = ast_variable_retrieve(cfg, "global", "connection");
00509    if (ptr) {
00510       ast_string_field_set(settings, hostname, ptr);
00511    } else {
00512       /* But we keep 'hostname' for backwards compatibility */
00513       ptr = ast_variable_retrieve(cfg, "global", "hostname");
00514       if (ptr) {
00515          ast_string_field_set(settings, hostname, ptr);
00516       } else {
00517          ast_log(LOG_ERROR, "Failed to connect: Database server connection not specified.\n");
00518          goto failed;
00519       }
00520    }
00521 
00522    ptr = ast_variable_retrieve(cfg, "global", "dbname");
00523    if (ptr) {
00524       ast_string_field_set(settings, database, ptr);
00525    } else {
00526       ast_log(LOG_ERROR, "Failed to connect: Database dbname not specified.\n");
00527       goto failed;
00528    }
00529 
00530    ptr = ast_variable_retrieve(cfg, "global", "user");
00531    if (ptr) {
00532       ast_string_field_set(settings, username, ptr);
00533    } else {
00534       ast_log(LOG_ERROR, "Failed to connect: Database dbuser not specified.\n");
00535       goto failed;
00536    }
00537 
00538    ptr = ast_variable_retrieve(cfg, "global", "password");
00539    if (ptr) {
00540       ast_string_field_set(settings, password, ptr);
00541    } else {
00542       ast_log(LOG_ERROR, "Failed to connect: Database password not specified.\n");
00543       goto failed;
00544    }
00545 
00546    ptr = ast_variable_retrieve(cfg, "global", "charset");
00547    if (ptr) {
00548       ast_string_field_set(settings, charset, ptr);
00549    } else {
00550       ast_string_field_set(settings, charset, "iso_1");
00551    }
00552 
00553    ptr = ast_variable_retrieve(cfg, "global", "language");
00554    if (ptr) {
00555       ast_string_field_set(settings, language, ptr);
00556    } else {
00557       ast_string_field_set(settings, language, "us_english");
00558    }
00559 
00560    ptr = ast_variable_retrieve(cfg, "global", "table");
00561    if (ptr) {
00562       ast_string_field_set(settings, table, ptr);
00563    } else {
00564       ast_log(LOG_NOTICE, "Table name not specified, using 'cdr' by default.\n");
00565       ast_string_field_set(settings, table, "cdr");
00566    }
00567 
00568    ptr = ast_variable_retrieve(cfg, "global", "hrtime");
00569    if (ptr && ast_true(ptr)) {
00570       ast_string_field_set(settings, hrtime, ptr);
00571    } else {
00572       ast_log(LOG_NOTICE, "High Resolution Time not found, using integers for billsec and duration fields by default.\n");
00573    }
00574 
00575    mssql_disconnect();
00576 
00577    if (mssql_connect()) {
00578       /* We failed to connect (mssql_connect takes care of logging it) */
00579       goto failed;
00580    }
00581 
00582    ast_mutex_unlock(&tds_lock);
00583    ast_config_destroy(cfg);
00584 
00585    return 1;
00586 
00587 failed:
00588    ast_mutex_unlock(&tds_lock);
00589    ast_config_destroy(cfg);
00590 
00591    return 0;
00592 }
00593 
00594 static int reload(void)
00595 {
00596    return tds_load_module(1);
00597 }
00598 
00599 static int load_module(void)
00600 {
00601    if (dbinit() == FAIL) {
00602       ast_log(LOG_ERROR, "Failed to initialize FreeTDS db-lib\n");
00603       return AST_MODULE_LOAD_DECLINE;
00604    }
00605 
00606    dberrhandle(tds_error_handler);
00607    dbmsghandle(tds_message_handler);
00608 
00609    settings = ast_calloc_with_stringfields(1, struct cdr_tds_config, 256);
00610 
00611    if (!settings) {
00612       dbexit();
00613       return AST_MODULE_LOAD_DECLINE;
00614    }
00615 
00616    if (!tds_load_module(0)) {
00617       ast_string_field_free_memory(settings);
00618       ast_free(settings);
00619       settings = NULL;
00620       dbexit();
00621       return AST_MODULE_LOAD_DECLINE;
00622    }
00623 
00624    ast_cdr_register(name, ast_module_info->description, tds_log);
00625 
00626    return AST_MODULE_LOAD_SUCCESS;
00627 }
00628 
00629 static int unload_module(void)
00630 {
00631    return tds_unload_module();
00632 }
00633 
00634 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "FreeTDS CDR Backend",
00635       .support_level = AST_MODULE_SUPPORT_EXTENDED,
00636       .load = load_module,
00637       .unload = unload_module,
00638       .reload = reload,
00639       .load_pri = AST_MODPRI_CDR_DRIVER,
00640           );

Generated on Thu Apr 16 06:27:18 2015 for Asterisk - The Open Source Telephony Project by  doxygen 1.5.6