cdr_tds.c

Go to the documentation of this file.

/*
 * Asterisk -- An open source telephony toolkit.
 *
 * Copyright (C) 2004 - 2006, Digium, Inc.
 *
 * See http://www.asterisk.org for more information about
 * the Asterisk project. Please do not directly contact
 * any of the maintainers of this project for assistance;
 * the project provides a web site, mailing lists and IRC
 * channels for your use.
 *
 * This program is free software, distributed under the terms of
 * the GNU General Public License Version 2. See the LICENSE file
 * at the top of the source tree.
 */

/*!
 * \file
 * \brief FreeTDS CDR logger
 *
 * See also
 * \arg \ref Config_cdr
 * \arg http://www.freetds.org/
 * \ingroup cdr_drivers
 */

/*!
 * \verbatim
 *
 * Table Structure for `cdr`
 *
 * Created on: 05/20/2004 16:16
 * Last changed on: 07/27/2004 20:01

CREATE TABLE [dbo].[cdr] (
 [accountcode] [varchar] (20) NULL ,
 [src] [varchar] (80) NULL ,
 [dst] [varchar] (80) NULL ,
 [dcontext] [varchar] (80) NULL ,
 [clid] [varchar] (80) NULL ,
 [channel] [varchar] (80) NULL ,
 [dstchannel] [varchar] (80) NULL ,
 [lastapp] [varchar] (80) NULL ,
 [lastdata] [varchar] (80) NULL ,
 [start] [datetime] NULL ,
 [answer] [datetime] NULL ,
 [end] [datetime] NULL ,
 [duration] [int] NULL ,
 [billsec] [int] NULL ,
 [disposition] [varchar] (20) NULL ,
 [amaflags] [varchar] (16) NULL ,
 [uniqueid] [varchar] (32) NULL ,
 [userfield] [varchar] (256) NULL
) ON [PRIMARY]

\endverbatim

*/

/*** MODULEINFO
 <depend>freetds</depend>
 <support_level>extended</support_level>
 ***/

#include "asterisk.h"

#include "asterisk/config.h"
#include "asterisk/channel.h"
#include "asterisk/cdr.h"
#include "asterisk/module.h"

#include <sqlfront.h>
#include <sybdb.h>

#define DATE_FORMAT "%Y/%m/%d %T"

static const char name[] = "FreeTDS (MSSQL)";
static const char config[] = "cdr_tds.conf";

struct cdr_tds_config {
 AST_DECLARE_STRING_FIELDS(
 AST_STRING_FIELD(hostname);
 AST_STRING_FIELD(database);
 AST_STRING_FIELD(username);
 AST_STRING_FIELD(password);
 AST_STRING_FIELD(table);
 AST_STRING_FIELD(charset);
 AST_STRING_FIELD(language);
 AST_STRING_FIELD(hrtime);
 );
 DBPROCESS *dbproc;
 unsigned int connected:1;
 unsigned int has_userfield:1;
};

AST_MUTEX_DEFINE_STATIC(tds_lock);

static struct cdr_tds_config *settings;

static char *anti_injection(const char *, int);
static void get_date(char *, size_t len, struct timeval);

static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
 __attribute__((format(printf, 2, 3)));

static int mssql_connect(void);
static int mssql_disconnect(void);

static int tds_log(struct ast_cdr *cdr)
{
 char start[80], answer[80], end[80];
 char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid, *userfield = NULL;
 RETCODE erc;
 int res = -1;
 int attempt = 1;

 accountcode = anti_injection(cdr->accountcode, 20);
 src = anti_injection(cdr->src, 80);
 dst = anti_injection(cdr->dst, 80);
 dcontext = anti_injection(cdr->dcontext, 80);
 clid = anti_injection(cdr->clid, 80);
 channel = anti_injection(cdr->channel, 80);
 dstchannel = anti_injection(cdr->dstchannel, 80);
 lastapp = anti_injection(cdr->lastapp, 80);
 lastdata = anti_injection(cdr->lastdata, 80);
 uniqueid = anti_injection(cdr->uniqueid, 32);

 get_date(start, sizeof(start), cdr->start);
 get_date(answer, sizeof(answer), cdr->answer);
 get_date(end, sizeof(end), cdr->end);

 ast_mutex_lock(&tds_lock);

 if (settings->has_userfield) {
 userfield = anti_injection(cdr->userfield, AST_MAX_USER_FIELD);
 }

retry:
 /* Ensure that we are connected */
 if (!settings->connected) {
 ast_log(LOG_NOTICE, "Attempting to reconnect to %s (Attempt %d)\n", settings->hostname, attempt);
 if (mssql_connect()) {
 /* Connect failed */
 if (attempt++ < 3) {
 goto retry;
 }
 goto done;
 }
 }

 if (settings->has_userfield) {
 if (settings->hrtime) {
 double hrbillsec = 0.0;
 double hrduration;

 if (!ast_tvzero(cdr->answer)) {
 hrbillsec = (double)(ast_tvdiff_us(cdr->end, cdr->answer) / 1000000.0);
 }
 hrduration = (double)(ast_tvdiff_us(cdr->end, cdr->start) / 1000000.0);

 erc = dbfcmd(settings->dbproc,
 "INSERT INTO %s "
 "("
 "accountcode, src, dst, dcontext, clid, channel, "
 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
 "billsec, disposition, amaflags, uniqueid, userfield"
 ") "
 "VALUES "
 "("
 "'%s', '%s', '%s', '%s', '%s', '%s', "
 "'%s', '%s', '%s', %s, %s, %s, %lf, "
 "%lf, '%s', '%s', '%s', '%s'"
 ")",
 settings->table,
 accountcode, src, dst, dcontext, clid, channel,
 dstchannel, lastapp, lastdata, start, answer, end, hrduration,
 hrbillsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid,
 userfield
 );
 } else {
 erc = dbfcmd(settings->dbproc,
 "INSERT INTO %s "
 "("
 "accountcode, src, dst, dcontext, clid, channel, "
 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
 "billsec, disposition, amaflags, uniqueid, userfield"
 ") "
 "VALUES "
 "("
 "'%s', '%s', '%s', '%s', '%s', '%s', "
 "'%s', '%s', '%s', %s, %s, %s, %ld, "
 "%ld, '%s', '%s', '%s', '%s'"
 ")",
 settings->table,
 accountcode, src, dst, dcontext, clid, channel,
 dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
 cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid,
 userfield
 );
 }
 } else {
 if (settings->hrtime) {
 double hrbillsec = 0.0;
 double hrduration;

 if (!ast_tvzero(cdr->answer)) {
 hrbillsec = (double)(ast_tvdiff_us(cdr->end, cdr->answer) / 1000000.0);
 }
 hrduration = (double)(ast_tvdiff_us(cdr->end, cdr->start) / 1000000.0);

 erc = dbfcmd(settings->dbproc,
 "INSERT INTO %s "
 "("
 "accountcode, src, dst, dcontext, clid, channel, "
 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
 "billsec, disposition, amaflags, uniqueid"
 ") "
 "VALUES "
 "("
 "'%s', '%s', '%s', '%s', '%s', '%s', "
 "'%s', '%s', '%s', %s, %s, %s, %lf, "
 "%lf, '%s', '%s', '%s'"
 ")",
 settings->table,
 accountcode, src, dst, dcontext, clid, channel,
 dstchannel, lastapp, lastdata, start, answer, end, hrduration,
 hrbillsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid
 );
 } else {
 erc = dbfcmd(settings->dbproc,
 "INSERT INTO %s "
 "("
 "accountcode, src, dst, dcontext, clid, channel, "
 "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
 "billsec, disposition, amaflags, uniqueid"
 ") "
 "VALUES "
 "("
 "'%s', '%s', '%s', '%s', '%s', '%s', "
 "'%s', '%s', '%s', %s, %s, %s, %ld, "
 "%ld, '%s', '%s', '%s'"
 ")",
 settings->table,
 accountcode, src, dst, dcontext, clid, channel,
 dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
 cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_channel_amaflags2string(cdr->amaflags), uniqueid
 );
 }
 }

 if (erc == FAIL) {
 if (attempt++ < 3) {
 ast_log(LOG_NOTICE, "Failed to build INSERT statement, retrying...\n");
 mssql_disconnect();
 goto retry;
 } else {
 ast_log(LOG_ERROR, "Failed to build INSERT statement, no CDR was logged.\n");
 goto done;
 }
 }

 if (dbsqlexec(settings->dbproc) == FAIL) {
 if (attempt++ < 3) {
 ast_log(LOG_NOTICE, "Failed to execute INSERT statement, retrying...\n");
 mssql_disconnect();
 goto retry;
 } else {
 ast_log(LOG_ERROR, "Failed to execute INSERT statement, no CDR was logged.\n");
 goto done;
 }
 }

 /* Consume any results we might get back (this is more of a sanity check than
 * anything else, since an INSERT shouldn't return results). */
 while (dbresults(settings->dbproc) != NO_MORE_RESULTS) {
 while (dbnextrow(settings->dbproc) != NO_MORE_ROWS);
 }

 res = 0;

done:
 ast_mutex_unlock(&tds_lock);

 ast_free(accountcode);
 ast_free(src);
 ast_free(dst);
 ast_free(dcontext);
 ast_free(clid);
 ast_free(channel);
 ast_free(dstchannel);
 ast_free(lastapp);
 ast_free(lastdata);
 ast_free(uniqueid);

 if (userfield) {
 ast_free(userfield);
 }

 return res;
}

static char *anti_injection(const char *str, int len)
{
 /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
 char *buf;
 char *buf_ptr, *srh_ptr;
 char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
 int idx;

 if (!(buf = ast_calloc(1, len + 1))) {
 ast_log(LOG_ERROR, "Out of memory\n");
 return NULL;
 }

 buf_ptr = buf;

 /* Escape single quotes */
 for (; *str && strlen(buf) < len; str++) {
 if (*str == '\'') {
 *buf_ptr++ = '\'';
 }
 *buf_ptr++ = *str;
 }
 *buf_ptr = '\0';

 /* Erase known bad input */
 for (idx = 0; *known_bad[idx]; idx++) {
 while ((srh_ptr = strcasestr(buf, known_bad[idx]))) {
 memmove(srh_ptr, srh_ptr + strlen(known_bad[idx]), strlen(srh_ptr + strlen(known_bad[idx])) + 1);
 }
 }

 return buf;
}

static void get_date(char *dateField, size_t len, struct timeval when)
{
 /* To make sure we have date variable if not insert null to SQL */
 if (!ast_tvzero(when)) {
 struct ast_tm tm;
 ast_localtime(&when, &tm, NULL);
 ast_strftime(dateField, len, "'" DATE_FORMAT "'", &tm);
 } else {
 ast_copy_string(dateField, "null", len);
 }
}

static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
{
 va_list ap;
 char *buffer;

 va_start(ap, fmt);
 if (ast_vasprintf(&buffer, fmt, ap) < 0) {
 va_end(ap);
 return 1;
 }
 va_end(ap);

 if (dbfcmd(dbproc, buffer) == FAIL) {
 ast_free(buffer);
 return 1;
 }

 ast_free(buffer);

 if (dbsqlexec(dbproc) == FAIL) {
 return 1;
 }

 /* Consume the result set (we don't really care about the result, though) */
 while (dbresults(dbproc) != NO_MORE_RESULTS) {
 while (dbnextrow(dbproc) != NO_MORE_ROWS);
 }

 return 0;
}

static int mssql_disconnect(void)
{
 if (settings->dbproc) {
 dbclose(settings->dbproc);
 settings->dbproc = NULL;
 }

 settings->connected = 0;

 return 0;
}

static int mssql_connect(void)
{
 LOGINREC *login;

 if ((login = dblogin()) == NULL) {
 ast_log(LOG_ERROR, "Unable to allocate login structure for db-lib\n");
 return -1;
 }

 DBSETLAPP(login, "TSQL");
 DBSETLUSER(login, (char *) settings->username);
 DBSETLPWD(login, (char *) settings->password);
 DBSETLCHARSET(login, (char *) settings->charset);
 DBSETLNATLANG(login, (char *) settings->language);

 if ((settings->dbproc = dbopen(login, (char *) settings->hostname)) == NULL) {
 ast_log(LOG_ERROR, "Unable to connect to %s\n", settings->hostname);
 dbloginfree(login);
 return -1;
 }

 dbloginfree(login);

 if (dbuse(settings->dbproc, (char *) settings->database) == FAIL) {
 ast_log(LOG_ERROR, "Unable to select database %s\n", settings->database);
 goto failed;
 }

 if (execute_and_consume(settings->dbproc, "SELECT 1 FROM [%s] WHERE 1 = 0", settings->table)) {
 ast_log(LOG_ERROR, "Unable to find table '%s'\n", settings->table);
 goto failed;
 }

 /* Check to see if we have a userfield column in the table */
 if (execute_and_consume(settings->dbproc, "SELECT userfield FROM [%s] WHERE 1 = 0", settings->table)) {
 ast_log(LOG_NOTICE, "Unable to find 'userfield' column in table '%s'\n", settings->table);
 settings->has_userfield = 0;
 } else {
 settings->has_userfield = 1;
 }

 settings->connected = 1;

 return 0;

failed:
 dbclose(settings->dbproc);
 settings->dbproc = NULL;
 return -1;
}

static int tds_unload_module(void)
{
 if (ast_cdr_unregister(name)) {
 return -1;
 }

 if (settings) {
 ast_mutex_lock(&tds_lock);
 mssql_disconnect();
 ast_mutex_unlock(&tds_lock);

 ast_string_field_free_memory(settings);
 ast_free(settings);
 }

 dbexit();

 return 0;
}

static int tds_error_handler(DBPROCESS *dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
{
 ast_log(LOG_ERROR, "%s (%d)\n", dberrstr, dberr);

 if (oserr != DBNOERR) {
 ast_log(LOG_ERROR, "%s (%d)\n", oserrstr, oserr);
 }

 return INT_CANCEL;
}

static int tds_message_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
{
 ast_debug(1, "Msg %d, Level %d, State %d, Line %d\n", msgno, severity, msgstate, line);
 ast_log(LOG_NOTICE, "%s\n", msgtext);

 return 0;
}

static int tds_load_module(int reload)
{
 struct ast_config *cfg;
 const char *ptr = NULL;
 struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };

 cfg = ast_config_load(config, config_flags);
 if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
 ast_log(LOG_NOTICE, "Unable to load TDS config for CDRs: %s\n", config);
 return 0;
 } else if (cfg == CONFIG_STATUS_FILEUNCHANGED)
 return 0;

 if (!ast_variable_browse(cfg, "global")) {
 /* nothing configured */
 ast_config_destroy(cfg);
 return 0;
 }

 ast_mutex_lock(&tds_lock);

 /* Clear out any existing settings */
 ast_string_field_init(settings, 0);

 /* 'connection' is the new preferred configuration option */
 ptr = ast_variable_retrieve(cfg, "global", "connection");
 if (ptr) {
 ast_string_field_set(settings, hostname, ptr);
 } else {
 /* But we keep 'hostname' for backwards compatibility */
 ptr = ast_variable_retrieve(cfg, "global", "hostname");
 if (ptr) {
 ast_string_field_set(settings, hostname, ptr);
 } else {
 ast_log(LOG_ERROR, "Failed to connect: Database server connection not specified.\n");
 goto failed;
 }
 }

 ptr = ast_variable_retrieve(cfg, "global", "dbname");
 if (ptr) {
 ast_string_field_set(settings, database, ptr);
 } else {
 ast_log(LOG_ERROR, "Failed to connect: Database dbname not specified.\n");
 goto failed;
 }

 ptr = ast_variable_retrieve(cfg, "global", "user");
 if (ptr) {
 ast_string_field_set(settings, username, ptr);
 } else {
 ast_log(LOG_ERROR, "Failed to connect: Database dbuser not specified.\n");
 goto failed;
 }

 ptr = ast_variable_retrieve(cfg, "global", "password");
 if (ptr) {
 ast_string_field_set(settings, password, ptr);
 } else {
 ast_log(LOG_ERROR, "Failed to connect: Database password not specified.\n");
 goto failed;
 }

 ptr = ast_variable_retrieve(cfg, "global", "charset");
 if (ptr) {
 ast_string_field_set(settings, charset, ptr);
 } else {
 ast_string_field_set(settings, charset, "iso_1");
 }

 ptr = ast_variable_retrieve(cfg, "global", "language");
 if (ptr) {
 ast_string_field_set(settings, language, ptr);
 } else {
 ast_string_field_set(settings, language, "us_english");
 }

 ptr = ast_variable_retrieve(cfg, "global", "table");
 if (ptr) {
 ast_string_field_set(settings, table, ptr);
 } else {
 ast_log(LOG_NOTICE, "Table name not specified, using 'cdr' by default.\n");
 ast_string_field_set(settings, table, "cdr");
 }

 ptr = ast_variable_retrieve(cfg, "global", "hrtime");
 if (ptr && ast_true(ptr)) {
 ast_string_field_set(settings, hrtime, ptr);
 } else {
 ast_log(LOG_NOTICE, "High Resolution Time not found, using integers for billsec and duration fields by default.\n");
 }

 mssql_disconnect();

 if (mssql_connect()) {
 /* We failed to connect (mssql_connect takes care of logging it) */
 goto failed;
 }

 ast_mutex_unlock(&tds_lock);
 ast_config_destroy(cfg);

 return 1;

failed:
 ast_mutex_unlock(&tds_lock);
 ast_config_destroy(cfg);

 return 0;
}

static int reload(void)
{
 return tds_load_module(1);
}

static int load_module(void)
{
 if (dbinit() == FAIL) {
 ast_log(LOG_ERROR, "Failed to initialize FreeTDS db-lib\n");
 return AST_MODULE_LOAD_DECLINE;
 }

 dberrhandle(tds_error_handler);
 dbmsghandle(tds_message_handler);

 settings = ast_calloc_with_stringfields(1, struct cdr_tds_config, 256);

 if (!settings) {
 dbexit();
 return AST_MODULE_LOAD_DECLINE;
 }

 if (!tds_load_module(0)) {
 ast_string_field_free_memory(settings);
 ast_free(settings);
 settings = NULL;
 dbexit();
 return AST_MODULE_LOAD_DECLINE;
 }

 ast_cdr_register(name, ast_module_info->description, tds_log);

 return AST_MODULE_LOAD_SUCCESS;
}

static int unload_module(void)
{
 return tds_unload_module();
}

AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "FreeTDS CDR Backend",
 .support_level = AST_MODULE_SUPPORT_EXTENDED,
 .load = load_module,
 .unload = unload_module,
 .reload = reload,
 .load_pri = AST_MODPRI_CDR_DRIVER,
 .requires = "cdr",
);