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