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