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