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