Argument macro janitor for func_odbc, fixes #7171
[asterisk/asterisk.git] / funcs / func_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (c) 2005, 2006 Tilghman Lesher
5  *
6  * Tilghman Lesher <func_odbc__200508@the-tilghman.com>
7  *
8  * See http://www.asterisk.org for more information about
9  * the Asterisk project. Please do not directly contact
10  * any of the maintainers of this project for assistance;
11  * the project provides a web site, mailing lists and IRC
12  * channels for your use.
13  *
14  * This program is free software, distributed under the terms of
15  * the GNU General Public License Version 2. See the LICENSE file
16  * at the top of the source tree.
17  */
18
19 /*!
20  * \file
21  *
22  * \brief ODBC lookups
23  *
24  * \author Tilghman Lesher <func_odbc__200508@the-tilghman.com>
25  */
26
27 /*** MODULEINFO
28         <depend>unixodbc</depend>
29  ***/
30
31 #include <sys/types.h>
32 #include <stdio.h>
33 #include <stdlib.h>
34 #include <unistd.h>
35 #include <string.h>
36
37 #include "asterisk.h"
38
39 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
40
41 #include "asterisk/module.h"
42 #include "asterisk/file.h"
43 #include "asterisk/logger.h"
44 #include "asterisk/options.h"
45 #include "asterisk/channel.h"
46 #include "asterisk/pbx.h"
47 #include "asterisk/module.h"
48 #include "asterisk/config.h"
49 #include "asterisk/res_odbc.h"
50 #include "asterisk/app.h"
51
52 static char *tdesc = "ODBC lookups";
53
54 static char *config = "func_odbc.conf";
55
56 struct acf_odbc_query {
57         AST_LIST_ENTRY(acf_odbc_query) list;
58         char dsn[30];
59         char sql_read[2048];
60         char sql_write[2048];
61         struct ast_custom_function *acf;
62 };
63
64 AST_LIST_HEAD_STATIC(queries, acf_odbc_query);
65
66 #ifdef NEEDTRACE
67 static void acf_odbc_error(SQLHSTMT stmt, int res)
68 {
69         char state[10] = "", diagnostic[256] = "";
70         SQLINTEGER nativeerror = 0;
71         SQLSMALLINT diagbytes = 0;
72         SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
73         ast_log(LOG_WARNING, "SQL return value %d: error %s: %s (len %d)\n", res, state, diagnostic, diagbytes);
74 }
75 #endif
76
77 /*
78  * Master control routine
79  */
80 static int acf_odbc_write(struct ast_channel *chan, char *cmd, char *s, const char *value)
81 {
82         struct odbc_obj *obj;
83         struct acf_odbc_query *query;
84         char *t, buf[2048]="", varname[15];
85         int res, i, retry=0;
86         AST_DECLARE_APP_ARGS(values,
87                 AST_APP_ARG(field)[100];
88         );
89         AST_DECLARE_APP_ARGS(args,
90                 AST_APP_ARG(field)[100];
91         );
92         SQLHSTMT stmt;
93         SQLINTEGER nativeerror=0, numfields=0, rows=0;
94         SQLSMALLINT diagbytes=0;
95         unsigned char state[10], diagnostic[256];
96 #ifdef NEEDTRACE
97         SQLINTEGER enable = 1;
98         char *tracefile = "/tmp/odbc.trace";
99 #endif
100
101         AST_LIST_LOCK(&queries);
102         AST_LIST_TRAVERSE(&queries, query, list) {
103                 if (!strcmp(query->acf->name, cmd)) {
104                         break;
105                 }
106         }
107
108         if (!query) {
109                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
110                 AST_LIST_UNLOCK(&queries);
111                 return -1;
112         }
113
114         obj = odbc_request_obj(query->dsn, 0);
115
116         if (!obj) {
117                 ast_log(LOG_ERROR, "No such DSN registered (or out of connections): %s (check res_odbc.conf)\n", query->dsn);
118                 AST_LIST_UNLOCK(&queries);
119                 return -1;
120         }
121
122         /* Parse our arguments */
123         t = value ? ast_strdupa(value) : "";
124
125         if (!s || !t) {
126                 ast_log(LOG_ERROR, "Out of memory\n");
127                 AST_LIST_UNLOCK(&queries);
128                 return -1;
129         }
130
131         AST_STANDARD_APP_ARGS(args, s);
132         for (i = 0; i < args.argc; i++) {
133                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
134                 pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
135         }
136
137         /* Parse values, just like arguments */
138         /* Can't use the pipe, because app Set removes them */
139         AST_NONSTANDARD_APP_ARGS(values, t, ',');
140         for (i = 0; i < values.argc; i++) {
141                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
142                 pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
143         }
144
145         /* Additionally set the value as a whole (but push an empty string if value is NULL) */
146         pbx_builtin_pushvar_helper(chan, "VALUE", value ? value : "");
147
148         pbx_substitute_variables_helper(chan, query->sql_write, buf, sizeof(buf) - 1);
149
150         /* Restore prior values */
151         for (i = 0; i < args.argc; i++) {
152                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
153                 pbx_builtin_setvar_helper(chan, varname, NULL);
154         }
155
156         for (i = 0; i < values.argc; i++) {
157                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
158                 pbx_builtin_setvar_helper(chan, varname, NULL);
159         }
160         pbx_builtin_setvar_helper(chan, "VALUE", NULL);
161
162         AST_LIST_UNLOCK(&queries);
163
164 retry_write:
165 #ifdef NEEDTRACE
166         SQLSetConnectAttr(obj->con, SQL_ATTR_TRACE, &enable, SQL_IS_INTEGER);
167         SQLSetConnectAttr(obj->con, SQL_ATTR_TRACEFILE, tracefile, strlen(tracefile));
168 #endif
169
170         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
171         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
172                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
173                 pbx_builtin_setvar_helper(chan, "ODBCROWS", "-1");
174                 return -1;
175         }
176
177         res = SQLPrepare(stmt, (unsigned char *)buf, SQL_NTS);
178         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
179                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", buf);
180                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
181                 pbx_builtin_setvar_helper(chan, "ODBCROWS", "-1");
182                 return -1;
183         }
184
185         res = SQLExecute(stmt);
186         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
187                 if (res == SQL_ERROR) {
188                         SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
189                         for (i = 0; i <= numfields; i++) {
190                                 SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
191                                 ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
192                                 if (i > 10) {
193                                         ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
194                                         break;
195                                 }
196                         }
197                 }
198                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
199                 odbc_release_obj(obj);
200                 /* All handles are now invalid (after a disconnect), so we gotta redo all handles */
201                 obj = odbc_request_obj("asterisk", 1);
202                 if (!retry) {
203                         retry = 1;
204                         goto retry_write;
205                 }
206                 rows = -1;
207         } else {
208                 /* Rows affected */
209                 SQLRowCount(stmt, &rows);
210         }
211
212         /* Output the affected rows, for all cases.  In the event of failure, we
213          * flag this as -1 rows.  Note that this is different from 0 affected rows
214          * which would be the case if we succeeded in our query, but the values did
215          * not change. */
216         snprintf(varname, sizeof(varname), "%d", (int)rows);
217         pbx_builtin_setvar_helper(chan, "ODBCROWS", varname);
218
219         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
220                 ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", buf);
221         }
222
223         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
224
225         return 0;
226 }
227
228 static int acf_odbc_read(struct ast_channel *chan, char *cmd, char *s, char *buf, size_t len)
229 {
230         struct odbc_obj *obj;
231         struct acf_odbc_query *query;
232         char sql[2048] = "", varname[15];
233         int res, x, buflen = 0;
234         AST_DECLARE_APP_ARGS(args,
235                 AST_APP_ARG(field)[100];
236         );
237         SQLHSTMT stmt;
238         SQLSMALLINT colcount=0;
239         SQLINTEGER indicator;
240 #ifdef NEEDTRACE
241         SQLINTEGER enable = 1;
242         char *tracefile = "/tmp/odbc.trace";
243 #endif
244
245         AST_LIST_LOCK(&queries);
246         AST_LIST_TRAVERSE(&queries, query, list) {
247                 if (!strcmp(query->acf->name, cmd)) {
248                         break;
249                 }
250         }
251
252         if (!query) {
253                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
254                 AST_LIST_UNLOCK(&queries);
255                 return -1;
256         }
257
258         obj = odbc_request_obj(query->dsn, 0);
259
260         if (!obj) {
261                 ast_log(LOG_ERROR, "No such DSN registered (or out of connections): %s (check res_odbc.conf)\n", query->dsn);
262                 AST_LIST_UNLOCK(&queries);
263                 return -1;
264         }
265
266 #ifdef NEEDTRACE
267         SQLSetConnectAttr(obj->con, SQL_ATTR_TRACE, &enable, SQL_IS_INTEGER);
268         SQLSetConnectAttr(obj->con, SQL_ATTR_TRACEFILE, tracefile, strlen(tracefile));
269 #endif
270
271         AST_STANDARD_APP_ARGS(args, s);
272         for (x = 0; x < args.argc; x++) {
273                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
274                 pbx_builtin_pushvar_helper(chan, varname, args.field[x]);
275         }
276
277         pbx_substitute_variables_helper(chan, query->sql_read, sql, sizeof(sql) - 1);
278
279         /* Restore prior values */
280         for (x = 0; x < args.argc; x++) {
281                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
282                 pbx_builtin_setvar_helper(chan, varname, NULL);
283         }
284
285         AST_LIST_UNLOCK(&queries);
286
287         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
288         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
289                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
290                 return -1;
291         }
292
293         res = SQLPrepare(stmt, (unsigned char *)sql, SQL_NTS);
294         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
295                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
296                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
297                 return -1;
298         }
299
300         res = odbc_smart_execute(obj, stmt);
301         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
302                 ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql);
303                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
304                 return -1;
305         }
306
307         res = SQLNumResultCols(stmt, &colcount);
308         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
309                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
310                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
311                 return -1;
312         }
313
314         *buf = '\0';
315
316         res = SQLFetch(stmt);
317         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
318                 if (res == SQL_NO_DATA) {
319                         if (option_verbose > 3) {
320                                 ast_verbose(VERBOSE_PREFIX_4 "Found no rows [%s]\n", sql);
321                         }
322                 } else if (option_verbose > 3) {
323                         ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, sql);
324                 }
325                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
326                 return 0;
327         }
328
329         for (x = 0; x < colcount; x++) {
330                 int i;
331                 char coldata[256];
332
333                 buflen = strlen(buf);
334                 res = SQLGetData(stmt, x + 1, SQL_CHAR, coldata, sizeof(coldata), &indicator);
335                 if (indicator == SQL_NULL_DATA) {
336                         coldata[0] = '\0';
337                         res = SQL_SUCCESS;
338                 }
339
340                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
341                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
342                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
343                         return -1;
344                 }
345
346                 /* Copy data, encoding '\' and ',' for the argument parser */
347                 for (i = 0; i < sizeof(coldata); i++) {
348                         if (coldata[i] == '\\' || coldata[i] == ',') {
349                                 buf[buflen++] = '\\';
350                         }
351                         buf[buflen++] = coldata[i];
352
353                         if (buflen >= len - 2)
354                                 break;
355
356                         if (coldata[i] == '\0')
357                                 break;
358                 }
359
360                 buf[buflen - 1] = ',';
361         }
362         /* Trim trailing comma */
363         buf[buflen - 1] = '\0';
364
365         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
366         return 0;
367 }
368
369 static int acf_escape(struct ast_channel *chan, char *cmd, char *data, char *buf, size_t len)
370 {
371         char *out = buf;
372
373         for (; *data && out - buf < len; data++) {
374                 if (*data == '\'') {
375                         *out = '\'';
376                         out++;
377                 }
378                 *out++ = *data;
379         }
380         *out = '\0';
381
382         return 0;
383 }
384
385 static struct ast_custom_function escape_function = {
386         .name = "SQL_ESC",
387         .synopsis = "Escapes single ticks for use in SQL statements",
388         .syntax = "SQL_ESC(<string>)",
389         .desc =
390 "Used in SQL templates to escape data which may contain single ticks (') which\n"
391 "are otherwise used to delimit data.  For example:\n"
392 "SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}'\n",
393         .read = acf_escape,
394         .write = NULL,
395 };
396
397 static int init_acf_query(struct ast_config *cfg, char *catg, struct acf_odbc_query **query)
398 {
399         char *tmp;
400
401         if (!cfg || !catg) {
402                 return -1;
403         }
404
405         *query = ast_calloc(1, sizeof(struct acf_odbc_query));
406         if (! (*query))
407                 return -1;
408
409         if ((tmp = ast_variable_retrieve(cfg, catg, "dsn"))) {
410                 ast_copy_string((*query)->dsn, tmp, sizeof((*query)->dsn));
411         } else {
412                 return -1;
413         }
414
415         if ((tmp = ast_variable_retrieve(cfg, catg, "read"))) {
416                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
417         }
418
419         if ((tmp = ast_variable_retrieve(cfg, catg, "write"))) {
420                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
421         }
422
423         (*query)->acf = ast_calloc(1, sizeof(struct ast_custom_function));
424         if (! (*query)->acf) {
425                 free(*query);
426                 return -1;
427         }
428
429         if ((tmp = ast_variable_retrieve(cfg, catg, "prefix")) && !ast_strlen_zero(tmp)) {
430                 asprintf((char **)&((*query)->acf->name), "%s_%s", tmp, catg);
431         } else {
432                 asprintf((char **)&((*query)->acf->name), "ODBC_%s", catg);
433         }
434
435         if (!((*query)->acf->name)) {
436                 free((*query)->acf);
437                 free(*query);
438                 return -1;
439         }
440
441         asprintf((char **)&((*query)->acf->syntax), "%s(<arg1>[...[,<argN>]])", (*query)->acf->name);
442
443         if (!((*query)->acf->syntax)) {
444                 free((char *)(*query)->acf->name);
445                 free((*query)->acf);
446                 free(*query);
447                 return -1;
448         }
449
450         (*query)->acf->synopsis = "Runs the referenced query with the specified arguments";
451         if (!ast_strlen_zero((*query)->sql_read) && !ast_strlen_zero((*query)->sql_write)) {
452                 asprintf((char **)&((*query)->acf->desc),
453                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
454                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
455                                         "${ARG2}, ... ${ARGn}.  When setting the function, the values are provided\n"
456                                         "either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
457                                         "\nRead:\n%s\n\nWrite:\n%s\n",
458                                         (*query)->sql_read,
459                                         (*query)->sql_write);
460         } else if (!ast_strlen_zero((*query)->sql_read)) {
461                 asprintf((char **)&((*query)->acf->desc),
462                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
463                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
464                                         "${ARG2}, ... ${ARGn}.  This function may only be read, not set.\n\nSQL:\n%s\n",
465                                         (*query)->sql_read);
466         } else if (!ast_strlen_zero((*query)->sql_write)) {
467                 asprintf((char **)&((*query)->acf->desc),
468                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
469                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
470                                         "${ARG2}, ... ${ARGn}.  The values are provided either in whole as\n"
471                                         "${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
472                                         "This function may only be set.\nSQL:\n%s\n",
473                                         (*query)->sql_write);
474         }
475
476         /* Could be out of memory, or could be we have neither sql_read nor sql_write */
477         if (! ((*query)->acf->desc)) {
478                 free((char *)(*query)->acf->syntax);
479                 free((char *)(*query)->acf->name);
480                 free((*query)->acf);
481                 free(*query);
482                 return -1;
483         }
484
485         if (ast_strlen_zero((*query)->sql_read)) {
486                 (*query)->acf->read = NULL;
487         } else {
488                 (*query)->acf->read = acf_odbc_read;
489         }
490
491         if (ast_strlen_zero((*query)->sql_write)) {
492                 (*query)->acf->write = NULL;
493         } else {
494                 (*query)->acf->write = acf_odbc_write;
495         }
496
497         return 0;
498 }
499
500 static int free_acf_query(struct acf_odbc_query *query)
501 {
502         if (query) {
503                 if (query->acf) {
504                         if (query->acf->name)
505                                 free((char *)query->acf->name);
506                         if (query->acf->syntax)
507                                 free((char *)query->acf->syntax);
508                         if (query->acf->desc)
509                                 free((char *)query->acf->desc);
510                         free(query->acf);
511                 }
512                 free(query);
513         }
514         return 0;
515 }
516
517 static int odbc_load_module(void)
518 {
519         int res = 0;
520         struct ast_config *cfg;
521         char *catg;
522
523         AST_LIST_LOCK(&queries);
524
525         cfg = ast_config_load(config);
526         if (!cfg) {
527                 ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
528                 AST_LIST_UNLOCK(&queries);
529                 return -1;
530         }
531
532         for (catg = ast_category_browse(cfg, NULL);
533              catg;
534              catg = ast_category_browse(cfg, catg)) {
535                 struct acf_odbc_query *query = NULL;
536
537                 if (init_acf_query(cfg, catg, &query)) {
538                         ast_log(LOG_ERROR, "Out of memory\n");
539                         free_acf_query(query);
540                 } else {
541                         AST_LIST_INSERT_HEAD(&queries, query, list);
542                         ast_custom_function_register(query->acf);
543                 }
544         }
545
546         ast_config_destroy(cfg);
547         ast_custom_function_register(&escape_function);
548
549         AST_LIST_UNLOCK(&queries);
550         return res;
551 }
552
553 static int odbc_unload_module(void)
554 {
555         struct acf_odbc_query *query;
556
557         AST_LIST_LOCK(&queries);
558         while (!AST_LIST_EMPTY(&queries)) {
559                 query = AST_LIST_REMOVE_HEAD(&queries, list);
560                 ast_custom_function_unregister(query->acf);
561                 free_acf_query(query);
562         }
563
564         ast_custom_function_unregister(&escape_function);
565
566         /* Allow any threads waiting for this lock to pass (avoids a race) */
567         AST_LIST_UNLOCK(&queries);
568         AST_LIST_LOCK(&queries);
569
570         AST_LIST_UNLOCK(&queries);
571         return 0;
572 }
573
574 static int reload(void *mod)
575 {
576         int res = 0;
577         struct ast_config *cfg;
578         struct acf_odbc_query *oldquery;
579         char *catg;
580
581         AST_LIST_LOCK(&queries);
582
583         while (!AST_LIST_EMPTY(&queries)) {
584                 oldquery = AST_LIST_REMOVE_HEAD(&queries, list);
585                 ast_custom_function_unregister(oldquery->acf);
586                 free_acf_query(oldquery);
587         }
588
589         cfg = ast_config_load(config);
590         if (!cfg) {
591                 ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
592                 goto reload_out;
593         }
594
595         for (catg = ast_category_browse(cfg, NULL);
596              catg;
597              catg = ast_category_browse(cfg, catg)) {
598                 struct acf_odbc_query *query = NULL;
599
600                 if (init_acf_query(cfg, catg, &query)) {
601                         ast_log(LOG_ERROR, "Cannot initialize query %s\n", catg);
602                 } else {
603                         AST_LIST_INSERT_HEAD(&queries, query, list);
604                         ast_custom_function_register(query->acf);
605                 }
606         }
607
608         ast_config_destroy(cfg);
609 reload_out:
610         AST_LIST_UNLOCK(&queries);
611         return res;
612 }
613
614 static int unload_module(void *mod)
615 {
616         return odbc_unload_module();
617 }
618
619 static int load_module(void *mod)
620 {
621         return odbc_load_module();
622 }
623
624 static const char *description(void)
625 {
626         return tdesc;
627 }
628
629 /* XXX need to revise usecount - set if query_lock is set */
630
631 static const char *key(void)
632 {
633         return ASTERISK_GPL_KEY;
634 }
635
636 STD_MOD(MOD_1, reload, NULL, NULL);
637