Merge the new Channel Event Logging (CEL) subsystem.
[asterisk/asterisk.git] / cel / cel_tds.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 2008, 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 CEL 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
32 CREATE TABLE [dbo].[cdr] (
33         [accountcode] [varchar] (20) NULL ,
34         [cidname] [varchar] (80) NULL ,
35         [cidnum] [varchar] (80) NULL ,
36         [cidani] [varchar] (80) NULL ,
37         [cidrdnis] [varchar] (80) NULL ,
38         [ciddnid] [varchar] (80) NULL ,
39         [exten] [varchar] (80) NULL ,
40         [context] [varchar] (80) NULL ,
41         [channame] [varchar] (80) NULL ,
42         [appname] [varchar] (80) NULL ,
43         [appdata] [varchar] (80) NULL ,
44         [eventtime] [datetime] NULL ,
45         [eventtype] [varchar] (32) NULL ,
46         [uniqueid] [varchar] (32) NULL ,
47         [linkedid] [varchar] (32) NULL ,
48         [amaflags] [varchar] (16) NULL ,
49         [userfield] [varchar] (32) NULL ,
50         [peer] [varchar] (32) NULL
51 ) ON [PRIMARY]
52
53 \endverbatim
54
55 */
56
57 /*** MODULEINFO
58         <depend>freetds</depend>
59  ***/
60
61 #include "asterisk.h"
62
63 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
64
65 #include <time.h>
66 #include <math.h>
67
68 #include "asterisk/config.h"
69 #include "asterisk/channel.h"
70 #include "asterisk/cel.h"
71 #include "asterisk/module.h"
72 #include "asterisk/logger.h"
73
74 #include <sqlfront.h>
75 #include <sybdb.h>
76
77 #ifdef FREETDS_PRE_0_62
78 #warning "You have older TDS, you should upgrade!"
79 #endif
80
81 #define DATE_FORMAT "%Y/%m/%d %T"
82
83 static char *config = "cel_tds.conf";
84
85 static struct ast_event_sub *event_sub = NULL;
86
87 struct cel_tds_config {
88         AST_DECLARE_STRING_FIELDS(
89                 AST_STRING_FIELD(connection);
90                 AST_STRING_FIELD(database);
91                 AST_STRING_FIELD(username);
92                 AST_STRING_FIELD(password);
93                 AST_STRING_FIELD(table);
94                 AST_STRING_FIELD(charset);
95                 AST_STRING_FIELD(language);
96         );
97         DBPROCESS *dbproc;
98         unsigned int connected:1;
99 };
100
101 AST_MUTEX_DEFINE_STATIC(tds_lock);
102
103 static struct cel_tds_config *settings;
104
105 static char *anti_injection(const char *, int);
106 static void get_date(char *, size_t len, struct timeval);
107
108 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
109         __attribute__((format(printf, 2, 3)));
110
111 static int mssql_connect(void);
112 static int mssql_disconnect(void);
113
114 static void tds_log(const struct ast_event *event, void *userdata)
115 {
116         char start[80];
117         char *accountcode_ai, *clidnum_ai, *exten_ai, *context_ai, *clid_ai, *channel_ai, *app_ai, *appdata_ai, *uniqueid_ai, *linkedid_ai, *cidani_ai, *cidrdnis_ai, *ciddnid_ai, *peer_ai, *userfield_ai;
118         RETCODE erc;
119         int attempt = 1;
120         struct ast_cel_event_record record = {
121                 .version = AST_CEL_EVENT_RECORD_VERSION,
122         };
123
124         if (ast_cel_fill_record(event, &record)) {
125                 return;
126         }
127
128         ast_mutex_lock(&tds_lock);
129
130         accountcode_ai = anti_injection(record.account_code, 20);
131         clidnum_ai     = anti_injection(record.caller_id_num, 80);
132         clid_ai        = anti_injection(record.caller_id_name, 80);
133         cidani_ai      = anti_injection(record.caller_id_ani, 80);
134         cidrdnis_ai    = anti_injection(record.caller_id_rdnis, 80);
135         ciddnid_ai     = anti_injection(record.caller_id_dnid, 80);
136         exten_ai       = anti_injection(record.extension, 80);
137         context_ai     = anti_injection(record.context, 80);
138         channel_ai     = anti_injection(record.channel_name, 80);
139         app_ai         = anti_injection(record.application_name, 80);
140         appdata_ai     = anti_injection(record.application_data, 80);
141         uniqueid_ai    = anti_injection(record.unique_id, 32);
142         linkedid_ai    = anti_injection(record.linked_id, 32);
143         userfield_ai   = anti_injection(record.user_field, 32);
144         peer_ai        = anti_injection(record.peer, 32);
145
146         get_date(start, sizeof(start), record.event_time);
147
148 retry:
149         /* Ensure that we are connected */
150         if (!settings->connected) {
151                 ast_log(LOG_NOTICE, "Attempting to reconnect to %s (Attempt %d)\n", settings->connection, attempt);
152                 if (mssql_connect()) {
153                         /* Connect failed */
154                         if (attempt++ < 3) {
155                                 goto retry;
156                         }
157                         goto done;
158                 }
159         }
160
161         erc = dbfcmd(settings->dbproc,
162                 "INSERT INTO %s "
163                 "("
164                 "accountcode,"
165                 "cidnum,"
166                 "cidname,"
167                 "cidani,"
168                 "cidrdnis,"
169                 "ciddnid,"
170                 "exten,"
171                 "context,"
172                 "channel,"
173                 "appname,"
174                 "appdata,"
175                 "eventtime,"
176                 "eventtype,"
177                 "amaflags, "
178                 "uniqueid,"
179                 "linkedid,"
180                 "userfield,"
181                 "peer"
182                 ") "
183                 "VALUES "
184                 "("
185                 "'%s'," /* accountcode */
186                 "'%s'," /* clidnum */
187                 "'%s'," /* clid */
188                 "'%s'," /* cid-ani */
189                 "'%s'," /* cid-rdnis */
190                 "'%s'," /* cid-dnid */
191                 "'%s'," /* exten */
192                 "'%s'," /* context */
193                 "'%s'," /* channel */
194                 "'%s'," /* app */
195                 "'%s'," /* appdata */
196                 "%s, "  /* eventtime */
197                 "'%s'," /* eventtype */
198                 "'%s'," /* amaflags */
199                 "'%s'," /* uniqueid */
200                 "'%s'," /* linkedid */
201                 "'%s'," /* userfield */
202                 "'%s'"  /* peer */
203                 ")",
204                 settings->table, accountcode_ai, clidnum_ai, clid_ai, cidani_ai, cidrdnis_ai,
205                 ciddnid_ai, exten_ai, context_ai, channel_ai, app_ai, appdata_ai, start,
206                 record.event_name, ast_cel_get_ama_flag_name(record.amaflag), uniqueid_ai, linkedid_ai,
207                 userfield_ai, peer_ai);
208
209         if (erc == FAIL) {
210                 if (attempt++ < 3) {
211                         ast_log(LOG_NOTICE, "Failed to build INSERT statement, retrying...\n");
212                         mssql_disconnect();
213                         goto retry;
214                 } else {
215                         ast_log(LOG_ERROR, "Failed to build INSERT statement, no CEL was logged.\n");
216                         goto done;
217                 }
218         }
219
220         if (dbsqlexec(settings->dbproc) == FAIL) {
221                 if (attempt++ < 3) {
222                         ast_log(LOG_NOTICE, "Failed to execute INSERT statement, retrying...\n");
223                         mssql_disconnect();
224                         goto retry;
225                 } else {
226                         ast_log(LOG_ERROR, "Failed to execute INSERT statement, no CEL was logged.\n");
227                         goto done;
228                 }
229         }
230
231         /* Consume any results we might get back (this is more of a sanity check than
232          * anything else, since an INSERT shouldn't return results). */
233         while (dbresults(settings->dbproc) != NO_MORE_RESULTS) {
234                 while (dbnextrow(settings->dbproc) != NO_MORE_ROWS);
235         }
236
237 done:
238         ast_mutex_unlock(&tds_lock);
239
240         free(accountcode_ai);
241         free(clidnum_ai);
242         free(clid_ai);
243         free(cidani_ai);
244         free(cidrdnis_ai);
245         free(ciddnid_ai);
246         free(exten_ai);
247         free(context_ai);
248         free(channel_ai);
249         free(app_ai);
250         free(appdata_ai);
251         free(uniqueid_ai);
252         free(linkedid_ai);
253         free(userfield_ai);
254         free(peer_ai);
255
256         return;
257 }
258
259 static char *anti_injection(const char *str, int len)
260 {
261         /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
262         char *buf;
263         char *buf_ptr, *srh_ptr;
264         char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
265         int idx;
266
267         if (!(buf = ast_calloc(1, len + 1))) {
268                 ast_log(LOG_ERROR, "Out of memory\n");
269                 return NULL;
270         }
271
272         buf_ptr = buf;
273
274         /* Escape single quotes */
275         for (; *str && strlen(buf) < len; str++) {
276                 if (*str == '\'') {
277                         *buf_ptr++ = '\'';
278                 }
279                 *buf_ptr++ = *str;
280         }
281         *buf_ptr = '\0';
282
283         /* Erase known bad input */
284         for (idx = 0; *known_bad[idx]; idx++) {
285                 while ((srh_ptr = strcasestr(buf, known_bad[idx]))) {
286                         memmove(srh_ptr, srh_ptr + strlen(known_bad[idx]), strlen(srh_ptr + strlen(known_bad[idx])) + 1);
287                 }
288         }
289         return buf;
290 }
291
292 static void get_date(char *dateField, size_t len, struct timeval when)
293 {
294         /* To make sure we have date variable if not insert null to SQL */
295         if (!ast_tvzero(when)) {
296                 struct ast_tm tm;
297                 ast_localtime(&when, &tm, NULL);
298                 ast_strftime(dateField, len, "'" DATE_FORMAT "'", &tm);
299         } else {
300                 ast_copy_string(dateField, "null", len);
301         }
302 }
303
304 static int execute_and_consume(DBPROCESS *dbproc, const char *fmt, ...)
305 {
306         va_list ap;
307         char *buffer;
308
309         va_start(ap, fmt);
310         if (ast_vasprintf(&buffer, fmt, ap) < 0) {
311                 va_end(ap);
312                 return 1;
313         }
314         va_end(ap);
315
316         if (dbfcmd(dbproc, buffer) == FAIL) {
317                 free(buffer);
318                 return 1;
319         }
320
321         free(buffer);
322
323         if (dbsqlexec(dbproc) == FAIL) {
324                 return 1;
325         }
326
327         /* Consume the result set (we don't really care about the result, though) */
328         while (dbresults(dbproc) != NO_MORE_RESULTS) {
329                 while (dbnextrow(dbproc) != NO_MORE_ROWS);
330         }
331
332         return 0;
333 }
334
335 static int mssql_disconnect(void)
336 {
337         if (settings->dbproc) {
338                 dbclose(settings->dbproc);
339                 settings->dbproc = NULL;
340         }
341         settings->connected = 0;
342
343         return 0;
344 }
345
346 static int mssql_connect(void)
347 {
348         LOGINREC *login;
349
350         if ((login = dblogin()) == NULL) {
351                 ast_log(LOG_ERROR, "Unable to allocate login structure for db-lib\n");
352                 return -1;
353         }
354
355         DBSETLAPP(login,  "TSQL");
356         DBSETLUSER(login, (char *) settings->username);
357         DBSETLPWD(login,  (char *) settings->password);
358
359         if (!ast_strlen_zero(settings->charset)) {
360                 DBSETLCHARSET(login, (char *) settings->charset);
361         }
362
363         if (!ast_strlen_zero(settings->language)) {
364                 DBSETLNATLANG(login, (char *) settings->language);
365         }
366
367         if ((settings->dbproc = dbopen(login, (char *) settings->connection)) == NULL) {
368                 ast_log(LOG_ERROR, "Unable to connect to %s\n", settings->connection);
369                 dbloginfree(login);
370                 return -1;
371         }
372
373         dbloginfree(login);
374
375         if (dbuse(settings->dbproc, (char *) settings->database) == FAIL) {
376                 ast_log(LOG_ERROR, "Unable to select database %s\n", settings->database);
377                 goto failed;
378         }
379
380         if (execute_and_consume(settings->dbproc, "SELECT 1 FROM [%s]", settings->table)) {
381                 ast_log(LOG_ERROR, "Unable to find table '%s'\n", settings->table);
382                 goto failed;
383         }
384
385         settings->connected = 1;
386
387         return 0;
388
389 failed:
390         dbclose(settings->dbproc);
391         settings->dbproc = NULL;
392         return -1;
393 }
394
395 static int tds_unload_module(void)
396 {
397         if (event_sub) {
398                 event_sub = ast_event_unsubscribe(event_sub);
399         }
400
401         if (settings) {
402                 ast_mutex_lock(&tds_lock);
403                 mssql_disconnect();
404                 ast_mutex_unlock(&tds_lock);
405
406                 ast_string_field_free_memory(settings);
407                 ast_free(settings);
408         }
409
410         dbexit();
411
412         return 0;
413 }
414
415 static int tds_error_handler(DBPROCESS *dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
416 {
417         ast_log(LOG_ERROR, "%s (%d)\n", dberrstr, dberr);
418
419         if (oserr != DBNOERR) {
420                 ast_log(LOG_ERROR, "%s (%d)\n", oserrstr, oserr);
421         }
422
423         return INT_CANCEL;
424 }
425
426 static int tds_message_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
427 {
428         ast_debug(1, "Msg %d, Level %d, State %d, Line %d\n", msgno, severity, msgstate, line);
429         ast_log(LOG_NOTICE, "%s\n", msgtext);
430
431         return 0;
432 }
433
434 static int tds_load_module(int reload)
435 {
436         struct ast_config *cfg;
437         const char *ptr = NULL;
438         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
439
440         cfg = ast_config_load(config, config_flags);
441         if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
442                 ast_log(LOG_NOTICE, "Unable to load TDS config for CELs: %s\n", config);
443                 return 0;
444         } else if (cfg == CONFIG_STATUS_FILEUNCHANGED) {
445                 return 0;
446         }
447
448         if (!ast_variable_browse(cfg, "global")) {
449                 /* nothing configured */
450                 ast_config_destroy(cfg);
451                 ast_log(LOG_NOTICE, "cel_tds has no global category, nothing to configure.\n");
452                 return 0;
453         }
454
455         ast_mutex_lock(&tds_lock);
456
457         /* Clear out any existing settings */
458         ast_string_field_init(settings, 0);
459
460         ptr = ast_variable_retrieve(cfg, "global", "connection");
461         if (ptr) {
462                 ast_string_field_set(settings, connection, ptr);
463         } else {
464                 ast_log(LOG_ERROR, "Failed to connect: Database connection name not specified.\n");
465                 goto failed;
466         }
467
468         ptr = ast_variable_retrieve(cfg, "global", "dbname");
469         if (ptr) {
470                 ast_string_field_set(settings, database, ptr);
471         } else {
472                 ast_log(LOG_ERROR, "Failed to connect: Database dbname not specified.\n");
473                 goto failed;
474         }
475
476         ptr = ast_variable_retrieve(cfg, "global", "user");
477         if (ptr) {
478                 ast_string_field_set(settings, username, ptr);
479         } else {
480                 ast_log(LOG_ERROR, "Failed to connect: Database dbuser not specified.\n");
481                 goto failed;
482         }
483
484         ptr = ast_variable_retrieve(cfg, "global", "password");
485         if (ptr) {
486                 ast_string_field_set(settings, password, ptr);
487         } else {
488                 ast_log(LOG_ERROR, "Failed to connect: Database password not specified.\n");
489                 goto failed;
490         }
491
492         ptr = ast_variable_retrieve(cfg, "global", "charset");
493         if (ptr) {
494                 ast_string_field_set(settings, charset, ptr);
495         }
496
497         ptr = ast_variable_retrieve(cfg, "global", "language");
498         if (ptr) {
499                 ast_string_field_set(settings, language, ptr);
500         }
501
502         ptr = ast_variable_retrieve(cfg, "global", "table");
503         if (ptr) {
504                 ast_string_field_set(settings, table, ptr);
505         } else {
506                 ast_log(LOG_NOTICE, "Table name not specified, using 'cel' by default.\n");
507                 ast_string_field_set(settings, table, "cel");
508         }
509
510         mssql_disconnect();
511
512         if (mssql_connect()) {
513                 /* We failed to connect (mssql_connect takes care of logging it) */
514                 goto failed;
515         }
516
517         ast_mutex_unlock(&tds_lock);
518         ast_config_destroy(cfg);
519
520         return 1;
521
522 failed:
523         ast_mutex_unlock(&tds_lock);
524         ast_config_destroy(cfg);
525
526         return 0;
527 }
528
529 static int reload(void)
530 {
531         return tds_load_module(1);
532 }
533
534 static int load_module(void)
535 {
536         if (dbinit() == FAIL) {
537                 ast_log(LOG_ERROR, "Failed to initialize FreeTDS db-lib\n");
538                 return AST_MODULE_LOAD_DECLINE;
539         }
540
541         dberrhandle(tds_error_handler);
542         dbmsghandle(tds_message_handler);
543
544         settings = ast_calloc(1, sizeof(*settings));
545
546         if (!settings || ast_string_field_init(settings, 256)) {
547                 if (settings) {
548                         ast_free(settings);
549                         settings = NULL;
550                 }
551                 dbexit();
552                 return AST_MODULE_LOAD_DECLINE;
553         }
554
555         if (!tds_load_module(0)) {
556                 ast_string_field_free_memory(settings);
557                 ast_free(settings);
558                 settings = NULL;
559                 dbexit();
560                 ast_log(LOG_WARNING,"cel_tds module had config problems; declining load\n");
561                 return AST_MODULE_LOAD_DECLINE;
562         }
563
564         /* Register MSSQL CEL handler */
565         event_sub = ast_event_subscribe(AST_EVENT_CEL, tds_log, "CEL TDS logging backend", NULL, AST_EVENT_IE_END);
566         if (!event_sub) {
567                 ast_log(LOG_ERROR, "Unable to register MSSQL CEL handling\n");
568                 ast_string_field_free_memory(settings);
569                 ast_free(settings);
570                 settings = NULL;
571                 dbexit();
572                 return AST_MODULE_LOAD_DECLINE;
573         }
574
575         return AST_MODULE_LOAD_SUCCESS;
576 }
577
578 static int unload_module(void)
579 {
580         return tds_unload_module();
581 }
582
583 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "FreeTDS CEL Backend",
584         .load = load_module,
585         .unload = unload_module,
586         .reload = reload,
587 );