allows the table field to be configurable for
[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 static char *table = NULL;
93
94 static int connected = 0;
95
96 AST_MUTEX_DEFINE_STATIC(tds_lock);
97
98 static TDSSOCKET *tds;
99 static TDSLOGIN *login;
100 static TDSCONTEXT *context;
101
102 static char *anti_injection(const char *, int);
103 static void get_date(char *, struct timeval);
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 sqlcmd[2048], start[80], answer[80], end[80];
111         char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid;
112         int res = 0;
113         int retried = 0;
114 #ifdef FREETDS_PRE_0_62
115         TDS_INT result_type;
116 #endif
117
118         ast_mutex_lock(&tds_lock);
119
120         memset(sqlcmd, 0, 2048);
121
122         accountcode = anti_injection(cdr->accountcode, 20);
123         src = anti_injection(cdr->src, 80);
124         dst = anti_injection(cdr->dst, 80);
125         dcontext = anti_injection(cdr->dcontext, 80);
126         clid = anti_injection(cdr->clid, 80);
127         channel = anti_injection(cdr->channel, 80);
128         dstchannel = anti_injection(cdr->dstchannel, 80);
129         lastapp = anti_injection(cdr->lastapp, 80);
130         lastdata = anti_injection(cdr->lastdata, 80);
131         uniqueid = anti_injection(cdr->uniqueid, 32);
132
133         get_date(start, cdr->start);
134         get_date(answer, cdr->answer);
135         get_date(end, cdr->end);
136
137         sprintf(
138                 sqlcmd,
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                 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         do {
200                 if (!connected) {
201                         if (mssql_connect())
202                                 ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
203                         else
204                                 ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
205
206                         retried = 1;    /* note that we have now tried */
207                 }
208
209 #ifdef FREETDS_PRE_0_62
210                 if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
211 #else
212                 if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
213 #endif
214                 {
215                         ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
216
217                         mssql_disconnect();     /* this is ok even if we are already disconnected */
218                 }
219         } while (!connected && !retried);
220
221         free(accountcode);
222         free(src);
223         free(dst);
224         free(dcontext);
225         free(clid);
226         free(channel);
227         free(dstchannel);
228         free(lastapp);
229         free(lastdata);
230         free(uniqueid);
231
232         ast_mutex_unlock(&tds_lock);
233
234         return res;
235 }
236
237 static char *anti_injection(const char *str, int len)
238 {
239         /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
240
241         char *buf;
242         char *buf_ptr, *srh_ptr;
243         char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
244         int idx;
245
246         if ((buf = malloc(len + 1)) == NULL)
247         {
248                 ast_log(LOG_ERROR, "cdr_tds:  Out of memory error\n");
249                 return NULL;
250         }
251         memset(buf, 0, len);
252
253         buf_ptr = buf;
254
255         /* Escape single quotes */
256         for (; *str && strlen(buf) < len; str++)
257         {
258                 if (*str == '\'')
259                         *buf_ptr++ = '\'';
260                 *buf_ptr++ = *str;
261         }
262         *buf_ptr = '\0';
263
264         /* Erase known bad input */
265         for (idx=0; *known_bad[idx]; idx++)
266         {
267                 while((srh_ptr = strcasestr(buf, known_bad[idx])))
268                 {
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, struct timeval tv)
277 {
278         struct tm tm;
279         time_t t;
280         char buf[80];
281
282         /* To make sure we have date variable if not insert null to SQL */
283         if (!ast_tvzero(tv))
284         {
285                 t = tv.tv_sec;
286                 localtime_r(&t, &tm);
287                 strftime(buf, 80, DATE_FORMAT, &tm);
288                 sprintf(dateField, "'%s'", buf);
289         }
290         else
291         {
292                 strcpy(dateField, "null");
293         }
294 }
295
296 char *description(void)
297 {
298         return desc;
299 }
300
301 static int mssql_disconnect(void)
302 {
303         if (tds) {
304                 tds_free_socket(tds);
305                 tds = NULL;
306         }
307
308         if (context) {
309                 tds_free_context(context);
310                 context = NULL;
311         }
312
313         if (login) {
314                 tds_free_login(login);
315                 login = NULL;
316         }
317
318         connected = 0;
319
320         return 0;
321 }
322
323 static int mssql_connect(void)
324 {
325 #ifdef FREETDS_0_63
326         TDSCONNECTION *connection = NULL;
327 #else
328         TDSCONNECTINFO *connection = NULL;
329 #endif
330         char query[128];
331
332         /* Connect to M$SQL Server */
333         if (!(login = tds_alloc_login()))
334         {
335                 ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
336                 return -1;
337         }
338         
339         tds_set_server(login, hostname);
340         tds_set_user(login, dbuser);
341         tds_set_passwd(login, password);
342         tds_set_app(login, "TSQL");
343         tds_set_library(login, "TDS-Library");
344 #ifndef FREETDS_PRE_0_62
345         tds_set_client_charset(login, charset);
346 #endif
347         tds_set_language(login, language);
348         tds_set_packet(login, 512);
349         tds_set_version(login, 7, 0);
350
351         if (!(context = tds_alloc_context()))
352         {
353                 ast_log(LOG_ERROR, "tds_alloc_context() failed.\n");
354                 goto connect_fail;
355         }
356
357         if (!(tds = tds_alloc_socket(context, 512))) {
358                 ast_log(LOG_ERROR, "tds_alloc_socket() failed.\n");
359                 goto connect_fail;
360         }
361
362         tds_set_parent(tds, NULL);
363         connection = tds_read_config_info(tds, login, context->locale);
364         if (!connection)
365         {
366                 ast_log(LOG_ERROR, "tds_read_config() failed.\n");
367                 goto connect_fail;
368         }
369
370         if (tds_connect(tds, connection) == TDS_FAIL)
371         {
372                 ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
373                 tds = NULL;     /* freed by tds_connect() on error */
374 #ifdef FREETDS_0_63
375                 tds_free_connection(connection);
376 #else
377                 tds_free_connect(connection);
378 #endif
379                 connection = NULL;
380                 goto connect_fail;
381         }
382 #ifdef FREETDS_0_63
383         tds_free_connection(connection);
384 #else
385         tds_free_connect(connection);
386 #endif
387         connection = NULL;
388
389         sprintf(query, "USE %s", dbname);
390 #ifdef FREETDS_PRE_0_62
391         if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
392 #else
393         if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
394 #endif
395         {
396                 ast_log(LOG_ERROR, "Could not change database (%s)\n", dbname);
397                 goto connect_fail;
398         }
399
400         connected = 1;
401         return 0;
402
403 connect_fail:
404         mssql_disconnect();
405         return -1;
406 }
407
408 static int tds_unload_module(void)
409 {
410         mssql_disconnect();
411
412         ast_cdr_unregister(name);
413
414         if (hostname) free(hostname);
415         if (dbname) free(dbname);
416         if (dbuser) free(dbuser);
417         if (password) free(password);
418         if (charset) free(charset);
419         if (language) free(language);
420         if (table) free(table);
421
422         return 0;
423 }
424
425 static int tds_load_module(void)
426 {
427         int res = 0;
428         struct ast_config *cfg;
429         struct ast_variable *var;
430         char *ptr = NULL;
431 #ifdef FREETDS_PRE_0_62
432         TDS_INT result_type;
433 #endif
434
435         cfg = ast_config_load(config);
436         if (!cfg) {
437                 ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
438                 return 0;
439         }
440
441         var = ast_variable_browse(cfg, "global");
442         if (!var) /* nothing configured */
443                 return 0;
444
445         ptr = ast_variable_retrieve(cfg, "global", "hostname");
446         if (ptr)
447                 hostname = strdup(ptr);
448         else
449                 ast_log(LOG_ERROR,"Database server hostname not specified.\n");
450
451         ptr = ast_variable_retrieve(cfg, "global", "dbname");
452         if (ptr)
453                 dbname = strdup(ptr);
454         else
455                 ast_log(LOG_ERROR,"Database dbname not specified.\n");
456
457         ptr = ast_variable_retrieve(cfg, "global", "user");
458         if (ptr)
459                 dbuser = strdup(ptr);
460         else
461                 ast_log(LOG_ERROR,"Database dbuser not specified.\n");
462
463         ptr = ast_variable_retrieve(cfg, "global", "password");
464         if (ptr)
465                 password = strdup(ptr);
466         else
467                 ast_log(LOG_ERROR,"Database password not specified.\n");
468
469         ptr = ast_variable_retrieve(cfg, "global", "charset");
470         if (ptr)
471                 charset = strdup(ptr);
472         else
473                 charset = strdup("iso_1");
474
475         ptr = ast_variable_retrieve(cfg, "global", "language");
476         if (ptr)
477                 language = strdup(ptr);
478         else
479                 language = strdup("us_english");
480
481         ptr = ast_variable_retrieve(cfg,"global","table");
482         if (ptr == NULL) {
483                 ast_log(LOG_DEBUG,"cdr_tds: table not specified.  Assuming cdr\n");
484                 ptr = "cdr";
485         }
486         table = strdup(ptr);
487
488         ast_config_destroy(cfg);
489
490         mssql_connect();
491
492         /* Register MSSQL CDR handler */
493         res = ast_cdr_register(name, desc, tds_log);
494         if (res)
495         {
496                 ast_log(LOG_ERROR, "Unable to register MSSQL CDR handling\n");
497         }
498
499         return res;
500 }
501
502 int reload(void)
503 {
504         tds_unload_module();
505         return tds_load_module();
506 }
507
508 int load_module(void)
509 {
510         return tds_load_module();
511 }
512
513 int unload_module(void)
514 {
515         return tds_unload_module();
516 }
517
518 int usecount(void)
519 {
520         /* Simplistic use count */
521         if (ast_mutex_trylock(&tds_lock)) {
522                 return 1;
523         } else {
524                 ast_mutex_unlock(&tds_lock);
525                 return 0;
526         }
527 }
528
529 char *key()
530 {
531         return ASTERISK_GPL_KEY;
532 }