1 /*
2 ** Zabbix
3 ** Copyright (C) 2001-2021 Zabbix SIA
4 **
5 ** This program is free software; you can redistribute it and/or modify
6 ** it under the terms of the GNU General Public License as published by
7 ** the Free Software Foundation; either version 2 of the License, or
8 ** (at your option) any later version.
9 **
10 ** This program is distributed in the hope that it will be useful,
11 ** but WITHOUT ANY WARRANTY; without even the implied warranty of
12 ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 ** GNU General Public License for more details.
14 **
15 ** You should have received a copy of the GNU General Public License
16 ** along with this program; if not, write to the Free Software
17 ** Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 **/
19
20 #include "common.h"
21
22 #include "zbxdb.h"
23
24 #if defined(HAVE_MYSQL)
25 # include "mysql.h"
26 # include "errmsg.h"
27 # include "mysqld_error.h"
28 #elif defined(HAVE_ORACLE)
29 # include "oci.h"
30 #elif defined(HAVE_POSTGRESQL)
31 # include <libpq-fe.h>
32 #elif defined(HAVE_SQLITE3)
33 # include <sqlite3.h>
34 #endif
35
36 #include "dbschema.h"
37 #include "log.h"
38 #if defined(HAVE_SQLITE3)
39 # include "mutexs.h"
40 #endif
41
42 struct zbx_db_result
43 {
44 #if defined(HAVE_MYSQL)
45 MYSQL_RES *result;
46 #elif defined(HAVE_ORACLE)
47 OCIStmt *stmthp; /* the statement handle for select operations */
48 int ncolumn;
49 DB_ROW values;
50 ub4 *values_alloc;
51 OCILobLocator **clobs;
52 #elif defined(HAVE_POSTGRESQL)
53 PGresult *pg_result;
54 int row_num;
55 int fld_num;
56 int cursor;
57 DB_ROW values;
58 #elif defined(HAVE_SQLITE3)
59 int curow;
60 char **data;
61 int nrow;
62 int ncolumn;
63 DB_ROW values;
64 #endif
65 };
66
67 static int txn_level = 0; /* transaction level, nested transactions are not supported */
68 static int txn_error = ZBX_DB_OK; /* failed transaction */
69 static int txn_end_error = ZBX_DB_OK; /* transaction result */
70
71 static char *last_db_strerror = NULL; /* last database error message */
72
73 extern int CONFIG_LOG_SLOW_QUERIES;
74
75 static int db_auto_increment;
76
77 #if defined(HAVE_MYSQL)
78 static MYSQL *conn = NULL;
79 #elif defined(HAVE_ORACLE)
80 #include "zbxalgo.h"
81
82 typedef struct
83 {
84 OCIEnv *envhp;
85 OCIError *errhp;
86 OCISvcCtx *svchp;
87 OCIServer *srvhp;
88 OCIStmt *stmthp; /* the statement handle for execute operations */
89 zbx_vector_ptr_t db_results;
90 }
91 zbx_oracle_db_handle_t;
92
93 static zbx_oracle_db_handle_t oracle;
94
95 static ub4 OCI_DBserver_status(void);
96
97 #elif defined(HAVE_POSTGRESQL)
98 static PGconn *conn = NULL;
99 static unsigned int ZBX_PG_BYTEAOID = 0;
100 static int ZBX_PG_SVERSION = 0, ZBX_TSDB_VERSION = -1;
101 char ZBX_PG_ESCAPE_BACKSLASH = 1;
102 #elif defined(HAVE_SQLITE3)
103 static sqlite3 *conn = NULL;
104 static zbx_mutex_t sqlite_access = ZBX_MUTEX_NULL;
105 #endif
106
107 #if defined(HAVE_ORACLE)
108 static void OCI_DBclean_result(DB_RESULT result);
109 #endif
110
zbx_db_errlog(zbx_err_codes_t zbx_errno,int db_errno,const char * db_error,const char * context)111 static void zbx_db_errlog(zbx_err_codes_t zbx_errno, int db_errno, const char *db_error, const char *context)
112 {
113 char *s;
114
115 if (NULL != db_error)
116 last_db_strerror = zbx_strdup(last_db_strerror, db_error);
117 else
118 last_db_strerror = zbx_strdup(last_db_strerror, "");
119
120 switch (zbx_errno)
121 {
122 case ERR_Z3001:
123 s = zbx_dsprintf(NULL, "connection to database '%s' failed: [%d] %s", context, db_errno,
124 last_db_strerror);
125 break;
126 case ERR_Z3002:
127 s = zbx_dsprintf(NULL, "cannot create database '%s': [%d] %s", context, db_errno,
128 last_db_strerror);
129 break;
130 case ERR_Z3003:
131 s = zbx_strdup(NULL, "no connection to the database");
132 break;
133 case ERR_Z3004:
134 s = zbx_dsprintf(NULL, "cannot close database: [%d] %s", db_errno, last_db_strerror);
135 break;
136 case ERR_Z3005:
137 s = zbx_dsprintf(NULL, "query failed: [%d] %s [%s]", db_errno, last_db_strerror, context);
138 break;
139 case ERR_Z3006:
140 s = zbx_dsprintf(NULL, "fetch failed: [%d] %s", db_errno, last_db_strerror);
141 break;
142 case ERR_Z3007:
143 s = zbx_dsprintf(NULL, "query failed: [%d] %s", db_errno, last_db_strerror);
144 break;
145 default:
146 s = zbx_strdup(NULL, "unknown error");
147 }
148
149 zabbix_log(LOG_LEVEL_ERR, "[Z%04d] %s", (int)zbx_errno, s);
150
151 zbx_free(s);
152 }
153
154 /******************************************************************************
155 * *
156 * Function: zbx_db_last_strerr *
157 * *
158 * Purpose: get last error set by database *
159 * *
160 * Return value: last database error message *
161 * *
162 ******************************************************************************/
163
zbx_db_last_strerr(void)164 const char *zbx_db_last_strerr(void)
165 {
166 return last_db_strerror;
167 }
168
169 #if defined(HAVE_ORACLE)
zbx_oci_error(sword status,sb4 * err)170 static const char *zbx_oci_error(sword status, sb4 *err)
171 {
172 static char errbuf[512];
173 sb4 errcode, *perrcode;
174
175 perrcode = (NULL == err ? &errcode : err);
176
177 errbuf[0] = '\0';
178 *perrcode = 0;
179
180 switch (status)
181 {
182 case OCI_SUCCESS_WITH_INFO:
183 OCIErrorGet((dvoid *)oracle.errhp, (ub4)1, (text *)NULL, perrcode,
184 (text *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
185 break;
186 case OCI_NEED_DATA:
187 zbx_snprintf(errbuf, sizeof(errbuf), "%s", "OCI_NEED_DATA");
188 break;
189 case OCI_NO_DATA:
190 zbx_snprintf(errbuf, sizeof(errbuf), "%s", "OCI_NODATA");
191 break;
192 case OCI_ERROR:
193 OCIErrorGet((dvoid *)oracle.errhp, (ub4)1, (text *)NULL, perrcode,
194 (text *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
195 break;
196 case OCI_INVALID_HANDLE:
197 zbx_snprintf(errbuf, sizeof(errbuf), "%s", "OCI_INVALID_HANDLE");
198 break;
199 case OCI_STILL_EXECUTING:
200 zbx_snprintf(errbuf, sizeof(errbuf), "%s", "OCI_STILL_EXECUTING");
201 break;
202 case OCI_CONTINUE:
203 zbx_snprintf(errbuf, sizeof(errbuf), "%s", "OCI_CONTINUE");
204 break;
205 }
206
207 zbx_rtrim(errbuf, ZBX_WHITESPACE);
208
209 return errbuf;
210 }
211
212 /******************************************************************************
213 * *
214 * Function: OCI_handle_sql_error *
215 * *
216 * Purpose: handles Oracle prepare/bind/execute/select operation error *
217 * *
218 * Parameters: zerrcode - [IN] the Zabbix errorcode for the failed database *
219 * operation *
220 * oci_error - [IN] the return code from failed Oracle operation *
221 * sql - [IN] the failed sql statement (can be NULL) *
222 * *
223 * Return value: ZBX_DB_DOWN - database connection is down *
224 * ZBX_DB_FAIL - otherwise *
225 * *
226 * Comments: This function logs the error description and checks the *
227 * database connection status. *
228 * *
229 ******************************************************************************/
OCI_handle_sql_error(int zerrcode,sword oci_error,const char * sql)230 static int OCI_handle_sql_error(int zerrcode, sword oci_error, const char *sql)
231 {
232 sb4 errcode;
233 int ret = ZBX_DB_DOWN;
234
235 zbx_db_errlog(zerrcode, oci_error, zbx_oci_error(oci_error, &errcode), sql);
236
237 /* after ORA-02396 (and consequent ORA-01012) errors the OCI_SERVER_NORMAL server status is still returned */
238 switch (errcode)
239 {
240 case 1012: /* ORA-01012: not logged on */
241 case 2396: /* ORA-02396: exceeded maximum idle time */
242 goto out;
243 }
244
245 if (OCI_SERVER_NORMAL == OCI_DBserver_status())
246 ret = ZBX_DB_FAIL;
247 out:
248 return ret;
249 }
250
251 #endif /* HAVE_ORACLE */
252
253 #ifdef HAVE_POSTGRESQL
zbx_postgresql_error(char ** error,const PGresult * pg_result)254 static void zbx_postgresql_error(char **error, const PGresult *pg_result)
255 {
256 char *result_error_msg;
257 size_t error_alloc = 0, error_offset = 0;
258
259 zbx_snprintf_alloc(error, &error_alloc, &error_offset, "%s", PQresStatus(PQresultStatus(pg_result)));
260
261 result_error_msg = PQresultErrorMessage(pg_result);
262
263 if ('\0' != *result_error_msg)
264 zbx_snprintf_alloc(error, &error_alloc, &error_offset, ":%s", result_error_msg);
265 }
266 #endif /*HAVE_POSTGRESQL*/
267
268 __zbx_attr_format_printf(1, 2)
zbx_db_execute(const char * fmt,...)269 static int zbx_db_execute(const char *fmt, ...)
270 {
271 va_list args;
272 int ret;
273
274 va_start(args, fmt);
275 ret = zbx_db_vexecute(fmt, args);
276 va_end(args);
277
278 return ret;
279 }
280
281 __zbx_attr_format_printf(1, 2)
zbx_db_select(const char * fmt,...)282 static DB_RESULT zbx_db_select(const char *fmt, ...)
283 {
284 va_list args;
285 DB_RESULT result;
286
287 va_start(args, fmt);
288 result = zbx_db_vselect(fmt, args);
289 va_end(args);
290
291 return result;
292 }
293
294 #if defined(HAVE_MYSQL)
is_recoverable_mysql_error(void)295 static int is_recoverable_mysql_error(void)
296 {
297 switch (mysql_errno(conn))
298 {
299 case CR_CONN_HOST_ERROR:
300 case CR_SERVER_GONE_ERROR:
301 case CR_CONNECTION_ERROR:
302 case CR_SERVER_LOST:
303 case CR_UNKNOWN_HOST:
304 case CR_COMMANDS_OUT_OF_SYNC:
305 case ER_SERVER_SHUTDOWN:
306 case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
307 case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
308 case ER_TABLEACCESS_DENIED_ERROR: /* user without some privilege */
309 case ER_UNKNOWN_ERROR:
310 case ER_UNKNOWN_COM_ERROR:
311 case ER_LOCK_DEADLOCK:
312 case ER_LOCK_WAIT_TIMEOUT:
313 #ifdef CR_SSL_CONNECTION_ERROR
314 case CR_SSL_CONNECTION_ERROR:
315 #endif
316 #ifdef ER_CONNECTION_KILLED
317 case ER_CONNECTION_KILLED:
318 #endif
319 return SUCCEED;
320 }
321
322 return FAIL;
323 }
324 #elif defined(HAVE_POSTGRESQL)
is_recoverable_postgresql_error(const PGconn * pg_conn,const PGresult * pg_result)325 static int is_recoverable_postgresql_error(const PGconn *pg_conn, const PGresult *pg_result)
326 {
327 if (CONNECTION_OK != PQstatus(pg_conn))
328 return SUCCEED;
329
330 if (0 == zbx_strcmp_null(PQresultErrorField(pg_result, PG_DIAG_SQLSTATE), "40P01"))
331 return SUCCEED;
332
333 return FAIL;
334 }
335 #endif
336
337 /******************************************************************************
338 * *
339 * Function: zbx_db_init_autoincrement_options *
340 * *
341 * Purpose: specify the autoincrement options during db connect *
342 * *
343 ******************************************************************************/
zbx_db_init_autoincrement_options(void)344 void zbx_db_init_autoincrement_options(void)
345 {
346 db_auto_increment = 1;
347 }
348
349 /******************************************************************************
350 * *
351 * Function: zbx_db_connect *
352 * *
353 * Purpose: connect to the database *
354 * *
355 * Return value: ZBX_DB_OK - successfully connected *
356 * ZBX_DB_DOWN - database is down *
357 * ZBX_DB_FAIL - failed to connect *
358 * *
359 ******************************************************************************/
zbx_db_connect(char * host,char * user,char * password,char * dbname,char * dbschema,char * dbsocket,int port,char * tls_connect,char * cert,char * key,char * ca,char * cipher,char * cipher_13)360 int zbx_db_connect(char *host, char *user, char *password, char *dbname, char *dbschema, char *dbsocket, int port,
361 char *tls_connect, char *cert, char *key, char *ca, char *cipher, char *cipher_13)
362 {
363 int ret = ZBX_DB_OK, last_txn_error, last_txn_level;
364 #if defined(HAVE_MYSQL)
365 #if LIBMYSQL_VERSION_ID >= 80000 /* my_bool type is removed in MySQL 8.0 */
366 bool mysql_reconnect = 1;
367 #else
368 my_bool mysql_reconnect = 1;
369 #endif
370 #elif defined(HAVE_ORACLE)
371 char *connect = NULL;
372 sword err = OCI_SUCCESS;
373 static ub2 csid = 0;
374 #elif defined(HAVE_POSTGRESQL)
375 # define ZBX_DB_MAX_PARAMS 9
376
377 int rc;
378 char *cport = NULL;
379 DB_RESULT result;
380 DB_ROW row;
381 const char *keywords[ZBX_DB_MAX_PARAMS + 1];
382 const char *values[ZBX_DB_MAX_PARAMS + 1];
383 unsigned int i = 0;
384 #elif defined(HAVE_SQLITE3)
385 char *p, *path = NULL;
386 #endif
387
388 #ifndef HAVE_MYSQL
389 ZBX_UNUSED(dbsocket);
390 #endif
391 /* Allow executing statements during a connection initialization. Make sure to mark transaction as failed. */
392 if (0 != txn_level)
393 txn_error = ZBX_DB_DOWN;
394
395 last_txn_error = txn_error;
396 last_txn_level = txn_level;
397
398 txn_error = ZBX_DB_OK;
399 txn_level = 0;
400
401 #if defined(HAVE_MYSQL)
402 ZBX_UNUSED(dbschema);
403
404 if (NULL == (conn = mysql_init(NULL)))
405 {
406 zabbix_log(LOG_LEVEL_CRIT, "cannot allocate or initialize MYSQL database connection object");
407 exit(EXIT_FAILURE);
408 }
409
410 if (1 == db_auto_increment)
411 {
412 /* Shadow global auto_increment variables. */
413 /* Setting session variables requires special permissions in MySQL 8.0.14-8.0.17. */
414
415 if (0 != MYSQL_OPTIONS(conn, MYSQL_INIT_COMMAND, MYSQL_OPTIONS_ARGS_VOID_CAST
416 "set @@session.auto_increment_increment=1"))
417 {
418 zabbix_log(LOG_LEVEL_ERR, "Cannot set auto_increment_increment option.");
419 ret = ZBX_DB_FAIL;
420 }
421
422 if (ZBX_DB_OK == ret && 0 != MYSQL_OPTIONS(conn, MYSQL_INIT_COMMAND, MYSQL_OPTIONS_ARGS_VOID_CAST
423 "set @@session.auto_increment_offset=1"))
424 {
425 zabbix_log(LOG_LEVEL_ERR, "Cannot set auto_increment_offset option.");
426 ret = ZBX_DB_FAIL;
427 }
428 }
429
430 #if defined(HAVE_MYSQL_TLS)
431 if (ZBX_DB_OK == ret && NULL != tls_connect)
432 {
433 unsigned int mysql_tls_mode;
434
435 if (0 == strcmp(tls_connect, ZBX_DB_TLS_CONNECT_REQUIRED_TXT))
436 mysql_tls_mode = SSL_MODE_REQUIRED;
437 else if (0 == strcmp(tls_connect, ZBX_DB_TLS_CONNECT_VERIFY_CA_TXT))
438 mysql_tls_mode = SSL_MODE_VERIFY_CA;
439 else
440 mysql_tls_mode = SSL_MODE_VERIFY_IDENTITY;
441
442 if (0 != mysql_options(conn, MYSQL_OPT_SSL_MODE, &mysql_tls_mode))
443 {
444 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_MODE option.");
445 ret = ZBX_DB_FAIL;
446 }
447 }
448
449 if (ZBX_DB_OK == ret && NULL != ca && 0 != mysql_options(conn, MYSQL_OPT_SSL_CA, ca))
450 {
451 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_CA option.");
452 ret = ZBX_DB_FAIL;
453 }
454
455 if (ZBX_DB_OK == ret && NULL != key && 0 != mysql_options(conn, MYSQL_OPT_SSL_KEY, key))
456 {
457 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_KEY option.");
458 ret = ZBX_DB_FAIL;
459 }
460
461 if (ZBX_DB_OK == ret && NULL != cert && 0 != mysql_options(conn, MYSQL_OPT_SSL_CERT, cert))
462 {
463 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_CERT option.");
464 ret = ZBX_DB_FAIL;
465 }
466
467 if (ZBX_DB_OK == ret && NULL != cipher && 0 != mysql_options(conn, MYSQL_OPT_SSL_CIPHER, cipher))
468 {
469 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_CIPHER option.");
470 ret = ZBX_DB_FAIL;
471 }
472 #if defined(HAVE_MYSQL_TLS_CIPHERSUITES)
473 if (ZBX_DB_OK == ret && NULL != cipher_13 && 0 != mysql_options(conn, MYSQL_OPT_TLS_CIPHERSUITES, cipher_13))
474 {
475 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_TLS_CIPHERSUITES option.");
476 ret = ZBX_DB_FAIL;
477 }
478 #else
479 ZBX_UNUSED(cipher_13);
480 #endif
481 #elif defined(HAVE_MARIADB_TLS)
482 ZBX_UNUSED(cipher_13);
483
484 if (ZBX_DB_OK == ret && NULL != tls_connect)
485 {
486 if (0 == strcmp(tls_connect, ZBX_DB_TLS_CONNECT_REQUIRED_TXT))
487 {
488 my_bool enforce_tls = 1;
489
490 if (0 != mysql_optionsv(conn, MYSQL_OPT_SSL_ENFORCE, (void *)&enforce_tls))
491 {
492 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_ENFORCE option.");
493 ret = ZBX_DB_FAIL;
494 }
495 }
496 else
497 {
498 my_bool verify = 1;
499
500 if (0 != mysql_optionsv(conn, MYSQL_OPT_SSL_VERIFY_SERVER_CERT, (void *)&verify))
501 {
502 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_VERIFY_SERVER_CERT option.");
503 ret = ZBX_DB_FAIL;
504 }
505 }
506 }
507
508 if (ZBX_DB_OK == ret && NULL != ca && 0 != mysql_optionsv(conn, MYSQL_OPT_SSL_CA, ca))
509 {
510 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_CA option.");
511 ret = ZBX_DB_FAIL;
512 }
513
514 if (ZBX_DB_OK == ret && NULL != key && 0 != mysql_optionsv(conn, MYSQL_OPT_SSL_KEY, key))
515 {
516 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_KEY option.");
517 ret = ZBX_DB_FAIL;
518 }
519
520 if (ZBX_DB_OK == ret && NULL != cert && 0 != mysql_optionsv(conn, MYSQL_OPT_SSL_CERT, cert))
521 {
522 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_CERT option.");
523 ret = ZBX_DB_FAIL;
524 }
525
526 if (ZBX_DB_OK == ret && NULL != cipher && 0 != mysql_optionsv(conn, MYSQL_OPT_SSL_CIPHER, cipher))
527 {
528 zabbix_log(LOG_LEVEL_ERR, "Cannot set MYSQL_OPT_SSL_CIPHER option.");
529 ret = ZBX_DB_FAIL;
530 }
531 #else
532 ZBX_UNUSED(tls_connect);
533 ZBX_UNUSED(cert);
534 ZBX_UNUSED(key);
535 ZBX_UNUSED(ca);
536 ZBX_UNUSED(cipher);
537 ZBX_UNUSED(cipher_13);
538 #endif
539 if (ZBX_DB_OK == ret &&
540 NULL == mysql_real_connect(conn, host, user, password, dbname, port, dbsocket,
541 CLIENT_MULTI_STATEMENTS))
542 {
543 zbx_db_errlog(ERR_Z3001, mysql_errno(conn), mysql_error(conn), dbname);
544 ret = ZBX_DB_FAIL;
545 }
546
547 /* The RECONNECT option setting is placed here, AFTER the connection */
548 /* is made, due to a bug in MySQL versions prior to 5.1.6 where it */
549 /* reset the options value to the default, regardless of what it was */
550 /* set to prior to the connection. MySQL allows changing connection */
551 /* options on an open connection, so setting it here is safe. */
552
553 if (ZBX_DB_OK == ret && 0 != mysql_options(conn, MYSQL_OPT_RECONNECT, &mysql_reconnect))
554 zabbix_log(LOG_LEVEL_WARNING, "Cannot set MySQL reconnect option.");
555
556 /* in contrast to "set names utf8" results of this call will survive auto-reconnects */
557 if (ZBX_DB_OK == ret && 0 != mysql_set_character_set(conn, "utf8"))
558 zabbix_log(LOG_LEVEL_WARNING, "cannot set MySQL character set to \"utf8\"");
559
560 if (ZBX_DB_OK == ret && 0 != mysql_autocommit(conn, 1))
561 {
562 zbx_db_errlog(ERR_Z3001, mysql_errno(conn), mysql_error(conn), dbname);
563 ret = ZBX_DB_FAIL;
564 }
565
566 if (ZBX_DB_OK == ret && 0 != mysql_select_db(conn, dbname))
567 {
568 zbx_db_errlog(ERR_Z3001, mysql_errno(conn), mysql_error(conn), dbname);
569 ret = ZBX_DB_FAIL;
570 }
571
572 if (ZBX_DB_FAIL == ret && SUCCEED == is_recoverable_mysql_error())
573 ret = ZBX_DB_DOWN;
574
575 #elif defined(HAVE_ORACLE)
576 ZBX_UNUSED(dbschema);
577 ZBX_UNUSED(tls_connect);
578 ZBX_UNUSED(cert);
579 ZBX_UNUSED(key);
580 ZBX_UNUSED(ca);
581 ZBX_UNUSED(cipher);
582 ZBX_UNUSED(cipher_13);
583
584 memset(&oracle, 0, sizeof(oracle));
585
586 zbx_vector_ptr_create(&oracle.db_results);
587
588 /* connection string format: [//]host[:port][/service name] */
589
590 if ('\0' != *host)
591 {
592 connect = zbx_strdcatf(connect, "//%s", host);
593 if (0 != port)
594 connect = zbx_strdcatf(connect, ":%d", port);
595 if (NULL != dbname && '\0' != *dbname)
596 connect = zbx_strdcatf(connect, "/%s", dbname);
597 }
598 else
599 ret = ZBX_DB_FAIL;
600
601 while (ZBX_DB_OK == ret)
602 {
603 /* initialize environment */
604 if (OCI_SUCCESS == (err = OCIEnvNlsCreate((OCIEnv **)&oracle.envhp, (ub4)OCI_DEFAULT, (dvoid *)0,
605 (dvoid * (*)(dvoid *,size_t))0, (dvoid * (*)(dvoid *, dvoid *, size_t))0,
606 (void (*)(dvoid *, dvoid *))0, (size_t)0, (dvoid **)0, csid, csid)))
607 {
608 if (0 != csid)
609 break; /* environment with UTF8 character set successfully created */
610
611 /* try to find out the id of UTF8 character set */
612 if (0 == (csid = OCINlsCharSetNameToId(oracle.envhp, (const oratext *)"UTF8")))
613 {
614 zabbix_log(LOG_LEVEL_WARNING, "Cannot find out the ID of \"UTF8\" character set."
615 " Relying on current \"NLS_LANG\" settings.");
616 break; /* use default environment with character set derived from NLS_LANG */
617 }
618
619 /* get rid of this environment to create a better one on the next iteration */
620 OCIHandleFree((dvoid *)oracle.envhp, OCI_HTYPE_ENV);
621 oracle.envhp = NULL;
622 }
623 else
624 {
625 zbx_db_errlog(ERR_Z3001, err, zbx_oci_error(err, NULL), connect);
626 ret = ZBX_DB_FAIL;
627 }
628 }
629
630 if (ZBX_DB_OK == ret)
631 {
632 /* allocate an error handle */
633 (void)OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&oracle.errhp, OCI_HTYPE_ERROR,
634 (size_t)0, (dvoid **)0);
635
636 /* get the session */
637 err = OCILogon2(oracle.envhp, oracle.errhp, &oracle.svchp,
638 (text *)user, (ub4)(NULL != user ? strlen(user) : 0),
639 (text *)password, (ub4)(NULL != password ? strlen(password) : 0),
640 (text *)connect, (ub4)strlen(connect),
641 OCI_DEFAULT);
642
643 switch (err)
644 {
645 case OCI_SUCCESS_WITH_INFO:
646 zabbix_log(LOG_LEVEL_WARNING, "%s", zbx_oci_error(err, NULL));
647 /* break; is not missing here */
648 ZBX_FALLTHROUGH;
649 case OCI_SUCCESS:
650 err = OCIAttrGet((void *)oracle.svchp, OCI_HTYPE_SVCCTX, (void *)&oracle.srvhp,
651 (ub4 *)0, OCI_ATTR_SERVER, oracle.errhp);
652 }
653
654 if (OCI_SUCCESS != err)
655 {
656 zbx_db_errlog(ERR_Z3001, err, zbx_oci_error(err, NULL), connect);
657 ret = ZBX_DB_DOWN;
658 }
659 }
660
661 if (ZBX_DB_OK == ret)
662 {
663 /* initialize statement handle */
664 err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&oracle.stmthp, OCI_HTYPE_STMT,
665 (size_t)0, (dvoid **)0);
666
667 if (OCI_SUCCESS != err)
668 {
669 zbx_db_errlog(ERR_Z3001, err, zbx_oci_error(err, NULL), connect);
670 ret = ZBX_DB_DOWN;
671 }
672 }
673
674 if (ZBX_DB_OK == ret)
675 {
676 if (0 < (ret = zbx_db_execute("alter session set nls_numeric_characters='. '")))
677 ret = ZBX_DB_OK;
678 }
679
680 zbx_free(connect);
681 #elif defined(HAVE_POSTGRESQL)
682 ZBX_UNUSED(cipher);
683 ZBX_UNUSED(cipher_13);
684
685 if (NULL != tls_connect)
686 {
687 keywords[i] = "sslmode";
688
689 if (0 == strcmp(tls_connect, ZBX_DB_TLS_CONNECT_REQUIRED_TXT))
690 values[i++] = "require";
691 else if (0 == strcmp(tls_connect, ZBX_DB_TLS_CONNECT_VERIFY_CA_TXT))
692 values[i++] = "verify-ca";
693 else
694 values[i++] = "verify-full";
695 }
696
697 if (NULL != cert)
698 {
699 keywords[i] = "sslcert";
700 values[i++] = cert;
701 }
702
703 if (NULL != key)
704 {
705 keywords[i] = "sslkey";
706 values[i++] = key;
707 }
708
709 if (NULL != ca)
710 {
711 keywords[i] = "sslrootcert";
712 values[i++] = ca;
713 }
714
715 if (NULL != host)
716 {
717 keywords[i] = "host";
718 values[i++] = host;
719 }
720
721 if (NULL != dbname)
722 {
723 keywords[i] = "dbname";
724 values[i++] = dbname;
725 }
726
727 if (NULL != user)
728 {
729 keywords[i] = "user";
730 values[i++] = user;
731 }
732
733 if (NULL != password)
734 {
735 keywords[i] = "password";
736 values[i++] = password;
737 }
738
739 if (0 != port)
740 {
741 keywords[i] = "port";
742 values[i++] = cport = zbx_dsprintf(cport, "%d", port);
743 }
744
745 keywords[i] = NULL;
746 values[i] = NULL;
747
748 conn = PQconnectdbParams(keywords, values, 0);
749
750 zbx_free(cport);
751
752 /* check to see that the backend connection was successfully made */
753 if (CONNECTION_OK != PQstatus(conn))
754 {
755 zbx_db_errlog(ERR_Z3001, 0, PQerrorMessage(conn), dbname);
756 ret = ZBX_DB_DOWN;
757 goto out;
758 }
759
760 if (NULL != dbschema && '\0' != *dbschema)
761 {
762 char *dbschema_esc;
763
764 dbschema_esc = zbx_db_dyn_escape_string(dbschema, ZBX_SIZE_T_MAX, ZBX_SIZE_T_MAX, ESCAPE_SEQUENCE_ON);
765 if (ZBX_DB_DOWN == (rc = zbx_db_execute("set schema '%s'", dbschema_esc)) || ZBX_DB_FAIL == rc)
766 ret = rc;
767 zbx_free(dbschema_esc);
768 }
769
770 if (ZBX_DB_FAIL == ret || ZBX_DB_DOWN == ret)
771 goto out;
772
773 result = zbx_db_select("select oid from pg_type where typname='bytea'");
774
775 if ((DB_RESULT)ZBX_DB_DOWN == result || NULL == result)
776 {
777 ret = (NULL == result) ? ZBX_DB_FAIL : ZBX_DB_DOWN;
778 goto out;
779 }
780
781 if (NULL != (row = zbx_db_fetch(result)))
782 ZBX_PG_BYTEAOID = atoi(row[0]);
783 DBfree_result(result);
784
785 ZBX_PG_SVERSION = PQserverVersion(conn);
786 zabbix_log(LOG_LEVEL_DEBUG, "PostgreSQL Server version: %d", ZBX_PG_SVERSION);
787
788 /* disable "nonstandard use of \' in a string literal" warning */
789 if (0 < (ret = zbx_db_execute("set escape_string_warning to off")))
790 ret = ZBX_DB_OK;
791
792 if (ZBX_DB_OK != ret)
793 goto out;
794
795 /* increase float precision */
796 if (0 < (ret = zbx_db_execute("set extra_float_digits to 3")))
797 ret = ZBX_DB_OK;
798
799 if (ZBX_DB_OK != ret)
800 goto out;
801
802 result = zbx_db_select("show standard_conforming_strings");
803
804 if ((DB_RESULT)ZBX_DB_DOWN == result || NULL == result)
805 {
806 ret = (NULL == result) ? ZBX_DB_FAIL : ZBX_DB_DOWN;
807 goto out;
808 }
809
810 if (NULL != (row = zbx_db_fetch(result)))
811 ZBX_PG_ESCAPE_BACKSLASH = (0 == strcmp(row[0], "off"));
812 DBfree_result(result);
813
814 if (90000 <= ZBX_PG_SVERSION)
815 {
816 /* change the output format for values of type bytea from hex (the default) to escape */
817 if (0 < (ret = zbx_db_execute("set bytea_output=escape")))
818 ret = ZBX_DB_OK;
819 }
820 out:
821 #elif defined(HAVE_SQLITE3)
822 ZBX_UNUSED(host);
823 ZBX_UNUSED(user);
824 ZBX_UNUSED(password);
825 ZBX_UNUSED(dbschema);
826 ZBX_UNUSED(port);
827 ZBX_UNUSED(tls_connect);
828 ZBX_UNUSED(cert);
829 ZBX_UNUSED(key);
830 ZBX_UNUSED(ca);
831 ZBX_UNUSED(cipher);
832 ZBX_UNUSED(cipher_13);
833 #ifdef HAVE_FUNCTION_SQLITE3_OPEN_V2
834 if (SQLITE_OK != sqlite3_open_v2(dbname, &conn, SQLITE_OPEN_READWRITE, NULL))
835 #else
836 if (SQLITE_OK != sqlite3_open(dbname, &conn))
837 #endif
838 {
839 zbx_db_errlog(ERR_Z3001, 0, sqlite3_errmsg(conn), dbname);
840 ret = ZBX_DB_DOWN;
841 goto out;
842 }
843
844 /* do not return SQLITE_BUSY immediately, wait for N ms */
845 sqlite3_busy_timeout(conn, SEC_PER_MIN * 1000);
846
847 if (0 < (ret = zbx_db_execute("pragma synchronous=0")))
848 ret = ZBX_DB_OK;
849
850 if (ZBX_DB_OK != ret)
851 goto out;
852
853 if (0 < (ret = zbx_db_execute("pragma temp_store=2")))
854 ret = ZBX_DB_OK;
855
856 if (ZBX_DB_OK != ret)
857 goto out;
858
859 path = zbx_strdup(NULL, dbname);
860
861 if (NULL != (p = strrchr(path, '/')))
862 *++p = '\0';
863 else
864 *path = '\0';
865
866 if (0 < (ret = zbx_db_execute("pragma temp_store_directory='%s'", path)))
867 ret = ZBX_DB_OK;
868
869 zbx_free(path);
870 out:
871 #endif /* HAVE_SQLITE3 */
872 if (ZBX_DB_OK != ret)
873 zbx_db_close();
874
875 txn_error = last_txn_error;
876 txn_level = last_txn_level;
877
878 return ret;
879 }
880
zbx_db_init(const char * dbname,const char * const dbschema,char ** error)881 int zbx_db_init(const char *dbname, const char *const dbschema, char **error)
882 {
883 #ifdef HAVE_SQLITE3
884 zbx_stat_t buf;
885
886 if (0 != zbx_stat(dbname, &buf))
887 {
888 zabbix_log(LOG_LEVEL_WARNING, "cannot open database file \"%s\": %s", dbname, zbx_strerror(errno));
889 zabbix_log(LOG_LEVEL_WARNING, "creating database ...");
890
891 if (SQLITE_OK != sqlite3_open(dbname, &conn))
892 {
893 zbx_db_errlog(ERR_Z3002, 0, sqlite3_errmsg(conn), dbname);
894 *error = zbx_strdup(*error, "cannot open database");
895 return FAIL;
896 }
897
898 if (SUCCEED != zbx_mutex_create(&sqlite_access, ZBX_MUTEX_SQLITE3, error))
899 return FAIL;
900
901 zbx_db_execute("%s", dbschema);
902 zbx_db_close();
903 return SUCCEED;
904 }
905
906 return zbx_mutex_create(&sqlite_access, ZBX_MUTEX_SQLITE3, error);
907 #else /* not HAVE_SQLITE3 */
908 ZBX_UNUSED(dbname);
909 ZBX_UNUSED(dbschema);
910 ZBX_UNUSED(error);
911
912 return SUCCEED;
913 #endif /* HAVE_SQLITE3 */
914 }
915
zbx_db_deinit(void)916 void zbx_db_deinit(void)
917 {
918 #ifdef HAVE_SQLITE3
919 zbx_mutex_destroy(&sqlite_access);
920 #endif
921 }
922
zbx_db_close(void)923 void zbx_db_close(void)
924 {
925 #if defined(HAVE_MYSQL)
926 if (NULL != conn)
927 {
928 mysql_close(conn);
929 conn = NULL;
930 }
931 #elif defined(HAVE_ORACLE)
932 if (0 != oracle.db_results.values_num)
933 {
934 int i;
935
936 zabbix_log(LOG_LEVEL_WARNING, "cannot process queries: database is closed");
937
938 for (i = 0; i < oracle.db_results.values_num; i++)
939 {
940 /* deallocate all handles before environment is deallocated */
941 OCI_DBclean_result(oracle.db_results.values[i]);
942 }
943 }
944
945 /* deallocate statement handle */
946 if (NULL != oracle.stmthp)
947 {
948 OCIHandleFree((dvoid *)oracle.stmthp, OCI_HTYPE_STMT);
949 oracle.stmthp = NULL;
950 }
951
952 if (NULL != oracle.svchp)
953 {
954 OCILogoff(oracle.svchp, oracle.errhp);
955 oracle.svchp = NULL;
956 }
957
958 if (NULL != oracle.errhp)
959 {
960 OCIHandleFree(oracle.errhp, OCI_HTYPE_ERROR);
961 oracle.errhp = NULL;
962 }
963
964 if (NULL != oracle.srvhp)
965 {
966 OCIHandleFree(oracle.srvhp, OCI_HTYPE_SERVER);
967 oracle.srvhp = NULL;
968 }
969
970 if (NULL != oracle.envhp)
971 {
972 /* delete the environment handle, which deallocates all other handles associated with it */
973 OCIHandleFree((dvoid *)oracle.envhp, OCI_HTYPE_ENV);
974 oracle.envhp = NULL;
975 }
976
977 zbx_vector_ptr_destroy(&oracle.db_results);
978 #elif defined(HAVE_POSTGRESQL)
979 if (NULL != conn)
980 {
981 PQfinish(conn);
982 conn = NULL;
983 }
984 #elif defined(HAVE_SQLITE3)
985 if (NULL != conn)
986 {
987 sqlite3_close(conn);
988 conn = NULL;
989 }
990 #endif
991 }
992
993 /******************************************************************************
994 * *
995 * Function: zbx_db_begin *
996 * *
997 * Purpose: start transaction *
998 * *
999 * Comments: do nothing if DB does not support transactions *
1000 * *
1001 ******************************************************************************/
zbx_db_begin(void)1002 int zbx_db_begin(void)
1003 {
1004 int rc = ZBX_DB_OK;
1005
1006 if (txn_level > 0)
1007 {
1008 zabbix_log(LOG_LEVEL_CRIT, "ERROR: nested transaction detected. Please report it to Zabbix Team.");
1009 assert(0);
1010 }
1011
1012 txn_level++;
1013
1014 #if defined(HAVE_MYSQL) || defined(HAVE_POSTGRESQL)
1015 rc = zbx_db_execute("begin;");
1016 #elif defined(HAVE_SQLITE3)
1017 zbx_mutex_lock(sqlite_access);
1018 rc = zbx_db_execute("begin;");
1019 #endif
1020
1021 if (ZBX_DB_DOWN == rc)
1022 txn_level--;
1023
1024 return rc;
1025 }
1026
1027 /******************************************************************************
1028 * *
1029 * Function: zbx_db_commit *
1030 * *
1031 * Purpose: commit transaction *
1032 * *
1033 * Comments: do nothing if DB does not support transactions *
1034 * *
1035 ******************************************************************************/
zbx_db_commit(void)1036 int zbx_db_commit(void)
1037 {
1038 int rc = ZBX_DB_OK;
1039 #ifdef HAVE_ORACLE
1040 sword err;
1041 #endif
1042
1043 if (0 == txn_level)
1044 {
1045 zabbix_log(LOG_LEVEL_CRIT, "ERROR: commit without transaction."
1046 " Please report it to Zabbix Team.");
1047 assert(0);
1048 }
1049
1050 if (ZBX_DB_OK != txn_error)
1051 return ZBX_DB_FAIL; /* commit called on failed transaction */
1052
1053 #if defined(HAVE_ORACLE)
1054 if (OCI_SUCCESS != (err = OCITransCommit(oracle.svchp, oracle.errhp, OCI_DEFAULT)))
1055 rc = OCI_handle_sql_error(ERR_Z3005, err, "commit failed");
1056 #elif defined(HAVE_MYSQL) || defined(HAVE_POSTGRESQL) || defined(HAVE_SQLITE3)
1057 rc = zbx_db_execute("commit;");
1058 #endif
1059
1060 if (ZBX_DB_OK > rc) { /* commit failed */
1061 txn_error = rc;
1062 return rc;
1063 }
1064
1065 #ifdef HAVE_SQLITE3
1066 zbx_mutex_unlock(sqlite_access);
1067 #endif
1068
1069 txn_level--;
1070 txn_end_error = ZBX_DB_OK;
1071
1072 return rc;
1073 }
1074
1075 /******************************************************************************
1076 * *
1077 * Function: zbx_db_rollback *
1078 * *
1079 * Purpose: rollback transaction *
1080 * *
1081 * Comments: do nothing if DB does not support transactions *
1082 * *
1083 ******************************************************************************/
zbx_db_rollback(void)1084 int zbx_db_rollback(void)
1085 {
1086 int rc = ZBX_DB_OK, last_txn_error;
1087 #ifdef HAVE_ORACLE
1088 sword err;
1089 #endif
1090
1091 if (0 == txn_level)
1092 {
1093 zabbix_log(LOG_LEVEL_CRIT, "ERROR: rollback without transaction."
1094 " Please report it to Zabbix Team.");
1095 assert(0);
1096 }
1097
1098 last_txn_error = txn_error;
1099
1100 /* allow rollback of failed transaction */
1101 txn_error = ZBX_DB_OK;
1102
1103 #if defined(HAVE_MYSQL) || defined(HAVE_POSTGRESQL)
1104 rc = zbx_db_execute("rollback;");
1105 #elif defined(HAVE_ORACLE)
1106 if (OCI_SUCCESS != (err = OCITransRollback(oracle.svchp, oracle.errhp, OCI_DEFAULT)))
1107 rc = OCI_handle_sql_error(ERR_Z3005, err, "rollback failed");
1108 #elif defined(HAVE_SQLITE3)
1109 rc = zbx_db_execute("rollback;");
1110 zbx_mutex_unlock(sqlite_access);
1111 #endif
1112
1113 /* There is no way to recover from rollback errors, so there is no need to preserve transaction level / error. */
1114 txn_level = 0;
1115 txn_error = ZBX_DB_OK;
1116
1117 if (ZBX_DB_FAIL == rc)
1118 txn_end_error = ZBX_DB_FAIL;
1119 else
1120 txn_end_error = last_txn_error; /* error that caused rollback */
1121
1122 return rc;
1123 }
1124
zbx_db_txn_level(void)1125 int zbx_db_txn_level(void)
1126 {
1127 return txn_level;
1128 }
1129
zbx_db_txn_error(void)1130 int zbx_db_txn_error(void)
1131 {
1132 return txn_error;
1133 }
1134
zbx_db_txn_end_error(void)1135 int zbx_db_txn_end_error(void)
1136 {
1137 return txn_end_error;
1138 }
1139
1140 #ifdef HAVE_ORACLE
zbx_oracle_statement_prepare(const char * sql)1141 static sword zbx_oracle_statement_prepare(const char *sql)
1142 {
1143 return OCIStmtPrepare(oracle.stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql), (ub4)OCI_NTV_SYNTAX,
1144 (ub4)OCI_DEFAULT);
1145 }
1146
zbx_oracle_statement_execute(ub4 iters,ub4 * nrows)1147 static sword zbx_oracle_statement_execute(ub4 iters, ub4 *nrows)
1148 {
1149 sword err;
1150
1151 if (OCI_SUCCESS == (err = OCIStmtExecute(oracle.svchp, oracle.stmthp, oracle.errhp, iters, (ub4)0,
1152 (CONST OCISnapshot *)NULL, (OCISnapshot *)NULL,
1153 0 == txn_level ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT)))
1154 {
1155 err = OCIAttrGet((void *)oracle.stmthp, OCI_HTYPE_STMT, nrows, (ub4 *)0, OCI_ATTR_ROW_COUNT,
1156 oracle.errhp);
1157 }
1158
1159 return err;
1160 }
1161 #endif
1162
1163 #ifdef HAVE_ORACLE
zbx_db_statement_prepare(const char * sql)1164 int zbx_db_statement_prepare(const char *sql)
1165 {
1166 sword err;
1167 int ret = ZBX_DB_OK;
1168
1169 if (0 == txn_level)
1170 zabbix_log(LOG_LEVEL_DEBUG, "query without transaction detected");
1171
1172 if (ZBX_DB_OK != txn_error)
1173 {
1174 zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] within failed transaction", txn_level);
1175 return ZBX_DB_FAIL;
1176 }
1177
1178 zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
1179
1180 if (OCI_SUCCESS != (err = zbx_oracle_statement_prepare(sql)))
1181 ret = OCI_handle_sql_error(ERR_Z3005, err, sql);
1182
1183 if (ZBX_DB_FAIL == ret && 0 < txn_level)
1184 {
1185 zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
1186 txn_error = ZBX_DB_FAIL;
1187 }
1188
1189 return ret;
1190 }
1191
1192 /******************************************************************************
1193 * *
1194 * Function: db_bind_dynamic_cb *
1195 * *
1196 * Purpose: callback function used by dynamic parameter binding *
1197 * *
1198 ******************************************************************************/
db_bind_dynamic_cb(dvoid * ctxp,OCIBind * bindp,ub4 iter,ub4 index,dvoid ** bufpp,ub4 * alenp,ub1 * piecep,dvoid ** indpp)1199 static sb4 db_bind_dynamic_cb(dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index, dvoid **bufpp, ub4 *alenp, ub1 *piecep,
1200 dvoid **indpp)
1201 {
1202 zbx_db_bind_context_t *context = (zbx_db_bind_context_t *)ctxp;
1203
1204 ZBX_UNUSED(bindp);
1205 ZBX_UNUSED(index);
1206
1207 switch (context->type)
1208 {
1209 case ZBX_TYPE_ID: /* handle 0 -> NULL conversion */
1210 if (0 == context->rows[iter][context->position].ui64)
1211 {
1212 *bufpp = NULL;
1213 *alenp = 0;
1214 break;
1215 }
1216 ZBX_FALLTHROUGH;
1217 case ZBX_TYPE_UINT:
1218 *bufpp = &((OCINumber *)context->data)[iter];
1219 *alenp = sizeof(OCINumber);
1220 break;
1221 case ZBX_TYPE_INT:
1222 *bufpp = &context->rows[iter][context->position].i32;
1223 *alenp = sizeof(int);
1224 break;
1225 case ZBX_TYPE_FLOAT:
1226 *bufpp = &context->rows[iter][context->position].dbl;
1227 *alenp = sizeof(double);
1228 break;
1229 case ZBX_TYPE_CHAR:
1230 case ZBX_TYPE_TEXT:
1231 case ZBX_TYPE_SHORTTEXT:
1232 case ZBX_TYPE_LONGTEXT:
1233 *bufpp = context->rows[iter][context->position].str;
1234 *alenp = ((size_t *)context->data)[iter];
1235 break;
1236 default:
1237 return FAIL;
1238 }
1239
1240 *indpp = NULL;
1241 *piecep = OCI_ONE_PIECE;
1242
1243 return OCI_CONTINUE;
1244 }
1245
1246 /******************************************************************************
1247 * *
1248 * Function: zbx_db_bind_parameter_dyn *
1249 * *
1250 * Purpose: performs dynamic parameter binding, converting value if necessary *
1251 * *
1252 * Parameters: context - [OUT] the bind context *
1253 * position - [IN] the parameter position *
1254 * type - [IN] the parameter type (ZBX_TYPE_* ) *
1255 * rows - [IN] the data to bind - array of rows, *
1256 * each row being an array of columns *
1257 * rows_num - [IN] the number of rows in the data *
1258 * *
1259 ******************************************************************************/
zbx_db_bind_parameter_dyn(zbx_db_bind_context_t * context,int position,unsigned char type,zbx_db_value_t ** rows,int rows_num)1260 int zbx_db_bind_parameter_dyn(zbx_db_bind_context_t *context, int position, unsigned char type,
1261 zbx_db_value_t **rows, int rows_num)
1262 {
1263 int i, ret = ZBX_DB_OK;
1264 size_t *sizes;
1265 sword err;
1266 OCINumber *values;
1267 ub2 data_type;
1268 OCIBind *bindhp = NULL;
1269
1270 context->position = position;
1271 context->rows = rows;
1272 context->data = NULL;
1273 context->type = type;
1274
1275 switch (type)
1276 {
1277 case ZBX_TYPE_ID:
1278 case ZBX_TYPE_UINT:
1279 values = (OCINumber *)zbx_malloc(NULL, sizeof(OCINumber) * rows_num);
1280
1281 for (i = 0; i < rows_num; i++)
1282 {
1283 err = OCINumberFromInt(oracle.errhp, &rows[i][position].ui64, sizeof(zbx_uint64_t),
1284 OCI_NUMBER_UNSIGNED, &values[i]);
1285
1286 if (OCI_SUCCESS != err)
1287 {
1288 ret = OCI_handle_sql_error(ERR_Z3007, err, NULL);
1289 goto out;
1290 }
1291 }
1292
1293 context->data = (OCINumber *)values;
1294 context->size_max = sizeof(OCINumber);
1295 data_type = SQLT_VNU;
1296 break;
1297 case ZBX_TYPE_INT:
1298 context->size_max = sizeof(int);
1299 data_type = SQLT_INT;
1300 break;
1301 case ZBX_TYPE_FLOAT:
1302 context->size_max = sizeof(double);
1303 data_type = SQLT_BDOUBLE;
1304 break;
1305 case ZBX_TYPE_CHAR:
1306 case ZBX_TYPE_TEXT:
1307 case ZBX_TYPE_SHORTTEXT:
1308 case ZBX_TYPE_LONGTEXT:
1309 sizes = (size_t *)zbx_malloc(NULL, sizeof(size_t) * rows_num);
1310 context->size_max = 0;
1311
1312 for (i = 0; i < rows_num; i++)
1313 {
1314 sizes[i] = strlen(rows[i][position].str);
1315
1316 if (sizes[i] > context->size_max)
1317 context->size_max = sizes[i];
1318 }
1319
1320 context->data = sizes;
1321 data_type = SQLT_LNG;
1322 break;
1323 default:
1324 THIS_SHOULD_NEVER_HAPPEN;
1325 exit(EXIT_FAILURE);
1326 }
1327
1328 err = OCIBindByPos(oracle.stmthp, &bindhp, oracle.errhp, context->position + 1, NULL, context->size_max,
1329 data_type, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DATA_AT_EXEC);
1330
1331 if (OCI_SUCCESS != err)
1332 {
1333 ret = OCI_handle_sql_error(ERR_Z3007, err, NULL);
1334
1335 if (ZBX_DB_FAIL == ret && 0 < txn_level)
1336 {
1337 zabbix_log(LOG_LEVEL_DEBUG, "query failed, setting transaction as failed");
1338 txn_error = ZBX_DB_FAIL;
1339 }
1340
1341 goto out;
1342 }
1343
1344 err = OCIBindDynamic(bindhp, oracle.errhp, (dvoid *)context, db_bind_dynamic_cb, NULL, NULL);
1345
1346 if (OCI_SUCCESS != err)
1347 {
1348 ret = OCI_handle_sql_error(ERR_Z3007, err, NULL);
1349
1350 if (ZBX_DB_FAIL == ret && 0 < txn_level)
1351 {
1352 zabbix_log(LOG_LEVEL_DEBUG, "query failed, setting transaction as failed");
1353 txn_error = ZBX_DB_FAIL;
1354 }
1355
1356 goto out;
1357 }
1358 out:
1359 if (ret != ZBX_DB_OK)
1360 zbx_db_clean_bind_context(context);
1361
1362 return ret;
1363 }
1364
zbx_db_clean_bind_context(zbx_db_bind_context_t * context)1365 void zbx_db_clean_bind_context(zbx_db_bind_context_t *context)
1366 {
1367 zbx_free(context->data);
1368 }
1369
zbx_db_statement_execute(int iters)1370 int zbx_db_statement_execute(int iters)
1371 {
1372 sword err;
1373 ub4 nrows;
1374 int ret;
1375
1376 if (ZBX_DB_OK != txn_error)
1377 {
1378 zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] within failed transaction", txn_level);
1379 ret = ZBX_DB_FAIL;
1380 goto out;
1381 }
1382
1383 if (OCI_SUCCESS != (err = zbx_oracle_statement_execute(iters, &nrows)))
1384 ret = OCI_handle_sql_error(ERR_Z3007, err, NULL);
1385 else
1386 ret = (int)nrows;
1387
1388 if (ZBX_DB_FAIL == ret && 0 < txn_level)
1389 {
1390 zabbix_log(LOG_LEVEL_DEBUG, "query failed, setting transaction as failed");
1391 txn_error = ZBX_DB_FAIL;
1392 }
1393 out:
1394 zabbix_log(LOG_LEVEL_DEBUG, "%s():%d", __func__, ret);
1395
1396 return ret;
1397 }
1398 #endif
1399
1400 /******************************************************************************
1401 * *
1402 * Function: zbx_db_vexecute *
1403 * *
1404 * Purpose: Execute SQL statement. For non-select statements only. *
1405 * *
1406 * Return value: ZBX_DB_FAIL (on error) or ZBX_DB_DOWN (on recoverable error) *
1407 * or number of rows affected (on success) *
1408 * *
1409 ******************************************************************************/
zbx_db_vexecute(const char * fmt,va_list args)1410 int zbx_db_vexecute(const char *fmt, va_list args)
1411 {
1412 char *sql = NULL;
1413 int ret = ZBX_DB_OK;
1414 double sec = 0;
1415 #if defined(HAVE_MYSQL)
1416 #elif defined(HAVE_ORACLE)
1417 sword err = OCI_SUCCESS;
1418 #elif defined(HAVE_POSTGRESQL)
1419 PGresult *result;
1420 char *error = NULL;
1421 #elif defined(HAVE_SQLITE3)
1422 int err;
1423 char *error = NULL;
1424 #endif
1425
1426 if (0 != CONFIG_LOG_SLOW_QUERIES)
1427 sec = zbx_time();
1428
1429 sql = zbx_dvsprintf(sql, fmt, args);
1430
1431 if (0 == txn_level)
1432 zabbix_log(LOG_LEVEL_DEBUG, "query without transaction detected");
1433
1434 if (ZBX_DB_OK != txn_error)
1435 {
1436 zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level,
1437 sql);
1438 ret = ZBX_DB_FAIL;
1439 goto clean;
1440 }
1441
1442 zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
1443
1444 #if defined(HAVE_MYSQL)
1445 if (NULL == conn)
1446 {
1447 zbx_db_errlog(ERR_Z3003, 0, NULL, NULL);
1448 ret = ZBX_DB_FAIL;
1449 }
1450 else
1451 {
1452 if (0 != mysql_query(conn, sql))
1453 {
1454 zbx_db_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
1455
1456 ret = (SUCCEED == is_recoverable_mysql_error() ? ZBX_DB_DOWN : ZBX_DB_FAIL);
1457 }
1458 else
1459 {
1460 int status;
1461
1462 do
1463 {
1464 if (0 != mysql_field_count(conn))
1465 {
1466 zabbix_log(LOG_LEVEL_DEBUG, "cannot retrieve result set");
1467 break;
1468 }
1469
1470 ret += (int)mysql_affected_rows(conn);
1471
1472 /* more results? 0 = yes (keep looping), -1 = no, >0 = error */
1473 if (0 < (status = mysql_next_result(conn)))
1474 {
1475 zbx_db_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
1476 ret = (SUCCEED == is_recoverable_mysql_error() ? ZBX_DB_DOWN : ZBX_DB_FAIL);
1477 }
1478 }
1479 while (0 == status);
1480 }
1481 }
1482 #elif defined(HAVE_ORACLE)
1483 if (OCI_SUCCESS == (err = zbx_oracle_statement_prepare(sql)))
1484 {
1485 ub4 nrows = 0;
1486
1487 if (OCI_SUCCESS == (err = zbx_oracle_statement_execute(1, &nrows)))
1488 ret = (int)nrows;
1489 }
1490
1491 if (OCI_SUCCESS != err)
1492 ret = OCI_handle_sql_error(ERR_Z3005, err, sql);
1493
1494 #elif defined(HAVE_POSTGRESQL)
1495 result = PQexec(conn,sql);
1496
1497 if (NULL == result)
1498 {
1499 zbx_db_errlog(ERR_Z3005, 0, "result is NULL", sql);
1500 ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
1501 }
1502 else if (PGRES_COMMAND_OK != PQresultStatus(result))
1503 {
1504 zbx_postgresql_error(&error, result);
1505 zbx_db_errlog(ERR_Z3005, 0, error, sql);
1506 zbx_free(error);
1507
1508 ret = (SUCCEED == is_recoverable_postgresql_error(conn, result) ? ZBX_DB_DOWN : ZBX_DB_FAIL);
1509 }
1510
1511 if (ZBX_DB_OK == ret)
1512 ret = atoi(PQcmdTuples(result));
1513
1514 PQclear(result);
1515 #elif defined(HAVE_SQLITE3)
1516 if (0 == txn_level)
1517 zbx_mutex_lock(sqlite_access);
1518
1519 lbl_exec:
1520 if (SQLITE_OK != (err = sqlite3_exec(conn, sql, NULL, 0, &error)))
1521 {
1522 if (SQLITE_BUSY == err)
1523 goto lbl_exec;
1524
1525 zbx_db_errlog(ERR_Z3005, 0, error, sql);
1526 sqlite3_free(error);
1527
1528 switch (err)
1529 {
1530 case SQLITE_ERROR: /* SQL error or missing database; assuming SQL error, because if we
1531 are this far into execution, zbx_db_connect() was successful */
1532 case SQLITE_NOMEM: /* A malloc() failed */
1533 case SQLITE_TOOBIG: /* String or BLOB exceeds size limit */
1534 case SQLITE_CONSTRAINT: /* Abort due to constraint violation */
1535 case SQLITE_MISMATCH: /* Data type mismatch */
1536 ret = ZBX_DB_FAIL;
1537 break;
1538 default:
1539 ret = ZBX_DB_DOWN;
1540 break;
1541 }
1542 }
1543
1544 if (ZBX_DB_OK == ret)
1545 ret = sqlite3_changes(conn);
1546
1547 if (0 == txn_level)
1548 zbx_mutex_unlock(sqlite_access);
1549 #endif /* HAVE_SQLITE3 */
1550
1551 if (0 != CONFIG_LOG_SLOW_QUERIES)
1552 {
1553 sec = zbx_time() - sec;
1554 if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
1555 zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
1556 }
1557
1558 if (ZBX_DB_FAIL == ret && 0 < txn_level)
1559 {
1560 zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
1561 txn_error = ZBX_DB_FAIL;
1562 }
1563 clean:
1564 zbx_free(sql);
1565
1566 return ret;
1567 }
1568
1569 /******************************************************************************
1570 * *
1571 * Function: zbx_db_vselect *
1572 * *
1573 * Purpose: execute a select statement *
1574 * *
1575 * Return value: data, NULL (on error) or (DB_RESULT)ZBX_DB_DOWN *
1576 * *
1577 ******************************************************************************/
zbx_db_vselect(const char * fmt,va_list args)1578 DB_RESULT zbx_db_vselect(const char *fmt, va_list args)
1579 {
1580 char *sql = NULL;
1581 DB_RESULT result = NULL;
1582 double sec = 0;
1583 #if defined(HAVE_ORACLE)
1584 sword err = OCI_SUCCESS;
1585 ub4 prefetch_rows = 200, counter;
1586
1587 ZBX_UNUSED(counter);
1588 #elif defined(HAVE_POSTGRESQL)
1589 char *error = NULL;
1590 #elif defined(HAVE_SQLITE3)
1591 int ret = FAIL;
1592 char *error = NULL;
1593 #endif
1594
1595 if (0 != CONFIG_LOG_SLOW_QUERIES)
1596 sec = zbx_time();
1597
1598 sql = zbx_dvsprintf(sql, fmt, args);
1599
1600 if (ZBX_DB_OK != txn_error)
1601 {
1602 zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
1603 goto clean;
1604 }
1605
1606 zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
1607
1608 #if defined(HAVE_MYSQL)
1609 result = (DB_RESULT)zbx_malloc(NULL, sizeof(struct zbx_db_result));
1610 result->result = NULL;
1611
1612 if (NULL == conn)
1613 {
1614 zbx_db_errlog(ERR_Z3003, 0, NULL, NULL);
1615
1616 DBfree_result(result);
1617 result = NULL;
1618 }
1619 else
1620 {
1621 if (0 != mysql_query(conn, sql) || NULL == (result->result = mysql_store_result(conn)))
1622 {
1623 zbx_db_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
1624
1625 DBfree_result(result);
1626 result = (SUCCEED == is_recoverable_mysql_error() ? (DB_RESULT)ZBX_DB_DOWN : NULL);
1627 }
1628 }
1629 #elif defined(HAVE_ORACLE)
1630 result = zbx_malloc(NULL, sizeof(struct zbx_db_result));
1631 memset(result, 0, sizeof(struct zbx_db_result));
1632 zbx_vector_ptr_append(&oracle.db_results, result);
1633
1634 err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&result->stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
1635
1636 /* Prefetching when working with Oracle is needed because otherwise it fetches only 1 row at a time when doing */
1637 /* selects (default behavior). There are 2 ways to do prefetching: memory based and rows based. Based on the */
1638 /* study optimal (speed-wise) memory based prefetch is 2 MB. But in case of many subsequent selects CPU usage */
1639 /* jumps up to 100 %. Using rows prefetch with up to 200 rows does not affect CPU usage, it is the same as */
1640 /* without prefetching at all. See ZBX-5920, ZBX-6493 for details. */
1641 /* */
1642 /* Tested on Oracle 11gR2. */
1643 /* */
1644 /* Oracle info: docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci04sql.htm */
1645
1646 if (OCI_SUCCESS == err)
1647 {
1648 err = OCIAttrSet(result->stmthp, OCI_HTYPE_STMT, &prefetch_rows, sizeof(prefetch_rows),
1649 OCI_ATTR_PREFETCH_ROWS, oracle.errhp);
1650 }
1651
1652 if (OCI_SUCCESS == err)
1653 {
1654 err = OCIStmtPrepare(result->stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
1655 (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
1656 }
1657
1658 if (OCI_SUCCESS == err)
1659 {
1660 err = OCIStmtExecute(oracle.svchp, result->stmthp, oracle.errhp, (ub4)0, (ub4)0,
1661 (CONST OCISnapshot *)NULL, (OCISnapshot *)NULL,
1662 0 == txn_level ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT);
1663 }
1664
1665 if (OCI_SUCCESS == err)
1666 {
1667 /* get the number of columns in the query */
1668 err = OCIAttrGet((void *)result->stmthp, OCI_HTYPE_STMT, (void *)&result->ncolumn,
1669 (ub4 *)0, OCI_ATTR_PARAM_COUNT, oracle.errhp);
1670 }
1671
1672 if (OCI_SUCCESS != err)
1673 goto error;
1674
1675 assert(0 < result->ncolumn);
1676
1677 result->values = zbx_malloc(NULL, result->ncolumn * sizeof(char *));
1678 result->clobs = zbx_malloc(NULL, result->ncolumn * sizeof(OCILobLocator *));
1679 result->values_alloc = zbx_malloc(NULL, result->ncolumn * sizeof(ub4));
1680 memset(result->values, 0, result->ncolumn * sizeof(char *));
1681 memset(result->clobs, 0, result->ncolumn * sizeof(OCILobLocator *));
1682 memset(result->values_alloc, 0, result->ncolumn * sizeof(ub4));
1683
1684 for (counter = 1; OCI_SUCCESS == err && counter <= (ub4)result->ncolumn; counter++)
1685 {
1686 OCIParam *parmdp = NULL;
1687 OCIDefine *defnp = NULL;
1688 ub4 char_semantics;
1689 ub2 col_width = 0, data_type = 0;
1690
1691 /* request a parameter descriptor in the select-list */
1692 err = OCIParamGet((void *)result->stmthp, OCI_HTYPE_STMT, oracle.errhp, (void **)&parmdp, (ub4)counter);
1693
1694 if (OCI_SUCCESS == err)
1695 {
1696 /* retrieve the data type for the column */
1697 err = OCIAttrGet((void *)parmdp, OCI_DTYPE_PARAM, (dvoid *)&data_type, (ub4 *)NULL,
1698 (ub4)OCI_ATTR_DATA_TYPE, (OCIError *)oracle.errhp);
1699 }
1700
1701 if (SQLT_CLOB == data_type)
1702 {
1703 if (OCI_SUCCESS == err)
1704 {
1705 /* allocate the lob locator variable */
1706 err = OCIDescriptorAlloc((dvoid *)oracle.envhp, (dvoid **)&result->clobs[counter - 1],
1707 OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);
1708 }
1709
1710 if (OCI_SUCCESS == err)
1711 {
1712 /* associate clob var with its define handle */
1713 err = OCIDefineByPos((void *)result->stmthp, &defnp, (OCIError *)oracle.errhp,
1714 (ub4)counter, (dvoid *)&result->clobs[counter - 1], (sb4)-1,
1715 data_type, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT);
1716 }
1717 }
1718 else
1719 {
1720 if (SQLT_IBDOUBLE != data_type && SQLT_BDOUBLE != data_type)
1721 {
1722 if (OCI_SUCCESS == err)
1723 {
1724 /* retrieve the length semantics for the column */
1725 char_semantics = 0;
1726 err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&char_semantics,
1727 (ub4 *)NULL, (ub4)OCI_ATTR_CHAR_USED, (OCIError *)oracle.errhp);
1728 }
1729
1730 if (OCI_SUCCESS == err)
1731 {
1732 if (0 != char_semantics)
1733 {
1734 /* retrieve the column width in characters */
1735 err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width,
1736 (ub4 *)NULL, (ub4)OCI_ATTR_CHAR_SIZE, (OCIError *)oracle.errhp);
1737
1738 /* adjust for UTF-8 */
1739 col_width *= 4;
1740 }
1741 else
1742 {
1743 /* retrieve the column width in bytes */
1744 err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width,
1745 (ub4 *)NULL, (ub4)OCI_ATTR_DATA_SIZE, (OCIError *)oracle.errhp);
1746 }
1747 }
1748 col_width++; /* add 1 byte for terminating '\0' */
1749 }
1750 else
1751 col_width = ZBX_MAX_DOUBLE_LEN + 1;
1752
1753 result->values_alloc[counter - 1] = col_width;
1754 result->values[counter - 1] = zbx_malloc(NULL, col_width);
1755 *result->values[counter - 1] = '\0';
1756
1757 if (OCI_SUCCESS == err)
1758 {
1759 /* represent any data as characters */
1760 err = OCIDefineByPos(result->stmthp, &defnp, oracle.errhp, counter,
1761 (dvoid *)result->values[counter - 1], col_width, SQLT_STR,
1762 (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
1763 }
1764 }
1765
1766 /* free cell descriptor */
1767 OCIDescriptorFree(parmdp, OCI_DTYPE_PARAM);
1768 parmdp = NULL;
1769 }
1770 error:
1771 if (OCI_SUCCESS != err)
1772 {
1773 int server_status;
1774
1775 server_status = OCI_handle_sql_error(ERR_Z3005, err, sql);
1776 DBfree_result(result);
1777
1778 result = (ZBX_DB_DOWN == server_status ? (DB_RESULT)(intptr_t)server_status : NULL);
1779 }
1780 #elif defined(HAVE_POSTGRESQL)
1781 result = zbx_malloc(NULL, sizeof(struct zbx_db_result));
1782 result->pg_result = PQexec(conn, sql);
1783 result->values = NULL;
1784 result->cursor = 0;
1785 result->row_num = 0;
1786
1787 if (NULL == result->pg_result)
1788 zbx_db_errlog(ERR_Z3005, 0, "result is NULL", sql);
1789
1790 if (PGRES_TUPLES_OK != PQresultStatus(result->pg_result))
1791 {
1792 zbx_postgresql_error(&error, result->pg_result);
1793 zbx_db_errlog(ERR_Z3005, 0, error, sql);
1794 zbx_free(error);
1795
1796 if (SUCCEED == is_recoverable_postgresql_error(conn, result->pg_result))
1797 {
1798 DBfree_result(result);
1799 result = (DB_RESULT)ZBX_DB_DOWN;
1800 }
1801 else
1802 {
1803 DBfree_result(result);
1804 result = NULL;
1805 }
1806 }
1807 else /* init rownum */
1808 result->row_num = PQntuples(result->pg_result);
1809 #elif defined(HAVE_SQLITE3)
1810 if (0 == txn_level)
1811 zbx_mutex_lock(sqlite_access);
1812
1813 result = zbx_malloc(NULL, sizeof(struct zbx_db_result));
1814 result->curow = 0;
1815
1816 lbl_get_table:
1817 if (SQLITE_OK != (ret = sqlite3_get_table(conn,sql, &result->data, &result->nrow, &result->ncolumn, &error)))
1818 {
1819 if (SQLITE_BUSY == ret)
1820 goto lbl_get_table;
1821
1822 zbx_db_errlog(ERR_Z3005, 0, error, sql);
1823 sqlite3_free(error);
1824
1825 DBfree_result(result);
1826
1827 switch (ret)
1828 {
1829 case SQLITE_ERROR: /* SQL error or missing database; assuming SQL error, because if we
1830 are this far into execution, zbx_db_connect() was successful */
1831 case SQLITE_NOMEM: /* a malloc() failed */
1832 case SQLITE_MISMATCH: /* data type mismatch */
1833 result = NULL;
1834 break;
1835 default:
1836 result = (DB_RESULT)ZBX_DB_DOWN;
1837 break;
1838 }
1839 }
1840
1841 if (0 == txn_level)
1842 zbx_mutex_unlock(sqlite_access);
1843 #endif /* HAVE_SQLITE3 */
1844 if (0 != CONFIG_LOG_SLOW_QUERIES)
1845 {
1846 sec = zbx_time() - sec;
1847 if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
1848 zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
1849 }
1850
1851 if (NULL == result && 0 < txn_level)
1852 {
1853 zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
1854 txn_error = ZBX_DB_FAIL;
1855 }
1856 clean:
1857 zbx_free(sql);
1858
1859 return result;
1860 }
1861
1862 /*
1863 * Execute SQL statement. For select statements only.
1864 */
zbx_db_select_n(const char * query,int n)1865 DB_RESULT zbx_db_select_n(const char *query, int n)
1866 {
1867 #if defined(HAVE_ORACLE)
1868 return zbx_db_select("select * from (%s) where rownum<=%d", query, n);
1869 #elif defined(HAVE_MYSQL) || defined(HAVE_POSTGRESQL) || defined(HAVE_SQLITE3)
1870 return zbx_db_select("%s limit %d", query, n);
1871 #endif
1872 }
1873
1874 #ifdef HAVE_POSTGRESQL
1875 /******************************************************************************
1876 * *
1877 * Function: zbx_db_bytea_unescape *
1878 * *
1879 * Purpose: converts the null terminated string into binary buffer *
1880 * *
1881 * Transformations: *
1882 * \ooo == a byte whose value = ooo (ooo is an octal number) *
1883 * \\ == \ *
1884 * *
1885 * Parameters: *
1886 * io - [IN/OUT] null terminated string / binary data *
1887 * *
1888 * Return value: length of the binary buffer *
1889 * *
1890 ******************************************************************************/
zbx_db_bytea_unescape(u_char * io)1891 static size_t zbx_db_bytea_unescape(u_char *io)
1892 {
1893 const u_char *i = io;
1894 u_char *o = io;
1895
1896 while ('\0' != *i)
1897 {
1898 switch (*i)
1899 {
1900 case '\\':
1901 i++;
1902 if ('\\' == *i)
1903 {
1904 *o++ = *i++;
1905 }
1906 else
1907 {
1908 if (0 != isdigit(i[0]) && 0 != isdigit(i[1]) && 0 != isdigit(i[2]))
1909 {
1910 *o = (*i++ - 0x30) << 6;
1911 *o += (*i++ - 0x30) << 3;
1912 *o++ += *i++ - 0x30;
1913 }
1914 }
1915 break;
1916
1917 default:
1918 *o++ = *i++;
1919 }
1920 }
1921
1922 return o - io;
1923 }
1924 #endif
1925
zbx_db_fetch(DB_RESULT result)1926 DB_ROW zbx_db_fetch(DB_RESULT result)
1927 {
1928 #if defined(HAVE_ORACLE)
1929 int i;
1930 sword rc;
1931 static char errbuf[512];
1932 sb4 errcode;
1933 #endif
1934
1935 if (NULL == result)
1936 return NULL;
1937
1938 #if defined(HAVE_MYSQL)
1939 if (NULL == result->result)
1940 return NULL;
1941
1942 return (DB_ROW)mysql_fetch_row(result->result);
1943 #elif defined(HAVE_ORACLE)
1944 if (NULL == result->stmthp)
1945 return NULL;
1946
1947 if (OCI_NO_DATA == (rc = OCIStmtFetch2(result->stmthp, oracle.errhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT)))
1948 return NULL;
1949
1950 if (OCI_SUCCESS != rc)
1951 {
1952 ub4 rows_fetched;
1953 ub4 sizep = sizeof(ub4);
1954
1955 if (OCI_SUCCESS != (rc = OCIErrorGet((dvoid *)oracle.errhp, (ub4)1, (text *)NULL,
1956 &errcode, (text *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR)))
1957 {
1958 zbx_db_errlog(ERR_Z3006, rc, zbx_oci_error(rc, NULL), NULL);
1959 return NULL;
1960 }
1961
1962 switch (errcode)
1963 {
1964 case 1012: /* ORA-01012: not logged on */
1965 case 2396: /* ORA-02396: exceeded maximum idle time */
1966 case 3113: /* ORA-03113: end-of-file on communication channel */
1967 case 3114: /* ORA-03114: not connected to ORACLE */
1968 zbx_db_errlog(ERR_Z3006, errcode, errbuf, NULL);
1969 return NULL;
1970 default:
1971 rc = OCIAttrGet((void *)result->stmthp, (ub4)OCI_HTYPE_STMT, (void *)&rows_fetched,
1972 (ub4 *)&sizep, (ub4)OCI_ATTR_ROWS_FETCHED, (OCIError *)oracle.errhp);
1973
1974 if (OCI_SUCCESS != rc || 1 != rows_fetched)
1975 {
1976 zbx_db_errlog(ERR_Z3006, errcode, errbuf, NULL);
1977 return NULL;
1978 }
1979 }
1980 }
1981
1982 for (i = 0; i < result->ncolumn; i++)
1983 {
1984 ub4 alloc, amount;
1985 ub1 csfrm;
1986 sword rc2;
1987
1988 if (NULL == result->clobs[i])
1989 continue;
1990
1991 if (OCI_SUCCESS != (rc2 = OCILobGetLength(oracle.svchp, oracle.errhp, result->clobs[i], &amount)))
1992 {
1993 /* If the LOB is NULL, the length is undefined. */
1994 /* In this case the function returns OCI_INVALID_HANDLE. */
1995 if (OCI_INVALID_HANDLE != rc2)
1996 {
1997 zbx_db_errlog(ERR_Z3006, rc2, zbx_oci_error(rc2, NULL), NULL);
1998 return NULL;
1999 }
2000 else
2001 amount = 0;
2002 }
2003 else if (OCI_SUCCESS != (rc2 = OCILobCharSetForm(oracle.envhp, oracle.errhp, result->clobs[i], &csfrm)))
2004 {
2005 zbx_db_errlog(ERR_Z3006, rc2, zbx_oci_error(rc2, NULL), NULL);
2006 return NULL;
2007 }
2008
2009 if (result->values_alloc[i] < (alloc = amount * ZBX_MAX_BYTES_IN_UTF8_CHAR + 1))
2010 {
2011 result->values_alloc[i] = alloc;
2012 result->values[i] = zbx_realloc(result->values[i], result->values_alloc[i]);
2013 }
2014
2015 if (OCI_SUCCESS == rc2)
2016 {
2017 if (OCI_SUCCESS != (rc2 = OCILobRead(oracle.svchp, oracle.errhp, result->clobs[i], &amount,
2018 (ub4)1, (dvoid *)result->values[i], (ub4)(result->values_alloc[i] - 1),
2019 (dvoid *)NULL, (OCICallbackLobRead)NULL, (ub2)0, csfrm)))
2020 {
2021 zbx_db_errlog(ERR_Z3006, rc2, zbx_oci_error(rc2, NULL), NULL);
2022 return NULL;
2023 }
2024 }
2025
2026 result->values[i][amount] = '\0';
2027 }
2028
2029 return result->values;
2030 #elif defined(HAVE_POSTGRESQL)
2031 /* free old data */
2032 if (NULL != result->values)
2033 zbx_free(result->values);
2034
2035 /* EOF */
2036 if (result->cursor == result->row_num)
2037 return NULL;
2038
2039 /* init result */
2040 result->fld_num = PQnfields(result->pg_result);
2041
2042 if (result->fld_num > 0)
2043 {
2044 int i;
2045
2046 result->values = zbx_malloc(result->values, sizeof(char *) * result->fld_num);
2047
2048 for (i = 0; i < result->fld_num; i++)
2049 {
2050 if (PQgetisnull(result->pg_result, result->cursor, i))
2051 {
2052 result->values[i] = NULL;
2053 }
2054 else
2055 {
2056 result->values[i] = PQgetvalue(result->pg_result, result->cursor, i);
2057 if (PQftype(result->pg_result, i) == ZBX_PG_BYTEAOID) /* binary data type BYTEAOID */
2058 zbx_db_bytea_unescape((u_char *)result->values[i]);
2059 }
2060 }
2061 }
2062
2063 result->cursor++;
2064
2065 return result->values;
2066 #elif defined(HAVE_SQLITE3)
2067 /* EOF */
2068 if (result->curow >= result->nrow)
2069 return NULL;
2070
2071 if (NULL == result->data)
2072 return NULL;
2073
2074 result->curow++; /* NOTE: first row == header row */
2075
2076 return &(result->data[result->curow * result->ncolumn]);
2077 #endif
2078 }
2079
zbx_db_is_null(const char * field)2080 int zbx_db_is_null(const char *field)
2081 {
2082 if (NULL == field)
2083 return SUCCEED;
2084 #ifdef HAVE_ORACLE
2085 if ('\0' == *field)
2086 return SUCCEED;
2087 #endif
2088 return FAIL;
2089 }
2090
2091 #ifdef HAVE_ORACLE
OCI_DBclean_result(DB_RESULT result)2092 static void OCI_DBclean_result(DB_RESULT result)
2093 {
2094 if (NULL == result)
2095 return;
2096
2097 if (NULL != result->values)
2098 {
2099 int i;
2100
2101 for (i = 0; i < result->ncolumn; i++)
2102 {
2103 zbx_free(result->values[i]);
2104
2105 /* deallocate the lob locator variable */
2106 if (NULL != result->clobs[i])
2107 {
2108 OCIDescriptorFree((dvoid *)result->clobs[i], OCI_DTYPE_LOB);
2109 result->clobs[i] = NULL;
2110 }
2111 }
2112
2113 zbx_free(result->values);
2114 zbx_free(result->clobs);
2115 zbx_free(result->values_alloc);
2116 }
2117
2118 if (result->stmthp)
2119 {
2120 OCIHandleFree((dvoid *)result->stmthp, OCI_HTYPE_STMT);
2121 result->stmthp = NULL;
2122 }
2123 }
2124 #endif
2125
DBfree_result(DB_RESULT result)2126 void DBfree_result(DB_RESULT result)
2127 {
2128 #if defined(HAVE_MYSQL)
2129 if (NULL == result)
2130 return;
2131
2132 mysql_free_result(result->result);
2133 zbx_free(result);
2134 #elif defined(HAVE_ORACLE)
2135 int i;
2136
2137 if (NULL == result)
2138 return;
2139
2140 OCI_DBclean_result(result);
2141
2142 for (i = 0; i < oracle.db_results.values_num; i++)
2143 {
2144 if (oracle.db_results.values[i] == result)
2145 {
2146 zbx_vector_ptr_remove_noorder(&oracle.db_results, i);
2147 break;
2148 }
2149 }
2150
2151 zbx_free(result);
2152 #elif defined(HAVE_POSTGRESQL)
2153 if (NULL == result)
2154 return;
2155
2156 if (NULL != result->values)
2157 {
2158 result->fld_num = 0;
2159 zbx_free(result->values);
2160 result->values = NULL;
2161 }
2162
2163 PQclear(result->pg_result);
2164 zbx_free(result);
2165 #elif defined(HAVE_SQLITE3)
2166 if (NULL == result)
2167 return;
2168
2169 if (NULL != result->data)
2170 {
2171 sqlite3_free_table(result->data);
2172 }
2173
2174 zbx_free(result);
2175 #endif /* HAVE_SQLITE3 */
2176 }
2177
2178 #ifdef HAVE_ORACLE
2179 /* server status: OCI_SERVER_NORMAL or OCI_SERVER_NOT_CONNECTED */
OCI_DBserver_status(void)2180 static ub4 OCI_DBserver_status(void)
2181 {
2182 sword err;
2183 ub4 server_status = OCI_SERVER_NOT_CONNECTED;
2184
2185 err = OCIAttrGet((void *)oracle.srvhp, OCI_HTYPE_SERVER, (void *)&server_status,
2186 (ub4 *)0, OCI_ATTR_SERVER_STATUS, (OCIError *)oracle.errhp);
2187
2188 if (OCI_SUCCESS != err)
2189 zabbix_log(LOG_LEVEL_WARNING, "cannot determine Oracle server status, assuming not connected");
2190
2191 return server_status;
2192 }
2193 #endif /* HAVE_ORACLE */
2194
zbx_db_is_escape_sequence(char c)2195 static int zbx_db_is_escape_sequence(char c)
2196 {
2197 #if defined(HAVE_MYSQL)
2198 if ('\'' == c || '\\' == c)
2199 #elif defined(HAVE_POSTGRESQL)
2200 if ('\'' == c || ('\\' == c && 1 == ZBX_PG_ESCAPE_BACKSLASH))
2201 #else
2202 if ('\'' == c)
2203 #endif
2204 return SUCCEED;
2205
2206 return FAIL;
2207 }
2208
2209 /******************************************************************************
2210 * *
2211 * Function: zbx_db_escape_string *
2212 * *
2213 * Return value: escaped string *
2214 * *
2215 * Comments: sync changes with 'zbx_db_get_escape_string_len' *
2216 * and 'zbx_db_dyn_escape_string' *
2217 * *
2218 ******************************************************************************/
zbx_db_escape_string(const char * src,char * dst,size_t len,zbx_escape_sequence_t flag)2219 static void zbx_db_escape_string(const char *src, char *dst, size_t len, zbx_escape_sequence_t flag)
2220 {
2221 const char *s;
2222 char *d;
2223
2224 assert(dst);
2225
2226 len--; /* '\0' */
2227
2228 for (s = src, d = dst; NULL != s && '\0' != *s && 0 < len; s++)
2229 {
2230 if (ESCAPE_SEQUENCE_ON == flag && SUCCEED == zbx_db_is_escape_sequence(*s))
2231 {
2232 if (2 > len)
2233 break;
2234
2235 #if defined(HAVE_MYSQL)
2236 *d++ = '\\';
2237 #elif defined(HAVE_POSTGRESQL)
2238 *d++ = *s;
2239 #else
2240 *d++ = '\'';
2241 #endif
2242 len--;
2243 }
2244 *d++ = *s;
2245 len--;
2246 }
2247 *d = '\0';
2248 }
2249
2250 /******************************************************************************
2251 * *
2252 * Function: zbx_db_get_escape_string_len *
2253 * *
2254 * Purpose: to calculate escaped string length limited by bytes or characters *
2255 * whichever is reached first. *
2256 * *
2257 * Parameters: s - [IN] string to escape *
2258 * max_bytes - [IN] limit in bytes *
2259 * max_chars - [IN] limit in characters *
2260 * flag - [IN] sequences need to be escaped on/off *
2261 * *
2262 * Return value: return length in bytes of escaped string *
2263 * with terminating '\0' *
2264 * *
2265 ******************************************************************************/
zbx_db_get_escape_string_len(const char * s,size_t max_bytes,size_t max_chars,zbx_escape_sequence_t flag)2266 static size_t zbx_db_get_escape_string_len(const char *s, size_t max_bytes, size_t max_chars,
2267 zbx_escape_sequence_t flag)
2268 {
2269 size_t csize, len = 1; /* '\0' */
2270
2271 if (NULL == s)
2272 return len;
2273
2274 while ('\0' != *s && 0 < max_chars)
2275 {
2276 csize = zbx_utf8_char_len(s);
2277
2278 /* process non-UTF-8 characters as single byte characters */
2279 if (0 == csize)
2280 csize = 1;
2281
2282 if (max_bytes < csize)
2283 break;
2284
2285 if (ESCAPE_SEQUENCE_ON == flag && SUCCEED == zbx_db_is_escape_sequence(*s))
2286 len++;
2287
2288 s += csize;
2289 len += csize;
2290 max_bytes -= csize;
2291 max_chars--;
2292 }
2293
2294 return len;
2295 }
2296
2297 /******************************************************************************
2298 * *
2299 * Function: zbx_db_dyn_escape_string *
2300 * *
2301 * Purpose: to escape string limited by bytes or characters, whichever limit *
2302 * is reached first. *
2303 * *
2304 * Parameters: src - [IN] string to escape *
2305 * max_bytes - [IN] limit in bytes *
2306 * max_chars - [IN] limit in characters *
2307 * flag - [IN] sequences need to be escaped on/off *
2308 * *
2309 * Return value: escaped string *
2310 * *
2311 ******************************************************************************/
zbx_db_dyn_escape_string(const char * src,size_t max_bytes,size_t max_chars,zbx_escape_sequence_t flag)2312 char *zbx_db_dyn_escape_string(const char *src, size_t max_bytes, size_t max_chars, zbx_escape_sequence_t flag)
2313 {
2314 char *dst = NULL;
2315 size_t len;
2316
2317 len = zbx_db_get_escape_string_len(src, max_bytes, max_chars, flag);
2318
2319 dst = (char *)zbx_malloc(dst, len);
2320
2321 zbx_db_escape_string(src, dst, len, flag);
2322
2323 return dst;
2324 }
2325
2326 /******************************************************************************
2327 * *
2328 * Function: zbx_db_get_escape_like_pattern_len *
2329 * *
2330 * Return value: return length of escaped LIKE pattern with terminating '\0' *
2331 * *
2332 * Comments: sync changes with 'zbx_db_escape_like_pattern' *
2333 * *
2334 ******************************************************************************/
zbx_db_get_escape_like_pattern_len(const char * src)2335 static int zbx_db_get_escape_like_pattern_len(const char *src)
2336 {
2337 int len;
2338 const char *s;
2339
2340 len = zbx_db_get_escape_string_len(src, ZBX_SIZE_T_MAX, ZBX_SIZE_T_MAX, ESCAPE_SEQUENCE_ON) - 1; /* minus '\0' */
2341
2342 for (s = src; s && *s; s++)
2343 {
2344 len += (*s == '_' || *s == '%' || *s == ZBX_SQL_LIKE_ESCAPE_CHAR);
2345 len += 1;
2346 }
2347
2348 len++; /* '\0' */
2349
2350 return len;
2351 }
2352
2353 /******************************************************************************
2354 * *
2355 * Function: zbx_db_escape_like_pattern *
2356 * *
2357 * Return value: escaped string to be used as pattern in LIKE *
2358 * *
2359 * Comments: sync changes with 'zbx_db_get_escape_like_pattern_len' *
2360 * *
2361 * For instance, we wish to find string a_b%c\d'e!f in our database *
2362 * using '!' as escape character. Our queries then become: *
2363 * *
2364 * ... LIKE 'a!_b!%c\\d\'e!!f' ESCAPE '!' (MySQL, PostgreSQL) *
2365 * ... LIKE 'a!_b!%c\d''e!!f' ESCAPE '!' (Oracle, SQLite3) *
2366 * *
2367 * Using backslash as escape character in LIKE would be too much *
2368 * trouble, because escaping backslashes would have to be escaped *
2369 * as well, like so: *
2370 * *
2371 * ... LIKE 'a\\_b\\%c\\\\d\'e!f' ESCAPE '\\' or *
2372 * ... LIKE 'a\\_b\\%c\\\\d\\\'e!f' ESCAPE '\\' (MySQL, PostgreSQL) *
2373 * ... LIKE 'a\_b\%c\\d''e!f' ESCAPE '\' (Oracle, SQLite3) *
2374 * *
2375 * Hence '!' instead of backslash. *
2376 * *
2377 ******************************************************************************/
zbx_db_escape_like_pattern(const char * src,char * dst,int len)2378 static void zbx_db_escape_like_pattern(const char *src, char *dst, int len)
2379 {
2380 char *d;
2381 char *tmp = NULL;
2382 const char *t;
2383
2384 assert(dst);
2385
2386 tmp = (char *)zbx_malloc(tmp, len);
2387
2388 zbx_db_escape_string(src, tmp, len, ESCAPE_SEQUENCE_ON);
2389
2390 len--; /* '\0' */
2391
2392 for (t = tmp, d = dst; t && *t && len; t++)
2393 {
2394 if (*t == '_' || *t == '%' || *t == ZBX_SQL_LIKE_ESCAPE_CHAR)
2395 {
2396 if (len <= 1)
2397 break;
2398 *d++ = ZBX_SQL_LIKE_ESCAPE_CHAR;
2399 len--;
2400 }
2401 *d++ = *t;
2402 len--;
2403 }
2404
2405 *d = '\0';
2406
2407 zbx_free(tmp);
2408 }
2409
2410 /******************************************************************************
2411 * *
2412 * Function: zbx_db_dyn_escape_like_pattern *
2413 * *
2414 * Return value: escaped string to be used as pattern in LIKE *
2415 * *
2416 ******************************************************************************/
zbx_db_dyn_escape_like_pattern(const char * src)2417 char *zbx_db_dyn_escape_like_pattern(const char *src)
2418 {
2419 int len;
2420 char *dst = NULL;
2421
2422 len = zbx_db_get_escape_like_pattern_len(src);
2423
2424 dst = (char *)zbx_malloc(dst, len);
2425
2426 zbx_db_escape_like_pattern(src, dst, len);
2427
2428 return dst;
2429 }
2430
2431 /******************************************************************************
2432 * *
2433 * Function: zbx_db_strlen_n *
2434 * *
2435 * Purpose: return the string length to fit into a database field of the *
2436 * specified size *
2437 * *
2438 * Return value: the string length in bytes *
2439 * *
2440 ******************************************************************************/
zbx_db_strlen_n(const char * text,size_t maxlen)2441 int zbx_db_strlen_n(const char *text, size_t maxlen)
2442 {
2443 return zbx_strlen_utf8_nchars(text, maxlen);
2444 }
2445
2446 #if defined(HAVE_POSTGRESQL)
2447 /******************************************************************************
2448 * *
2449 * Function: zbx_dbms_get_version *
2450 * *
2451 * Purpose: returns DBMS version as integer: MMmmuu *
2452 * M = major version part *
2453 * m = minor version part *
2454 * u = patch version part *
2455 * *
2456 * Example: 1.2.34 version will be returned as 10234 *
2457 * *
2458 * Return value: DBMS version or 0 if unknown *
2459 * *
2460 ******************************************************************************/
zbx_dbms_get_version(void)2461 int zbx_dbms_get_version(void)
2462 {
2463 return ZBX_PG_SVERSION;
2464 }
2465
2466 /******************************************************************************
2467 * *
2468 * Function: zbx_tsdb_get_version *
2469 * *
2470 * Purpose: returns TimescaleDB (TSDB) version as integer: MMmmuu *
2471 * M = major version part *
2472 * m = minor version part *
2473 * u = patch version part *
2474 * *
2475 * Example: TSDB 1.5.1 version will be returned as 10501 *
2476 * *
2477 * Return value: TSDB version or 0 if unknown or the extension not installed *
2478 * *
2479 ******************************************************************************/
zbx_tsdb_get_version(void)2480 int zbx_tsdb_get_version(void)
2481 {
2482 int ver, major, minor, patch;
2483 DB_RESULT result;
2484 DB_ROW row;
2485
2486 if (-1 == ZBX_TSDB_VERSION)
2487 {
2488 /* catalog pg_extension not available */
2489 if (90001 > ZBX_PG_SVERSION)
2490 {
2491 ver = ZBX_TSDB_VERSION = 0;
2492 goto out;
2493 }
2494
2495 result = zbx_db_select("select extversion from pg_extension where extname = 'timescaledb'");
2496
2497 /* database down, can re-query in the next call */
2498 if ((DB_RESULT)ZBX_DB_DOWN == result)
2499 {
2500 ver = 0;
2501 goto out;
2502 }
2503
2504 /* extension is not installed */
2505 if (NULL == result)
2506 {
2507 ver = ZBX_TSDB_VERSION = 0;
2508 goto out;
2509 }
2510
2511 if (NULL != (row = zbx_db_fetch(result)) &&
2512 3 == sscanf((const char*)row[0], "%d.%d.%d", &major, &minor, &patch))
2513 {
2514 ver = major * 10000;
2515 ver += minor * 100;
2516 ver += patch;
2517 ZBX_TSDB_VERSION = ver;
2518 }
2519 else
2520 ver = ZBX_TSDB_VERSION = 0;
2521
2522 DBfree_result(result);
2523 }
2524 else
2525 ver = ZBX_TSDB_VERSION;
2526 out:
2527 return ver;
2528 }
2529
2530 #endif
2531