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