res_config_odbc: Fix old and new ast_string_field memory leaks.
[asterisk/asterisk.git] / res / res_config_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 1999 - 2010, Digium, Inc.
5  *
6  * Mark Spencer <markster@digium.com>
7  *
8  * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
9  *
10  * See http://www.asterisk.org for more information about
11  * the Asterisk project. Please do not directly contact
12  * any of the maintainers of this project for assistance;
13  * the project provides a web site, mailing lists and IRC
14  * channels for your use.
15  *
16  * This program is free software, distributed under the terms of
17  * the GNU General Public License Version 2. See the LICENSE file
18  * at the top of the source tree.
19  */
20
21 /*! \file
22  *
23  * \brief odbc+odbc plugin for portable configuration engine
24  *
25  * \author Mark Spencer <markster@digium.com>
26  * \author Anthony Minessale II <anthmct@yahoo.com>
27  *
28  * \arg http://www.unixodbc.org
29  */
30
31 /*** MODULEINFO
32         <depend>res_odbc</depend>
33         <support_level>core</support_level>
34  ***/
35
36 #include "asterisk.h"
37
38 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
39
40 #include "asterisk/file.h"
41 #include "asterisk/channel.h"
42 #include "asterisk/pbx.h"
43 #include "asterisk/config.h"
44 #include "asterisk/module.h"
45 #include "asterisk/lock.h"
46 #include "asterisk/res_odbc.h"
47 #include "asterisk/utils.h"
48 #include "asterisk/stringfields.h"
49
50 AST_THREADSTORAGE(sql_buf);
51
52 struct custom_prepare_struct {
53         const char *sql;
54         const char *extra;
55         AST_DECLARE_STRING_FIELDS(
56                 AST_STRING_FIELD(encoding)[256];
57         );
58         const struct ast_variable *fields;
59         unsigned long long skip;
60 };
61
62 #define ENCODE_CHUNK(buffer, s) \
63         do { \
64                 char *eptr = buffer; \
65                 const char *vptr = s; \
66                 for (; *vptr && eptr < buffer + sizeof(buffer); vptr++) { \
67                         if (strchr("^;", *vptr)) { \
68                                 /* We use ^XX, instead of %XX because '%' is a special character in SQL */ \
69                                 snprintf(eptr, buffer + sizeof(buffer) - eptr, "^%02hhX", *vptr); \
70                                 eptr += 3; \
71                         } else { \
72                                 *eptr++ = *vptr; \
73                         } \
74                 } \
75                 if (eptr < buffer + sizeof(buffer)) { \
76                         *eptr = '\0'; \
77                 } else { \
78                         buffer[sizeof(buffer) - 1] = '\0'; \
79                 } \
80         } while(0)
81
82 static void decode_chunk(char *chunk)
83 {
84         for (; *chunk; chunk++) {
85                 if (*chunk == '^' && strchr("0123456789ABCDEF", chunk[1]) && strchr("0123456789ABCDEF", chunk[2])) {
86                         sscanf(chunk + 1, "%02hhX", (unsigned char *)chunk);
87                         memmove(chunk + 1, chunk + 3, strlen(chunk + 3) + 1);
88                 }
89         }
90 }
91
92 static inline int is_text(const struct odbc_cache_columns *column)
93 {
94         return column->type == SQL_CHAR || column->type == SQL_VARCHAR || column->type == SQL_LONGVARCHAR
95                 || column->type == SQL_WCHAR || column->type == SQL_WVARCHAR || column->type == SQL_WLONGVARCHAR;
96 }
97
98 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
99 {
100         int res, x = 1, count = 0;
101         struct custom_prepare_struct *cps = data;
102         const struct ast_variable *field;
103         char encodebuf[1024];
104         SQLHSTMT stmt;
105
106         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
107         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
108                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
109                 return NULL;
110         }
111
112         ast_debug(1, "Skip: %llu; SQL: %s\n", cps->skip, cps->sql);
113
114         res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
115         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
116                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
117                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
118                 return NULL;
119         }
120
121         for (field = cps->fields; field; field = field->next) {
122                 const char *newval = field->value;
123
124                 if ((1LL << count++) & cps->skip) {
125                         ast_debug(1, "Skipping field '%s'='%s' (%llo/%llo)\n", field->name, newval, 1ULL << (count - 1), cps->skip);
126                         continue;
127                 }
128                 ast_debug(1, "Parameter %d ('%s') = '%s'\n", x, field->name, newval);
129                 if (strchr(newval, ';') || strchr(newval, '^')) {
130                         ENCODE_CHUNK(encodebuf, newval);
131                         ast_string_field_set(cps, encoding[x], encodebuf);
132                         newval = cps->encoding[x];
133                 }
134                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
135         }
136
137         if (!ast_strlen_zero(cps->extra)) {
138                 const char *newval = cps->extra;
139                 if (strchr(newval, ';') || strchr(newval, '^')) {
140                         ENCODE_CHUNK(encodebuf, newval);
141                         ast_string_field_set(cps, encoding[x], encodebuf);
142                         newval = cps->encoding[x];
143                 } 
144                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
145         }
146
147         return stmt;
148 }
149
150 /*!
151  * \brief Excute an SQL query and return ast_variable list
152  * \param database
153  * \param table
154  * \param ap list containing one or more field/operator/value set.
155  *
156  * Select database and preform query on table, prepare the sql statement
157  * Sub-in the values to the prepared statement and execute it. Return results
158  * as a ast_variable list.
159  *
160  * \retval var on success
161  * \retval NULL on failure
162 */
163 static struct ast_variable *realtime_odbc(const char *database, const char *table, const struct ast_variable *fields)
164 {
165         struct odbc_obj *obj;
166         SQLHSTMT stmt;
167         char sql[1024];
168         char coltitle[256];
169         char rowdata[2048];
170         char *op;
171         const struct ast_variable *field = fields;
172         char *stringp;
173         char *chunk;
174         SQLSMALLINT collen;
175         int res;
176         int x;
177         struct ast_variable *var=NULL, *prev=NULL;
178         SQLULEN colsize;
179         SQLSMALLINT colcount=0;
180         SQLSMALLINT datatype;
181         SQLSMALLINT decimaldigits;
182         SQLSMALLINT nullable;
183         SQLLEN indicator;
184         struct custom_prepare_struct cps = { .sql = sql, .fields = fields, };
185         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
186
187         if (!table || !field) {
188                 return NULL;
189         }
190
191         obj = ast_odbc_request_obj2(database, connected_flag);
192
193         if (!obj) {
194                 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
195                 return NULL;
196         }
197
198         op = !strchr(field->name, ' ') ? " =" : "";
199         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, field->name, op,
200                 strcasestr(field->name, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
201         while ((field = field->next)) {
202                 op = !strchr(field->name, ' ') ? " =" : "";
203                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", field->name, op,
204                         strcasestr(field->name, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
205         }
206
207         if (ast_string_field_init(&cps, 256)) {
208                 ast_odbc_release_obj(obj);
209                 return NULL;
210         }
211         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
212         ast_string_field_free_memory(&cps);
213
214         if (!stmt) {
215                 ast_odbc_release_obj(obj);
216                 return NULL;
217         }
218
219         res = SQLNumResultCols(stmt, &colcount);
220         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
221                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
222                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
223                 ast_odbc_release_obj(obj);
224                 return NULL;
225         }
226
227         res = SQLFetch(stmt);
228         if (res == SQL_NO_DATA) {
229                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
230                 ast_odbc_release_obj(obj);
231                 return NULL;
232         }
233         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
234                 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
235                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
236                 ast_odbc_release_obj(obj);
237                 return NULL;
238         }
239         for (x = 0; x < colcount; x++) {
240                 rowdata[0] = '\0';
241                 colsize = 0;
242                 collen = sizeof(coltitle);
243                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
244                                         &datatype, &colsize, &decimaldigits, &nullable);
245                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
246                         ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
247                         if (var)
248                                 ast_variables_destroy(var);
249                         ast_odbc_release_obj(obj);
250                         return NULL;
251                 }
252
253                 indicator = 0;
254                 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
255                 if (indicator == SQL_NULL_DATA)
256                         rowdata[0] = '\0';
257                 else if (ast_strlen_zero(rowdata)) {
258                         /* Because we encode the empty string for a NULL, we will encode
259                          * actual empty strings as a string containing a single whitespace. */
260                         ast_copy_string(rowdata, " ", sizeof(rowdata));
261                 }
262
263                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
264                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
265                         if (var)
266                                 ast_variables_destroy(var);
267                         ast_odbc_release_obj(obj);
268                         return NULL;
269                 }
270                 stringp = rowdata;
271                 while (stringp) {
272                         chunk = strsep(&stringp, ";");
273                         if (!ast_strlen_zero(ast_strip(chunk))) {
274                                 if (strchr(chunk, '^')) {
275                                         decode_chunk(chunk);
276                                 }
277                                 if (prev) {
278                                         prev->next = ast_variable_new(coltitle, chunk, "");
279                                         if (prev->next) {
280                                                 prev = prev->next;
281                                         }
282                                 } else {
283                                         prev = var = ast_variable_new(coltitle, chunk, "");
284                                 }
285                         }
286                 }
287         }
288
289         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
290         ast_odbc_release_obj(obj);
291         return var;
292 }
293
294 /*!
295  * \brief Excute an Select query and return ast_config list
296  * \param database
297  * \param table
298  * \param ap list containing one or more field/operator/value set.
299  *
300  * Select database and preform query on table, prepare the sql statement
301  * Sub-in the values to the prepared statement and execute it. 
302  * Execute this prepared query against several ODBC connected databases.
303  * Return results as an ast_config variable.
304  *
305  * \retval var on success
306  * \retval NULL on failure
307 */
308 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, const struct ast_variable *fields)
309 {
310         struct odbc_obj *obj;
311         SQLHSTMT stmt;
312         char sql[1024];
313         char coltitle[256];
314         char rowdata[2048];
315         const char *initfield;
316         char *op;
317         const struct ast_variable *field = fields;
318         char *stringp;
319         char *chunk;
320         SQLSMALLINT collen;
321         int res;
322         int x;
323         struct ast_variable *var=NULL;
324         struct ast_config *cfg=NULL;
325         struct ast_category *cat=NULL;
326         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
327         SQLULEN colsize;
328         SQLSMALLINT colcount=0;
329         SQLSMALLINT datatype;
330         SQLSMALLINT decimaldigits;
331         SQLSMALLINT nullable;
332         SQLLEN indicator;
333         struct custom_prepare_struct cps = { .sql = sql, .fields = fields, };
334
335         if (!table || !field) {
336                 return NULL;
337         }
338
339         obj = ast_odbc_request_obj2(database, connected_flag);
340         if (!obj) {
341                 return NULL;
342         }
343
344         initfield = ast_strdupa(field->name);
345         if ((op = strchr(initfield, ' '))) {
346                 *op = '\0';
347         }
348
349         op = !strchr(field->name, ' ') ? " =" : "";
350         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, field->name, op,
351                 strcasestr(field->name, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
352         while ((field = field->next)) {
353                 op = !strchr(field->name, ' ') ? " =" : "";
354                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", field->name, op,
355                         strcasestr(field->name, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
356         }
357
358         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
359
360         if (ast_string_field_init(&cps, 256)) {
361                 ast_odbc_release_obj(obj);
362                 return NULL;
363         }
364         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
365         ast_string_field_free_memory(&cps);
366
367         if (!stmt) {
368                 ast_odbc_release_obj(obj);
369                 return NULL;
370         }
371
372         res = SQLNumResultCols(stmt, &colcount);
373         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
374                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
375                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
376                 ast_odbc_release_obj(obj);
377                 return NULL;
378         }
379
380         cfg = ast_config_new();
381         if (!cfg) {
382                 ast_log(LOG_WARNING, "Out of memory!\n");
383                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
384                 ast_odbc_release_obj(obj);
385                 return NULL;
386         }
387
388         while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
389                 var = NULL;
390                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
391                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
392                         continue;
393                 }
394                 cat = ast_category_new("","",99999);
395                 if (!cat) {
396                         ast_log(LOG_WARNING, "Out of memory!\n");
397                         continue;
398                 }
399                 for (x=0;x<colcount;x++) {
400                         rowdata[0] = '\0';
401                         colsize = 0;
402                         collen = sizeof(coltitle);
403                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
404                                                 &datatype, &colsize, &decimaldigits, &nullable);
405                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
406                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
407                                 ast_category_destroy(cat);
408                                 goto next_sql_fetch;
409                         }
410
411                         indicator = 0;
412                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
413                         if (indicator == SQL_NULL_DATA)
414                                 continue;
415
416                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
417                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
418                                 ast_category_destroy(cat);
419                                 goto next_sql_fetch;
420                         }
421                         stringp = rowdata;
422                         while (stringp) {
423                                 chunk = strsep(&stringp, ";");
424                                 if (!ast_strlen_zero(ast_strip(chunk))) {
425                                         if (strchr(chunk, '^')) {
426                                                 decode_chunk(chunk);
427                                         }
428                                         if (!strcmp(initfield, coltitle)) {
429                                                 ast_category_rename(cat, chunk);
430                                         }
431                                         var = ast_variable_new(coltitle, chunk, "");
432                                         ast_variable_append(cat, var);
433                                 }
434                         }
435                 }
436                 ast_category_append(cfg, cat);
437 next_sql_fetch:;
438         }
439
440         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
441         ast_odbc_release_obj(obj);
442         return cfg;
443 }
444
445 /*!
446  * \brief Excute an UPDATE query
447  * \param database
448  * \param table
449  * \param keyfield where clause field
450  * \param lookup value of field for where clause
451  * \param ap list containing one or more field/value set(s).
452  *
453  * Update a database table, prepare the sql statement using keyfield and lookup
454  * control the number of records to change. All values to be changed are stored in ap list.
455  * Sub-in the values to the prepared statement and execute it.
456  *
457  * \retval number of rows affected
458  * \retval -1 on failure
459 */
460 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, const struct ast_variable *fields)
461 {
462         struct odbc_obj *obj;
463         SQLHSTMT stmt;
464         char sql[256];
465         SQLLEN rowcount=0;
466         const struct ast_variable *field = fields;
467         int res, count = 0, paramcount = 0;
468         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup, .fields = fields, };
469         struct odbc_cache_tables *tableptr;
470         struct odbc_cache_columns *column = NULL;
471         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
472
473         if (!table || !field || !keyfield) {
474                 return -1;
475         }
476
477         tableptr = ast_odbc_find_table(database, table);
478         if (!(obj = ast_odbc_request_obj2(database, connected_flag))) {
479                 ast_odbc_release_table(tableptr);
480                 return -1;
481         }
482
483         if (tableptr && !ast_odbc_find_column(tableptr, keyfield)) {
484                 ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", keyfield, table, database);
485         }
486
487         snprintf(sql, sizeof(sql), "UPDATE %s SET ", table);
488         while (field) {
489                 if ((tableptr && (column = ast_odbc_find_column(tableptr, field->name))) || count >= 64) {
490                         if (paramcount++) {
491                                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", ");
492                         }
493                         /* NULL test for non-text columns */
494                         if (count < 64 && ast_strlen_zero(field->value) && column->nullable && !is_text(column)) {
495                                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=NULL", field->name);
496                                 cps.skip |= (1LL << count);
497                         } else {
498                                 /* Value is not an empty string, or column is of text type, or we couldn't fit any more into cps.skip (count >= 64 ?!). */
499                                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", field->name);
500                         }
501                 } else { /* the column does not exist in the table */
502                         cps.skip |= (1LL << count);
503                 }
504                 ++count;
505                 field = field->next;
506         }
507         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
508         ast_odbc_release_table(tableptr);
509
510         if (ast_string_field_init(&cps, 256)) {
511                 ast_odbc_release_obj(obj);
512                 return -1;
513         }
514         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
515         ast_string_field_free_memory(&cps);
516
517         if (!stmt) {
518                 ast_odbc_release_obj(obj);
519                 return -1;
520         }
521
522         res = SQLRowCount(stmt, &rowcount);
523         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
524         ast_odbc_release_obj(obj);
525
526         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
527                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
528                 return -1;
529         }
530
531         if (rowcount >= 0) {
532                 return (int) rowcount;
533         }
534
535         return -1;
536 }
537
538 struct update2_prepare_struct {
539         const char *database;
540         const char *table;
541         const struct ast_variable *lookup_fields;
542         const struct ast_variable *update_fields;
543 };
544
545 static SQLHSTMT update2_prepare(struct odbc_obj *obj, void *data)
546 {
547         int res, x = 1, first = 1;
548         struct update2_prepare_struct *ups = data;
549         const struct ast_variable *field;
550         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
551         SQLHSTMT stmt;
552         struct odbc_cache_tables *tableptr = ast_odbc_find_table(ups->database, ups->table);
553
554         if (!sql) {
555                 if (tableptr) {
556                         ast_odbc_release_table(tableptr);
557                 }
558                 return NULL;
559         }
560
561         if (!tableptr) {
562                 ast_log(LOG_ERROR, "Could not retrieve metadata for table '%s@%s'.  Update will fail!\n", ups->table, ups->database);
563                 return NULL;
564         }
565
566         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
567         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
568                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
569                 ast_odbc_release_table(tableptr);
570                 return NULL;
571         }
572
573         ast_str_set(&sql, 0, "UPDATE %s SET ", ups->table);
574
575         for (field = ups->update_fields; field; field = field->next) {
576                 if (ast_odbc_find_column(tableptr, field->name)) {
577                         ast_str_append(&sql, 0, "%s%s=? ", first ? "" : ", ", field->name);
578                         SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(field->name), 0, (void *)field->value, 0, NULL);
579                         first = 0;
580                 } else {
581                         ast_log(LOG_NOTICE, "Not updating column '%s' in '%s@%s' because that column does not exist!\n", field->name, ups->table, ups->database);
582                 }
583         }
584
585         ast_str_append(&sql, 0, "WHERE");
586         first = 1;
587
588         for (field = ups->lookup_fields; field; field = field->next) {
589                 if (!ast_odbc_find_column(tableptr, field->name)) {
590                         ast_log(LOG_ERROR, "One or more of the criteria columns '%s' on '%s@%s' for this update does not exist!\n", field->name, ups->table, ups->database);
591                         ast_odbc_release_table(tableptr);
592                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
593                         return NULL;
594                 }
595                 ast_str_append(&sql, 0, "%s %s=?", first ? "" : " AND", field->name);
596                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(field->value), 0, (void *)field->value, 0, NULL);
597                 first = 0;
598         }
599
600         /* Done with the table metadata */
601         ast_odbc_release_table(tableptr);
602
603         res = SQLPrepare(stmt, (unsigned char *)ast_str_buffer(sql), SQL_NTS);
604         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
605                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", ast_str_buffer(sql));
606                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
607                 return NULL;
608         }
609
610         return stmt;
611 }
612
613 /*!
614  * \brief Execute an UPDATE query
615  * \param database
616  * \param table
617  * \param ap list containing one or more field/value set(s).
618  *
619  * Update a database table, preparing the sql statement from a list of
620  * key/value pairs specified in ap.  The lookup pairs are specified first
621  * and are separated from the update pairs by a sentinel value.
622  * Sub-in the values to the prepared statement and execute it.
623  *
624  * \retval number of rows affected
625  * \retval -1 on failure
626 */
627 static int update2_odbc(const char *database, const char *table, const struct ast_variable *lookup_fields, const struct ast_variable *update_fields)
628 {
629         struct odbc_obj *obj;
630         SQLHSTMT stmt;
631         struct update2_prepare_struct ups = { .database = database, .table = table, .lookup_fields = lookup_fields, .update_fields = update_fields, };
632         struct ast_str *sql;
633         int res;
634         SQLLEN rowcount = 0;
635
636         if (!(obj = ast_odbc_request_obj(database, 0))) {
637                 return -1;
638         }
639
640         if (!(stmt = ast_odbc_prepare_and_execute(obj, update2_prepare, &ups))) {
641                 ast_odbc_release_obj(obj);
642                 return -1;
643         }
644
645         res = SQLRowCount(stmt, &rowcount);
646         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
647         ast_odbc_release_obj(obj);
648
649         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
650                 /* Since only a single thread can access this memory, we can retrieve what would otherwise be lost. */
651                 sql = ast_str_thread_get(&sql_buf, 16);
652                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n", ast_str_buffer(sql));
653                 return -1;
654         }
655
656         if (rowcount >= 0) {
657                 return (int)rowcount;
658         }
659
660         return -1;
661 }
662
663 /*!
664  * \brief Excute an INSERT query
665  * \param database
666  * \param table
667  * \param ap list containing one or more field/value set(s)
668  *
669  * Insert a new record into database table, prepare the sql statement.
670  * All values to be changed are stored in ap list.
671  * Sub-in the values to the prepared statement and execute it.
672  *
673  * \retval number of rows affected
674  * \retval -1 on failure
675 */
676 static int store_odbc(const char *database, const char *table, const struct ast_variable *fields)
677 {
678         struct odbc_obj *obj;
679         SQLHSTMT stmt;
680         char sql[256];
681         char keys[256];
682         char vals[256];
683         SQLLEN rowcount=0;
684         const struct ast_variable *field = fields;
685         int res;
686         struct custom_prepare_struct cps = { .sql = sql, .extra = NULL, .fields = fields, };
687         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
688
689         if (!table || !field) {
690                 return -1;
691         }
692
693         obj = ast_odbc_request_obj2(database, connected_flag);
694         if (!obj) {
695                 return -1;
696         }
697
698         snprintf(keys, sizeof(keys), "%s", field->name);
699         ast_copy_string(vals, "?", sizeof(vals));
700         while ((field = field->next)) {
701                 snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", field->name);
702                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
703         }
704         snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
705
706         if (ast_string_field_init(&cps, 256)) {
707                 ast_odbc_release_obj(obj);
708                 return -1;
709         }
710         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
711         ast_string_field_free_memory(&cps);
712
713         if (!stmt) {
714                 ast_odbc_release_obj(obj);
715                 return -1;
716         }
717
718         res = SQLRowCount(stmt, &rowcount);
719         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
720         ast_odbc_release_obj(obj);
721
722         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
723                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
724                 return -1;
725         }
726
727         if (rowcount >= 0)
728                 return (int)rowcount;
729
730         return -1;
731 }
732
733 /*!
734  * \brief Excute an DELETE query
735  * \param database
736  * \param table
737  * \param keyfield where clause field
738  * \param lookup value of field for where clause
739  * \param ap list containing one or more field/value set(s)
740  *
741  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
742  * control the number of records to change. Additional params to match rows are stored in ap list.
743  * Sub-in the values to the prepared statement and execute it.
744  *
745  * \retval number of rows affected
746  * \retval -1 on failure
747 */
748 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, const struct ast_variable *fields)
749 {
750         struct odbc_obj *obj;
751         SQLHSTMT stmt;
752         char sql[256];
753         SQLLEN rowcount=0;
754         const struct ast_variable *field;
755         int res;
756         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup, .fields = fields, };
757         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
758
759         if (!table) {
760                 return -1;
761         }
762
763         obj = ast_odbc_request_obj2(database, connected_flag);
764         if (!obj) {
765                 return -1;
766         }
767
768         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
769
770         for (field = fields; field; field = field->next) {
771                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", field->name);
772         }
773         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
774
775         if (ast_string_field_init(&cps, 256)) {
776                 ast_odbc_release_obj(obj);
777                 return -1;
778         }
779         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
780         ast_string_field_free_memory(&cps);
781
782         if (!stmt) {
783                 ast_odbc_release_obj(obj);
784                 return -1;
785         }
786
787         res = SQLRowCount(stmt, &rowcount);
788         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
789         ast_odbc_release_obj(obj);
790
791         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
792                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
793                 return -1;
794         }
795
796         if (rowcount >= 0)
797                 return (int)rowcount;
798
799         return -1;
800 }
801
802
803 struct config_odbc_obj {
804         char *sql;
805         unsigned long cat_metric;
806         char category[128];
807         char var_name[128];
808         char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
809         SQLLEN err;
810 };
811
812 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
813 {
814         struct config_odbc_obj *q = data;
815         SQLHSTMT sth;
816         int res;
817
818         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
819         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
820                 ast_verb(4, "Failure in AllocStatement %d\n", res);
821                 return NULL;
822         }
823
824         res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
825         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
826                 ast_verb(4, "Error in PREPARE %d\n", res);
827                 SQLFreeHandle(SQL_HANDLE_STMT, sth);
828                 return NULL;
829         }
830
831         SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
832         SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
833         SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
834         SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
835
836         return sth;
837 }
838
839 static struct ast_config *config_odbc(const char *database, const char *table, const char *file, struct ast_config *cfg, struct ast_flags flags, const char *sugg_incl, const char *who_asked)
840 {
841         struct ast_variable *new_v;
842         struct ast_category *cur_cat;
843         int res = 0;
844         struct odbc_obj *obj;
845         char sqlbuf[1024] = "";
846         char *sql = sqlbuf;
847         size_t sqlleft = sizeof(sqlbuf);
848         unsigned int last_cat_metric = 0;
849         SQLSMALLINT rowcount = 0;
850         SQLHSTMT stmt;
851         char last[128] = "";
852         struct config_odbc_obj q;
853         struct ast_flags loader_flags = { 0 };
854         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
855
856         memset(&q, 0, sizeof(q));
857
858         if (!file || !strcmp (file, "res_config_odbc.conf"))
859                 return NULL;            /* cant configure myself with myself ! */
860
861         obj = ast_odbc_request_obj2(database, connected_flag);
862         if (!obj)
863                 return NULL;
864
865         ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
866         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
867         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
868         q.sql = sqlbuf;
869
870         stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
871
872         if (!stmt) {
873                 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
874                 ast_odbc_release_obj(obj);
875                 return NULL;
876         }
877
878         res = SQLNumResultCols(stmt, &rowcount);
879
880         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
881                 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
882                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
883                 ast_odbc_release_obj(obj);
884                 return NULL;
885         }
886
887         if (!rowcount) {
888                 ast_log(LOG_NOTICE, "found nothing\n");
889                 ast_odbc_release_obj(obj);
890                 return cfg;
891         }
892
893         cur_cat = ast_config_get_current_category(cfg);
894
895         while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
896                 if (!strcmp (q.var_name, "#include")) {
897                         if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
898                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
899                                 ast_odbc_release_obj(obj);
900                                 return NULL;
901                         }
902                         continue;
903                 } 
904                 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
905                         cur_cat = ast_category_new(q.category, "", 99999);
906                         if (!cur_cat) {
907                                 ast_log(LOG_WARNING, "Out of memory!\n");
908                                 break;
909                         }
910                         strcpy(last, q.category);
911                         last_cat_metric = q.cat_metric;
912                         ast_category_append(cfg, cur_cat);
913                 }
914
915                 new_v = ast_variable_new(q.var_name, q.var_val, "");
916                 ast_variable_append(cur_cat, new_v);
917         }
918
919         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
920         ast_odbc_release_obj(obj);
921         return cfg;
922 }
923
924 #define warn_length(col, size)  ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' is not long enough to contain realtime data (needs %d)\n", table, database, col->name, size)
925 #define warn_type(col, type)    ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' is of the incorrect type (%d) to contain the required realtime data\n", table, database, col->name, col->type)
926
927 static int require_odbc(const char *database, const char *table, va_list ap)
928 {
929         struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
930         struct odbc_cache_columns *col;
931         char *elm;
932         int type, size;
933
934         if (!tableptr) {
935                 return -1;
936         }
937
938         while ((elm = va_arg(ap, char *))) {
939                 type = va_arg(ap, require_type);
940                 size = va_arg(ap, int);
941                 /* Check if the field matches the criteria */
942                 AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
943                         if (strcmp(col->name, elm) == 0) {
944                                 /* Type check, first.  Some fields are more particular than others */
945                                 switch (col->type) {
946                                 case SQL_CHAR:
947                                 case SQL_VARCHAR:
948                                 case SQL_LONGVARCHAR:
949 #ifdef HAVE_ODBC_WCHAR
950                                 case SQL_WCHAR:
951                                 case SQL_WVARCHAR:
952                                 case SQL_WLONGVARCHAR:
953 #endif
954                                 case SQL_BINARY:
955                                 case SQL_VARBINARY:
956                                 case SQL_LONGVARBINARY:
957                                 case SQL_GUID:
958 #define CHECK_SIZE(n) \
959                                                 if (col->size < n) {      \
960                                                         warn_length(col, n);  \
961                                                 }                         \
962                                                 break;
963                                         switch (type) {
964                                         case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
965                                         case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
966                                         case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
967                                         case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
968                                         case RQ_UINTEGER3:                /*    16777215 */
969                                         case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
970                                         case RQ_DATE:                     /*  2008-06-09 */
971                                         case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
972                                         case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
973                                         case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
974                                         case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
975                                         case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
976                                         case RQ_FLOAT:
977                                         case RQ_CHAR:      CHECK_SIZE(size)
978                                         }
979 #undef CHECK_SIZE
980                                         break;
981                                 case SQL_TYPE_DATE:
982                                         if (type != RQ_DATE) {
983                                                 warn_type(col, type);
984                                         }
985                                         break;
986                                 case SQL_TYPE_TIMESTAMP:
987                                 case SQL_TIMESTAMP:
988                                         if (type != RQ_DATE && type != RQ_DATETIME) {
989                                                 warn_type(col, type);
990                                         }
991                                         break;
992                                 case SQL_BIT:
993                                         warn_length(col, size);
994                                         break;
995 #define WARN_TYPE_OR_LENGTH(n)  \
996                                                 if (!ast_rq_is_int(type)) {  \
997                                                         warn_type(col, type);    \
998                                                 } else {                     \
999                                                         warn_length(col, n);  \
1000                                                 }
1001                                 case SQL_TINYINT:
1002                                         if (type != RQ_UINTEGER1) {
1003                                                 WARN_TYPE_OR_LENGTH(size)
1004                                         }
1005                                         break;
1006                                 case SQL_C_STINYINT:
1007                                         if (type != RQ_INTEGER1) {
1008                                                 WARN_TYPE_OR_LENGTH(size)
1009                                         }
1010                                         break;
1011                                 case SQL_C_USHORT:
1012                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
1013                                                 WARN_TYPE_OR_LENGTH(size)
1014                                         }
1015                                         break;
1016                                 case SQL_SMALLINT:
1017                                 case SQL_C_SSHORT:
1018                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
1019                                                 WARN_TYPE_OR_LENGTH(size)
1020                                         }
1021                                         break;
1022                                 case SQL_C_ULONG:
1023                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1024                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1025                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1026                                                 type != RQ_INTEGER4) {
1027                                                 WARN_TYPE_OR_LENGTH(size)
1028                                         }
1029                                         break;
1030                                 case SQL_INTEGER:
1031                                 case SQL_C_SLONG:
1032                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1033                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1034                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1035                                                 type != RQ_INTEGER4) {
1036                                                 WARN_TYPE_OR_LENGTH(size)
1037                                         }
1038                                         break;
1039                                 case SQL_C_UBIGINT:
1040                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1041                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1042                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1043                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1044                                                 type != RQ_INTEGER8) {
1045                                                 WARN_TYPE_OR_LENGTH(size)
1046                                         }
1047                                         break;
1048                                 case SQL_BIGINT:
1049                                 case SQL_C_SBIGINT:
1050                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1051                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1052                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1053                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1054                                                 type != RQ_INTEGER8) {
1055                                                 WARN_TYPE_OR_LENGTH(size)
1056                                         }
1057                                         break;
1058 #undef WARN_TYPE_OR_LENGTH
1059                                 case SQL_NUMERIC:
1060                                 case SQL_DECIMAL:
1061                                 case SQL_FLOAT:
1062                                 case SQL_REAL:
1063                                 case SQL_DOUBLE:
1064                                         if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
1065                                                 warn_type(col, type);
1066                                         }
1067                                         break;
1068                                 default:
1069                                         ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
1070                                 }
1071                                 break;
1072                         }
1073                 }
1074                 if (!col) {
1075                         ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
1076                 }
1077         }
1078         AST_RWLIST_UNLOCK(&tableptr->columns);
1079         return 0;
1080 }
1081 #undef warn_length
1082 #undef warn_type
1083
1084 static int unload_odbc(const char *a, const char *b)
1085 {
1086         return ast_odbc_clear_cache(a, b);
1087 }
1088
1089 static struct ast_config_engine odbc_engine = {
1090         .name = "odbc",
1091         .load_func = config_odbc,
1092         .realtime_func = realtime_odbc,
1093         .realtime_multi_func = realtime_multi_odbc,
1094         .store_func = store_odbc,
1095         .destroy_func = destroy_odbc,
1096         .update_func = update_odbc,
1097         .update2_func = update2_odbc,
1098         .require_func = require_odbc,
1099         .unload_func = unload_odbc,
1100 };
1101
1102 static int unload_module (void)
1103 {
1104         ast_config_engine_deregister(&odbc_engine);
1105
1106         ast_verb(1, "res_config_odbc unloaded.\n");
1107         return 0;
1108 }
1109
1110 static int load_module (void)
1111 {
1112         ast_config_engine_register(&odbc_engine);
1113         ast_verb(1, "res_config_odbc loaded.\n");
1114         return 0;
1115 }
1116
1117 static int reload_module(void)
1118 {
1119         return 0;
1120 }
1121
1122 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "Realtime ODBC configuration",
1123                 .load = load_module,
1124                 .unload = unload_module,
1125                 .reload = reload_module,
1126                 .load_pri = AST_MODPRI_REALTIME_DRIVER,
1127                 );