Wed Oct 28 11:45:28 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 - 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 /*! \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    [userfield] [varchar] (256) NULL
00053 ) ON [PRIMARY]
00054 
00055 \endverbatim
00056 
00057 */
00058 
00059 /*** MODULEINFO
00060    <depend>freetds</depend>
00061  ***/
00062 
00063 #include "asterisk.h"
00064 
00065 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 159855 $")
00066 
00067 #include <time.h>
00068 #include <math.h>
00069 
00070 #include "asterisk/config.h"
00071 #include "asterisk/channel.h"
00072 #include "asterisk/cdr.h"
00073 #include "asterisk/module.h"
00074 
00075 #include <sqlfront.h>
00076 #include <sybdb.h>
00077 
00078 #define DATE_FORMAT "%Y/%m/%d %T"
00079 
00080 static char *name = "FreeTDS (MSSQL)";
00081 static char *config = "cdr_tds.conf";
00082 
00083 struct cdr_tds_config {
00084    AST_DECLARE_STRING_FIELDS(
00085       AST_STRING_FIELD(hostname);
00086       AST_STRING_FIELD(database);
00087       AST_STRING_FIELD(username);
00088       AST_STRING_FIELD(password);
00089       AST_STRING_FIELD(table);
00090       AST_STRING_FIELD(charset);
00091       AST_STRING_FIELD(language);
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       erc = dbfcmd(settings->dbproc,
00155                 "INSERT INTO %s "
00156                 "("
00157                 "accountcode, src, dst, dcontext, clid, channel, "
00158                 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
00159                 "billsec, disposition, amaflags, uniqueid, userfield"
00160                 ") "
00161                 "VALUES "
00162                 "("
00163                 "'%s', '%s', '%s', '%s', '%s', '%s', "
00164                 "'%s', '%s', '%s', %s, %s, %s, %ld, "
00165                 "%ld, '%s', '%s', '%s', '%s'"
00166                 ")",
00167                 settings->table,
00168                 accountcode, src, dst, dcontext, clid, channel,
00169                 dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
00170                 cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_cdr_flags2str(cdr->amaflags), uniqueid,
00171                 userfield
00172          );
00173    } else {
00174       erc = dbfcmd(settings->dbproc,
00175                 "INSERT INTO %s "
00176                 "("
00177                 "accountcode, src, dst, dcontext, clid, channel, "
00178                 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
00179                 "billsec, disposition, amaflags, uniqueid"
00180                 ") "
00181                 "VALUES "
00182                 "("
00183                 "'%s', '%s', '%s', '%s', '%s', '%s', "
00184                 "'%s', '%s', '%s', %s, %s, %s, %ld, "
00185                 "%ld, '%s', '%s', '%s'"
00186                 ")",
00187                 settings->table,
00188                 accountcode, src, dst, dcontext, clid, channel,
00189                 dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
00190                 cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_cdr_flags2str(cdr->amaflags), uniqueid
00191          );
00192    }
00193 
00194    if (erc == FAIL) {
00195       if (attempt++ < 3) {
00196          ast_log(LOG_NOTICE, "Failed to build INSERT statement, retrying...\n");
00197          mssql_disconnect();
00198          goto retry;
00199       } else {
00200          ast_log(LOG_ERROR, "Failed to build INSERT statement, no CDR was logged.\n");
00201          goto done;
00202       }
00203    }
00204 
00205    if (dbsqlexec(settings->dbproc) == FAIL) {
00206       if (attempt++ < 3) {
00207          ast_log(LOG_NOTICE, "Failed to execute INSERT statement, retrying...\n");
00208          mssql_disconnect();
00209          goto retry;
00210       } else {
00211          ast_log(LOG_ERROR, "Failed to execute INSERT statement, no CDR was logged.\n");
00212          goto done;
00213       }
00214    }
00215 
00216    /* Consume any results we might get back (this is more of a sanity check than
00217     * anything else, since an INSERT shouldn't return results). */
00218    while (dbresults(settings->dbproc) != NO_MORE_RESULTS) {
00219       while (dbnextrow(settings->dbproc) != NO_MORE_ROWS);
00220    }
00221 
00222    res = 0;
00223 
00224 done:
00225    ast_mutex_unlock(&tds_lock);
00226 
00227    ast_free(accountcode);
00228    ast_free(src);
00229    ast_free(dst);
00230    ast_free(dcontext);
00231    ast_free(clid);
00232    ast_free(channel);
00233    ast_free(dstchannel);
00234    ast_free(lastapp);
00235    ast_free(lastdata);
00236    ast_free(uniqueid);
00237 
00238    if (userfield) {
00239       ast_free(userfield);
00240    }
00241 
00242    return res;
00243 }
00244 
00245 static char *anti_injection(const char *str, int len)
00246 {
00247    /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
00248    char *buf;
00249    char *buf_ptr, *srh_ptr;
00250    char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
00251    int idx;
00252 
00253    if (!(buf = ast_calloc(1, len + 1))) {
00254       ast_log(LOG_ERROR, "Out of memory\n");
00255       return NULL;
00256    }
00257 
00258    buf_ptr = buf;
00259 
00260    /* Escape single quotes */
00261    for (; *str && strlen(buf) < len; str++) {
00262       if (*str == '\'') {
00263          *buf_ptr++ = '\'';
00264       }
00265       *buf_ptr++ = *str;
00266    }
00267    *buf_ptr = '\0';
00268 
00269    /* Erase known bad input */
00270    for (idx = 0; *known_bad[idx]; idx++) {
00271       while ((srh_ptr = strcasestr(buf, known_bad[idx]))) {
00272          memmove(srh_ptr, srh_ptr + strlen(known_bad[idx]), strlen(srh_ptr + strlen(known_bad[idx])) + 1);
00273       }
00274    }
00275 
00276    return buf;
00277 }
00278 
00279 static void get_date(char *dateField, size_t len, struct timeval tv)
00280 {
00281    /* To make sure we have date variable if not insert null to SQL */
00282    if (!ast_tvzero(tv)) {
00283       struct ast_tm tm;
00284       ast_localtime(&tv, &tm, NULL);
00285       ast_strftime(dateField, len, "'" DATE_FORMAT "'", &tm);
00286    } else {
00287       ast_copy_string(dateField, "null", len);
00288    }
00289 }
00290 
00291 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
00292 {
00293    va_list ap;
00294    char *buffer;
00295 
00296    va_start(ap, fmt);
00297    if (ast_vasprintf(&buffer, fmt, ap) < 0) {
00298       va_end(ap);
00299       return 1;
00300    }
00301    va_end(ap);
00302 
00303    if (dbfcmd(dbproc, buffer) == FAIL) {
00304       free(buffer);
00305       return 1;
00306    }
00307 
00308    free(buffer);
00309 
00310    if (dbsqlexec(dbproc) == FAIL) {
00311       return 1;
00312    }
00313 
00314    /* Consume the result set (we don't really care about the result, though) */
00315    while (dbresults(dbproc) != NO_MORE_RESULTS) {
00316       while (dbnextrow(dbproc) != NO_MORE_ROWS);
00317    }
00318 
00319    return 0;
00320 }
00321 
00322 static int mssql_disconnect(void)
00323 {
00324    if (settings->dbproc) {
00325       dbclose(settings->dbproc);
00326       settings->dbproc = NULL;
00327    }
00328 
00329    settings->connected = 0;
00330 
00331    return 0;
00332 }
00333 
00334 static int mssql_connect(void)
00335 {
00336    LOGINREC *login;
00337 
00338    if ((login = dblogin()) == NULL) {
00339       ast_log(LOG_ERROR, "Unable to allocate login structure for db-lib\n");
00340       return -1;
00341    }
00342 
00343    DBSETLAPP(login,     "TSQL");
00344    DBSETLUSER(login,    (char *) settings->username);
00345    DBSETLPWD(login,     (char *) settings->password);
00346    DBSETLCHARSET(login, (char *) settings->charset);
00347    DBSETLNATLANG(login, (char *) settings->language);
00348 
00349    if ((settings->dbproc = dbopen(login, (char *) settings->hostname)) == NULL) {
00350       ast_log(LOG_ERROR, "Unable to connect to %s\n", settings->hostname);
00351       dbloginfree(login);
00352       return -1;
00353    }
00354 
00355    dbloginfree(login);
00356 
00357    if (dbuse(settings->dbproc, (char *) settings->database) == FAIL) {
00358       ast_log(LOG_ERROR, "Unable to select database %s\n", settings->database);
00359       goto failed;
00360    }
00361 
00362    if (execute_and_consume(settings->dbproc, "SELECT 1 FROM [%s]", settings->table)) {
00363       ast_log(LOG_ERROR, "Unable to find table '%s'\n", settings->table);
00364       goto failed;
00365    }
00366 
00367    /* Check to see if we have a userfield column in the table */
00368    if (execute_and_consume(settings->dbproc, "SELECT userfield FROM [%s] WHERE 1 = 0", settings->table)) {
00369       ast_log(LOG_NOTICE, "Unable to find 'userfield' column in table '%s'\n", settings->table);
00370       settings->has_userfield = 0;
00371    } else {
00372       settings->has_userfield = 1;
00373    }
00374 
00375    settings->connected = 1;
00376 
00377    return 0;
00378 
00379 failed:
00380    dbclose(settings->dbproc);
00381    settings->dbproc = NULL;
00382    return -1;
00383 }
00384 
00385 static int tds_unload_module(void)
00386 {
00387    if (settings) {
00388       ast_mutex_lock(&tds_lock);
00389       mssql_disconnect();
00390       ast_mutex_unlock(&tds_lock);
00391 
00392       ast_string_field_free_memory(settings);
00393       ast_free(settings);
00394    }
00395 
00396    ast_cdr_unregister(name);
00397 
00398    dbexit();
00399 
00400    return 0;
00401 }
00402 
00403 static int tds_error_handler(DBPROCESS *dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
00404 {
00405    ast_log(LOG_ERROR, "%s (%d)\n", dberrstr, dberr);
00406 
00407    if (oserr != DBNOERR) {
00408       ast_log(LOG_ERROR, "%s (%d)\n", oserrstr, oserr);
00409    }
00410 
00411    return INT_CANCEL;
00412 }
00413 
00414 static int tds_message_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
00415 {
00416    ast_debug(1, "Msg %d, Level %d, State %d, Line %d\n", msgno, severity, msgstate, line);
00417    ast_log(LOG_NOTICE, "%s\n", msgtext);
00418 
00419    return 0;
00420 }
00421 
00422 static int tds_load_module(int reload)
00423 {
00424    struct ast_config *cfg;
00425    const char *ptr = NULL;
00426    struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
00427 
00428    cfg = ast_config_load(config, config_flags);
00429    if (!cfg) {
00430       ast_log(LOG_NOTICE, "Unable to load TDS config for CDRs: %s\n", config);
00431       return 0;
00432    } else if (cfg == CONFIG_STATUS_FILEUNCHANGED)
00433       return 0;
00434 
00435    if (!ast_variable_browse(cfg, "global")) {
00436       /* nothing configured */
00437       ast_config_destroy(cfg);
00438       return 0;
00439    }
00440 
00441    ast_mutex_lock(&tds_lock);
00442 
00443    /* Clear out any existing settings */
00444    ast_string_field_init(settings, 0);
00445 
00446    ptr = ast_variable_retrieve(cfg, "global", "hostname");
00447    if (ptr) {
00448       ast_string_field_set(settings, hostname, ptr);
00449    } else {
00450       ast_log(LOG_ERROR, "Failed to connect: Database server hostname not specified.\n");
00451       goto failed;
00452    }
00453 
00454    ptr = ast_variable_retrieve(cfg, "global", "dbname");
00455    if (ptr) {
00456       ast_string_field_set(settings, database, ptr);
00457    } else {
00458       ast_log(LOG_ERROR, "Failed to connect: Database dbname not specified.\n");
00459       goto failed;
00460    }
00461 
00462    ptr = ast_variable_retrieve(cfg, "global", "user");
00463    if (ptr) {
00464       ast_string_field_set(settings, username, ptr);
00465    } else {
00466       ast_log(LOG_ERROR, "Failed to connect: Database dbuser not specified.\n");
00467       goto failed;
00468    }
00469 
00470    ptr = ast_variable_retrieve(cfg, "global", "password");
00471    if (ptr) {
00472       ast_string_field_set(settings, password, ptr);
00473    } else {
00474       ast_log(LOG_ERROR, "Failed to connect: Database password not specified.\n");
00475       goto failed;
00476    }
00477 
00478    ptr = ast_variable_retrieve(cfg, "global", "charset");
00479    if (ptr) {
00480       ast_string_field_set(settings, charset, ptr);
00481    } else {
00482       ast_string_field_set(settings, charset, "iso_1");
00483    }
00484 
00485    ptr = ast_variable_retrieve(cfg, "global", "language");
00486    if (ptr) {
00487       ast_string_field_set(settings, language, ptr);
00488    } else {
00489       ast_string_field_set(settings, language, "us_english");
00490    }
00491 
00492    ptr = ast_variable_retrieve(cfg, "global", "table");
00493    if (ptr) {
00494       ast_string_field_set(settings, table, ptr);
00495    } else {
00496       ast_log(LOG_NOTICE, "Table name not specified, using 'cdr' by default.\n");
00497       ast_string_field_set(settings, table, "cdr");
00498    }
00499 
00500    mssql_disconnect();
00501 
00502    if (mssql_connect()) {
00503       /* We failed to connect (mssql_connect takes care of logging it) */
00504       goto failed;
00505    }
00506 
00507    ast_mutex_unlock(&tds_lock);
00508    ast_config_destroy(cfg);
00509 
00510    return 1;
00511 
00512 failed:
00513    ast_mutex_unlock(&tds_lock);
00514    ast_config_destroy(cfg);
00515 
00516    return 0;
00517 }
00518 
00519 static int reload(void)
00520 {
00521    return tds_load_module(1);
00522 }
00523 
00524 static int load_module(void)
00525 {
00526    if (dbinit() == FAIL) {
00527       ast_log(LOG_ERROR, "Failed to initialize FreeTDS db-lib\n");
00528       return AST_MODULE_LOAD_DECLINE;
00529    }
00530 
00531    dberrhandle(tds_error_handler);
00532    dbmsghandle(tds_message_handler);
00533 
00534    settings = ast_calloc(1, sizeof(*settings));
00535 
00536    if (!settings || ast_string_field_init(settings, 256)) {
00537       if (settings) {
00538          ast_free(settings);
00539          settings = NULL;
00540       }
00541       dbexit();
00542       return AST_MODULE_LOAD_DECLINE;
00543    }
00544 
00545    if (!tds_load_module(0)) {
00546       ast_string_field_free_memory(settings);
00547       ast_free(settings);
00548       settings = NULL;
00549       dbexit();
00550       return AST_MODULE_LOAD_DECLINE;
00551    }
00552 
00553    ast_cdr_register(name, ast_module_info->description, tds_log);
00554 
00555    return AST_MODULE_LOAD_SUCCESS;
00556 }
00557 
00558 static int unload_module(void)
00559 {
00560    return tds_unload_module();
00561 }
00562 
00563 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "FreeTDS CDR Backend",
00564       .load = load_module,
00565       .unload = unload_module,
00566       .reload = reload,
00567           );

Generated on Wed Oct 28 11:45:28 2009 for Asterisk - the Open Source PBX by  doxygen 1.5.6