6ac5c09c592dc0ed3ef6f118e52f07efc80160f3
[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 <tds.h>
70 #include <tdsconvert.h>
71 #include <ctype.h>
72
73 #include "asterisk/config.h"
74 #include "asterisk/channel.h"
75 #include "asterisk/cdr.h"
76 #include "asterisk/module.h"
77
78 #ifdef FREETDS_PRE_0_62
79 #warning "You have older TDS, you should upgrade!"
80 #endif
81
82 #define DATE_FORMAT "%Y/%m/%d %T"
83
84 static char *name = "mssql";
85 static char *config = "cdr_tds.conf";
86
87 struct cdr_tds_config {
88         AST_DECLARE_STRING_FIELDS(
89                 AST_STRING_FIELD(hostname);
90                 AST_STRING_FIELD(dbname);
91                 AST_STRING_FIELD(dbuser);
92                 AST_STRING_FIELD(password);
93                 AST_STRING_FIELD(table);
94                 AST_STRING_FIELD(charset);
95                 AST_STRING_FIELD(language);
96         );
97         TDSSOCKET *tds;
98         TDSLOGIN *login;
99         TDSCONTEXT *context;
100         unsigned int connected:1;
101         ast_mutex_t lock;
102 };
103
104 static struct cdr_tds_config *settings;
105
106 static char *anti_injection(const char *, int);
107 static void get_date(char *, struct timeval);
108
109 static int mssql_connect(void);
110 static int mssql_disconnect(void);
111
112 static int tds_log(struct ast_cdr *cdr)
113 {
114         char sqlcmd[2048], start[80], answer[80], end[80];
115         char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid;
116         int res = 0;
117         int retried = 0;
118 #ifdef FREETDS_PRE_0_62
119         TDS_INT result_type;
120 #endif
121
122         ast_mutex_lock(&settings->lock);
123
124         memset(sqlcmd, 0, 2048);
125
126         accountcode = anti_injection(cdr->accountcode, 20);
127         src = anti_injection(cdr->src, 80);
128         dst = anti_injection(cdr->dst, 80);
129         dcontext = anti_injection(cdr->dcontext, 80);
130         clid = anti_injection(cdr->clid, 80);
131         channel = anti_injection(cdr->channel, 80);
132         dstchannel = anti_injection(cdr->dstchannel, 80);
133         lastapp = anti_injection(cdr->lastapp, 80);
134         lastdata = anti_injection(cdr->lastdata, 80);
135         uniqueid = anti_injection(cdr->uniqueid, 32);
136
137         get_date(start, cdr->start);
138         get_date(answer, cdr->answer);
139         get_date(end, cdr->end);
140
141         sprintf(
142                 sqlcmd,
143                 "INSERT INTO %s "
144                 "("
145                         "accountcode, "
146                         "src, "
147                         "dst, "
148                         "dcontext, "
149                         "clid, "
150                         "channel, "
151                         "dstchannel, "
152                         "lastapp, "
153                         "lastdata, "
154                         "start, "
155                         "answer, "
156                         "[end], "
157                         "duration, "
158                         "billsec, "
159                         "disposition, "
160                         "amaflags, "
161                         "uniqueid"
162                 ") "
163                 "VALUES "
164                 "("
165                         "'%s', "        /* accountcode */
166                         "'%s', "        /* src */
167                         "'%s', "        /* dst */
168                         "'%s', "        /* dcontext */
169                         "'%s', "        /* clid */
170                         "'%s', "        /* channel */
171                         "'%s', "        /* dstchannel */
172                         "'%s', "        /* lastapp */
173                         "'%s', "        /* lastdata */
174                         "%s, "          /* start */
175                         "%s, "          /* answer */
176                         "%s, "          /* end */
177                         "%ld, "         /* duration */
178                         "%ld, "         /* billsec */
179                         "'%s', "        /* disposition */
180                         "'%s', "        /* amaflags */
181                         "'%s'"          /* uniqueid */
182                 ")",
183                 settings->table,
184                 accountcode,
185                 src,
186                 dst,
187                 dcontext,
188                 clid,
189                 channel,
190                 dstchannel,
191                 lastapp,
192                 lastdata,
193                 start,
194                 answer,
195                 end,
196                 cdr->duration,
197                 cdr->billsec,
198                 ast_cdr_disp2str(cdr->disposition),
199                 ast_cdr_flags2str(cdr->amaflags),
200                 uniqueid
201         );
202
203         do {
204                 if (!settings->connected) {
205                         if (mssql_connect())
206                                 ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
207                         else
208                                 ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
209
210                         retried = 1;    /* note that we have now tried */
211                 }
212
213 #ifdef FREETDS_PRE_0_62
214                 if (!settings->connected || (tds_submit_query(settings->tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(settings->tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
215 #else
216                 if (!settings->connected || (tds_submit_query(settings->tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(settings->tds) != TDS_SUCCEED))
217 #endif
218                 {
219                         ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
220
221                         mssql_disconnect();     /* this is ok even if we are already disconnected */
222                 }
223         } while (!settings->connected && !retried);
224
225         ast_free(accountcode);
226         ast_free(src);
227         ast_free(dst);
228         ast_free(dcontext);
229         ast_free(clid);
230         ast_free(channel);
231         ast_free(dstchannel);
232         ast_free(lastapp);
233         ast_free(lastdata);
234         ast_free(uniqueid);
235
236         ast_mutex_unlock(&settings->lock);
237
238         return res;
239 }
240
241 static char *anti_injection(const char *str, int len)
242 {
243         /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
244         char *buf;
245         char *buf_ptr, *srh_ptr;
246         char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
247         int idx;
248
249         if (!(buf = ast_calloc(1, len + 1))) {
250                 ast_log(LOG_ERROR, "Out of memory\n");
251                 return NULL;
252         }
253
254         buf_ptr = buf;
255
256         /* Escape single quotes */
257         for (; *str && strlen(buf) < len; str++) {
258                 if (*str == '\'') {
259                         *buf_ptr++ = '\'';
260                 }
261                 *buf_ptr++ = *str;
262         }
263         *buf_ptr = '\0';
264
265         /* Erase known bad input */
266         for (idx = 0; *known_bad[idx]; idx++) {
267                 while ((srh_ptr = strcasestr(buf, known_bad[idx]))) {
268                         memmove(srh_ptr, srh_ptr + strlen(known_bad[idx]), strlen(srh_ptr + strlen(known_bad[idx])) + 1);
269                 }
270         }
271
272         return buf;
273 }
274
275 static void get_date(char *dateField, struct timeval tv)
276 {
277         struct ast_tm tm;
278         char buf[80];
279
280         /* To make sure we have date variable if not insert null to SQL */
281         if (!ast_tvzero(tv)) {
282                 ast_localtime(&tv, &tm, NULL);
283                 ast_strftime(buf, 80, DATE_FORMAT, &tm);
284                 sprintf(dateField, "'%s'", buf);
285         } else {
286                 strcpy(dateField, "null");
287         }
288 }
289
290 static int mssql_disconnect(void)
291 {
292         if (settings->tds) {
293                 tds_free_socket(settings->tds);
294                 settings->tds = NULL;
295         }
296
297         if (settings->context) {
298                 tds_free_context(settings->context);
299                 settings->context = NULL;
300         }
301
302         if (settings->login) {
303                 tds_free_login(settings->login);
304                 settings->login = NULL;
305         }
306
307         settings->connected = 0;
308
309         return 0;
310 }
311
312 static int mssql_connect(void)
313 {
314 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
315         TDSCONNECTION *connection = NULL;
316 #else
317         TDSCONNECTINFO *connection = NULL;
318 #endif
319         char query[128];
320
321         /* Connect to M$SQL Server */
322         if (!(settings->login = tds_alloc_login())) {
323                 ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
324                 return -1;
325         }
326
327         tds_set_server(settings->login, settings->hostname);
328         tds_set_user(settings->login, settings->dbuser);
329         tds_set_passwd(settings->login, settings->password);
330         tds_set_app(settings->login, "TSQL");
331         tds_set_library(settings->login, "TDS-Library");
332 #ifndef FREETDS_PRE_0_62
333         tds_set_client_charset(settings->login, settings->charset);
334 #endif
335         tds_set_language(settings->login, settings->language);
336         tds_set_packet(settings->login, 512);
337         tds_set_version(settings->login, 7, 0);
338
339 #ifdef FREETDS_0_64
340         if (!(settings->context = tds_alloc_context(NULL)))
341 #else
342         if (!(settings->context = tds_alloc_context()))
343 #endif
344         {
345                 ast_log(LOG_ERROR, "tds_alloc_context() failed.\n");
346                 goto connect_fail;
347         }
348
349         if (!(settings->tds = tds_alloc_socket(settings->context, 512))) {
350                 ast_log(LOG_ERROR, "tds_alloc_socket() failed.\n");
351                 goto connect_fail;
352         }
353
354         tds_set_parent(settings->tds, NULL);
355         connection = tds_read_config_info(settings->tds, settings->login, settings->context->locale);
356         if (!connection) {
357                 ast_log(LOG_ERROR, "tds_read_config() failed.\n");
358                 goto connect_fail;
359         }
360
361         if (tds_connect(settings->tds, connection) == TDS_FAIL) {
362                 ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
363                 settings->tds = NULL;   /* freed by tds_connect() on error */
364 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
365                 tds_free_connection(connection);
366 #else
367                 tds_free_connect(connection);
368 #endif
369                 connection = NULL;
370                 goto connect_fail;
371         }
372 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
373         tds_free_connection(connection);
374 #else
375         tds_free_connect(connection);
376 #endif
377         connection = NULL;
378
379         sprintf(query, "USE %s", settings->dbname);
380 #ifdef FREETDS_PRE_0_62
381         if ((tds_submit_query(settings->tds, query) != TDS_SUCCEED) || (tds_process_simple_query(settings->tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
382 #else
383         if ((tds_submit_query(settings->tds, query) != TDS_SUCCEED) || (tds_process_simple_query(settings->tds) != TDS_SUCCEED))
384 #endif
385         {
386                 ast_log(LOG_ERROR, "Could not change database (%s)\n", settings->dbname);
387                 goto connect_fail;
388         }
389
390         settings->connected = 1;
391         return 0;
392
393 connect_fail:
394         mssql_disconnect();
395         return -1;
396 }
397
398 static int tds_unload_module(void)
399 {
400         mssql_disconnect();
401
402         ast_cdr_unregister(name);
403
404         ast_mutex_destroy(&settings->lock);
405         ast_string_field_free_memory(settings);
406         ast_free(settings);
407
408         return 0;
409 }
410
411 static int tds_load_module(int reload)
412 {
413         struct ast_config *cfg;
414         struct ast_variable *var;
415         const char *ptr = NULL;
416         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
417
418         cfg = ast_config_load(config, config_flags);
419         if (!cfg) {
420                 ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
421                 return 0;
422         } else if (cfg == CONFIG_STATUS_FILEUNCHANGED)
423                 return 0;
424
425         var = ast_variable_browse(cfg, "global");
426         if (!var) /* nothing configured */ {
427                 ast_config_destroy(cfg);
428                 return 0;
429         }
430
431         if (reload) {
432                 ast_string_field_init(settings, 0);
433         } else {
434                 settings = ast_calloc(1, sizeof(*settings));
435
436                 if (!settings || ast_string_field_init(settings, 256)) {
437                         if (settings) {
438                                 ast_free(settings);
439                                 settings = NULL;
440                         }
441                         ast_config_destroy(cfg);
442                         return 0;
443                 }
444         }
445
446         ast_mutex_init(&settings->lock);
447
448         ptr = ast_variable_retrieve(cfg, "global", "hostname");
449         if (ptr) {
450                 ast_string_field_set(settings, hostname, ptr);
451         } else {
452                 ast_log(LOG_ERROR, "Database server hostname not specified.\n");
453         }
454
455         ptr = ast_variable_retrieve(cfg, "global", "dbname");
456         if (ptr) {
457                 ast_string_field_set(settings, dbname, ptr);
458         } else {
459                 ast_log(LOG_ERROR, "Database dbname not specified.\n");
460         }
461
462         ptr = ast_variable_retrieve(cfg, "global", "user");
463         if (ptr) {
464                 ast_string_field_set(settings, dbuser, ptr);
465         } else {
466                 ast_log(LOG_ERROR, "Database dbuser not specified.\n");
467         }
468
469         ptr = ast_variable_retrieve(cfg, "global", "password");
470         if (ptr) {
471                 ast_string_field_set(settings, password, ptr);
472         } else {
473                 ast_log(LOG_ERROR,"Database password not specified.\n");
474         }
475
476         ptr = ast_variable_retrieve(cfg, "global", "charset");
477         if (ptr) {
478                 ast_string_field_set(settings, charset, ptr);
479         } else {
480                 ast_string_field_set(settings, charset, "iso_1");
481         }
482
483         ptr = ast_variable_retrieve(cfg, "global", "language");
484         if (ptr) {
485                 ast_string_field_set(settings, language, ptr);
486         } else {
487                 ast_string_field_set(settings, language, "us_english");
488         }
489
490         ptr = ast_variable_retrieve(cfg, "global", "table");
491         if (ptr) {
492                 ast_string_field_set(settings, table, ptr);
493         } else {        
494                 ast_debug(1, "Table not specified.  Assuming 'cdr'\n");
495                 ast_string_field_set(settings, table, "cdr");
496         }
497
498         ast_config_destroy(cfg);
499
500         ast_mutex_lock(&settings->lock);
501         mssql_disconnect();
502         mssql_connect();
503         ast_mutex_unlock(&settings->lock);
504
505         return 1;
506 }
507
508 static int reload(void)
509 {
510         return tds_load_module(1);
511 }
512
513 static int load_module(void)
514 {
515         if (!tds_load_module(0))
516                 return AST_MODULE_LOAD_DECLINE;
517         ast_cdr_register(name, ast_module_info->description, tds_log);
518         return AST_MODULE_LOAD_SUCCESS;
519 }
520
521 static int unload_module(void)
522 {
523         return tds_unload_module();
524 }
525
526 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "MSSQL CDR Backend",
527                 .load = load_module,
528                 .unload = unload_module,
529                 .reload = reload,
530                );