(closes issue #12846)
[asterisk-bristuff.git] / cdr / cdr_tds.c
blob2536fae079ecebe9771560b18b5a1025e34ca91e
1 /*
2 * Asterisk -- An open source telephony toolkit.
4 * Copyright (C) 2004 - 2006, Digium, Inc.
6 * See http://www.asterisk.org for more information about
7 * the Asterisk project. Please do not directly contact
8 * any of the maintainers of this project for assistance;
9 * the project provides a web site, mailing lists and IRC
10 * channels for your use.
12 * This program is free software, distributed under the terms of
13 * the GNU General Public License Version 2. See the LICENSE file
14 * at the top of the source tree.
17 /*! \file
19 * \brief FreeTDS CDR logger
21 * See also
22 * \arg \ref Config_cdr
23 * \arg http://www.freetds.org/
24 * \ingroup cdr_drivers
27 /*! \verbatim
29 * Table Structure for `cdr`
31 * Created on: 05/20/2004 16:16
32 * Last changed on: 07/27/2004 20:01
34 CREATE TABLE [dbo].[cdr] (
35 [accountcode] [varchar] (20) NULL ,
36 [src] [varchar] (80) NULL ,
37 [dst] [varchar] (80) NULL ,
38 [dcontext] [varchar] (80) NULL ,
39 [clid] [varchar] (80) NULL ,
40 [channel] [varchar] (80) NULL ,
41 [dstchannel] [varchar] (80) NULL ,
42 [lastapp] [varchar] (80) NULL ,
43 [lastdata] [varchar] (80) NULL ,
44 [start] [datetime] NULL ,
45 [answer] [datetime] NULL ,
46 [end] [datetime] NULL ,
47 [duration] [int] NULL ,
48 [billsec] [int] NULL ,
49 [disposition] [varchar] (20) NULL ,
50 [amaflags] [varchar] (16) NULL ,
51 [uniqueid] [varchar] (32) NULL
52 ) ON [PRIMARY]
54 \endverbatim
58 /*** MODULEINFO
59 <depend>freetds</depend>
60 ***/
62 #include "asterisk.h"
64 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
66 #include <sys/types.h>
67 #include <stdio.h>
68 #include <string.h>
69 #include <stdlib.h>
70 #include <unistd.h>
71 #include <time.h>
72 #include <math.h>
74 #include <tds.h>
75 #include <tdsconvert.h>
76 #include <ctype.h>
78 #include "asterisk/config.h"
79 #include "asterisk/options.h"
80 #include "asterisk/channel.h"
81 #include "asterisk/cdr.h"
82 #include "asterisk/module.h"
83 #include "asterisk/logger.h"
85 #ifdef FREETDS_PRE_0_62
86 #warning "You have older TDS, you should upgrade!"
87 #endif
89 #define DATE_FORMAT "%Y/%m/%d %T"
91 static char *name = "mssql";
92 static char *config = "cdr_tds.conf";
94 static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *charset = NULL, *language = NULL;
95 static char *table = NULL;
97 static int connected = 0;
99 AST_MUTEX_DEFINE_STATIC(tds_lock);
101 static TDSSOCKET *tds;
102 static TDSLOGIN *login;
103 static TDSCONTEXT *context;
105 static char *anti_injection(const char *, int);
106 static void get_date(char *, struct timeval);
108 static int mssql_connect(void);
109 static int mssql_disconnect(void);
111 static int tds_log(struct ast_cdr *cdr)
113 char sqlcmd[2048], start[80], answer[80], end[80];
114 char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid;
115 int res = 0;
116 int retried = 0;
117 #ifdef FREETDS_PRE_0_62
118 TDS_INT result_type;
119 #endif
121 ast_mutex_lock(&tds_lock);
123 memset(sqlcmd, 0, 2048);
125 accountcode = anti_injection(cdr->accountcode, 20);
126 src = anti_injection(cdr->src, 80);
127 dst = anti_injection(cdr->dst, 80);
128 dcontext = anti_injection(cdr->dcontext, 80);
129 clid = anti_injection(cdr->clid, 80);
130 channel = anti_injection(cdr->channel, 80);
131 dstchannel = anti_injection(cdr->dstchannel, 80);
132 lastapp = anti_injection(cdr->lastapp, 80);
133 lastdata = anti_injection(cdr->lastdata, 80);
134 uniqueid = anti_injection(cdr->uniqueid, 32);
136 get_date(start, cdr->start);
137 get_date(answer, cdr->answer);
138 get_date(end, cdr->end);
140 sprintf(
141 sqlcmd,
142 "INSERT INTO %s "
144 "accountcode, "
145 "src, "
146 "dst, "
147 "dcontext, "
148 "clid, "
149 "channel, "
150 "dstchannel, "
151 "lastapp, "
152 "lastdata, "
153 "start, "
154 "answer, "
155 "[end], "
156 "duration, "
157 "billsec, "
158 "disposition, "
159 "amaflags, "
160 "uniqueid"
161 ") "
162 "VALUES "
164 "'%s', " /* accountcode */
165 "'%s', " /* src */
166 "'%s', " /* dst */
167 "'%s', " /* dcontext */
168 "'%s', " /* clid */
169 "'%s', " /* channel */
170 "'%s', " /* dstchannel */
171 "'%s', " /* lastapp */
172 "'%s', " /* lastdata */
173 "%s, " /* start */
174 "%s, " /* answer */
175 "%s, " /* end */
176 "%ld, " /* duration */
177 "%ld, " /* billsec */
178 "'%s', " /* disposition */
179 "'%s', " /* amaflags */
180 "'%s'" /* uniqueid */
181 ")",
182 table,
183 accountcode,
184 src,
185 dst,
186 dcontext,
187 clid,
188 channel,
189 dstchannel,
190 lastapp,
191 lastdata,
192 start,
193 answer,
194 end,
195 cdr->duration,
196 cdr->billsec,
197 ast_cdr_disp2str(cdr->disposition),
198 ast_cdr_flags2str(cdr->amaflags),
199 uniqueid
202 do {
203 if (!connected) {
204 if (mssql_connect())
205 ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
206 else
207 ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
209 retried = 1; /* note that we have now tried */
212 #ifdef FREETDS_PRE_0_62
213 if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
214 #else
215 if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
216 #endif
218 ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
220 mssql_disconnect(); /* this is ok even if we are already disconnected */
222 } while (!connected && !retried);
224 free(accountcode);
225 free(src);
226 free(dst);
227 free(dcontext);
228 free(clid);
229 free(channel);
230 free(dstchannel);
231 free(lastapp);
232 free(lastdata);
233 free(uniqueid);
235 ast_mutex_unlock(&tds_lock);
237 return res;
240 static char *anti_injection(const char *str, int len)
242 /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
244 char *buf;
245 char *buf_ptr, *srh_ptr;
246 char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
247 int idx;
249 if ((buf = malloc(len + 1)) == NULL)
251 ast_log(LOG_ERROR, "cdr_tds: Out of memory error\n");
252 return NULL;
254 memset(buf, 0, len);
256 buf_ptr = buf;
258 /* Escape single quotes */
259 for (; *str && strlen(buf) < len; str++)
261 if (*str == '\'')
262 *buf_ptr++ = '\'';
263 *buf_ptr++ = *str;
265 *buf_ptr = '\0';
267 /* Erase known bad input */
268 for (idx=0; *known_bad[idx]; idx++)
270 while((srh_ptr = strcasestr(buf, known_bad[idx])))
272 memmove(srh_ptr, srh_ptr+strlen(known_bad[idx]), strlen(srh_ptr+strlen(known_bad[idx]))+1);
276 return buf;
279 static void get_date(char *dateField, struct timeval tv)
281 struct tm tm;
282 time_t t;
283 char buf[80];
285 /* To make sure we have date variable if not insert null to SQL */
286 if (!ast_tvzero(tv))
288 t = tv.tv_sec;
289 ast_localtime(&t, &tm, NULL);
290 strftime(buf, 80, DATE_FORMAT, &tm);
291 sprintf(dateField, "'%s'", buf);
293 else
295 strcpy(dateField, "null");
299 static int mssql_disconnect(void)
301 if (tds) {
302 tds_free_socket(tds);
303 tds = NULL;
306 if (context) {
307 tds_free_context(context);
308 context = NULL;
311 if (login) {
312 tds_free_login(login);
313 login = NULL;
316 connected = 0;
318 return 0;
321 static int mssql_connect(void)
323 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
324 TDSCONNECTION *connection = NULL;
325 #else
326 TDSCONNECTINFO *connection = NULL;
327 #endif
328 char query[128];
330 /* Connect to M$SQL Server */
331 if (!(login = tds_alloc_login()))
333 ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
334 return -1;
337 tds_set_server(login, hostname);
338 tds_set_user(login, dbuser);
339 tds_set_passwd(login, password);
340 tds_set_app(login, "TSQL");
341 tds_set_library(login, "TDS-Library");
342 #ifndef FREETDS_PRE_0_62
343 tds_set_client_charset(login, charset);
344 #endif
345 tds_set_language(login, language);
346 tds_set_packet(login, 512);
347 tds_set_version(login, 7, 0);
349 #ifdef FREETDS_0_64
350 if (!(context = tds_alloc_context(NULL)))
351 #else
352 if (!(context = tds_alloc_context()))
353 #endif
355 ast_log(LOG_ERROR, "tds_alloc_context() failed.\n");
356 goto connect_fail;
359 if (!(tds = tds_alloc_socket(context, 512))) {
360 ast_log(LOG_ERROR, "tds_alloc_socket() failed.\n");
361 goto connect_fail;
364 tds_set_parent(tds, NULL);
365 connection = tds_read_config_info(tds, login, context->locale);
366 if (!connection)
368 ast_log(LOG_ERROR, "tds_read_config() failed.\n");
369 goto connect_fail;
372 if (tds_connect(tds, connection) == TDS_FAIL)
374 ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
375 tds = NULL; /* freed by tds_connect() on error */
376 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
377 tds_free_connection(connection);
378 #else
379 tds_free_connect(connection);
380 #endif
381 connection = NULL;
382 goto connect_fail;
384 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
385 tds_free_connection(connection);
386 #else
387 tds_free_connect(connection);
388 #endif
389 connection = NULL;
391 sprintf(query, "USE %s", dbname);
392 #ifdef FREETDS_PRE_0_62
393 if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
394 #else
395 if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
396 #endif
398 ast_log(LOG_ERROR, "Could not change database (%s)\n", dbname);
399 goto connect_fail;
402 connected = 1;
403 return 0;
405 connect_fail:
406 mssql_disconnect();
407 return -1;
410 static int tds_unload_module(void)
412 mssql_disconnect();
414 ast_cdr_unregister(name);
416 if (hostname) free(hostname);
417 if (dbname) free(dbname);
418 if (dbuser) free(dbuser);
419 if (password) free(password);
420 if (charset) free(charset);
421 if (language) free(language);
422 if (table) free(table);
424 return 0;
427 static int tds_load_module(void)
429 int res = 0;
430 struct ast_config *cfg;
431 struct ast_variable *var;
432 const char *ptr = NULL;
433 #ifdef FREETDS_PRE_0_62
434 TDS_INT result_type;
435 #endif
437 cfg = ast_config_load(config);
438 if (!cfg) {
439 ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
440 return 0;
443 var = ast_variable_browse(cfg, "global");
444 if (!var) /* nothing configured */ {
445 ast_config_destroy(cfg);
446 return 0;
449 ptr = ast_variable_retrieve(cfg, "global", "hostname");
450 if (ptr)
451 hostname = strdup(ptr);
452 else
453 ast_log(LOG_ERROR,"Database server hostname not specified.\n");
455 ptr = ast_variable_retrieve(cfg, "global", "dbname");
456 if (ptr)
457 dbname = strdup(ptr);
458 else
459 ast_log(LOG_ERROR,"Database dbname not specified.\n");
461 ptr = ast_variable_retrieve(cfg, "global", "user");
462 if (ptr)
463 dbuser = strdup(ptr);
464 else
465 ast_log(LOG_ERROR,"Database dbuser not specified.\n");
467 ptr = ast_variable_retrieve(cfg, "global", "password");
468 if (ptr)
469 password = strdup(ptr);
470 else
471 ast_log(LOG_ERROR,"Database password not specified.\n");
473 ptr = ast_variable_retrieve(cfg, "global", "charset");
474 if (ptr)
475 charset = strdup(ptr);
476 else
477 charset = strdup("iso_1");
479 ptr = ast_variable_retrieve(cfg, "global", "language");
480 if (ptr)
481 language = strdup(ptr);
482 else
483 language = strdup("us_english");
485 ptr = ast_variable_retrieve(cfg,"global","table");
486 if (ptr == NULL) {
487 ast_log(LOG_DEBUG,"cdr_tds: table not specified. Assuming cdr\n");
488 ptr = "cdr";
490 table = strdup(ptr);
492 ast_config_destroy(cfg);
494 mssql_connect();
496 /* Register MSSQL CDR handler */
497 res = ast_cdr_register(name, ast_module_info->description, tds_log);
498 if (res)
500 ast_log(LOG_ERROR, "Unable to register MSSQL CDR handling\n");
503 return res;
506 static int reload(void)
508 tds_unload_module();
509 return tds_load_module();
512 static int load_module(void)
514 if(!tds_load_module())
515 return AST_MODULE_LOAD_DECLINE;
516 else
517 return AST_MODULE_LOAD_SUCCESS;
520 static int unload_module(void)
522 return tds_unload_module();
525 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "MSSQL CDR Backend",
526 .load = load_module,
527 .unload = unload_module,
528 .reload = reload,