Merge in ast_strftime branch, which changes timestamps to be accurate to the microsec...
[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 <sys/types.h>
67 #include <stdio.h>
68 #include <string.h>
69 #include <stdlib.h>
70 #include <unistd.h>
71 #include <time.h>
72 #include <math.h>
73
74 #include <tds.h>
75 #include <tdsconvert.h>
76 #include <ctype.h>
77
78 #include "asterisk/config.h"
79 #include "asterisk/options.h"
80 #include "asterisk/channel.h"
81 #include "asterisk/cdr.h"
82 #include "asterisk/module.h"
83 #include "asterisk/logger.h"
84
85 #ifdef FREETDS_PRE_0_62
86 #warning "You have older TDS, you should upgrade!"
87 #endif
88
89 #define DATE_FORMAT "%Y/%m/%d %T"
90
91 static char *name = "mssql";
92 static char *config = "cdr_tds.conf";
93
94 static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *charset = NULL, *language = NULL;
95 static char *table = NULL;
96
97 static int connected = 0;
98
99 AST_MUTEX_DEFINE_STATIC(tds_lock);
100
101 static TDSSOCKET *tds;
102 static TDSLOGIN *login;
103 static TDSCONTEXT *context;
104
105 static char *anti_injection(const char *, int);
106 static void get_date(char *, struct timeval);
107
108 static int mssql_connect(void);
109 static int mssql_disconnect(void);
110
111 static int tds_log(struct ast_cdr *cdr)
112 {
113         char sqlcmd[2048], start[80], answer[80], end[80];
114         char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid;
115         int res = 0;
116         int retried = 0;
117 #ifdef FREETDS_PRE_0_62
118         TDS_INT result_type;
119 #endif
120
121         ast_mutex_lock(&tds_lock);
122
123         memset(sqlcmd, 0, 2048);
124
125         accountcode = anti_injection(cdr->accountcode, 20);
126         src = anti_injection(cdr->src, 80);
127         dst = anti_injection(cdr->dst, 80);
128         dcontext = anti_injection(cdr->dcontext, 80);
129         clid = anti_injection(cdr->clid, 80);
130         channel = anti_injection(cdr->channel, 80);
131         dstchannel = anti_injection(cdr->dstchannel, 80);
132         lastapp = anti_injection(cdr->lastapp, 80);
133         lastdata = anti_injection(cdr->lastdata, 80);
134         uniqueid = anti_injection(cdr->uniqueid, 32);
135
136         get_date(start, cdr->start);
137         get_date(answer, cdr->answer);
138         get_date(end, cdr->end);
139
140         sprintf(
141                 sqlcmd,
142                 "INSERT INTO %s "
143                 "("
144                         "accountcode, "
145                         "src, "
146                         "dst, "
147                         "dcontext, "
148                         "clid, "
149                         "channel, "
150                         "dstchannel, "
151                         "lastapp, "
152                         "lastdata, "
153                         "start, "
154                         "answer, "
155                         "[end], "
156                         "duration, "
157                         "billsec, "
158                         "disposition, "
159                         "amaflags, "
160                         "uniqueid"
161                 ") "
162                 "VALUES "
163                 "("
164                         "'%s', "        /* accountcode */
165                         "'%s', "        /* src */
166                         "'%s', "        /* dst */
167                         "'%s', "        /* dcontext */
168                         "'%s', "        /* clid */
169                         "'%s', "        /* channel */
170                         "'%s', "        /* dstchannel */
171                         "'%s', "        /* lastapp */
172                         "'%s', "        /* lastdata */
173                         "%s, "          /* start */
174                         "%s, "          /* answer */
175                         "%s, "          /* end */
176                         "%ld, "         /* duration */
177                         "%ld, "         /* billsec */
178                         "'%s', "        /* disposition */
179                         "'%s', "        /* amaflags */
180                         "'%s'"          /* uniqueid */
181                 ")",
182                 table,
183                 accountcode,
184                 src,
185                 dst,
186                 dcontext,
187                 clid,
188                 channel,
189                 dstchannel,
190                 lastapp,
191                 lastdata,
192                 start,
193                 answer,
194                 end,
195                 cdr->duration,
196                 cdr->billsec,
197                 ast_cdr_disp2str(cdr->disposition),
198                 ast_cdr_flags2str(cdr->amaflags),
199                 uniqueid
200         );
201
202         do {
203                 if (!connected) {
204                         if (mssql_connect())
205                                 ast_log(LOG_ERROR, "Failed to reconnect to SQL database.\n");
206                         else
207                                 ast_log(LOG_WARNING, "Reconnected to SQL database.\n");
208
209                         retried = 1;    /* note that we have now tried */
210                 }
211
212 #ifdef FREETDS_PRE_0_62
213                 if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds, &result_type) != TDS_SUCCEED || result_type != TDS_CMD_SUCCEED))
214 #else
215                 if (!connected || (tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
216 #endif
217                 {
218                         ast_log(LOG_ERROR, "Failed to insert Call Data Record into SQL database.\n");
219
220                         mssql_disconnect();     /* this is ok even if we are already disconnected */
221                 }
222         } while (!connected && !retried);
223
224         ast_free(accountcode);
225         ast_free(src);
226         ast_free(dst);
227         ast_free(dcontext);
228         ast_free(clid);
229         ast_free(channel);
230         ast_free(dstchannel);
231         ast_free(lastapp);
232         ast_free(lastdata);
233         ast_free(uniqueid);
234
235         ast_mutex_unlock(&tds_lock);
236
237         return res;
238 }
239
240 static char *anti_injection(const char *str, int len)
241 {
242         /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
243
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_malloc(len + 1)) == NULL)
250         {
251                 ast_log(LOG_ERROR, "cdr_tds:  Out of memory error\n");
252                 return NULL;
253         }
254         memset(buf, 0, len);
255
256         buf_ptr = buf;
257
258         /* Escape single quotes */
259         for (; *str && strlen(buf) < len; str++)
260         {
261                 if (*str == '\'')
262                         *buf_ptr++ = '\'';
263                 *buf_ptr++ = *str;
264         }
265         *buf_ptr = '\0';
266
267         /* Erase known bad input */
268         for (idx=0; *known_bad[idx]; idx++)
269         {
270                 while((srh_ptr = strcasestr(buf, known_bad[idx])))
271                 {
272                         memmove(srh_ptr, srh_ptr+strlen(known_bad[idx]), strlen(srh_ptr+strlen(known_bad[idx]))+1);
273                 }
274         }
275
276         return buf;
277 }
278
279 static void get_date(char *dateField, struct timeval tv)
280 {
281         struct ast_tm tm;
282         char buf[80];
283
284         /* To make sure we have date variable if not insert null to SQL */
285         if (!ast_tvzero(tv))
286         {
287                 ast_localtime(&tv, &tm, NULL);
288                 ast_strftime(buf, 80, DATE_FORMAT, &tm);
289                 sprintf(dateField, "'%s'", buf);
290         }
291         else
292         {
293                 strcpy(dateField, "null");
294         }
295 }
296
297 static int mssql_disconnect(void)
298 {
299         if (tds) {
300                 tds_free_socket(tds);
301                 tds = NULL;
302         }
303
304         if (context) {
305                 tds_free_context(context);
306                 context = NULL;
307         }
308
309         if (login) {
310                 tds_free_login(login);
311                 login = NULL;
312         }
313
314         connected = 0;
315
316         return 0;
317 }
318
319 static int mssql_connect(void)
320 {
321 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
322         TDSCONNECTION *connection = NULL;
323 #else
324         TDSCONNECTINFO *connection = NULL;
325 #endif
326         char query[128];
327
328         /* Connect to M$SQL Server */
329         if (!(login = tds_alloc_login()))
330         {
331                 ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
332                 return -1;
333         }
334         
335         tds_set_server(login, hostname);
336         tds_set_user(login, dbuser);
337         tds_set_passwd(login, password);
338         tds_set_app(login, "TSQL");
339         tds_set_library(login, "TDS-Library");
340 #ifndef FREETDS_PRE_0_62
341         tds_set_client_charset(login, charset);
342 #endif
343         tds_set_language(login, language);
344         tds_set_packet(login, 512);
345         tds_set_version(login, 7, 0);
346
347 #ifdef FREETDS_0_64
348         if (!(context = tds_alloc_context(NULL)))
349 #else
350         if (!(context = tds_alloc_context()))
351 #endif
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 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
375                 tds_free_connection(connection);
376 #else
377                 tds_free_connect(connection);
378 #endif
379                 connection = NULL;
380                 goto connect_fail;
381         }
382 #if (defined(FREETDS_0_63) || defined(FREETDS_0_64))
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) ast_free(hostname);
415         if (dbname) ast_free(dbname);
416         if (dbuser) ast_free(dbuser);
417         if (password) ast_free(password);
418         if (charset) ast_free(charset);
419         if (language) ast_free(language);
420         if (table) ast_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         const 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_debug(1,"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, ast_module_info->description, 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 static int reload(void)
503 {
504         tds_unload_module();
505         return tds_load_module();
506 }
507
508 static int load_module(void)
509 {
510         if(!tds_load_module())
511                 return AST_MODULE_LOAD_DECLINE;
512         else 
513                 return AST_MODULE_LOAD_SUCCESS;
514 }
515
516 static int unload_module(void)
517 {
518         return tds_unload_module();
519 }
520
521 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "MSSQL CDR Backend",
522                 .load = load_module,
523                 .unload = unload_module,
524                 .reload = reload,
525                );