Add High Resolution Times to CDRs for Asterisk
[asterisk/asterisk.git] / cdr / cdr_tds.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 2004 - 2006, Digium, Inc.
5  *
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.
11  *
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.
15  */
16
17 /*!
18  * \file
19  * \brief FreeTDS CDR logger
20  *
21  * See also
22  * \arg \ref Config_cdr
23  * \arg http://www.freetds.org/
24  * \ingroup cdr_drivers
25  */
26
27 /*!
28  * \verbatim
29  *
30  * Table Structure for `cdr`
31  *
32  * Created on: 05/20/2004 16:16
33  * Last changed on: 07/27/2004 20:01
34
35 CREATE TABLE [dbo].[cdr] (
36         [accountcode] [varchar] (20) NULL ,
37         [src] [varchar] (80) NULL ,
38         [dst] [varchar] (80) NULL ,
39         [dcontext] [varchar] (80) NULL ,
40         [clid] [varchar] (80) NULL ,
41         [channel] [varchar] (80) NULL ,
42         [dstchannel] [varchar] (80) NULL ,
43         [lastapp] [varchar] (80) NULL ,
44         [lastdata] [varchar] (80) NULL ,
45         [start] [datetime] NULL ,
46         [answer] [datetime] NULL ,
47         [end] [datetime] NULL ,
48         [duration] [int] NULL ,
49         [billsec] [int] NULL ,
50         [disposition] [varchar] (20) NULL ,
51         [amaflags] [varchar] (16) NULL ,
52         [uniqueid] [varchar] (32) NULL ,
53         [userfield] [varchar] (256) NULL
54 ) ON [PRIMARY]
55
56 \endverbatim
57
58 */
59
60 /*** MODULEINFO
61         <depend>freetds</depend>
62  ***/
63
64 #include "asterisk.h"
65
66 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
67
68 #include "asterisk/config.h"
69 #include "asterisk/channel.h"
70 #include "asterisk/cdr.h"
71 #include "asterisk/module.h"
72
73 #include <sqlfront.h>
74 #include <sybdb.h>
75
76 #define DATE_FORMAT "%Y/%m/%d %T"
77
78 static const char name[] = "FreeTDS (MSSQL)";
79 static const char config[] = "cdr_tds.conf";
80
81 struct cdr_tds_config {
82         AST_DECLARE_STRING_FIELDS(
83                 AST_STRING_FIELD(hostname);
84                 AST_STRING_FIELD(database);
85                 AST_STRING_FIELD(username);
86                 AST_STRING_FIELD(password);
87                 AST_STRING_FIELD(table);
88                 AST_STRING_FIELD(charset);
89                 AST_STRING_FIELD(language);
90                 AST_STRING_FIELD(hrtime);
91         );
92         DBPROCESS *dbproc;
93         unsigned int connected:1;
94         unsigned int has_userfield:1;
95 };
96
97 AST_MUTEX_DEFINE_STATIC(tds_lock);
98
99 static struct cdr_tds_config *settings;
100
101 static char *anti_injection(const char *, int);
102 static void get_date(char *, size_t len, struct timeval);
103
104 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
105         __attribute__((format(printf, 2, 3)));
106
107 static int mssql_connect(void);
108 static int mssql_disconnect(void);
109
110 static int tds_log(struct ast_cdr *cdr)
111 {
112         char start[80], answer[80], end[80];
113         char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid, *userfield = NULL;
114         RETCODE erc;
115         int res = -1;
116         int attempt = 1;
117
118         accountcode = anti_injection(cdr->accountcode, 20);
119         src         = anti_injection(cdr->src, 80);
120         dst         = anti_injection(cdr->dst, 80);
121         dcontext    = anti_injection(cdr->dcontext, 80);
122         clid        = anti_injection(cdr->clid, 80);
123         channel     = anti_injection(cdr->channel, 80);
124         dstchannel  = anti_injection(cdr->dstchannel, 80);
125         lastapp     = anti_injection(cdr->lastapp, 80);
126         lastdata    = anti_injection(cdr->lastdata, 80);
127         uniqueid    = anti_injection(cdr->uniqueid, 32);
128
129         get_date(start, sizeof(start), cdr->start);
130         get_date(answer, sizeof(answer), cdr->answer);
131         get_date(end, sizeof(end), cdr->end);
132
133         ast_mutex_lock(&tds_lock);
134
135         if (settings->has_userfield) {
136                 userfield = anti_injection(cdr->userfield, AST_MAX_USER_FIELD);
137         }
138
139 retry:
140         /* Ensure that we are connected */
141         if (!settings->connected) {
142                 ast_log(LOG_NOTICE, "Attempting to reconnect to %s (Attempt %d)\n", settings->hostname, attempt);
143                 if (mssql_connect()) {
144                         /* Connect failed */
145                         if (attempt++ < 3) {
146                                 goto retry;
147                         }
148                         goto done;
149                 }
150         }
151
152         if (settings->has_userfield) {
153                 if (settings->hrtime) {
154                         double hrbillsec = 0.0;
155                         double hrduration;
156
157                         if (!ast_tvzero(cdr->answer)) {
158                                 hrbillsec = (double)(ast_tvdiff_us(cdr->end, cdr->answer) / 1000000.0);
159                         }
160                         hrduration = (double)(ast_tvdiff_us(cdr->end, cdr->start) / 1000000.0);
161
162                         erc = dbfcmd(settings->dbproc,
163                                          "INSERT INTO %s "
164                                          "("
165                                          "accountcode, src, dst, dcontext, clid, channel, "
166                                          "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
167                                          "billsec, disposition, amaflags, uniqueid, userfield"
168                                          ") "
169                                          "VALUES "
170                                          "("
171                                          "'%s', '%s', '%s', '%s', '%s', '%s', "
172                                          "'%s', '%s', '%s', %s, %s, %s, %lf, "
173                                          "%lf, '%s', '%s', '%s', '%s'"
174                                          ")",
175                                          settings->table,
176                                          accountcode, src, dst, dcontext, clid, channel,
177                                          dstchannel, lastapp, lastdata, start, answer, end, hrduration,
178                                          hrbillsec, ast_cdr_disp2str(cdr->disposition), ast_cdr_flags2str(cdr->amaflags), uniqueid,
179                                          userfield
180                         );
181                 } else {
182                         erc = dbfcmd(settings->dbproc,
183                                          "INSERT INTO %s "
184                                          "("
185                                          "accountcode, src, dst, dcontext, clid, channel, "
186                                          "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
187                                          "billsec, disposition, amaflags, uniqueid, userfield"
188                                          ") "
189                                          "VALUES "
190                                          "("
191                                          "'%s', '%s', '%s', '%s', '%s', '%s', "
192                                          "'%s', '%s', '%s', %s, %s, %s, %ld, "
193                                          "%ld, '%s', '%s', '%s', '%s'"
194                                          ")",
195                                          settings->table,
196                                          accountcode, src, dst, dcontext, clid, channel,
197                                          dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
198                                          cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_cdr_flags2str(cdr->amaflags), uniqueid,
199                                          userfield
200                         );
201                 }
202         } else {
203                 if (settings->hrtime) {
204                         double hrbillsec = 0.0;
205                         double hrduration;
206
207                         if (!ast_tvzero(cdr->answer)) {
208                                 hrbillsec = (double)(ast_tvdiff_us(cdr->end, cdr->answer) / 1000000.0);
209                         }
210                         hrduration = (double)(ast_tvdiff_us(cdr->end, cdr->start) / 1000000.0);
211
212                         erc = dbfcmd(settings->dbproc,
213                                          "INSERT INTO %s "
214                                          "("
215                                          "accountcode, src, dst, dcontext, clid, channel, "
216                                          "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
217                                          "billsec, disposition, amaflags, uniqueid"
218                                          ") "
219                                          "VALUES "
220                                          "("
221                                          "'%s', '%s', '%s', '%s', '%s', '%s', "
222                                          "'%s', '%s', '%s', %s, %s, %s, %lf, "
223                                          "%lf, '%s', '%s', '%s'"
224                                          ")",
225                                          settings->table,
226                                          accountcode, src, dst, dcontext, clid, channel,
227                                          dstchannel, lastapp, lastdata, start, answer, end, hrduration,
228                                          hrbillsec, ast_cdr_disp2str(cdr->disposition), ast_cdr_flags2str(cdr->amaflags), uniqueid
229                         );
230                 } else {
231                         erc = dbfcmd(settings->dbproc,
232                                          "INSERT INTO %s "
233                                          "("
234                                          "accountcode, src, dst, dcontext, clid, channel, "
235                                          "dstchannel, lastapp, lastdata, start, answer, [end], duration, "
236                                          "billsec, disposition, amaflags, uniqueid"
237                                          ") "
238                                          "VALUES "
239                                          "("
240                                          "'%s', '%s', '%s', '%s', '%s', '%s', "
241                                          "'%s', '%s', '%s', %s, %s, %s, %ld, "
242                                          "%ld, '%s', '%s', '%s'"
243                                          ")",
244                                          settings->table,
245                                          accountcode, src, dst, dcontext, clid, channel,
246                                          dstchannel, lastapp, lastdata, start, answer, end, cdr->duration,
247                                          cdr->billsec, ast_cdr_disp2str(cdr->disposition), ast_cdr_flags2str(cdr->amaflags), uniqueid
248                         );
249                 }
250         }
251
252         if (erc == FAIL) {
253                 if (attempt++ < 3) {
254                         ast_log(LOG_NOTICE, "Failed to build INSERT statement, retrying...\n");
255                         mssql_disconnect();
256                         goto retry;
257                 } else {
258                         ast_log(LOG_ERROR, "Failed to build INSERT statement, no CDR was logged.\n");
259                         goto done;
260                 }
261         }
262
263         if (dbsqlexec(settings->dbproc) == FAIL) {
264                 if (attempt++ < 3) {
265                         ast_log(LOG_NOTICE, "Failed to execute INSERT statement, retrying...\n");
266                         mssql_disconnect();
267                         goto retry;
268                 } else {
269                         ast_log(LOG_ERROR, "Failed to execute INSERT statement, no CDR was logged.\n");
270                         goto done;
271                 }
272         }
273
274         /* Consume any results we might get back (this is more of a sanity check than
275          * anything else, since an INSERT shouldn't return results). */
276         while (dbresults(settings->dbproc) != NO_MORE_RESULTS) {
277                 while (dbnextrow(settings->dbproc) != NO_MORE_ROWS);
278         }
279
280         res = 0;
281
282 done:
283         ast_mutex_unlock(&tds_lock);
284
285         ast_free(accountcode);
286         ast_free(src);
287         ast_free(dst);
288         ast_free(dcontext);
289         ast_free(clid);
290         ast_free(channel);
291         ast_free(dstchannel);
292         ast_free(lastapp);
293         ast_free(lastdata);
294         ast_free(uniqueid);
295
296         if (userfield) {
297                 ast_free(userfield);
298         }
299
300         return res;
301 }
302
303 static char *anti_injection(const char *str, int len)
304 {
305         /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
306         char *buf;
307         char *buf_ptr, *srh_ptr;
308         char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
309         int idx;
310
311         if (!(buf = ast_calloc(1, len + 1))) {
312                 ast_log(LOG_ERROR, "Out of memory\n");
313                 return NULL;
314         }
315
316         buf_ptr = buf;
317
318         /* Escape single quotes */
319         for (; *str && strlen(buf) < len; str++) {
320                 if (*str == '\'') {
321                         *buf_ptr++ = '\'';
322                 }
323                 *buf_ptr++ = *str;
324         }
325         *buf_ptr = '\0';
326
327         /* Erase known bad input */
328         for (idx = 0; *known_bad[idx]; idx++) {
329                 while ((srh_ptr = strcasestr(buf, known_bad[idx]))) {
330                         memmove(srh_ptr, srh_ptr + strlen(known_bad[idx]), strlen(srh_ptr + strlen(known_bad[idx])) + 1);
331                 }
332         }
333
334         return buf;
335 }
336
337 static void get_date(char *dateField, size_t len, struct timeval when)
338 {
339         /* To make sure we have date variable if not insert null to SQL */
340         if (!ast_tvzero(when)) {
341                 struct ast_tm tm;
342                 ast_localtime(&when, &tm, NULL);
343                 ast_strftime(dateField, len, "'" DATE_FORMAT "'", &tm);
344         } else {
345                 ast_copy_string(dateField, "null", len);
346         }
347 }
348
349 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
350 {
351         va_list ap;
352         char *buffer;
353
354         va_start(ap, fmt);
355         if (ast_vasprintf(&buffer, fmt, ap) < 0) {
356                 va_end(ap);
357                 return 1;
358         }
359         va_end(ap);
360
361         if (dbfcmd(dbproc, buffer) == FAIL) {
362                 free(buffer);
363                 return 1;
364         }
365
366         free(buffer);
367
368         if (dbsqlexec(dbproc) == FAIL) {
369                 return 1;
370         }
371
372         /* Consume the result set (we don't really care about the result, though) */
373         while (dbresults(dbproc) != NO_MORE_RESULTS) {
374                 while (dbnextrow(dbproc) != NO_MORE_ROWS);
375         }
376
377         return 0;
378 }
379
380 static int mssql_disconnect(void)
381 {
382         if (settings->dbproc) {
383                 dbclose(settings->dbproc);
384                 settings->dbproc = NULL;
385         }
386
387         settings->connected = 0;
388
389         return 0;
390 }
391
392 static int mssql_connect(void)
393 {
394         LOGINREC *login;
395
396         if ((login = dblogin()) == NULL) {
397                 ast_log(LOG_ERROR, "Unable to allocate login structure for db-lib\n");
398                 return -1;
399         }
400
401         DBSETLAPP(login,     "TSQL");
402         DBSETLUSER(login,    (char *) settings->username);
403         DBSETLPWD(login,     (char *) settings->password);
404         DBSETLCHARSET(login, (char *) settings->charset);
405         DBSETLNATLANG(login, (char *) settings->language);
406
407         if ((settings->dbproc = dbopen(login, (char *) settings->hostname)) == NULL) {
408                 ast_log(LOG_ERROR, "Unable to connect to %s\n", settings->hostname);
409                 dbloginfree(login);
410                 return -1;
411         }
412
413         dbloginfree(login);
414
415         if (dbuse(settings->dbproc, (char *) settings->database) == FAIL) {
416                 ast_log(LOG_ERROR, "Unable to select database %s\n", settings->database);
417                 goto failed;
418         }
419
420         if (execute_and_consume(settings->dbproc, "SELECT 1 FROM [%s] WHERE 1 = 0", settings->table)) {
421                 ast_log(LOG_ERROR, "Unable to find table '%s'\n", settings->table);
422                 goto failed;
423         }
424
425         /* Check to see if we have a userfield column in the table */
426         if (execute_and_consume(settings->dbproc, "SELECT userfield FROM [%s] WHERE 1 = 0", settings->table)) {
427                 ast_log(LOG_NOTICE, "Unable to find 'userfield' column in table '%s'\n", settings->table);
428                 settings->has_userfield = 0;
429         } else {
430                 settings->has_userfield = 1;
431         }
432
433         settings->connected = 1;
434
435         return 0;
436
437 failed:
438         dbclose(settings->dbproc);
439         settings->dbproc = NULL;
440         return -1;
441 }
442
443 static int tds_unload_module(void)
444 {
445         if (settings) {
446                 ast_mutex_lock(&tds_lock);
447                 mssql_disconnect();
448                 ast_mutex_unlock(&tds_lock);
449
450                 ast_string_field_free_memory(settings);
451                 ast_free(settings);
452         }
453
454         ast_cdr_unregister(name);
455
456         dbexit();
457
458         return 0;
459 }
460
461 static int tds_error_handler(DBPROCESS *dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
462 {
463         ast_log(LOG_ERROR, "%s (%d)\n", dberrstr, dberr);
464
465         if (oserr != DBNOERR) {
466                 ast_log(LOG_ERROR, "%s (%d)\n", oserrstr, oserr);
467         }
468
469         return INT_CANCEL;
470 }
471
472 static int tds_message_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
473 {
474         ast_debug(1, "Msg %d, Level %d, State %d, Line %d\n", msgno, severity, msgstate, line);
475         ast_log(LOG_NOTICE, "%s\n", msgtext);
476
477         return 0;
478 }
479
480 static int tds_load_module(int reload)
481 {
482         struct ast_config *cfg;
483         const char *ptr = NULL;
484         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
485
486         cfg = ast_config_load(config, config_flags);
487         if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
488                 ast_log(LOG_NOTICE, "Unable to load TDS config for CDRs: %s\n", config);
489                 return 0;
490         } else if (cfg == CONFIG_STATUS_FILEUNCHANGED)
491                 return 0;
492
493         if (!ast_variable_browse(cfg, "global")) {
494                 /* nothing configured */
495                 ast_config_destroy(cfg);
496                 return 0;
497         }
498
499         ast_mutex_lock(&tds_lock);
500
501         /* Clear out any existing settings */
502         ast_string_field_init(settings, 0);
503
504         /* 'connection' is the new preferred configuration option */
505         ptr = ast_variable_retrieve(cfg, "global", "connection");
506         if (ptr) {
507                 ast_string_field_set(settings, hostname, ptr);
508         } else {
509                 /* But we keep 'hostname' for backwards compatibility */
510                 ptr = ast_variable_retrieve(cfg, "global", "hostname");
511                 if (ptr) {
512                         ast_string_field_set(settings, hostname, ptr);
513                 } else {
514                         ast_log(LOG_ERROR, "Failed to connect: Database server connection not specified.\n");
515                         goto failed;
516                 }
517         }
518
519         ptr = ast_variable_retrieve(cfg, "global", "dbname");
520         if (ptr) {
521                 ast_string_field_set(settings, database, ptr);
522         } else {
523                 ast_log(LOG_ERROR, "Failed to connect: Database dbname not specified.\n");
524                 goto failed;
525         }
526
527         ptr = ast_variable_retrieve(cfg, "global", "user");
528         if (ptr) {
529                 ast_string_field_set(settings, username, ptr);
530         } else {
531                 ast_log(LOG_ERROR, "Failed to connect: Database dbuser not specified.\n");
532                 goto failed;
533         }
534
535         ptr = ast_variable_retrieve(cfg, "global", "password");
536         if (ptr) {
537                 ast_string_field_set(settings, password, ptr);
538         } else {
539                 ast_log(LOG_ERROR, "Failed to connect: Database password not specified.\n");
540                 goto failed;
541         }
542
543         ptr = ast_variable_retrieve(cfg, "global", "charset");
544         if (ptr) {
545                 ast_string_field_set(settings, charset, ptr);
546         } else {
547                 ast_string_field_set(settings, charset, "iso_1");
548         }
549
550         ptr = ast_variable_retrieve(cfg, "global", "language");
551         if (ptr) {
552                 ast_string_field_set(settings, language, ptr);
553         } else {
554                 ast_string_field_set(settings, language, "us_english");
555         }
556
557         ptr = ast_variable_retrieve(cfg, "global", "table");
558         if (ptr) {
559                 ast_string_field_set(settings, table, ptr);
560         } else {
561                 ast_log(LOG_NOTICE, "Table name not specified, using 'cdr' by default.\n");
562                 ast_string_field_set(settings, table, "cdr");
563         }
564
565         ptr = ast_variable_retrieve(cfg, "global", "hrtime");
566         if (ptr && ast_true(ptr)) {
567                 ast_string_field_set(settings, hrtime, ptr);
568         } else {
569                 ast_log(LOG_NOTICE, "High Resolution Time not found, using integers for billsec and duration fields by default.\n");
570         }
571
572         mssql_disconnect();
573
574         if (mssql_connect()) {
575                 /* We failed to connect (mssql_connect takes care of logging it) */
576                 goto failed;
577         }
578
579         ast_mutex_unlock(&tds_lock);
580         ast_config_destroy(cfg);
581
582         return 1;
583
584 failed:
585         ast_mutex_unlock(&tds_lock);
586         ast_config_destroy(cfg);
587
588         return 0;
589 }
590
591 static int reload(void)
592 {
593         return tds_load_module(1);
594 }
595
596 static int load_module(void)
597 {
598         if (dbinit() == FAIL) {
599                 ast_log(LOG_ERROR, "Failed to initialize FreeTDS db-lib\n");
600                 return AST_MODULE_LOAD_DECLINE;
601         }
602
603         dberrhandle(tds_error_handler);
604         dbmsghandle(tds_message_handler);
605
606         settings = ast_calloc_with_stringfields(1, struct cdr_tds_config, 256);
607
608         if (!settings) {
609                 dbexit();
610                 return AST_MODULE_LOAD_DECLINE;
611         }
612
613         if (!tds_load_module(0)) {
614                 ast_string_field_free_memory(settings);
615                 ast_free(settings);
616                 settings = NULL;
617                 dbexit();
618                 return AST_MODULE_LOAD_DECLINE;
619         }
620
621         ast_cdr_register(name, ast_module_info->description, tds_log);
622
623         return AST_MODULE_LOAD_SUCCESS;
624 }
625
626 static int unload_module(void)
627 {
628         return tds_unload_module();
629 }
630
631 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "FreeTDS CDR Backend",
632                 .load = load_module,
633                 .unload = unload_module,
634                 .reload = reload,
635                );