00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033 #include "asterisk.h"
00034
00035 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 204170 $")
00036
00037 #include <sys/types.h>
00038 #include <stdio.h>
00039 #include <stdlib.h>
00040 #include <unistd.h>
00041 #include <string.h>
00042 #include <errno.h>
00043
00044 #include "asterisk/module.h"
00045 #include "asterisk/file.h"
00046 #include "asterisk/logger.h"
00047 #include "asterisk/options.h"
00048 #include "asterisk/channel.h"
00049 #include "asterisk/pbx.h"
00050 #include "asterisk/module.h"
00051 #include "asterisk/config.h"
00052 #include "asterisk/res_odbc.h"
00053 #include "asterisk/app.h"
00054
00055 static char *config = "func_odbc.conf";
00056
00057 enum {
00058 OPT_ESCAPECOMMAS = (1 << 0),
00059 } odbc_option_flags;
00060
00061 struct acf_odbc_query {
00062 AST_LIST_ENTRY(acf_odbc_query) list;
00063 char dsn[30];
00064 char sql_read[2048];
00065 char sql_write[2048];
00066 unsigned int flags;
00067 struct ast_custom_function *acf;
00068 };
00069
00070 AST_LIST_HEAD_STATIC(queries, acf_odbc_query);
00071
00072 static SQLHSTMT generic_prepare(struct odbc_obj *obj, void *data)
00073 {
00074 int res;
00075 char *sql = data;
00076 SQLHSTMT stmt;
00077
00078 res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
00079 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00080 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00081 return NULL;
00082 }
00083
00084 res = SQLPrepare(stmt, (unsigned char *)sql, SQL_NTS);
00085 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00086 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
00087 SQLCloseCursor(stmt);
00088 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00089 return NULL;
00090 }
00091
00092 return stmt;
00093 }
00094
00095
00096
00097
00098 static int acf_odbc_write(struct ast_channel *chan, char *cmd, char *s, const char *value)
00099 {
00100 struct odbc_obj *obj;
00101 struct acf_odbc_query *query;
00102 char *t, buf[2048]="", varname[15];
00103 int i, bogus_chan = 0;
00104 AST_DECLARE_APP_ARGS(values,
00105 AST_APP_ARG(field)[100];
00106 );
00107 AST_DECLARE_APP_ARGS(args,
00108 AST_APP_ARG(field)[100];
00109 );
00110 SQLHSTMT stmt;
00111 SQLLEN rows=0;
00112
00113 AST_LIST_LOCK(&queries);
00114 AST_LIST_TRAVERSE(&queries, query, list) {
00115 if (!strcmp(query->acf->name, cmd)) {
00116 break;
00117 }
00118 }
00119
00120 if (!query) {
00121 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
00122 AST_LIST_UNLOCK(&queries);
00123 return -1;
00124 }
00125
00126 obj = ast_odbc_request_obj(query->dsn, 0);
00127
00128 if (!obj) {
00129 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", query->dsn);
00130 AST_LIST_UNLOCK(&queries);
00131 return -1;
00132 }
00133
00134 if (!chan) {
00135 if ((chan = ast_channel_alloc(0, 0, "", "", "", "", "", 0, "Bogus/func_odbc")))
00136 bogus_chan = 1;
00137 }
00138
00139 if (chan)
00140 ast_autoservice_start(chan);
00141
00142
00143 t = value ? ast_strdupa(value) : "";
00144
00145 if (!s || !t) {
00146 ast_log(LOG_ERROR, "Out of memory\n");
00147 AST_LIST_UNLOCK(&queries);
00148 if (chan)
00149 ast_autoservice_stop(chan);
00150 if (bogus_chan)
00151 ast_channel_free(chan);
00152 return -1;
00153 }
00154
00155 AST_STANDARD_APP_ARGS(args, s);
00156 for (i = 0; i < args.argc; i++) {
00157 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
00158 pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
00159 }
00160
00161
00162
00163 AST_NONSTANDARD_APP_ARGS(values, t, ',');
00164 for (i = 0; i < values.argc; i++) {
00165 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
00166 pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
00167 }
00168
00169
00170 pbx_builtin_pushvar_helper(chan, "VALUE", value ? value : "");
00171
00172 pbx_substitute_variables_helper(chan, query->sql_write, buf, sizeof(buf) - 1);
00173
00174
00175 for (i = 0; i < args.argc; i++) {
00176 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
00177 pbx_builtin_setvar_helper(chan, varname, NULL);
00178 }
00179
00180 for (i = 0; i < values.argc; i++) {
00181 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
00182 pbx_builtin_setvar_helper(chan, varname, NULL);
00183 }
00184 pbx_builtin_setvar_helper(chan, "VALUE", NULL);
00185
00186 AST_LIST_UNLOCK(&queries);
00187
00188 stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, buf);
00189
00190 if (stmt) {
00191
00192 SQLRowCount(stmt, &rows);
00193 }
00194
00195
00196
00197
00198
00199 snprintf(varname, sizeof(varname), "%d", (int)rows);
00200 pbx_builtin_setvar_helper(chan, "ODBCROWS", varname);
00201
00202 if (stmt) {
00203 SQLCloseCursor(stmt);
00204 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00205 }
00206 if (obj)
00207 ast_odbc_release_obj(obj);
00208
00209 if (chan)
00210 ast_autoservice_stop(chan);
00211 if (bogus_chan)
00212 ast_channel_free(chan);
00213
00214 return 0;
00215 }
00216
00217 static int acf_odbc_read(struct ast_channel *chan, char *cmd, char *s, char *buf, size_t len)
00218 {
00219 struct odbc_obj *obj;
00220 struct acf_odbc_query *query;
00221 char sql[2048] = "", varname[15];
00222 int res, x, buflen = 1, escapecommas, bogus_chan = 0;
00223 AST_DECLARE_APP_ARGS(args,
00224 AST_APP_ARG(field)[100];
00225 );
00226 SQLHSTMT stmt;
00227 SQLSMALLINT colcount=0;
00228 SQLLEN indicator;
00229
00230 AST_LIST_LOCK(&queries);
00231 AST_LIST_TRAVERSE(&queries, query, list) {
00232 if (!strcmp(query->acf->name, cmd)) {
00233 break;
00234 }
00235 }
00236
00237 if (!query) {
00238 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
00239 AST_LIST_UNLOCK(&queries);
00240 return -1;
00241 }
00242
00243 obj = ast_odbc_request_obj(query->dsn, 0);
00244
00245 if (!obj) {
00246 ast_log(LOG_ERROR, "No such DSN registered (or out of connections): %s (check res_odbc.conf)\n", query->dsn);
00247 AST_LIST_UNLOCK(&queries);
00248 return -1;
00249 }
00250
00251 if (!chan) {
00252 if ((chan = ast_channel_alloc(0, 0, "", "", "", "", "", 0, "Bogus/func_odbc")))
00253 bogus_chan = 1;
00254 }
00255
00256 if (chan)
00257 ast_autoservice_start(chan);
00258
00259 AST_STANDARD_APP_ARGS(args, s);
00260 for (x = 0; x < args.argc; x++) {
00261 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
00262 pbx_builtin_pushvar_helper(chan, varname, args.field[x]);
00263 }
00264
00265 pbx_substitute_variables_helper(chan, query->sql_read, sql, sizeof(sql) - 1);
00266
00267
00268 for (x = 0; x < args.argc; x++) {
00269 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
00270 pbx_builtin_setvar_helper(chan, varname, NULL);
00271 }
00272
00273
00274 escapecommas = ast_test_flag(query, OPT_ESCAPECOMMAS);
00275
00276 AST_LIST_UNLOCK(&queries);
00277
00278 stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, sql);
00279
00280 if (!stmt) {
00281 ast_odbc_release_obj(obj);
00282 if (chan)
00283 ast_autoservice_stop(chan);
00284 if (bogus_chan)
00285 ast_channel_free(chan);
00286 return -1;
00287 }
00288
00289 res = SQLNumResultCols(stmt, &colcount);
00290 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00291 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00292 SQLCloseCursor(stmt);
00293 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00294 ast_odbc_release_obj(obj);
00295 if (chan)
00296 ast_autoservice_stop(chan);
00297 if (bogus_chan)
00298 ast_channel_free(chan);
00299 return -1;
00300 }
00301
00302 *buf = '\0';
00303
00304 res = SQLFetch(stmt);
00305 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00306 int res1 = -1;
00307 if (res == SQL_NO_DATA) {
00308 if (option_verbose > 3) {
00309 ast_verbose(VERBOSE_PREFIX_4 "Found no rows [%s]\n", sql);
00310 }
00311 res1 = 0;
00312 } else if (option_verbose > 3) {
00313 ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, sql);
00314 }
00315 SQLCloseCursor(stmt);
00316 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00317 ast_odbc_release_obj(obj);
00318 if (chan)
00319 ast_autoservice_stop(chan);
00320 if (bogus_chan)
00321 ast_channel_free(chan);
00322 return res1;
00323 }
00324
00325 for (x = 0; x < colcount; x++) {
00326 int i;
00327 char coldata[256];
00328
00329 buflen = strlen(buf);
00330 res = SQLGetData(stmt, x + 1, SQL_CHAR, coldata, sizeof(coldata), &indicator);
00331 if (indicator == SQL_NULL_DATA) {
00332 coldata[0] = '\0';
00333 res = SQL_SUCCESS;
00334 }
00335
00336 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00337 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00338 SQLCloseCursor(stmt);
00339 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00340 ast_odbc_release_obj(obj);
00341 if (chan)
00342 ast_autoservice_stop(chan);
00343 if (bogus_chan)
00344 ast_channel_free(chan);
00345 return -1;
00346 }
00347
00348
00349 for (i = 0; i < sizeof(coldata); i++) {
00350 if (escapecommas && (coldata[i] == '\\' || coldata[i] == ',')) {
00351 buf[buflen++] = '\\';
00352 }
00353 buf[buflen++] = coldata[i];
00354
00355 if (buflen >= len - 2)
00356 break;
00357
00358 if (coldata[i] == '\0')
00359 break;
00360 }
00361
00362 buf[buflen - 1] = ',';
00363 buf[buflen] = '\0';
00364 }
00365
00366 buf[buflen - 1] = '\0';
00367
00368 SQLCloseCursor(stmt);
00369 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00370 ast_odbc_release_obj(obj);
00371 if (chan)
00372 ast_autoservice_stop(chan);
00373 if (bogus_chan)
00374 ast_channel_free(chan);
00375 return 0;
00376 }
00377
00378 static int acf_escape(struct ast_channel *chan, char *cmd, char *data, char *buf, size_t len)
00379 {
00380 char *out = buf;
00381
00382 for (; *data && out - buf < len; data++) {
00383 if (*data == '\'') {
00384 *out = '\'';
00385 out++;
00386 }
00387 *out++ = *data;
00388 }
00389 *out = '\0';
00390
00391 return 0;
00392 }
00393
00394 static struct ast_custom_function escape_function = {
00395 .name = "SQL_ESC",
00396 .synopsis = "Escapes single ticks for use in SQL statements",
00397 .syntax = "SQL_ESC(<string>)",
00398 .desc =
00399 "Used in SQL templates to escape data which may contain single ticks (') which\n"
00400 "are otherwise used to delimit data. For example:\n"
00401 "SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}'\n",
00402 .read = acf_escape,
00403 .write = NULL,
00404 };
00405
00406 static int init_acf_query(struct ast_config *cfg, char *catg, struct acf_odbc_query **query)
00407 {
00408 const char *tmp;
00409 int res;
00410
00411 if (!cfg || !catg) {
00412 return -1;
00413 }
00414
00415 *query = ast_calloc(1, sizeof(struct acf_odbc_query));
00416 if (! (*query))
00417 return -1;
00418
00419 if ((tmp = ast_variable_retrieve(cfg, catg, "dsn"))) {
00420 ast_copy_string((*query)->dsn, tmp, sizeof((*query)->dsn));
00421 } else if ((tmp = ast_variable_retrieve(cfg, catg, "writehandle")) || (tmp = ast_variable_retrieve(cfg, catg, "readhandle"))) {
00422 ast_log(LOG_WARNING, "Separate read and write handles are not supported in this version of func_odbc.so\n");
00423 ast_copy_string((*query)->dsn, tmp, sizeof((*query)->dsn));
00424 } else {
00425 free(*query);
00426 *query = NULL;
00427 ast_log(LOG_ERROR, "No database handle was specified for func_odbc class '%s'\n", catg);
00428 return -1;
00429 }
00430
00431 if ((tmp = ast_variable_retrieve(cfg, catg, "read")) || (tmp = ast_variable_retrieve(cfg, catg, "readsql"))) {
00432 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
00433 }
00434
00435 if ((tmp = ast_variable_retrieve(cfg, catg, "write")) || (tmp = ast_variable_retrieve(cfg, catg, "writesql"))) {
00436 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
00437 }
00438
00439
00440 ast_set_flag((*query), OPT_ESCAPECOMMAS);
00441 if ((tmp = ast_variable_retrieve(cfg, catg, "escapecommas"))) {
00442 if (ast_false(tmp))
00443 ast_clear_flag((*query), OPT_ESCAPECOMMAS);
00444 }
00445
00446 (*query)->acf = ast_calloc(1, sizeof(struct ast_custom_function));
00447 if (! (*query)->acf) {
00448 free(*query);
00449 *query = NULL;
00450 return -1;
00451 }
00452
00453 if ((tmp = ast_variable_retrieve(cfg, catg, "prefix")) && !ast_strlen_zero(tmp)) {
00454 if (asprintf((char **)&((*query)->acf->name), "%s_%s", tmp, catg) < 0) {
00455 ast_log(LOG_WARNING, "asprintf() failed: %s\n", strerror(errno));
00456 }
00457 } else {
00458 if (asprintf((char **)&((*query)->acf->name), "ODBC_%s", catg) < 0) {
00459 ast_log(LOG_WARNING, "asprintf() failed: %s\n", strerror(errno));
00460 }
00461 }
00462
00463 if (!((*query)->acf->name)) {
00464 free((*query)->acf);
00465 free(*query);
00466 *query = NULL;
00467 return -1;
00468 }
00469
00470 if (asprintf((char **)&((*query)->acf->syntax), "%s(<arg1>[...[,<argN>]])", (*query)->acf->name) < 0) {
00471 ast_log(LOG_WARNING, "asprintf() failed: %s\n", strerror(errno));
00472 (*query)->acf->syntax = NULL;
00473 }
00474
00475 if (!((*query)->acf->syntax)) {
00476 free((char *)(*query)->acf->name);
00477 free((*query)->acf);
00478 free(*query);
00479 *query = NULL;
00480 return -1;
00481 }
00482
00483 res = 0;
00484 (*query)->acf->synopsis = "Runs the referenced query with the specified arguments";
00485 if (!ast_strlen_zero((*query)->sql_read) && !ast_strlen_zero((*query)->sql_write)) {
00486 res = asprintf((char **)&((*query)->acf->desc),
00487 "Runs the following query, as defined in func_odbc.conf, performing\n"
00488 "substitution of the arguments into the query as specified by ${ARG1},\n"
00489 "${ARG2}, ... ${ARGn}. When setting the function, the values are provided\n"
00490 "either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
00491 "\nRead:\n%s\n\nWrite:\n%s\n",
00492 (*query)->sql_read,
00493 (*query)->sql_write);
00494 } else if (!ast_strlen_zero((*query)->sql_read)) {
00495 res = asprintf((char **)&((*query)->acf->desc),
00496 "Runs the following query, as defined in func_odbc.conf, performing\n"
00497 "substitution of the arguments into the query as specified by ${ARG1},\n"
00498 "${ARG2}, ... ${ARGn}. This function may only be read, not set.\n\nSQL:\n%s\n",
00499 (*query)->sql_read);
00500 } else if (!ast_strlen_zero((*query)->sql_write)) {
00501 res = asprintf((char **)&((*query)->acf->desc),
00502 "Runs the following query, as defined in func_odbc.conf, performing\n"
00503 "substitution of the arguments into the query as specified by ${ARG1},\n"
00504 "${ARG2}, ... ${ARGn}. The values are provided either in whole as\n"
00505 "${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
00506 "This function may only be set.\nSQL:\n%s\n",
00507 (*query)->sql_write);
00508 } else {
00509 ast_log(LOG_ERROR, "No SQL was found for func_odbc class '%s'\n", catg);
00510 }
00511
00512 if (res < 0) {
00513 ast_log(LOG_WARNING, "asprintf() failed: %s\n", strerror(errno));
00514 (*query)->acf->desc = NULL;
00515 }
00516
00517
00518 if (!((*query)->acf->desc)) {
00519 free((char *)(*query)->acf->syntax);
00520 free((char *)(*query)->acf->name);
00521 free((*query)->acf);
00522 free(*query);
00523 *query = NULL;
00524 return -1;
00525 }
00526
00527 if (ast_strlen_zero((*query)->sql_read)) {
00528 (*query)->acf->read = NULL;
00529 } else {
00530 (*query)->acf->read = acf_odbc_read;
00531 }
00532
00533 if (ast_strlen_zero((*query)->sql_write)) {
00534 (*query)->acf->write = NULL;
00535 } else {
00536 (*query)->acf->write = acf_odbc_write;
00537 }
00538
00539 return 0;
00540 }
00541
00542 static int free_acf_query(struct acf_odbc_query *query)
00543 {
00544 if (query) {
00545 if (query->acf) {
00546 if (query->acf->name)
00547 free((char *)query->acf->name);
00548 if (query->acf->syntax)
00549 free((char *)query->acf->syntax);
00550 if (query->acf->desc)
00551 free((char *)query->acf->desc);
00552 free(query->acf);
00553 }
00554 free(query);
00555 }
00556 return 0;
00557 }
00558
00559 static int odbc_load_module(void)
00560 {
00561 int res = 0;
00562 struct ast_config *cfg;
00563 char *catg;
00564
00565 AST_LIST_LOCK(&queries);
00566
00567 cfg = ast_config_load(config);
00568 if (!cfg) {
00569 ast_log(LOG_NOTICE, "Unable to load config for func_odbc: %s\n", config);
00570 AST_LIST_UNLOCK(&queries);
00571 return AST_MODULE_LOAD_DECLINE;
00572 }
00573
00574 for (catg = ast_category_browse(cfg, NULL);
00575 catg;
00576 catg = ast_category_browse(cfg, catg)) {
00577 struct acf_odbc_query *query = NULL;
00578
00579 if (init_acf_query(cfg, catg, &query)) {
00580 free_acf_query(query);
00581 } else {
00582 AST_LIST_INSERT_HEAD(&queries, query, list);
00583 ast_custom_function_register(query->acf);
00584 }
00585 }
00586
00587 ast_config_destroy(cfg);
00588 ast_custom_function_register(&escape_function);
00589
00590 AST_LIST_UNLOCK(&queries);
00591 return res;
00592 }
00593
00594 static int odbc_unload_module(void)
00595 {
00596 struct acf_odbc_query *query;
00597
00598 AST_LIST_LOCK(&queries);
00599 while (!AST_LIST_EMPTY(&queries)) {
00600 query = AST_LIST_REMOVE_HEAD(&queries, list);
00601 ast_custom_function_unregister(query->acf);
00602 free_acf_query(query);
00603 }
00604
00605 ast_custom_function_unregister(&escape_function);
00606
00607
00608 AST_LIST_UNLOCK(&queries);
00609 AST_LIST_LOCK(&queries);
00610
00611 AST_LIST_UNLOCK(&queries);
00612 return 0;
00613 }
00614
00615 static int reload(void)
00616 {
00617 int res = 0;
00618 struct ast_config *cfg;
00619 struct acf_odbc_query *oldquery;
00620 char *catg;
00621
00622 AST_LIST_LOCK(&queries);
00623
00624 while (!AST_LIST_EMPTY(&queries)) {
00625 oldquery = AST_LIST_REMOVE_HEAD(&queries, list);
00626 ast_custom_function_unregister(oldquery->acf);
00627 free_acf_query(oldquery);
00628 }
00629
00630 cfg = ast_config_load(config);
00631 if (!cfg) {
00632 ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
00633 goto reload_out;
00634 }
00635
00636 for (catg = ast_category_browse(cfg, NULL);
00637 catg;
00638 catg = ast_category_browse(cfg, catg)) {
00639 struct acf_odbc_query *query = NULL;
00640
00641 if (init_acf_query(cfg, catg, &query)) {
00642 ast_log(LOG_ERROR, "Cannot initialize query %s\n", catg);
00643 } else {
00644 AST_LIST_INSERT_HEAD(&queries, query, list);
00645 ast_custom_function_register(query->acf);
00646 }
00647 }
00648
00649 ast_config_destroy(cfg);
00650 reload_out:
00651 AST_LIST_UNLOCK(&queries);
00652 return res;
00653 }
00654
00655 static int unload_module(void)
00656 {
00657 return odbc_unload_module();
00658 }
00659
00660 static int load_module(void)
00661 {
00662 return odbc_load_module();
00663 }
00664
00665
00666
00667 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "ODBC lookups",
00668 .load = load_module,
00669 .unload = unload_module,
00670 .reload = reload,
00671 );
00672