Wed Apr 6 11:29:41 2011

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

Generated on Wed Apr 6 11:29:41 2011 for Asterisk - The Open Source Telephony Project by  doxygen 1.4.7