00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033
00034
00035
00036
00037
00038
00039
00040
00041
00042
00043
00044
00045
00046
00047
00048
00049
00050
00051
00052
00053
00054
00055
00056
00057
00058
00059
00060
00061
00062
00063 #include "asterisk.h"
00064
00065 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 269006 $")
00066
00067 #include <sys/types.h>
00068 #include <stdio.h>
00069 #include <string.h>
00070 #include <stdlib.h>
00071 #include <unistd.h>
00072 #include <time.h>
00073 #include <math.h>
00074
00075 #include <tds.h>
00076 #include <tdsconvert.h>
00077 #include <ctype.h>
00078
00079 #include "asterisk/config.h"
00080 #include "asterisk/options.h"
00081 #include "asterisk/channel.h"
00082 #include "asterisk/cdr.h"
00083 #include "asterisk/module.h"
00084 #include "asterisk/logger.h"
00085
00086 #ifdef FREETDS_PRE_0_62
00087 #warning "You have older TDS, you should upgrade!"
00088 #endif
00089
00090 #define DATE_FORMAT "%Y/%m/%d %T"
00091
00092 static char *name = "mssql";
00093 static char *config = "cdr_tds.conf";
00094
00095 static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *charset = NULL, *language = NULL;
00096 static char *table = NULL;
00097
00098 static int connected = 0;
00099 static int has_userfield = 0;
00100
00101 AST_MUTEX_DEFINE_STATIC(tds_lock);
00102
00103 static TDSSOCKET *tds;
00104 static TDSLOGIN *login;
00105 static TDSCONTEXT *context;
00106
00107 static char *anti_injection(const char *, int);
00108 static void get_date(char *, size_t, struct timeval);
00109
00110 static int mssql_connect(void);
00111 static int mssql_disconnect(void);
00112
00113 static int tds_log(struct ast_cdr *cdr)
00114 {
00115 char sqlcmd[2048], start[80], answer[80], end[80];
00116 char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid, *userfield = NULL;
00117 int res = 0;
00118 int retried = 0;
00119 #ifdef FREETDS_PRE_0_62
00120 TDS_INT result_type;
00121 #endif
00122
00123 ast_mutex_lock(&tds_lock);
00124
00125 memset(sqlcmd, 0, 2048);
00126
00127 accountcode = anti_injection(cdr->accountcode, 20);
00128 src = anti_injection(cdr->src, 80);
00129 dst = anti_injection(cdr->dst, 80);
00130 dcontext = anti_injection(cdr->dcontext, 80);
00131 clid = anti_injection(cdr->clid, 80);
00132 channel = anti_injection(cdr->channel, 80);
00133 dstchannel = anti_injection(cdr->dstchannel, 80);
00134 lastapp = anti_injection(cdr->lastapp, 80);
00135 lastdata = anti_injection(cdr->lastdata, 80);
00136 uniqueid = anti_injection(cdr->uniqueid, 32);
00137
00138 if (has_userfield) {
00139 userfield = anti_injection(cdr->userfield, AST_MAX_USER_FIELD);
00140 }
00141
00142 get_date(start, sizeof(start), cdr->start);
00143 get_date(answer, sizeof(answer), cdr->answer);
00144 get_date(end, sizeof(end), cdr->end);
00145
00146 if (has_userfield) {
00147 snprintf(
00148 sqlcmd,
00149 sizeof(sqlcmd),
00150 "INSERT INTO %s "
00151 "("
00152 "accountcode, "
00153 "src, "
00154 "dst, "
00155 "dcontext, "
00156 "clid, "
00157 "channel, "
00158 "dstchannel, "
00159 "lastapp, "
00160 "lastdata, "
00161 "start, "
00162 "answer, "
00163 "[end], "
00164 "duration, "
00165 "billsec, "
00166 "disposition, "
00167 "amaflags, "
00168 "uniqueid, "
00169 "userfield"
00170 ") "
00171 "VALUES "
00172 "("
00173 "'%s', "
00174 "'%s', "
00175 "'%s', "
00176 "'%s', "
00177 "'%s', "
00178 "'%s', "
00179 "'%s', "
00180 "'%s', "
00181 "'%s', "
00182 "%s, "
00183 "%s, "
00184 "%s, "
00185 "%ld, "
00186 "%ld, "
00187 "'%s', "
00188 "'%s', "
00189 "'%s', "
00190 "'%s'"
00191 ")",
00192 table,
00193 accountcode,
00194 src,
00195 dst,
00196 dcontext,
00197 clid,
00198 channel,
00199 dstchannel,
00200 lastapp,
00201 lastdata,
00202 start,
00203 answer,
00204 end,
00205 cdr->duration,
00206 cdr->billsec,
00207 ast_cdr_disp2str(cdr->disposition),
00208 ast_cdr_flags2str(cdr->amaflags),
00209 uniqueid,
00210 userfield
00211 );
00212 } else {
00213 snprintf(
00214 sqlcmd,
00215 sizeof(sqlcmd),
00216 "INSERT INTO %s "
00217 "("
00218 "accountcode, "
00219 "src, "
00220 "dst, "
00221 "dcontext, "
00222 "clid, "
00223 "channel, "
00224 "dstchannel, "
00225 "lastapp, "
00226 "lastdata, "
00227 "start, "
00228 "answer, "
00229 "[end], "
00230 "duration, "
00231 "billsec, "
00232 "disposition, "
00233 "amaflags, "
00234 "uniqueid"
00235 ") "
00236 "VALUES "
00237 "("
00238 "'%s', "
00239 "'%s', "
00240 "'%s', "
00241 "'%s', "
00242 "'%s', "
00243 "'%s', "
00244 "'%s', "
00245 "'%s', "
00246 "'%s', "
00247 "%s, "
00248 "%s, "
00249 "%s, "
00250 "%ld, "
00251 "%ld, "
00252 "'%s', "
00253 "'%s', "
00254 "'%s'"
00255 ")",
00256 table,
00257 accountcode,
00258 src,
00259 dst,
00260 dcontext,
00261 clid,
00262 channel,
00263 dstchannel,
00264 lastapp,
00265 lastdata,
00266 start,
00267 answer,
00268 end,
00269 cdr->duration,
00270 cdr->billsec,
00271 ast_cdr_disp2str(cdr->disposition),
00272 ast_cdr_flags2str(cdr->amaflags),
00273 uniqueid
00274 );
00275 }
00276
00277 do {
00278 if (!connected) {
00279 if (mssql_connect())
00280 ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
00281 else
00282 ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
00283
00284 retried = 1;
00285 }
00286
00287 #ifdef FREETDS_PRE_0_62
00288 if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00289 #else
00290 if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00291 #endif
00292 {
00293 ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
00294
00295 mssql_disconnect();
00296 }
00297 } while (!connected && !retried);
00298
00299 free(accountcode);
00300 free(src);
00301 free(dst);
00302 free(dcontext);
00303 free(clid);
00304 free(channel);
00305 free(dstchannel);
00306 free(lastapp);
00307 free(lastdata);
00308 free(uniqueid);
00309 if (userfield) {
00310 free(userfield);
00311 }
00312
00313 ast_mutex_unlock(&tds_lock);
00314
00315 return res;
00316 }
00317
00318 static char *anti_injection(const char *str, int len)
00319 {
00320
00321
00322 char *buf;
00323 char *buf_ptr, *srh_ptr;
00324 char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
00325 int idx;
00326
00327 if ((buf = malloc(len + 1)) == NULL)
00328 {
00329 ast_log(LOG_ERROR, "cdr_tds: Out of memory error\n");
00330 return NULL;
00331 }
00332 memset(buf, 0, len);
00333
00334 buf_ptr = buf;
00335
00336
00337 for (; *str && strlen(buf) < len; str++)
00338 {
00339 if (*str == '\'')
00340 *buf_ptr++ = '\'';
00341 *buf_ptr++ = *str;
00342 }
00343 *buf_ptr = '\0';
00344
00345
00346 for (idx=0; *known_bad[idx]; idx++)
00347 {
00348 while((srh_ptr = strcasestr(buf, known_bad[idx])))
00349 {
00350 memmove(srh_ptr, srh_ptr+strlen(known_bad[idx]), strlen(srh_ptr+strlen(known_bad[idx]))+1);
00351 }
00352 }
00353
00354 return buf;
00355 }
00356
00357 static void get_date(char *dateField, size_t length, struct timeval tv)
00358 {
00359 struct tm tm;
00360 time_t t;
00361 char buf[80];
00362
00363
00364 if (!ast_tvzero(tv))
00365 {
00366 t = tv.tv_sec;
00367 ast_localtime(&t, &tm, NULL);
00368 strftime(buf, sizeof(buf), DATE_FORMAT, &tm);
00369 snprintf(dateField, length, "'%s'", buf);
00370 }
00371 else
00372 {
00373 ast_copy_string(dateField, "null", length);
00374 }
00375 }
00376
00377 static int mssql_disconnect(void)
00378 {
00379 if (tds) {
00380 tds_free_socket(tds);
00381 tds = NULL;
00382 }
00383
00384 if (context) {
00385 tds_free_context(context);
00386 context = NULL;
00387 }
00388
00389 if (login) {
00390 tds_free_login(login);
00391 login = NULL;
00392 }
00393
00394 connected = 0;
00395
00396 return 0;
00397 }
00398
00399 static int mssql_connect(void)
00400 {
00401 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00402 TDSCONNECTION *connection = NULL;
00403 #else
00404 TDSCONNECTINFO *connection = NULL;
00405 #endif
00406 char query[512];
00407
00408
00409 if (!(login = tds_alloc_login()))
00410 {
00411 ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
00412 return -1;
00413 }
00414
00415 tds_set_server(login, hostname);
00416 tds_set_user(login, dbuser);
00417 tds_set_passwd(login, password);
00418 tds_set_app(login, "TSQL");
00419 tds_set_library(login, "TDS-Library");
00420 #ifndef FREETDS_PRE_0_62
00421 tds_set_client_charset(login, charset);
00422 #endif
00423 tds_set_language(login, language);
00424 tds_set_packet(login, 512);
00425 tds_set_version(login, 7, 0);
00426
00427 #ifdef FREETDS_0_64
00428 if (!(context = tds_alloc_context(NULL)))
00429 #else
00430 if (!(context = tds_alloc_context()))
00431 #endif
00432 {
00433 ast_log(LOG_ERROR, "tds_alloc_context() failed.\n");
00434 goto connect_fail;
00435 }
00436
00437 if (!(tds = tds_alloc_socket(context, 512))) {
00438 ast_log(LOG_ERROR, "tds_alloc_socket() failed.\n");
00439 goto connect_fail;
00440 }
00441
00442 tds_set_parent(tds, NULL);
00443 connection = tds_read_config_info(tds, login, context->locale);
00444 if (!connection)
00445 {
00446 ast_log(LOG_ERROR, "tds_read_config() failed.\n");
00447 goto connect_fail;
00448 }
00449
00450 if (tds_connect(tds, connection) == TDS_FAIL)
00451 {
00452 ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
00453 tds = NULL;
00454 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00455 tds_free_connection(connection);
00456 #else
00457 tds_free_connect(connection);
00458 #endif
00459 connection = NULL;
00460 goto connect_fail;
00461 }
00462 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
00463 tds_free_connection(connection);
00464 #else
00465 tds_free_connect(connection);
00466 #endif
00467 connection = NULL;
00468
00469 snprintf(query, sizeof(query), "USE %s", dbname);
00470 #ifdef FREETDS_PRE_0_62
00471 if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00472 #else
00473 if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00474 #endif
00475 {
00476 ast_log(LOG_ERROR, "Could not change database (%s)\n", dbname);
00477 goto connect_fail;
00478 }
00479
00480 snprintf(query, sizeof(query), "SELECT 1 FROM %s WHERE 1 = 0", table);
00481 #ifdef FREETDS_PRE_0_62
00482 if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00483 #else
00484 if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00485 #endif
00486 {
00487 ast_log(LOG_ERROR, "Could not find table '%s' in database '%s'\n", table, dbname);
00488 goto connect_fail;
00489 }
00490
00491 has_userfield = 1;
00492 snprintf(query, sizeof(query), "SELECT userfield FROM %s WHERE 1 = 0", table);
00493 #ifdef FREETDS_PRE_0_62
00494 if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
00495 #else
00496 if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
00497 #endif
00498 {
00499 ast_log(LOG_NOTICE, "Unable to find 'userfield' column in table '%s'\n", table);
00500 has_userfield = 0;
00501 }
00502
00503 connected = 1;
00504 return 0;
00505
00506 connect_fail:
00507 mssql_disconnect();
00508 return -1;
00509 }
00510
00511 static int tds_unload_module(void)
00512 {
00513 mssql_disconnect();
00514
00515 ast_cdr_unregister(name);
00516
00517 if (hostname) free(hostname);
00518 if (dbname) free(dbname);
00519 if (dbuser) free(dbuser);
00520 if (password) free(password);
00521 if (charset) free(charset);
00522 if (language) free(language);
00523 if (table) free(table);
00524
00525 return 0;
00526 }
00527
00528 static int tds_load_module(void)
00529 {
00530 int res = 0;
00531 struct ast_config *cfg;
00532 struct ast_variable *var;
00533 const char *ptr = NULL;
00534 #ifdef FREETDS_PRE_0_62
00535 TDS_INT result_type;
00536 #endif
00537
00538 cfg = ast_config_load(config);
00539 if (!cfg) {
00540 ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
00541 return 0;
00542 }
00543
00544 var = ast_variable_browse(cfg, "global");
00545 if (!var) {
00546 ast_config_destroy(cfg);
00547 return 0;
00548 }
00549
00550 ptr = ast_variable_retrieve(cfg, "global", "hostname");
00551 if (ptr)
00552 hostname = strdup(ptr);
00553 else
00554 ast_log(LOG_ERROR,"Database server hostname not specified.\n");
00555
00556 ptr = ast_variable_retrieve(cfg, "global", "dbname");
00557 if (ptr)
00558 dbname = strdup(ptr);
00559 else
00560 ast_log(LOG_ERROR,"Database dbname not specified.\n");
00561
00562 ptr = ast_variable_retrieve(cfg, "global", "user");
00563 if (ptr)
00564 dbuser = strdup(ptr);
00565 else
00566 ast_log(LOG_ERROR,"Database dbuser not specified.\n");
00567
00568 ptr = ast_variable_retrieve(cfg, "global", "password");
00569 if (ptr)
00570 password = strdup(ptr);
00571 else
00572 ast_log(LOG_ERROR,"Database password not specified.\n");
00573
00574 ptr = ast_variable_retrieve(cfg, "global", "charset");
00575 if (ptr)
00576 charset = strdup(ptr);
00577 else
00578 charset = strdup("iso_1");
00579
00580 ptr = ast_variable_retrieve(cfg, "global", "language");
00581 if (ptr)
00582 language = strdup(ptr);
00583 else
00584 language = strdup("us_english");
00585
00586 ptr = ast_variable_retrieve(cfg,"global","table");
00587 if (ptr == NULL) {
00588 ast_log(LOG_DEBUG,"cdr_tds: table not specified. Assuming cdr\n");
00589 ptr = "cdr";
00590 }
00591 table = strdup(ptr);
00592
00593 ast_config_destroy(cfg);
00594
00595 mssql_connect();
00596
00597
00598 res = ast_cdr_register(name, ast_module_info->description, tds_log);
00599 if (res)
00600 {
00601 ast_log(LOG_ERROR, "Unable to register MSSQL CDR handling\n");
00602 }
00603
00604 return res;
00605 }
00606
00607 static int reload(void)
00608 {
00609 tds_unload_module();
00610 return tds_load_module();
00611 }
00612
00613 static int load_module(void)
00614 {
00615 if(!tds_load_module())
00616 return AST_MODULE_LOAD_DECLINE;
00617 else
00618 return AST_MODULE_LOAD_SUCCESS;
00619 }
00620
00621 static int unload_module(void)
00622 {
00623 return tds_unload_module();
00624 }
00625
00626 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "MSSQL CDR Backend",
00627 .load = load_module,
00628 .unload = unload_module,
00629 .reload = reload,
00630 );