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