1<?php 2/* 3** Zabbix 4** Copyright (C) 2001-2021 Zabbix SIA 5** 6** This program is free software; you can redistribute it and/or modify 7** it under the terms of the GNU General Public License as published by 8** the Free Software Foundation; either version 2 of the License, or 9** (at your option) any later version. 10** 11** This program is distributed in the hope that it will be useful, 12** but WITHOUT ANY WARRANTY; without even the implied warranty of 13** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 14** GNU General Public License for more details. 15** 16** You should have received a copy of the GNU General Public License 17** along with this program; if not, write to the Free Software 18** Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 19**/ 20 21 22/** 23 * Creates global database connection. 24 * 25 * @param string $error returns a message in case of an error 26 * @param bool $debug turns On or Off trace calls when making connections. Suggested debug mode Off during Zabbix setup 27 * 28 * @return bool 29 */ 30function DBconnect(&$error) { 31 global $DB; 32 33 if (isset($DB['DB'])) { 34 $error = _('Cannot create another database connection.'); 35 return false; 36 } 37 38 $result = true; 39 40 $DB['DB'] = null; // global db handler 41 $DB['TRANSACTIONS'] = 0; // level of a nested transation 42 $DB['TRANSACTION_NO_FAILED_SQLS'] = true; // true - if no statements failed in transaction, false - there are failed statements 43 $DB['SELECT_COUNT'] = 0; // stats 44 $DB['EXECUTE_COUNT'] = 0; 45 46 if (!isset($DB['TYPE'])) { 47 $error = 'Unknown database type.'; 48 $result = false; 49 } 50 else { 51 switch ($DB['TYPE']) { 52 case ZBX_DB_MYSQL: 53 $DB['DB'] = @mysqli_connect($DB['SERVER'], $DB['USER'], $DB['PASSWORD'], $DB['DATABASE'], $DB['PORT']); 54 if (!$DB['DB']) { 55 $error = 'Error connecting to database: '.trim(mysqli_connect_error()); 56 $result = false; 57 } 58 elseif (mysqli_autocommit($DB['DB'], true) === false) { 59 $error = 'Error setting auto commit.'; 60 $result = false; 61 } 62 else { 63 DBexecute('SET NAMES utf8'); 64 } 65 66 if ($result) { 67 $dbBackend = new MysqlDbBackend(); 68 } 69 break; 70 case ZBX_DB_POSTGRESQL: 71 $pg_connection_string = 72 (!empty($DB['SERVER']) ? 'host=\''.pg_connect_escape($DB['SERVER']).'\' ' : ''). 73 'dbname=\''.pg_connect_escape($DB['DATABASE']).'\' '. 74 (!empty($DB['USER']) ? 'user=\''.pg_connect_escape($DB['USER']).'\' ' : ''). 75 (!empty($DB['PASSWORD']) ? 'password=\''.pg_connect_escape($DB['PASSWORD']).'\' ' : ''). 76 (!empty($DB['PORT']) ? 'port='.pg_connect_escape($DB['PORT']) : ''); 77 78 $DB['DB']= @pg_connect($pg_connection_string); 79 if (!$DB['DB']) { 80 $error = 'Error connecting to database.'; 81 $result = false; 82 } 83 else { 84 $schemaSet = DBexecute('SET search_path = '.zbx_dbstr($DB['SCHEMA'] ? $DB['SCHEMA'] : 'public'), true); 85 86 if(!$schemaSet) { 87 clear_messages(); 88 $error = pg_last_error(); 89 $result = false; 90 } 91 else { 92 if (false !== ($pgsql_version = pg_parameter_status('server_version'))) { 93 if ((int) $pgsql_version >= 9) { 94 // change the output format for values of type bytea from hex (the default) to escape 95 DBexecute('SET bytea_output = escape'); 96 } 97 } 98 } 99 } 100 101 if ($result) { 102 $dbBackend = new PostgresqlDbBackend(); 103 } 104 break; 105 case ZBX_DB_ORACLE: 106 $connect = ''; 107 if (!empty($DB['SERVER'])) { 108 $connect = '//'.$DB['SERVER']; 109 110 if ($DB['PORT'] != '0') { 111 $connect .= ':'.$DB['PORT']; 112 } 113 if ($DB['DATABASE']) { 114 $connect .= '/'.$DB['DATABASE']; 115 } 116 } 117 118 $DB['DB'] = @oci_connect($DB['USER'], $DB['PASSWORD'], $connect); 119 if ($DB['DB']) { 120 DBexecute('ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.zbx_dbstr('. ')); 121 } 122 else { 123 $ociError = oci_error(); 124 $error = 'Error connecting to database: '.$ociError['message']; 125 $result = false; 126 } 127 128 if ($result) { 129 $dbBackend = new OracleDbBackend(); 130 } 131 break; 132 case ZBX_DB_DB2: 133 $connect = ''; 134 $connect .= 'DATABASE='.$DB['DATABASE'].';'; 135 $connect .= 'HOSTNAME='.$DB['SERVER'].';'; 136 $connect .= 'PORT='.$DB['PORT'].';'; 137 $connect .= 'PROTOCOL=TCPIP;'; 138 $connect .= 'UID='.$DB['USER'].';'; 139 $connect .= 'PWD='.$DB['PASSWORD'].';'; 140 141 $DB['DB'] = @db2_connect($connect, $DB['USER'], $DB['PASSWORD']); 142 if (!$DB['DB']) { 143 $error = 'Error connecting to database: '.db2_conn_errormsg(); 144 $result = false; 145 } 146 else { 147 $options = [ 148 'db2_attr_case' => DB2_CASE_LOWER, 149 ]; 150 db2_set_option($DB['DB'], $options, 1); 151 if (isset($DB['SCHEMA']) && $DB['SCHEMA'] != '') { 152 DBexecute('SET CURRENT SCHEMA='.zbx_dbstr($DB['SCHEMA'])); 153 } 154 } 155 156 if ($result) { 157 $dbBackend = new Db2DbBackend(); 158 } 159 break; 160 case ZBX_DB_SQLITE3: 161 if (file_exists($DB['DATABASE'])) { 162 init_sqlite3_access(); 163 lock_sqlite3_access(); 164 try{ 165 $DB['DB'] = @new SQLite3($DB['DATABASE'], SQLITE3_OPEN_READWRITE); 166 } 167 catch (Exception $e) { 168 $error = 'Error connecting to database.'; 169 $result = false; 170 } 171 unlock_sqlite3_access(); 172 } 173 else { 174 $error = 'Missing database'; 175 $result = false; 176 } 177 178 if ($result) { 179 $dbBackend = new SqliteDbBackend(); 180 } 181 break; 182 default: 183 $error = 'Unsupported database'; 184 $result = false; 185 } 186 } 187 188 if ($result && (!$dbBackend->checkDbVersion() || !$dbBackend->checkConfig())) { 189 $error = $dbBackend->getError(); 190 $result = false; 191 } 192 193 if (false == $result) { 194 $DB['DB'] = null; 195 } 196 197 return $result; 198} 199 200function DBclose() { 201 global $DB; 202 203 $result = false; 204 205 if (isset($DB['DB']) && !empty($DB['DB'])) { 206 switch ($DB['TYPE']) { 207 case ZBX_DB_MYSQL: 208 $result = mysqli_close($DB['DB']); 209 break; 210 case ZBX_DB_POSTGRESQL: 211 $result = pg_close($DB['DB']); 212 break; 213 case ZBX_DB_ORACLE: 214 $result = oci_close($DB['DB']); 215 break; 216 case ZBX_DB_DB2: 217 $result = db2_close($DB['DB']); 218 break; 219 case ZBX_DB_SQLITE3: 220 lock_sqlite3_access(); 221 $DB['DB']->close(); 222 unlock_sqlite3_access(); 223 $result = true; 224 break; 225 } 226 } 227 unset($DB['DB']); 228 return $result; 229} 230 231function DBstart() { 232 global $DB; 233 234 $result = false; 235 236 if ($DB['TRANSACTIONS'] != 0) { 237 info('POSSIBLE ERROR: Used incorrect logic in database processing, started subtransaction!'); 238 return $result; 239 } 240 241 $DB['TRANSACTIONS']++; 242 $DB['TRANSACTION_NO_FAILED_SQLS'] = true; 243 244 if (!isset($DB['DB']) || empty($DB['DB'])) { 245 return $result; 246 } 247 248 switch ($DB['TYPE']) { 249 case ZBX_DB_MYSQL: 250 $result = DBexecute('BEGIN'); 251 break; 252 case ZBX_DB_POSTGRESQL: 253 $result = DBexecute('BEGIN'); 254 break; 255 case ZBX_DB_ORACLE: 256 $result = true; 257 break; 258 case ZBX_DB_DB2: 259 $result = db2_autocommit($DB['DB'], DB2_AUTOCOMMIT_OFF); 260 break; 261 case ZBX_DB_SQLITE3: 262 lock_sqlite3_access(); 263 $result = DBexecute('BEGIN'); 264 break; 265 } 266 return $result; 267} 268 269/** 270 * Closes transaction. 271 * 272 * @param string $doCommit True - do commit, rollback otherwise. Rollback is also always performed if a sql failed within this transaction. 273 * 274 * @return bool True - successful commit, False - otherwise 275 */ 276function DBend($doCommit = true) { 277 global $DB; 278 279 $result = false; 280 281 if (!isset($DB['DB']) || empty($DB['DB'])) { 282 return $result; 283 } 284 285 if ($DB['TRANSACTIONS'] == 0) { 286 info('POSSIBLE ERROR: Used incorrect logic in database processing, transaction not started!'); 287 return $result; 288 } 289 290 $DBresult = $doCommit && $DB['TRANSACTION_NO_FAILED_SQLS']; 291 292 if ($DBresult) { 293 $DBresult = DBcommit(); 294 } 295 else { 296 DBrollback(); 297 } 298 299 $DB['TRANSACTIONS'] = 0; 300 301 return (!is_null($doCommit) && $DBresult) ? $doCommit : $DBresult; 302} 303 304function DBcommit() { 305 global $DB; 306 307 $result = false; 308 309 switch ($DB['TYPE']) { 310 case ZBX_DB_MYSQL: 311 $result = DBexecute('COMMIT'); 312 break; 313 case ZBX_DB_POSTGRESQL: 314 $result = DBexecute('COMMIT'); 315 break; 316 case ZBX_DB_ORACLE: 317 $result = oci_commit($DB['DB']); 318 break; 319 case ZBX_DB_DB2: 320 $result = db2_commit($DB['DB']); 321 if ($result) { 322 db2_autocommit($DB['DB'], DB2_AUTOCOMMIT_ON); 323 } 324 break; 325 case ZBX_DB_SQLITE3: 326 $result = DBexecute('COMMIT'); 327 unlock_sqlite3_access(); 328 break; 329 } 330 return $result; 331} 332 333function DBrollback() { 334 global $DB; 335 336 $result = false; 337 338 switch ($DB['TYPE']) { 339 case ZBX_DB_MYSQL: 340 $result = DBexecute('ROLLBACK'); 341 break; 342 case ZBX_DB_POSTGRESQL: 343 $result = DBexecute('ROLLBACK'); 344 break; 345 case ZBX_DB_ORACLE: 346 $result = oci_rollback($DB['DB']); 347 break; 348 case ZBX_DB_DB2: 349 $result = db2_rollback($DB['DB']); 350 db2_autocommit($DB['DB'], DB2_AUTOCOMMIT_ON); 351 break; 352 case ZBX_DB_SQLITE3: 353 $result = DBexecute('ROLLBACK'); 354 unlock_sqlite3_access(); 355 break; 356 } 357 return $result; 358} 359 360/** 361 * Select data from DB. Use function DBexecute for non-selects. 362 * 363 * Example: 364 * DBselect('select * from users') 365 * DBselect('select * from users',50,200) 366 * 367 * @param string $query 368 * @param int $limit max number of record to return 369 * @param int $offset return starting from $offset record 370 * 371 * @return resource or object, False if failed 372 */ 373function DBselect($query, $limit = null, $offset = 0) { 374 global $DB; 375 376 $result = false; 377 378 if (!isset($DB['DB']) || empty($DB['DB'])) { 379 return $result; 380 } 381 382 // add the LIMIT clause 383 if(!$query = DBaddLimit($query, $limit, $offset)) { 384 return false; 385 } 386 387 $time_start = microtime(true); 388 $DB['SELECT_COUNT']++; 389 390 switch ($DB['TYPE']) { 391 case ZBX_DB_MYSQL: 392 if (!$result = mysqli_query($DB['DB'], $query)) { 393 error('Error in query ['.$query.'] ['.mysqli_error($DB['DB']).']', 'sql'); 394 } 395 break; 396 case ZBX_DB_POSTGRESQL: 397 if (!$result = pg_query($DB['DB'], $query)) { 398 error('Error in query ['.$query.'] ['.pg_last_error().']', 'sql'); 399 } 400 break; 401 case ZBX_DB_ORACLE: 402 if (!$result = oci_parse($DB['DB'], $query)) { 403 $e = @oci_error(); 404 error('SQL error ['.$e['message'].'] in ['.$e['sqltext'].']', 'sql'); 405 } 406 elseif (!@oci_execute($result, ($DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS))) { 407 $e = oci_error($result); 408 error('SQL error ['.$e['message'].'] in ['.$e['sqltext'].']', 'sql'); 409 } 410 break; 411 case ZBX_DB_DB2: 412 $options = []; 413 if ($DB['TRANSACTIONS']) { 414 $options['autocommit'] = DB2_AUTOCOMMIT_OFF; 415 } 416 417 if (!$result = db2_prepare($DB['DB'], $query)) { 418 $e = @db2_stmt_errormsg($result); 419 error('SQL error ['.$query.'] in ['.$e.']', 'sql'); 420 } 421 elseif (true !== @db2_execute($result, $options)) { 422 $e = @db2_stmt_errormsg($result); 423 error('SQL error ['.$query.'] in ['.$e.']', 'sql'); 424 $result = false; 425 } 426 break; 427 case ZBX_DB_SQLITE3: 428 if ($DB['TRANSACTIONS'] == 0) { 429 lock_sqlite3_access(); 430 } 431 if (false === ($result = $DB['DB']->query($query))) { 432 $msg = 'Error in query ['.$query.'] Error code ['.$DB['DB']->lastErrorCode().'] Message ['. 433 $DB['DB']->lastErrorMsg().']'; 434 error($msg, 'sql'); 435 } 436 if ($DB['TRANSACTIONS'] == 0) { 437 unlock_sqlite3_access(); 438 } 439 break; 440 } 441 442 // $result is false only if an error occurred 443 if ($DB['TRANSACTION_NO_FAILED_SQLS'] && !$result) { 444 $DB['TRANSACTION_NO_FAILED_SQLS'] = false; 445 } 446 447 if (CApiService::$userData !== null && array_key_exists('debug_mode', CApiService::$userData) 448 && CApiService::$userData['debug_mode'] == GROUP_DEBUG_MODE_ENABLED) { 449 CProfiler::getInstance()->profileSql(microtime(true) - $time_start, $query); 450 } 451 452 return $result; 453} 454 455/** 456 * Add the LIMIT clause to the given query. 457 * 458 * NOTE: 459 * LIMIT and OFFSET records 460 * 461 * Example: select 6-15 row. 462 * 463 * MySQL: 464 * SELECT a FROM tbl LIMIT 5,10 465 * SELECT a FROM tbl LIMIT 10 OFFSET 5 466 * 467 * PostgreSQL: 468 * SELECT a FROM tbl LIMIT 10 OFFSET 5 469 * 470 * Oracle, DB2: 471 * SELECT a FROM tbe WHERE rownum < 15 // ONLY < 15 472 * SELECT * FROM (SELECT * FROM tbl) WHERE rownum BETWEEN 6 AND 15 473 * 474 * @param $query 475 * @param int $limit max number of record to return 476 * @param int $offset return starting from $offset record 477 * 478 * @return bool|string 479 */ 480function DBaddLimit($query, $limit = 0, $offset = 0) { 481 global $DB; 482 483 if ((isset($limit) && ($limit < 0 || !zbx_ctype_digit($limit))) || $offset < 0 || !zbx_ctype_digit($offset)) { 484 $moreDetails = isset($limit) ? ' Limit ['.$limit.'] Offset ['.$offset.']' : ' Offset ['.$offset.']'; 485 error('Incorrect parameters for limit and/or offset. Query ['.$query.']'.$moreDetails, 'sql'); 486 487 return false; 488 } 489 490 // Process limit and offset 491 if (isset($limit)) { 492 switch ($DB['TYPE']) { 493 case ZBX_DB_MYSQL: 494 case ZBX_DB_POSTGRESQL: 495 case ZBX_DB_SQLITE3: 496 $query .= ' LIMIT '.intval($limit).' OFFSET '.intval($offset); 497 break; 498 case ZBX_DB_ORACLE: 499 case ZBX_DB_DB2: 500 $till = $offset + $limit; 501 $query = 'SELECT * FROM ('.$query.') WHERE rownum BETWEEN '.intval($offset).' AND '.intval($till); 502 break; 503 } 504 } 505 506 return $query; 507} 508 509function DBexecute($query, $skip_error_messages = 0) { 510 global $DB; 511 512 if (!isset($DB['DB']) || empty($DB['DB'])) { 513 return false; 514 } 515 516 $result = false; 517 $time_start = microtime(true); 518 519 $DB['EXECUTE_COUNT']++; 520 521 switch ($DB['TYPE']) { 522 case ZBX_DB_MYSQL: 523 if (!$result = mysqli_query($DB['DB'], $query)) { 524 error('Error in query ['.$query.'] ['.mysqli_error($DB['DB']).']', 'sql'); 525 } 526 break; 527 case ZBX_DB_POSTGRESQL: 528 if (!$result = (bool) pg_query($DB['DB'], $query)) { 529 error('Error in query ['.$query.'] ['.pg_last_error().']', 'sql'); 530 } 531 break; 532 case ZBX_DB_ORACLE: 533 if (!$result = oci_parse($DB['DB'], $query)) { 534 $e = @oci_error(); 535 error('SQL error ['.$e['message'].'] in ['.$e['sqltext'].']', 'sql'); 536 } 537 elseif (!@oci_execute($result, ($DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS))) { 538 $e = oci_error($result); 539 error('SQL error ['.$e['message'].'] in ['.$e['sqltext'].']', 'sql'); 540 } 541 else { 542 $result = true; // function must return boolean 543 } 544 break; 545 case ZBX_DB_DB2: 546 if (!$result = db2_prepare($DB['DB'], $query)) { 547 $e = @db2_stmt_errormsg($result); 548 error('SQL error ['.$query.'] in ['.$e.']', 'sql'); 549 } 550 elseif (true !== @db2_execute($result)) { 551 $e = @db2_stmt_errormsg($result); 552 error('SQL error ['.$query.'] in ['.$e.']', 'sql'); 553 } 554 else { 555 $result = true; // function must return boolean 556 } 557 break; 558 case ZBX_DB_SQLITE3: 559 if ($DB['TRANSACTIONS'] == 0) { 560 lock_sqlite3_access(); 561 } 562 if (!$result = $DB['DB']->exec($query)) { 563 $msg = 'Error in query ['.$query.'] Error code ['.$DB['DB']->lastErrorCode().'] Message ['. 564 $DB['DB']->lastErrorMsg().']'; 565 error($msg, 'sql'); 566 } 567 if ($DB['TRANSACTIONS'] == 0) { 568 unlock_sqlite3_access(); 569 } 570 break; 571 } 572 if ($DB['TRANSACTIONS'] != 0 && !$result) { 573 $DB['TRANSACTION_NO_FAILED_SQLS'] = false; 574 } 575 576 if (CApiService::$userData !== null && array_key_exists('debug_mode', CApiService::$userData) 577 && CApiService::$userData['debug_mode'] == GROUP_DEBUG_MODE_ENABLED) { 578 CProfiler::getInstance()->profileSql(microtime(true) - $time_start, $query); 579 } 580 581 return (bool) $result; 582} 583 584/** 585 * Returns the next data set from a DB resource or false if there are no more results. 586 * 587 * @param resource $cursor 588 * @param bool $convertNulls convert all null values to string zeroes 589 * 590 * @return array|bool 591 */ 592function DBfetch($cursor, $convertNulls = true) { 593 global $DB; 594 595 $result = false; 596 597 if (!isset($DB['DB']) || empty($DB['DB']) || is_bool($cursor)) { 598 return $result; 599 } 600 601 switch ($DB['TYPE']) { 602 case ZBX_DB_MYSQL: 603 $result = mysqli_fetch_assoc($cursor); 604 if (!$result) { 605 mysqli_free_result($cursor); 606 } 607 break; 608 case ZBX_DB_POSTGRESQL: 609 if (!$result = pg_fetch_assoc($cursor)) { 610 pg_free_result($cursor); 611 } 612 break; 613 case ZBX_DB_ORACLE: 614 if ($row = oci_fetch_assoc($cursor)) { 615 $result = []; 616 foreach ($row as $key => $value) { 617 $field_type = strtolower(oci_field_type($cursor, $key)); 618 // Oracle does not support NULL values for string fields, so if the string is empty, it will return NULL 619 // convert it to an empty string to be consistent with other databases 620 $value = (str_in_array($field_type, ['varchar', 'varchar2', 'blob', 'clob']) && is_null($value)) ? '' : $value; 621 622 if (is_object($value) && (strpos($field_type, 'lob') !== false)) { 623 $value = $value->load(); 624 } 625 $result[strtolower($key)] = $value; 626 } 627 } 628 break; 629 case ZBX_DB_DB2: 630 if (!$result = db2_fetch_assoc($cursor)) { 631 db2_free_result($cursor); 632 } 633 else { 634 // cast all of the values to string to be consistent with other DB drivers: all of them return 635 // only strings. 636 foreach ($result as &$value) { 637 if ($value !== null) { 638 $value = (string) $value; 639 } 640 } 641 unset($value); 642 } 643 break; 644 case ZBX_DB_SQLITE3: 645 if ($DB['TRANSACTIONS'] == 0) { 646 lock_sqlite3_access(); 647 } 648 if (!$result = $cursor->fetchArray(SQLITE3_ASSOC)) { 649 unset($cursor); 650 } 651 else { 652 // cast all of the values to string to be consistent with other DB drivers: all of them return 653 // only strings. 654 foreach ($result as &$value) { 655 $value = (string) $value; 656 } 657 unset($value); 658 } 659 if ($DB['TRANSACTIONS'] == 0) { 660 unlock_sqlite3_access(); 661 } 662 break; 663 } 664 665 if ($result) { 666 if ($convertNulls) { 667 foreach ($result as $key => $val) { 668 if (is_null($val)) { 669 $result[$key] = '0'; 670 } 671 } 672 } 673 674 return $result; 675 } 676 677 return false; 678} 679 680function zbx_sql_mod($x, $y) { 681 global $DB; 682 683 switch ($DB['TYPE']) { 684 case ZBX_DB_SQLITE3: 685 return ' (('.$x.') % ('.$y.'))'; 686 default: 687 return ' MOD('.$x.','.$y.')'; 688 } 689} 690 691function get_dbid($table, $field) { 692 // PGSQL on transaction failure on all queries returns false.. 693 global $DB; 694 695 if ($DB['TYPE'] == ZBX_DB_POSTGRESQL && $DB['TRANSACTIONS'] && !$DB['TRANSACTION_NO_FAILED_SQLS']) { 696 return 0; 697 } 698 699 $found = false; 700 701 $min = 0; 702 $max = ZBX_DB_MAX_ID; 703 704 do { 705 $dbSelect = DBselect('SELECT i.nextid FROM ids i WHERE i.table_name='.zbx_dbstr($table).' AND i.field_name='.zbx_dbstr($field)); 706 if (!$dbSelect) { 707 return false; 708 } 709 710 $row = DBfetch($dbSelect); 711 if (!$row) { 712 $row = DBfetch(DBselect('SELECT MAX('.$field.') AS id FROM '.$table.' WHERE '.$field.' BETWEEN '.$min.' AND '.$max)); 713 if (!$row || ($row['id'] == 0)) { 714 DBexecute("INSERT INTO ids (table_name,field_name,nextid) VALUES ('$table','$field',$min)"); 715 } 716 else { 717 DBexecute("INSERT INTO ids (table_name,field_name,nextid) VALUES ('$table','$field',".$row['id'].')'); 718 } 719 continue; 720 } 721 else { 722 $ret1 = $row['nextid']; 723 if (bccomp($ret1, $min) < 0 || !bccomp($ret1, $max) < 0) { 724 DBexecute('DELETE FROM ids WHERE table_name='.zbx_dbstr($table).' AND field_name='.zbx_dbstr($field)); 725 continue; 726 } 727 728 $sql = 'UPDATE ids SET nextid=nextid+1 WHERE table_name='.zbx_dbstr($table).' AND field_name='.zbx_dbstr($field); 729 DBexecute($sql); 730 731 $row = DBfetch(DBselect('SELECT i.nextid FROM ids i WHERE i.table_name='.zbx_dbstr($table).' AND i.field_name='.zbx_dbstr($field))); 732 if (!$row || is_null($row['nextid'])) { 733 // should never be here 734 continue; 735 } 736 else { 737 $ret2 = $row['nextid']; 738 if (bccomp(bcadd($ret1, 1, 0), $ret2, 0) == 0) { 739 $found = true; 740 } 741 } 742 } 743 } 744 while (false == $found); 745 746 return $ret2; 747} 748 749function zbx_db_distinct($sql_parts) { 750 if (count($sql_parts['from']) > 1) { 751 return ' DISTINCT '; 752 } 753 else { 754 return ' '; 755 } 756} 757 758function zbx_db_search($table, $options, &$sql_parts) { 759 list($table, $tableShort) = explode(' ', $table); 760 761 $tableSchema = DB::getSchema($table); 762 if (!$tableSchema) { 763 info(_s('Error in search request for table "%1$s".', $table)); 764 } 765 766 $start = is_null($options['startSearch']) ? '%' : ''; 767 $exclude = is_null($options['excludeSearch']) ? '' : ' NOT '; 768 $glue = (!$options['searchByAny']) ? ' AND ' : ' OR '; 769 770 $search = []; 771 foreach ($options['search'] as $field => $patterns) { 772 if (!isset($tableSchema['fields'][$field]) || zbx_empty($patterns)) { 773 continue; 774 } 775 if ($tableSchema['fields'][$field]['type'] != DB::FIELD_TYPE_CHAR 776 && $tableSchema['fields'][$field]['type'] != DB::FIELD_TYPE_TEXT) { 777 continue; 778 } 779 780 $fieldSearch = []; 781 foreach ((array) $patterns as $pattern) { 782 if (zbx_empty($pattern)) { 783 continue; 784 } 785 786 // escaping parameter that is about to be used in LIKE statement 787 $pattern = str_replace("!", "!!", $pattern); 788 $pattern = str_replace("%", "!%", $pattern); 789 $pattern = str_replace("_", "!_", $pattern); 790 791 if (!$options['searchWildcardsEnabled']) { 792 $fieldSearch[] = 793 ' UPPER('.$tableShort.'.'.$field.') '. 794 $exclude.' LIKE '. 795 zbx_dbstr($start.mb_strtoupper($pattern).'%'). 796 " ESCAPE '!'"; 797 } 798 else { 799 $pattern = str_replace("*", "%", $pattern); 800 $fieldSearch[] = 801 ' UPPER('.$tableShort.'.'.$field.') '. 802 $exclude.' LIKE '. 803 zbx_dbstr(mb_strtoupper($pattern)). 804 " ESCAPE '!'"; 805 } 806 } 807 808 $search[$field] = '( '.implode($glue, $fieldSearch).' )'; 809 } 810 811 if (!empty($search)) { 812 if (isset($sql_parts['where']['search'])) { 813 $search[] = $sql_parts['where']['search']; 814 } 815 816 $sql_parts['where']['search'] = '( '.implode($glue, $search).' )'; 817 return true; 818 } 819 820 return false; 821} 822 823/** 824 * Checks whether all $db_fields keys exists as $args keys. 825 * 826 * If $db_fields element value is given and corresponding $args is not then it is assigned to $args element. 827 * 828 * @param $dbFields 829 * @param $args 830 * 831 * @return bool 832 */ 833function check_db_fields($dbFields, &$args) { 834 if (!is_array($args)) { 835 return false; 836 } 837 838 foreach ($dbFields as $field => $def) { 839 if (!isset($args[$field])) { 840 if (is_null($def)) { 841 return false; 842 } 843 else { 844 $args[$field] = $def; 845 } 846 } 847 } 848 849 return true; 850} 851 852/** 853 * Takes an initial part of SQL query and appends a generated WHERE condition. 854 * The WHERE condition is generated from the given list of values as a mix of 855 * <fieldname> BETWEEN <id1> AND <idN>" and "<fieldname> IN (<id1>,<id2>,...,<idN>)" elements. 856 * 857 * In some frontend places we can get array with bool as input values parameter. This is fail! 858 * Therefore we need check it and return 1=0 as temporary solution to not break the frontend. 859 * 860 * @param string $fieldName field name to be used in SQL WHERE condition 861 * @param array $values array of numerical values sorted in ascending order to be included in WHERE 862 * @param bool $notIn builds inverted condition 863 * @param bool $sort values mandatory must be sorted 864 * 865 * @return string 866 */ 867function dbConditionInt($fieldName, array $values, $notIn = false, $sort = true) { 868 $MAX_EXPRESSIONS = 950; // maximum number of values for using "IN (id1>,<id2>,...,<idN>)" 869 $MIN_NUM_BETWEEN = 4; // minimum number of consecutive values for using "BETWEEN <id1> AND <idN>" 870 871 if (is_bool(reset($values))) { 872 return '1=0'; 873 } 874 875 $values = array_keys(array_flip($values)); 876 877 if ($sort) { 878 natsort($values); 879 880 $values = array_values($values); 881 } 882 883 $betweens = []; 884 $data = []; 885 886 for ($i = 0, $size = count($values); $i < $size; $i++) { 887 $between = []; 888 889 // analyze by chunk 890 if (isset($values[$i + $MIN_NUM_BETWEEN]) 891 && bccomp(bcadd($values[$i], $MIN_NUM_BETWEEN), $values[$i + $MIN_NUM_BETWEEN]) == 0) { 892 for ($sizeMinBetween = $i + $MIN_NUM_BETWEEN; $i < $sizeMinBetween; $i++) { 893 $between[] = $values[$i]; 894 } 895 896 $i--; // shift 1 back 897 898 // analyze by one 899 for (; $i < $size; $i++) { 900 if (isset($values[$i + 1]) && bccomp(bcadd($values[$i], 1), $values[$i + 1]) == 0) { 901 $between[] = $values[$i + 1]; 902 } 903 else { 904 break; 905 } 906 } 907 908 $betweens[] = $between; 909 } 910 else { 911 $data[] = $values[$i]; 912 } 913 } 914 915 // concatenate conditions 916 $dataSize = count($data); 917 $betweenSize = count($betweens); 918 919 $condition = ''; 920 $operatorAnd = $notIn ? ' AND ' : ' OR '; 921 922 if ($betweens) { 923 $operatorNot = $notIn ? 'NOT ' : ''; 924 925 foreach ($betweens as $between) { 926 $between = $operatorNot.$fieldName.' BETWEEN '.zbx_dbstr($between[0]).' AND '.zbx_dbstr(end($between)); 927 928 $condition .= $condition ? $operatorAnd.$between : $between; 929 } 930 } 931 932 if ($dataSize == 1) { 933 $operator = $notIn ? '!=' : '='; 934 935 $condition .= ($condition ? $operatorAnd : '').$fieldName.$operator.zbx_dbstr($data[0]); 936 } 937 else { 938 $operatorNot = $notIn ? ' NOT' : ''; 939 $data = array_chunk($data, $MAX_EXPRESSIONS); 940 941 foreach ($data as $chunk) { 942 $chunkIns = ''; 943 944 foreach ($chunk as $value) { 945 $chunkIns .= ','.zbx_dbstr($value); 946 } 947 948 $chunkIns = $fieldName.$operatorNot.' IN ('.substr($chunkIns, 1).')'; 949 950 $condition .= $condition ? $operatorAnd.$chunkIns : $chunkIns; 951 } 952 } 953 954 return (($dataSize && $betweenSize) || $betweenSize > 1 || $dataSize > $MAX_EXPRESSIONS) ? '('.$condition.')' : $condition; 955} 956 957/** 958 * Takes an initial part of SQL query and appends a generated WHERE condition. 959 * 960 * @param string $fieldName field name to be used in SQL WHERE condition 961 * @param array $values array of string values sorted in ascending order to be included in WHERE 962 * @param bool $notIn builds inverted condition 963 * 964 * @return string 965 */ 966function dbConditionString($fieldName, array $values, $notIn = false) { 967 switch (count($values)) { 968 case 0: 969 return '1=0'; 970 case 1: 971 return $notIn 972 ? $fieldName.'!='.zbx_dbstr(reset($values)) 973 : $fieldName.'='.zbx_dbstr(reset($values)); 974 } 975 976 $in = $notIn ? ' NOT IN ' : ' IN '; 977 $concat = $notIn ? ' AND ' : ' OR '; 978 $items = array_chunk($values, 950); 979 980 $condition = ''; 981 foreach ($items as $values) { 982 $condition .= !empty($condition) ? ')'.$concat.$fieldName.$in.'(' : ''; 983 $condition .= implode(',', zbx_dbstr($values)); 984 } 985 986 return '('.$fieldName.$in.'('.$condition.'))'; 987} 988 989/** 990 * Transform DB cursor to array. 991 * 992 * @return array 993 */ 994function DBfetchArray($cursor) { 995 $result = []; 996 while ($row = DBfetch($cursor)) { 997 $result[] = $row; 998 } 999 return $result; 1000} 1001 1002/** 1003 * Transform DB cursor to array. 1004 * 1005 * @return array 1006 */ 1007function DBfetchArrayAssoc($cursor, $field) { 1008 $result = []; 1009 while ($row = DBfetch($cursor)) { 1010 $result[$row[$field]] = $row; 1011 } 1012 return $result; 1013} 1014 1015/** 1016 * Fetch only values from one column to array. 1017 * 1018 * @param resource $cursor 1019 * @param string $column 1020 * @param bool $asHash 1021 * 1022 * @return array 1023 */ 1024function DBfetchColumn($cursor, $column, $asHash = false) { 1025 $result = []; 1026 1027 while ($dbResult = DBfetch($cursor)) { 1028 if ($asHash) { 1029 $result[$dbResult[$column]] = $dbResult[$column]; 1030 } 1031 else { 1032 $result[] = $dbResult[$column]; 1033 } 1034 } 1035 1036 return $result; 1037} 1038 1039/** 1040 * Initialize access to SQLite3 database. 1041 * 1042 * The function creates a semaphore for exclusive SQLite3 access. It is 1043 * shared between Zabbix front-end and Zabbix Server. 1044 * 1045 * @return bool 1046 */ 1047function init_sqlite3_access() { 1048 global $DB; 1049 1050 $DB['SEM_ID'] = sem_get(ftok($DB['DATABASE'], 'z'), 1, 0660); 1051} 1052 1053/** 1054 * Get exclusive lock on SQLite3 database. 1055 * 1056 * @return bool 1057 */ 1058function lock_sqlite3_access() { 1059 global $DB; 1060 1061 sem_acquire($DB['SEM_ID']); 1062} 1063 1064/** 1065 * Release exclusive lock on SQLite3 database. 1066 * 1067 * @return bool 1068 */ 1069function unlock_sqlite3_access() { 1070 global $DB; 1071 1072 sem_release($DB['SEM_ID']); 1073} 1074 1075/** 1076 * Returns true if both IDs are equal. 1077 * 1078 * @param $id1 1079 * @param $id2 1080 * 1081 * @return bool 1082 */ 1083function idcmp($id1, $id2) { 1084 return (string) $id1 === (string) $id2; 1085} 1086 1087/** 1088 * Escapes the value to be used in the PostgreSQL connection string for the pg_connect() function. 1089 * 1090 * @param $string 1091 * 1092 * @return string 1093 */ 1094function pg_connect_escape($string) { 1095 return addcslashes($string, "'\\"); 1096} 1097 1098/** 1099 * Escape string for safe usage in SQL queries. 1100 * Works for ibmdb2, mysql, oracle, postgresql, sqlite. 1101 * 1102 * @param array|string $var 1103 * 1104 * @return array|bool|string 1105 */ 1106function zbx_dbstr($var) { 1107 global $DB; 1108 1109 if (!isset($DB['TYPE'])) { 1110 return false; 1111 } 1112 1113 switch ($DB['TYPE']) { 1114 case ZBX_DB_DB2: 1115 if (is_array($var)) { 1116 foreach ($var as $vnum => $value) { 1117 $var[$vnum] = "'".db2_escape_string($value)."'"; 1118 } 1119 return $var; 1120 } 1121 return "'".db2_escape_string($var)."'"; 1122 1123 case ZBX_DB_MYSQL: 1124 if (is_array($var)) { 1125 foreach ($var as $vnum => $value) { 1126 $var[$vnum] = "'".mysqli_real_escape_string($DB['DB'], $value)."'"; 1127 } 1128 return $var; 1129 } 1130 return "'".mysqli_real_escape_string($DB['DB'], $var)."'"; 1131 1132 case ZBX_DB_ORACLE: 1133 if (is_array($var)) { 1134 foreach ($var as $vnum => $value) { 1135 $var[$vnum] = "'".preg_replace('/\'/', '\'\'', $value)."'"; 1136 } 1137 return $var; 1138 } 1139 return "'".preg_replace('/\'/','\'\'',$var)."'"; 1140 1141 case ZBX_DB_POSTGRESQL: 1142 if (is_array($var)) { 1143 foreach ($var as $vnum => $value) { 1144 $var[$vnum] = "'".pg_escape_string($value)."'"; 1145 } 1146 return $var; 1147 } 1148 return "'".pg_escape_string($var)."'"; 1149 1150 case ZBX_DB_SQLITE3: 1151 if (is_array($var)) { 1152 foreach ($var as $vnum => $value) { 1153 $var[$vnum] = "'".$DB['DB']->escapeString($value)."'"; 1154 } 1155 return $var; 1156 } 1157 return "'".$DB['DB']->escapeString($var)."'"; 1158 1159 default: 1160 return false; 1161 } 1162} 1163 1164/** 1165 * Creates db dependent string with sql expression that casts passed value to bigint. 1166 * Works for ibmdb2, mysql, oracle, postgresql, sqlite. 1167 * 1168 * @param int $field 1169 * 1170 * @return bool|string 1171 */ 1172function zbx_dbcast_2bigint($field) { 1173 global $DB; 1174 1175 if (!isset($DB['TYPE'])) { 1176 return false; 1177 } 1178 1179 switch ($DB['TYPE']) { 1180 case ZBX_DB_DB2: 1181 case ZBX_DB_POSTGRESQL: 1182 case ZBX_DB_SQLITE3: 1183 return 'CAST('.$field.' AS BIGINT)'; 1184 1185 case ZBX_DB_MYSQL: 1186 return 'CAST('.$field.' AS UNSIGNED)'; 1187 1188 case ZBX_DB_ORACLE: 1189 return 'CAST('.$field.' AS NUMBER(20))'; 1190 1191 default: 1192 return false; 1193 } 1194} 1195