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