Fix date/time in TDS stuff (bug #2147)
[asterisk/asterisk.git] / cdr / cdr_tds.c
1 /*
2  * Asterisk -- A telephony toolkit for Linux.
3  *
4  * FreeTDS CDR logger
5  *
6  * This program is free software, distributed under the terms of
7  * the GNU General Public License.
8  *
9  *
10  * Table Structure for `cdr`
11  *
12  * Created on: 05/20/2004 16:16
13  * Last changed on: 07/27/2004 20:01
14
15 CREATE TABLE [dbo].[cdr] (
16         [accountcode] [varchar] (20) NULL ,
17         [src] [varchar] (80) NULL ,
18         [dst] [varchar] (80) NULL ,
19         [dcontext] [varchar] (80) NULL ,
20         [clid] [varchar] (80) NULL ,
21         [channel] [varchar] (80) NULL ,
22         [dstchannel] [varchar] (80) NULL ,
23         [lastapp] [varchar] (80) NULL ,
24         [lastdata] [varchar] (80) NULL ,
25         [start] [datetime] NULL ,
26         [answer] [datetime] NULL ,
27         [end] [datetime] NULL ,
28         [duration] [int] NULL ,
29         [billsec] [int] NULL ,
30         [disposition] [varchar] (20) NULL ,
31         [amaflags] [varchar] (16) NULL ,
32         [uniqueid] [varchar] (32) NULL
33 ) ON [PRIMARY]
34
35 */
36
37 #include <sys/types.h>
38 #include <asterisk/config.h>
39 #include <asterisk/options.h>
40 #include <asterisk/channel.h>
41 #include <asterisk/cdr.h>
42 #include <asterisk/module.h>
43 #include <asterisk/logger.h>
44 #include "../asterisk.h"
45
46 #include <stdio.h>
47 #include <string.h>
48 #include <stdlib.h>
49 #include <unistd.h>
50 #include <time.h>
51 #include <math.h>
52
53 #include <tds.h>
54 #include <tdsconvert.h>
55 #include <ctype.h>
56
57 #define DATE_FORMAT "%Y/%m/%d %T"
58
59 static char *desc = "MSSQL CDR Backend";
60 static char *name = "mssql";
61 static char *config = "cdr_tds.conf";
62
63 AST_MUTEX_DEFINE_STATIC(tds_lock);
64
65 static TDSSOCKET *tds;
66 static TDSLOGIN *login;
67 static TDSCONTEXT *context;
68
69 static char *stristr(const char*, const char*);
70 static char *anti_injection(const char *, int);
71 static void get_date(char *, struct timeval);
72
73 static int tds_log(struct ast_cdr *cdr)
74 {
75         char sqlcmd[2048], start[80], answer[80], end[80];
76         char *accountcode, *src, *dst, *dcontext, *clid, *channel, *dstchannel, *lastapp, *lastdata, *uniqueid;
77         int res = 0;
78
79         ast_mutex_lock(&tds_lock);
80
81         memset(sqlcmd, 0, 2048);
82
83         accountcode = anti_injection(cdr->accountcode, 20);
84         src = anti_injection(cdr->src, 80);
85         dst = anti_injection(cdr->dst, 80);
86         dcontext = anti_injection(cdr->dcontext, 80);
87         clid = anti_injection(cdr->clid, 80);
88         channel = anti_injection(cdr->channel, 80);
89         dstchannel = anti_injection(cdr->dstchannel, 80);
90         lastapp = anti_injection(cdr->lastapp, 80);
91         lastdata = anti_injection(cdr->lastdata, 80);
92         uniqueid = anti_injection(cdr->uniqueid, 32);
93
94         get_date(start, cdr->start);
95         get_date(answer, cdr->answer);
96         get_date(end, cdr->end);
97
98         sprintf(
99                 sqlcmd,
100                 "INSERT INTO cdr "
101                 "("
102                         "accountcode, "
103                         "src, "
104                         "dst, "
105                         "dcontext, "
106                         "clid, "
107                         "channel, "
108                         "dstchannel, "
109                         "lastapp, "
110                         "lastdata, "
111                         "start, "
112                         "answer, "
113                         "[end], "
114                         "duration, "
115                         "billsec, "
116                         "disposition, "
117                         "amaflags, "
118                         "uniqueid"
119                 ") "
120                 "VALUES "
121                 "("
122                         "'%s', "        /* accountcode */
123                         "'%s', "        /* src */
124                         "'%s', "        /* dst */
125                         "'%s', "        /* dcontext */
126                         "'%s', "        /* clid */
127                         "'%s', "        /* channel */
128                         "'%s', "        /* dstchannel */
129                         "'%s', "        /* lastapp */
130                         "'%s', "        /* lastdata */
131                         "%s, "          /* start */
132                         "%s, "          /* answer */
133                         "%s, "          /* end */
134                         "%i, "          /* duration */
135                         "%i, "          /* billsec */
136                         "'%s', "        /* disposition */
137                         "'%s', "        /* amaflags */
138                         "'%s'"          /* uniqueid */
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                 cdr->duration,
153                 cdr->billsec,
154                 ast_cdr_disp2str(cdr->disposition),
155                 ast_cdr_flags2str(cdr->amaflags),
156                 uniqueid
157         );
158
159         if ((tds_submit_query(tds, sqlcmd) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
160         {
161                 ast_log(LOG_ERROR, "Failed to insert record into database.\n");
162
163                 res = -1;
164         }
165
166         free(accountcode);
167         free(src);
168         free(dst);
169         free(dcontext);
170         free(clid);
171         free(channel);
172         free(dstchannel);
173         free(lastapp);
174         free(lastdata);
175         free(uniqueid);
176
177         ast_mutex_unlock(&tds_lock);
178
179         return res;
180 }
181
182 /* Return the offset of one string within another.
183    Copyright (C) 1994, 1996, 1997, 2000, 2001 Free Software Foundation, Inc.
184    This file is part of the GNU C Library.
185
186    The GNU C Library is free software; you can redistribute it and/or
187    modify it under the terms of the GNU Lesser General Public
188    License as published by the Free Software Foundation; either
189    version 2.1 of the License, or (at your option) any later version.
190
191    The GNU C Library is distributed in the hope that it will be useful,
192    but WITHOUT ANY WARRANTY; without even the implied warranty of
193    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
194    Lesser General Public License for more details.
195
196    You should have received a copy of the GNU Lesser General Public
197    License along with the GNU C Library; if not, write to the Free
198    Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
199    02111-1307 USA.  */
200
201 /*
202  * My personal strstr() implementation that beats most other algorithms.
203  * Until someone tells me otherwise, I assume that this is the
204  * fastest implementation of strstr() in C.
205  * I deliberately chose not to comment it.  You should have at least
206  * as much fun trying to understand it, as I had to write it :-).
207  *
208  * Stephen R. van den Berg, berg@pool.informatik.rwth-aachen.de */
209
210 static char *
211 stristr (phaystack, pneedle)
212      const char *phaystack;
213      const char *pneedle;
214 {
215   typedef unsigned chartype;
216
217   const unsigned char *haystack, *needle;
218   chartype b;
219   const unsigned char *rneedle;
220
221   haystack = (const unsigned char *) phaystack;
222
223   if ((b = toupper(*(needle = (const unsigned char *) pneedle))))
224     {
225       chartype c;
226       haystack--;               /* possible ANSI violation */
227
228       {
229         chartype a;
230         do
231           if (!(a = toupper(*++haystack)))
232             goto ret0;
233         while (a != b);
234       }
235
236       if (!(c = toupper(*++needle)))
237         goto foundneedle;
238       ++needle;
239       goto jin;
240
241       for (;;)
242         {
243           {
244             chartype a;
245             if (0)
246             jin:{
247                 if ((a = toupper(*++haystack)) == c)
248                   goto crest;
249               }
250             else
251               a = toupper(*++haystack);
252             do
253               {
254                 for (; a != b; a = toupper(*++haystack))
255                   {
256                     if (!a)
257                       goto ret0;
258                     if ((a = toupper(*++haystack)) == b)
259                       break;
260                     if (!a)
261                       goto ret0;
262                   }
263               }
264             while ((a = toupper(*++haystack)) != c);
265           }
266         crest:
267           {
268             chartype a;
269             {
270               const unsigned char *rhaystack;
271               if (toupper(*(rhaystack = haystack-- + 1)) == (a = toupper(*(rneedle = needle))))
272                 do
273                   {
274                     if (!a)
275                       goto foundneedle;
276                     if (toupper(*++rhaystack) != (a = toupper(*++needle)))
277                       break;
278                     if (!a)
279                       goto foundneedle;
280                   }
281                 while (toupper(*++rhaystack) == (a = toupper(*++needle)));
282               needle = rneedle; /* took the register-poor aproach */
283             }
284             if (!a)
285               break;
286           }
287         }
288     }
289 foundneedle:
290   return (char *) haystack;
291 ret0:
292   return 0;
293 }
294
295 static char *anti_injection(const char *str, int len)
296 {
297         /* Reference to http://www.nextgenss.com/papers/advanced_sql_injection.pdf */
298
299         char *buf;
300         char *buf_ptr, *srh_ptr;
301         char *known_bad[] = {"select", "insert", "update", "delete", "drop", ";", "--", "\0"};
302         int idx;
303
304         if ((buf = malloc(len + 1)) == NULL)
305         {
306                 ast_log(LOG_ERROR, "cdr_tds:  Out of memory error\n");
307                 return NULL;
308         }
309         memset(buf, 0, len);
310
311         buf_ptr = buf;
312
313         /* Escape single quotes */
314         for (; *str && strlen(buf) < len; str++)
315         {
316                 if (*str == '\'')
317                         *buf_ptr++ = '\'';
318                 *buf_ptr++ = *str;
319         }
320         *buf_ptr = '\0';
321
322         /* Erase known bad input */
323         for (idx=0; *known_bad[idx]; idx++)
324         {
325                 while(srh_ptr = stristr(buf, known_bad[idx])) /* fix me! */
326                 {
327                         memmove(srh_ptr, srh_ptr+strlen(known_bad[idx]), strlen(srh_ptr+strlen(known_bad[idx]))+1);
328                 }
329         }
330
331         return buf;
332 }
333
334 static void get_date(char *dateField, struct timeval tv)
335 {
336         struct tm tm;
337         time_t t;
338         char buf[80];
339
340         /* To make sure we have date variable if not insert null to SQL */
341         if (tv.tv_sec && tv.tv_usec)
342         {
343                 t = tv.tv_sec;
344                 localtime_r(&t, &tm);
345                 strftime(buf, 80, DATE_FORMAT, &tm);
346                 sprintf(dateField, "'%s'", buf);
347         }
348         else
349         {
350                 strcpy(dateField, "null");
351         }
352 }
353
354 char *description(void)
355 {
356         return desc;
357 }
358
359 int unload_module(void)
360 {
361         tds_free_socket(tds);
362         tds_free_login(login);
363         tds_free_context(context);
364
365         ast_cdr_unregister(name);
366
367         return 0;
368 }
369
370 int load_module(void)
371 {
372         TDSCONNECTINFO *connection;
373         int res = 0;
374         struct ast_config *cfg;
375         struct ast_variable *var;
376         char query[1024], *ptr = NULL;
377         char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *charset = NULL, *language = NULL;
378
379         cfg = ast_load(config);
380         if (!cfg)
381         {
382                 ast_log(LOG_NOTICE, "Unable to load config for MSSQL CDR's: %s\n", config);
383                 return 0;
384         }
385
386         var = ast_variable_browse(cfg, "global");
387         if (!var) /* nothing configured */
388                 return 0;
389
390         ptr = ast_variable_retrieve(cfg, "global", "hostname");
391         if (ptr)
392         {
393                 hostname = strdupa(ptr);
394         }
395         else
396         {
397                 ast_log(LOG_ERROR,"Database server hostname not specified.\n");
398         }
399
400         ptr = ast_variable_retrieve(cfg, "global", "dbname");
401         if (ptr)
402         {
403                 dbname = strdupa(ptr);
404         }
405         else
406         {
407                 ast_log(LOG_ERROR,"Database dbname not specified.\n");
408         }
409
410         ptr = ast_variable_retrieve(cfg, "global", "user");
411         if (ptr)
412         {
413                 dbuser = strdupa(ptr);
414         }
415         else
416         {
417                 ast_log(LOG_ERROR,"Database dbuser not specified.\n");
418         }
419
420         ptr = ast_variable_retrieve(cfg, "global", "password");
421         if (ptr)
422         {
423                 password = strdupa(ptr);
424         }
425         else
426         {
427                 ast_log(LOG_ERROR,"Database password not specified.\n");
428         }
429
430         ptr = ast_variable_retrieve(cfg, "global", "charset");
431         if (ptr)
432         {
433                 charset = strdupa(ptr);
434         }
435         else
436         {
437                 charset = strdupa("iso_1");
438         }
439
440         ptr = ast_variable_retrieve(cfg, "global", "language");
441         if (ptr)
442         {
443                 language = strdupa(ptr);
444         }
445         else
446         {
447                 language = strdupa("us_english");
448         }
449
450         ast_destroy(cfg);
451
452         /* Connect to M$SQL Server */
453         if (!(login = tds_alloc_login()))
454         {
455                 ast_log(LOG_ERROR, "tds_alloc_login() failed.\n");
456                 res = -1;
457         }
458         else
459         {
460                 tds_set_server(login, hostname);
461                 tds_set_user(login, dbuser);
462                 tds_set_passwd(login, password);
463                 tds_set_app(login, "TSQL");
464                 tds_set_library(login, "TDS-Library");
465                 tds_set_client_charset(login, charset);
466                 tds_set_language(login, language);
467                 tds_set_packet(login, 512);
468                 tds_set_version(login, 7, 0);
469
470                 context = tds_alloc_context();
471                 tds = tds_alloc_socket(context, 512);
472
473                 tds_set_parent(tds, NULL);
474                 connection = tds_read_config_info(NULL, login, context->locale);
475                 if (!connection || tds_connect(tds, connection) == TDS_FAIL)
476                 {
477                         ast_log(LOG_ERROR, "Failed to connect to MSSQL server.\n");
478                         res = -1;
479                 }
480                 tds_free_connect(connection);
481
482                 if (!res)
483                 {
484                         memset(query, 0, sizeof(query));
485                         sprintf(query, "USE %s", dbname);
486                         if ((tds_submit_query(tds, query) != TDS_SUCCEED) || (tds_process_simple_query(tds) != TDS_SUCCEED))
487                         {
488                                 ast_log(LOG_ERROR, "Could not change database (%s)\n", dbname);
489                                 res = -1;
490                         }
491                         else
492                         {
493                                 /* Register MSSQL CDR handler */
494                                 res = ast_cdr_register(name, desc, tds_log);
495                                 if (res)
496                                 {
497                                         ast_log(LOG_ERROR, "Unable to register MSSQL CDR handling\n");
498                                 }
499                         }
500                 }
501         }
502         return res;
503 }
504
505 int reload(void)
506 {
507         return 0;
508 }
509
510 int usecount(void)
511 {
512         return 0;
513 }
514
515 char *key()
516 {
517         return ASTERISK_GPL_KEY;
518 }