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