1<?php 2/* 3 +-------------------------------------------------------------------------+ 4 | Copyright (C) 2004-2021 The Cacti Group | 5 | | 6 | This program is free software; you can redistribute it and/or | 7 | modify it under the terms of the GNU General Public License | 8 | as published by the Free Software Foundation; either version 2 | 9 | of the License, or (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 | Cacti: The Complete RRDtool-based Graphing Solution | 17 +-------------------------------------------------------------------------+ 18 | This code is designed, written, and maintained by the Cacti Group. See | 19 | about.php and/or the AUTHORS file for specific developer information. | 20 +-------------------------------------------------------------------------+ 21 | http://www.cacti.net/ | 22 +-------------------------------------------------------------------------+ 23*/ 24 25/* db_connect_real - makes a connection to the database server 26 @param $device - the hostname of the database server, 'localhost' if the database server is running 27 on this machine 28 @param $user - the username to connect to the database server as 29 @param $pass - the password to connect to the database server with 30 @param $db_name - the name of the database to connect to 31 @param $db_type - the type of database server to connect to, only 'mysql' is currently supported 32 @param $port - the port to communicate with MySQL/MariaDB on 33 @param $retries - the number a time the server should attempt to connect before failing 34 @param $db_ssl - boolean true or false 35 @param $db_ssl_key - the client ssl key 36 @param $db_ssl_cert - the client ssl cert 37 @param $db_ssl_ca - the ssl ca 38 @returns - (bool) '1' for success, '0' for error */ 39function db_connect_real($device, $user, $pass, $db_name, $db_type = 'mysql', $port = '3306', $retries = 20, 40 $db_ssl = false, $db_ssl_key = '', $db_ssl_cert = '', $db_ssl_ca = '', $persist = false) { 41 global $database_sessions, $database_total_queries, $database_persist, $config; 42 $database_total_queries = 0; 43 44 $i = 0; 45 if (isset($database_sessions["$device:$port:$db_name"])) { 46 return $database_sessions["$device:$port:$db_name"]; 47 } 48 49 $odevice = $device; 50 51 $flags = array(); 52 if ($db_type == 'mysql') { 53 // Using 'localhost' will force unix sockets mode, which breaks when attempting to use mysql on a different port 54 if ($device == 'localhost' && $port != '3306') { 55 $device = '127.0.0.1'; 56 } 57 58 if (!defined('PDO::MYSQL_ATTR_FOUND_ROWS')) { 59 if (!empty($config['DEBUG_READ_CONFIG_OPTION'])) { 60 $prefix = get_debug_prefix(); 61 file_put_contents(sys_get_temp_dir() . '/cacti-option.log', "$prefix\n$prefix ************* DATABASE MODULE MISSING ****************\n$prefix session name: $odevice:$port:$db_name\n$prefix\n", FILE_APPEND); 62 } 63 64 return false; 65 } 66 67 if (isset($database_persist) && $database_persist == true || $persist) { 68 $flags[PDO::ATTR_PERSISTENT] = true; 69 } 70 71 $flags[PDO::MYSQL_ATTR_FOUND_ROWS] = true; 72 if ($db_ssl) { 73 if ($db_ssl_ca != '') { 74 if (file_exists($db_ssl_ca)) { 75 $flags[PDO::MYSQL_ATTR_SSL_CA] = $db_ssl_ca; 76 } 77 } 78 if ($db_ssl_key != '' && $db_ssl_cert != '') { 79 if (file_exists($db_ssl_key) && file_exists($db_ssl_cert)) { 80 $flags[PDO::MYSQL_ATTR_SSL_KEY] = $db_ssl_key; 81 $flags[PDO::MYSQL_ATTR_SSL_CERT] = $db_ssl_cert; 82 } 83 } 84 } 85 } 86 87 while ($i <= $retries) { 88 try { 89 if (strpos($device, '/') !== false && filetype($device) == 'socket') { 90 $cnn_id = new PDO("$db_type:unix_socket=$device;dbname=$db_name;charset=utf8", $user, $pass, $flags); 91 } else { 92 $cnn_id = new PDO("$db_type:host=$device;port=$port;dbname=$db_name;charset=utf8", $user, $pass, $flags); 93 } 94 $cnn_id->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); 95 96 $bad_modes = array( 97 'STRICT_TRANS_TABLES', 98 'STRICT_ALL_TABLES', 99 'TRADITIONAL', 100 'NO_ZERO_DATE', 101 'NO_ZERO_IN_DATE', 102 'ONLY_FULL_GROUP_BY', 103 'NO_AUTO_VALUE_ON_ZERO' 104 ); 105 106 $database_sessions["$odevice:$port:$db_name"] = $cnn_id; 107 108 $ver = db_get_global_variable('version', $cnn_id); 109 110 if (strpos($ver, 'MariaDB') !== false) { 111 $srv = 'MariaDB'; 112 $ver = str_replace('-MariaDB', '', $ver); 113 } else { 114 $srv = 'MySQL'; 115 } 116 117 if (version_compare('8.0.0', $ver, '<=')) { 118 $bad_modes[] = 'NO_AUTO_CREATE_USER'; 119 } 120 121 // Get rid of bad modes 122 $modes = explode(',', db_fetch_cell('SELECT @@sql_mode', '', false)); 123 $new_modes = array(); 124 125 foreach($modes as $mode) { 126 if (array_search($mode, $bad_modes) === false) { 127 $new_modes[] = $mode; 128 } 129 } 130 131 // Add Required modes 132 $required_modes[] = 'ALLOW_INVALID_DATES'; 133 $required_modes[] = 'NO_ENGINE_SUBSTITUTION'; 134 135 foreach($required_modes as $mode) { 136 if (array_search($mode, $new_modes) === false) { 137 $new_modes[] = $mode; 138 } 139 } 140 141 $sql_mode = implode(',', $new_modes); 142 143 db_execute_prepared('SET SESSION sql_mode = ?', array($sql_mode), false); 144 145 if (db_column_exists('poller', 'timezone')) { 146 $timezone = db_fetch_cell_prepared('SELECT timezone 147 FROM poller 148 WHERE id = ?', 149 array($config['poller_id']), false); 150 } else { 151 $timezone = ''; 152 } 153 154 if ($timezone != '') { 155 db_execute_prepared('SET SESSION time_zone = ?', array($timezone), false); 156 } 157 158 if (!empty($config['DEBUG_READ_CONFIG_OPTION'])) { 159 $prefix = get_debug_prefix(); 160 file_put_contents(sys_get_temp_dir() . '/cacti-option.log', "$prefix\n$prefix ************* DATABASE OPEN ****************\n$prefix session name: $odevice:$port:$db_name\n$prefix\n", FILE_APPEND); 161 } 162 163 if (!empty($config['DEBUG_READ_CONFIG_OPTION_DB_OPEN'])) { 164 $config['DEBUG_READ_CONFIG_OPTION'] = false; 165 } 166 return $cnn_id; 167 } catch (PDOException $e) { 168 if (!isset($config['DATABASE_ERROR'])) { 169 $config['DATABASE_ERROR'] = array(); 170 } 171 172 $config['DATABASE_ERROR'][] = array( 173 'Code' => $e->getCode(), 174 'Error' => $e->getMessage(), 175 ); 176 // Must catch this exception or else PDO will display an error with our username/password 177 //print $e->getMessage(); 178 //exit; 179 } 180 181 $i++; 182 usleep(40000); 183 } 184 185 return false; 186} 187 188function db_warning_handler($errno, $errstr, $errfile, $errline, $errcontext = []) { 189 throw new Exception($errstr, $errno); 190} 191 192function db_binlog_enabled() { 193 $enabled = db_fetch_row('SHOW GLOBAL VARIABLES LIKE "log_bin"'); 194 195 if (cacti_sizeof($enabled)) { 196 if (strtolower($enabled['Value']) == 'on' || $enabled['Value'] == 1) { 197 return true; 198 } 199 } 200 201 return false; 202} 203 204function db_get_active_replicas() { 205 return array_rekey( 206 db_fetch_assoc("SELECT SUBSTRING_INDEX(HOST, ':', 1) AS host 207 FROM information_schema.processlist 208 WHERE command = 'Binlog Dump'"), 209 'host', 'host' 210 ); 211} 212 213/* db_close - closes the open connection 214 @returns - the result of the close command */ 215function db_close($db_conn = false) { 216 global $database_sessions, $database_default, $database_hostname, $database_port; 217 218 /* check for a connection being passed, if not use legacy behavior */ 219 if (!is_object($db_conn)) { 220 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 221 222 if (!is_object($db_conn)) { 223 return false; 224 } 225 } 226 227 $db_conn = null; 228 $database_sessions["$database_hostname:$database_port:$database_default"] = null; 229 230 return true; 231} 232 233/* db_execute - run an sql query and do not return any output 234 @param $sql - the sql query to execute 235 @param $log - whether to log error messages, defaults to true 236 @returns - '1' for success, '0' for error */ 237function db_execute($sql, $log = true, $db_conn = false) { 238 return db_execute_prepared($sql, array(), $log, $db_conn); 239} 240 241/* db_execute_prepared - run an sql query and do not return any output 242 @param $sql - the sql query to execute 243 @param $log - whether to log error messages, defaults to true 244 @returns - '1' for success, '0' for error */ 245function db_execute_prepared($sql, $params = array(), $log = true, $db_conn = false, $execute_name = 'Exec', $default_value = true, $return_func = 'no_return_function', $return_params = array()) { 246 global $database_sessions, $database_default, $config, $database_hostname, $database_port, $database_total_queries, $database_last_error, $database_log; 247 $database_total_queries++; 248 249 if (!isset($database_log)) { 250 $database_log = false; 251 } 252 253 /* check for a connection being passed, if not use legacy behavior */ 254 if (!is_object($db_conn)) { 255 if (isset($database_sessions["$database_hostname:$database_port:$database_default"])) { 256 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 257 } 258 259 if (!is_object($db_conn)) { 260 $database_last_error = 'DB ' . $execute_name . ' -- No connection found'; 261 return false; 262 } 263 } 264 265 $sql = db_strip_control_chars($sql); 266 267 if (!empty($config['DEBUG_SQL_CMD'])) { 268 db_echo_sql('db_' . $execute_name . ': "' . $sql . "\"\n"); 269 } 270 271 $errors = 0; 272 $db_conn->affected_rows = 0; 273 274 while (true) { 275 $query = $db_conn->prepare($sql); 276 277 $code = 0; 278 $en = ''; 279 280 if (!empty($config['DEBUG_SQL_CMD'])) { 281 db_echo_sql('db_' . $execute_name . ' Memory [Before]: ' . memory_get_usage() . ' / ' . memory_get_peak_usage() . "\n"); 282 } 283 284 set_error_handler('db_warning_handler',E_WARNING | E_NOTICE); 285 try { 286 if (empty($params) || cacti_count($params) == 0) { 287 $query->execute(); 288 } else { 289 $query->execute($params); 290 } 291 } catch (Exception $ex) { 292 $code = $ex->getCode(); 293 $en = $code; 294 $errorinfo = array(1=>$code, 2=>$ex->getMessage()); 295 } 296 restore_error_handler(); 297 298 if (!empty($config['DEBUG_SQL_CMD'])) { 299 db_echo_sql('db_' . $execute_name . ' Memory [ After]: ' . memory_get_usage() . ' / ' . memory_get_peak_usage() . "\n"); 300 } 301 302 if ($code == 0) { 303 $code = $query->errorCode(); 304 if ($code != '00000' && $code != '01000') { 305 $errorinfo = $query->errorInfo(); 306 $en = $errorinfo[1]; 307 } else { 308 $code = $db_conn->errorCode(); 309 if ($code != '00000' && $code != '01000') { 310 $errorinfo = $db_conn->errorInfo(); 311 $en = $errorinfo[1]; 312 } 313 } 314 } 315 316 if ($en == '') { 317 // With PDO, we have to free this up 318 $db_conn->affected_rows = $query->rowCount(); 319 320 $return_value = $default_value; 321 if (function_exists($return_func)) { 322 $return_array = array($query); 323 if (!empty($return_params)) { 324 if (!is_array($return_params)) { 325 $return_params = array($return_params); 326 } 327 $return_array = array_merge($return_array, $return_params); 328 } 329 330 if (!empty($config['DEBUG_SQL_FLOW'])) { 331 db_echo_sql('db_' . $execute_name . '_return_func: \'' . $return_func .'\' (' . function_exists($return_func) . ")\n"); 332 db_echo_sql('db_' . $execute_name . '_return_func: params ' . clean_up_lines(var_export($return_array, true)) . "\n"); 333 } 334 335 $return_value = call_user_func_array($return_func, $return_array); 336 } 337 $query->closeCursor(); 338 unset($query); 339 340 if (!empty($config['DEBUG_SQL_FLOW'])) { 341 db_echo_sql('db_' . $execute_name . ': returns ' . clean_up_lines(var_export($return_value, true)) . "\n", true); 342 } 343 return $return_value; 344 } else { 345 $database_last_error = 'DB ' . $execute_name . ' Failed!, Error ' . $en . ': ' . (isset($errorinfo[2]) ? $errorinfo[2] : '<no error>'); 346 if (isset($query)) { 347 $query->closeCursor(); 348 } 349 unset($query); 350 351 if ($log) { 352 if ($en == 1213 || $en == 1205) { 353 $errors++; 354 if ($errors > 30) { 355 cacti_log("ERROR: Too many Lock/Deadlock errors occurred! SQL:'" . clean_up_lines($sql) . "'", true, 'DBCALL', POLLER_VERBOSITY_DEBUG); 356 $database_last_error = "Too many Lock/Deadlock errors occurred!"; 357 } else { 358 usleep(200000); 359 360 continue; 361 } 362 } else if ($en == 1153) { 363 if (strlen($sql) > 1024) { 364 $sql = substr($sql, 0, 1024) . '...'; 365 } 366 367 cacti_log('ERROR: A DB ' . $execute_name . ' Too Large!, Error: ' . $en . ', SQL: \'' . clean_up_lines($sql) . '\'', false, 'DBCALL', POLLER_VERBOSITY_DEBUG); 368 cacti_log('ERROR: A DB ' . $execute_name . ' Too Large!, Error: ' . $errorinfo[2], false, 'DBCALL', POLLER_VERBOSITY_DEBUG); 369 cacti_debug_backtrace('SQL', false, true, 0, 1); 370 371 $database_last_error = 'DB ' . $execute_name . ' Too Large!, Error ' . $en . ': ' . $errorinfo[2]; 372 } else { 373 cacti_log('ERROR: A DB ' . $execute_name . ' Failed!, Error: ' . $en . ', SQL: \'' . clean_up_lines($sql) . '\'', false, 'DBCALL', POLLER_VERBOSITY_DEBUG); 374 cacti_log('ERROR: A DB ' . $execute_name . ' Failed!, Error: ' . $errorinfo[2], false); 375 cacti_debug_backtrace('SQL', false, true, 0, 1); 376 377 $database_last_error = 'DB ' . $execute_name . ' Failed!, Error ' . $en . ': ' . (isset($errorinfo[2]) ? $errorinfo[2] : '<no error>'); 378 } 379 } 380 381 if (!empty($config['DEBUG_SQL_FLOW'])) { 382 db_echo_sql($database_last_error); 383 } 384 return false; 385 } 386 } 387 388 unset($query); 389 390 if (!empty($config['DEBUG_SQL_FLOW'])) { 391 db_echo_sql($database_last_error); 392 } 393 394 return false; 395} 396 397 398/* db_fetch_cell - run a 'select' sql query and return the first column of the 399 first row found 400 @param $sql - the sql query to execute 401 @param $col_name - use this column name instead of the first one 402 @param $log - whether to log error messages, defaults to true 403 @returns - (bool) the output of the sql query as a single variable */ 404function db_fetch_cell($sql, $col_name = '', $log = true, $db_conn = false) { 405 global $config; 406 407 if (!empty($config['DEBUG_SQL_FLOW'])) { 408 db_echo_sql('db_fetch_cell($sql, $col_name = \'' . $col_name . '\', $log = true, $db_conn = false)' . "\n"); 409 } 410 411 return db_fetch_cell_prepared($sql, array(), $col_name, $log, $db_conn); 412} 413 414/* db_fetch_cell_prepared - run a 'select' sql query and return the first column of the 415 first row found 416 @param $sql - the sql query to execute 417 @param $col_name - use this column name instead of the first one 418 @param $log - whether to log error messages, defaults to true 419 @returns - (bool) the output of the sql query as a single variable */ 420function db_fetch_cell_prepared($sql, $params = array(), $col_name = '', $log = true, $db_conn = false) { 421 global $config; 422 423 if (!empty($config['DEBUG_SQL_FLOW'])) { 424 db_echo_sql('db_fetch_cell_prepared($sql, $params = ' . clean_up_lines(var_export($params, true)) . ', $col_name = \'' . $col_name . '\', $log = true, $db_conn = false)' . "\n"); 425 } 426 427 return db_execute_prepared($sql, $params, $log, $db_conn, 'Cell', false, 'db_fetch_cell_return', $col_name); 428} 429 430function db_fetch_cell_return($query, $col_name = '') { 431 global $config; 432 433 if (!empty($config['DEBUG_SQL_FLOW'])) { 434 db_echo_sql('db_fetch_cell_return($query, $col_name = \'' . $col_name . '\')' . "\n"); 435 } 436 437 $r = $query->fetchAll(PDO::FETCH_BOTH); 438 if (isset($r[0]) && is_array($r[0])) { 439 if ($col_name != '') { 440 return $r[0][$col_name]; 441 } else { 442 return reset($r[0]); 443 } 444 } 445 return false; 446} 447 448/* db_fetch_row - run a 'select' sql query and return the first row found 449 @param $sql - the sql query to execute 450 @param $log - whether to log error messages, defaults to true 451 @returns - the first row of the result as a hash */ 452function db_fetch_row($sql, $log = true, $db_conn = false) { 453 global $config; 454 455 if (!empty($config['DEBUG_SQL_FLOW'])) { 456 db_echo_sql('db_fetch_row(\'' . clean_up_lines($sql) . '\', $log = ' . $log . ', $db_conn = ' . ($db_conn ? 'true' : 'false') .')' . "\n"); 457 } 458 459 return db_fetch_row_prepared($sql, array(), $log, $db_conn); 460} 461 462/* db_fetch_row_prepared - run a 'select' sql query and return the first row found 463 @param $sql - the sql query to execute 464 @param $log - whether to log error messages, defaults to true 465 @returns - the first row of the result as a hash */ 466function db_fetch_row_prepared($sql, $params = array(), $log = true, $db_conn = false) { 467 global $config; 468 469 if (!empty($config['DEBUG_SQL_FLOW'])) { 470 db_echo_sql('db_fetch_row_prepared(\'' . clean_up_lines($sql) . '\', $params = (\'' . implode('\', \'', $params) . '\'), $log = ' . $log . ', $db_conn = ' . ($db_conn ? 'true' : 'false') .')' . "\n"); 471 } 472 473 return db_execute_prepared($sql, $params, $log, $db_conn, 'Row', false, 'db_fetch_row_return'); 474} 475 476function db_fetch_row_return($query) { 477 global $config; 478 479 if (!empty($config['DEBUG_SQL_FLOW'])) { 480 db_echo_sql('db_fetch_row_return($query)' . "\n"); 481 } 482 483 if ($query->rowCount()) { 484 $r = $query->fetchAll(PDO::FETCH_ASSOC); 485 } 486 487 return (isset($r[0])) ? $r[0] : array(); 488} 489 490/* db_fetch_assoc - run a 'select' sql query and return all rows found 491 @param $sql - the sql query to execute 492 @param $log - whether to log error messages, defaults to true 493 @returns - the entire result set as a multi-dimensional hash */ 494function db_fetch_assoc($sql, $log = true, $db_conn = false) { 495 global $config; 496 497 if (!empty($config['DEBUG_SQL_FLOW'])) { 498 db_echo_sql('db_fetch_assoc($sql, $log = true, $db_conn = false)' . "\n"); 499 } 500 501 return db_fetch_assoc_prepared($sql, array(), $log, $db_conn); 502} 503 504/* db_fetch_assoc_prepared - run a 'select' sql query and return all rows found 505 @param $sql - the sql query to execute 506 @param $log - whether to log error messages, defaults to true 507 @returns - the entire result set as a multi-dimensional hash */ 508function db_fetch_assoc_prepared($sql, $params = array(), $log = true, $db_conn = false) { 509 global $config; 510 511 if (!empty($config['DEBUG_SQL_FLOW'])) { 512 db_echo_sql('db_fetch_assoc_prepared($sql, $params = array(), $log = true, $db_conn = false)' . "\n"); 513 } 514 515 return db_execute_prepared($sql, $params, $log, $db_conn, 'Row', array(), 'db_fetch_assoc_return'); 516} 517 518function db_fetch_assoc_return($query) { 519 global $config; 520 521 if (!empty($config['DEBUG_SQL_FLOW'])) { 522 db_echo_sql('db_fetch_assoc_return($query)' . "\n"); 523 } 524 525 $r = $query->fetchAll(PDO::FETCH_ASSOC); 526 return (is_array($r)) ? $r : array(); 527} 528 529/* db_fetch_insert_id - get the last insert_id or auto incriment 530 @returns - the id of the last auto increment row that was created */ 531function db_fetch_insert_id($db_conn = false) { 532 global $database_sessions, $database_default, $database_hostname, $database_port; 533 534 /* check for a connection being passed, if not use legacy behavior */ 535 if (!is_object($db_conn)) { 536 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 537 } 538 539 if (is_object($db_conn)) { 540 return $db_conn->lastInsertId(); 541 } 542 543 return false; 544} 545 546/* db_affected_rows - return the number of rows affected by the last transaction 547 * @returns - the number of rows affected by the last transaction */ 548function db_affected_rows($db_conn = false) { 549 global $database_sessions, $database_default, $database_hostname, $database_port; 550 551 /* check for a connection being passed, if not use legacy behavior */ 552 if (!is_object($db_conn)) { 553 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 554 555 if (!is_object($db_conn)) { 556 return false; 557 } 558 } 559 560 return $db_conn->affected_rows; 561} 562 563/* db_add_column - add a column to table 564 @param $table - the name of the table 565 @param $column - array of column data ex: array('name' => 'test' . rand(1, 200), 'type' => 'varchar (255)', 'NULL' => false) 566 @param $log - whether to log error messages, defaults to true 567 @returns - '1' for success, '0' for error */ 568function db_add_column($table, $column, $log = true, $db_conn = false) { 569 global $database_sessions, $database_default, $database_hostname, $database_port; 570 571 /* check for a connection being passed, if not use legacy behavior */ 572 if (!is_object($db_conn)) { 573 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 574 575 if (!is_object($db_conn)) { 576 return false; 577 } 578 } 579 580 $result = db_fetch_assoc('SHOW columns FROM `' . $table . '`', $log, $db_conn); 581 if ($result === false) { 582 return false; 583 } 584 585 $columns = array(); 586 foreach($result as $arr) { 587 $columns[] = $arr['Field']; 588 } 589 590 if (isset($column['name']) && !in_array($column['name'], $columns)) { 591 $sql = 'ALTER TABLE `' . $table . '` ADD `' . $column['name'] . '`'; 592 if (isset($column['type'])) { 593 $sql .= ' ' . $column['type']; 594 } 595 596 if (isset($column['unsigned'])) { 597 $sql .= ' unsigned'; 598 } 599 600 if (isset($column['NULL']) && $column['NULL'] === false) { 601 $sql .= ' NOT NULL'; 602 } 603 604 if (isset($column['NULL']) && $column['NULL'] === true && !isset($column['default'])) { 605 $sql .= ' default NULL'; 606 } 607 608 if (isset($column['default'])) { 609 if (strtolower($column['type']) == 'timestamp' && $column['default'] === 'CURRENT_TIMESTAMP') { 610 $sql .= ' default CURRENT_TIMESTAMP'; 611 } else { 612 $sql .= ' default ' . (is_numeric($column['default']) ? $column['default'] : "'" . $column['default'] . "'"); 613 } 614 } 615 616 if (isset($column['on_update'])) { 617 $sql .= ' ON UPDATE ' . $column['on_update']; 618 } 619 620 if (isset($column['auto_increment'])) { 621 $sql .= ' auto_increment'; 622 } 623 624 if (isset($column['comment'])) { 625 $sql .= " COMMENT '" . $column['comment'] . "'"; 626 } 627 628 if (isset($column['after'])) { 629 $sql .= ' AFTER ' . $column['after']; 630 } 631 632 return db_execute($sql, $log, $db_conn); 633 } 634 635 return true; 636} 637 638/* db_remove_column - remove a column to table 639 @param $table - the name of the table 640 @param $column - column name 641 @param $log - whether to log error messages, defaults to true 642 @returns - '1' for success, '0' for error */ 643function db_remove_column($table, $column, $log = true, $db_conn = false) { 644 global $database_sessions, $database_default, $database_hostname, $database_port; 645 646 /* check for a connection being passed, if not use legacy behavior */ 647 if (!is_object($db_conn)) { 648 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 649 650 if (!is_object($db_conn)) { 651 return false; 652 } 653 } 654 655 $result = db_fetch_assoc('SHOW columns FROM `' . $table . '`', $log, $db_conn); 656 $columns = array(); 657 foreach($result as $arr) { 658 $columns[] = $arr['Field']; 659 } 660 661 if (isset($column) && in_array($column, $columns)) { 662 $sql = 'ALTER TABLE `' . $table . '` DROP `' . $column . '`'; 663 return db_execute($sql, $log, $db_conn); 664 } 665 666 return true; 667} 668 669/* db_add_index - adds a new index to a table 670 @param $table - the name of the table 671 @param $type - the type of the index 672 @param $key - the name of the index 673 @param $columns - an array that defines the columns to include in the index 674 @returns - (bool) the result of the operation true or false */ 675function db_add_index($table, $type, $key, $columns) { 676 if (!is_array($columns)) { 677 $columns = array($columns); 678 } 679 680 $sql = 'ALTER TABLE `' . $table . '` ADD ' . $type . ' `' . $key . '`(`' . implode('`,`', $columns) . '`)'; 681 682 if (db_index_exists($table, $key, false)) { 683 $type = str_ireplace('UNIQUE ', '', $type); 684 if (!db_execute("ALTER TABLE $table DROP $type $key")) { 685 return false; 686 } 687 } 688 689 return db_execute($sql); 690} 691 692/* db_index_exists - checks whether an index exists 693 @param $table - the name of the table 694 @param $index - the name of the index 695 @param $log - whether to log error messages, defaults to true 696 @returns - (bool) the output of the sql query as a single variable */ 697function db_index_exists($table, $index, $log = true, $db_conn = false) { 698 global $database_log, $config; 699 700 if (!isset($database_log)) { 701 $database_log = false; 702 } 703 704 $_log = $database_log; 705 $database_log = false; 706 707 $_data = db_fetch_assoc("SHOW KEYS FROM `$table`", $log, $db_conn); 708 $_keys = array_rekey($_data, "Key_name", "Key_name"); 709 710 $database_log = $_log; 711 if (!empty($config['DEBUG_SQL_FLOW'])) { 712 db_echo_sql('db_index_exists(\'' . $table . '\', \'' . $index .'\'): ' 713 . in_array($index, $_keys) . ' - ' 714 . clean_up_lines(var_export($_keys, true))); 715 } 716 717 return in_array($index, $_keys); 718} 719 720/* db_index_exists - checks whether an index exists 721 @param $table - the name of the table 722 @param $index - the name of the index 723 @param $columns - the columns of the index that should match 724 @param $log - whether to log error messages, defaults to true 725 @returns - (bool) the output of the sql query as a single variable */ 726function db_index_matches($table, $index, $columns, $log = true, $db_conn = false) { 727 global $database_log, $config; 728 729 if (!isset($database_log)) { 730 $database_log = false; 731 } 732 733 if (!is_array($columns)) { 734 $columns = array($columns); 735 } 736 737 $_log = $database_log; 738 $database_log = false; 739 740 $_data = db_fetch_assoc("SHOW KEYS FROM `$table`", $log, $db_conn); 741 $_cols = array(); 742 if ($_data !== false) { 743 foreach ($_data as $key_col) { 744 $key = $key_col['Key_name']; 745 if ($key == $index) { 746 $_cols[] = $key_col['Column_name']; 747 } 748 } 749 } 750 751 $status = 0; 752 foreach ($columns as $column) { 753 if (!in_array($column, $_cols)) { 754 $status = -1; 755 break; 756 } 757 } 758 759 if ($status == 0) { 760 foreach ($_cols as $column) { 761 if (!in_array($column, $columns)) { 762 $status = 1; 763 } 764 } 765 } 766 767 $database_log = $_log; 768 if (!empty($config['DEBUG_SQL_FLOW'])) { 769 db_echo_sql('db_index_matches(\'' . $table . '\', \'' . $index .'\'): ' 770 . $status . "\n ::: " 771 . clean_up_lines(var_export($columns, true)) 772 . " ::: " 773 . clean_up_lines(var_export($_cols, true))); 774 } 775 776 return $status; 777} 778 779/* db_table_exists - checks whether a table exists 780 @param $table - the name of the table 781 @param $log - whether to log error messages, defaults to true 782 @returns - (bool) the output of the sql query as a single variable */ 783function db_table_exists($table, $log = true, $db_conn = false) { 784 static $results; 785 786 if (isset($results[$table]) && !defined('IN_CACTI_INSTALL') && !defined('IN_PLUGIN_INSTALL')) { 787 return $results[$table]; 788 } 789 790 // Separate the database from the table and remove backticks 791 preg_match("/([`]{0,1}(?<database>[\w_]+)[`]{0,1}\.){0,1}[`]{0,1}(?<table>[\w_]+)[`]{0,1}/", $table, $matches); 792 793 if ($matches !== false && array_key_exists('table', $matches)) { 794 $sql = 'SHOW TABLES LIKE \'' . $matches['table'] . '\''; 795 796 $results[$table] = (db_fetch_cell($sql, '', $log, $db_conn) ? true : false); 797 798 return $results[$table]; 799 } 800 801 return false; 802} 803 804/* db_cacti_initialized - checks whether cacti has been initialized properly and if not exits with a message 805 @param $is_web - is the session a web session. 806 @returns - (null) */ 807function db_cacti_initialized($is_web = true) { 808 global $database_sessions, $database_default, $config, $database_hostname, $database_port, $config; 809 810 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 811 812 if (!is_object($db_conn)) { 813 return false; 814 } 815 816 $query = $db_conn->prepare('SELECT cacti FROM version'); 817 $query->execute(); 818 $errorinfo = $query->errorInfo(); 819 $query->closeCursor(); 820 821 if ($errorinfo[1] != 0) { 822 print ($is_web ? '<head><link href="' . $config['url_path'] . 'include/themes/modern/main.css" type="text/css" rel="stylesheet"></head>':''); 823 print ($is_web ? '<table style="height:40px;"><tr><td></td></tr></table>':''); 824 print ($is_web ? '<table style="margin-left:auto;margin-right:auto;width:80%;border:1px solid rgba(98,125,77,1)" class="cactiTable"><tr class="cactiTableTitle"><td style="color:snow;font-weight:bold;">Fatal Error - Cacti Database Not Initialized</td></tr>':''); 825 print ($is_web ? '<tr class="installArea"><td>':''); 826 print ($is_web ? '<p>':'') . 'The Cacti Database has not been initialized. Please initilize it before continuing.' . ($is_web ? '</p>':"\n"); 827 print ($is_web ? '<p>':'') . 'To initilize the Cacti database, issue the following commands either as root or using a valid account.' . ($is_web ? '</p>':"\n"); 828 print ($is_web ? '<p style="font-weight:bold;padding-left:25px;">':'') . ' mysqladmin -uroot -p create cacti' . ($is_web ? '</p>':"\n"); 829 print ($is_web ? '<p style="font-weight:bold;padding-left:25px;">':'') . ' mysql -uroot -p -e "grant all on cacti.* to \'someuser\'@\'localhost\' identified by \'somepassword\'"' . ($is_web ? '</p>':"\n"); 830 print ($is_web ? '<p style="font-weight:bold;padding-left:25px;">':'') . ' mysql -uroot -p -e "grant select on mysql.time_zone_name to \'someuser\'@\'localhost\' identified by \'somepassword\'"' . ($is_web ? '</p>':"\n"); 831 print ($is_web ? '<p style="font-weight:bold;padding-left:25px;">':'') . ' mysql -uroot -p cacti < /pathcacti/cacti.sql' . ($is_web ? '</p>':"\n"); 832 print ($is_web ? '<p>':'') . 'Where <b>/pathcacti/</b> is the path to your Cacti install location.' . ($is_web ? '</p>':"\n"); 833 print ($is_web ? '<p>':'') . 'Change <b>someuser</b> and <b>somepassword</b> to match your site preferences. The defaults are <b>cactiuser</b> for both user and password.' . ($is_web ? '</p>':"\n"); 834 print ($is_web ? '<p>':'') . '<b>NOTE:</b> When installing a remote poller, the <b>config.php</b> file must be writable by the Web Server account, and must include valid connection information to the main Cacti server. The file should be changed to read only after the install is completed.' . ($is_web ? '</p>':"\n"); 835 print ($is_web ? '</td></tr></table>':''); 836 exit; 837 } 838} 839 840/* db_column_exists - checks whether a column exists 841 @param $table - the name of the table 842 @param $column - the name of the column 843 @param $log - whether to log error messages, defaults to true 844 @returns - (bool) the output of the sql query as a single variable */ 845function db_column_exists($table, $column, $log = true, $db_conn = false) { 846 static $results = array(); 847 848 if (isset($results[$table][$column]) && !defined('IN_CACTI_INSTALL') && !defined('IN_PLUGIN_INSTALL')) { 849 return $results[$table][$column]; 850 } 851 852 $results[$table][$column] = (db_fetch_cell("SHOW columns FROM `$table` LIKE '$column'", '', $log, $db_conn) ? true : false); 853 854 return $results[$table][$column]; 855} 856 857/* db_get_table_column_types - returns all the types for each column of a table 858 @param $table - the name of the table 859 @returns - (array) an array of column types indexed by the column names */ 860function db_get_table_column_types($table, $db_conn = false) { 861 global $database_sessions, $database_default, $database_hostname, $database_port; 862 863 /* check for a connection being passed, if not use legacy behavior */ 864 if (!is_object($db_conn)) { 865 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 866 867 if (!is_object($db_conn)) { 868 return false; 869 } 870 } 871 872 $columns = db_fetch_assoc("SHOW COLUMNS FROM $table", false, $db_conn); 873 $cols = array(); 874 if (cacti_sizeof($columns)) { 875 foreach($columns as $col) { 876 $cols[$col['Field']] = array('type' => $col['Type'], 'null' => $col['Null'], 'default' => $col['Default'], 'extra' => $col['Extra']);; 877 } 878 } 879 880 return $cols; 881} 882 883function db_update_table($table, $data, $removecolumns = false, $log = true, $db_conn = false) { 884 global $database_sessions, $database_default, $database_hostname, $database_port; 885 886 /* check for a connection being passed, if not use legacy behavior */ 887 if (!is_object($db_conn)) { 888 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 889 890 if (!is_object($db_conn)) { 891 return false; 892 } 893 } 894 895 if (!db_table_exists($table, $log, $db_conn)) { 896 return db_table_create($table, $data, $log, $db_conn); 897 } 898 899 $allcolumns = array(); 900 foreach ($data['columns'] as $column) { 901 $allcolumns[] = $column['name']; 902 if (!db_column_exists($table, $column['name'], $log, $db_conn)) { 903 if (!db_add_column($table, $column, $log, $db_conn)) { 904 return false; 905 } 906 } else { 907 // Check that column is correct and fix it 908 // FIXME: Need to still check default value 909 $arr = db_fetch_row("SHOW columns FROM `$table` LIKE '" . $column['name'] . "'", $log, $db_conn); 910 911 if (strpos(strtolower($arr['Type']), ' unsigned') !== false) { 912 $arr['Type'] = str_ireplace(' unsigned', '', $arr['Type']); 913 $arr['unsigned'] = true; 914 } 915 916 if ($column['type'] != $arr['Type'] || (isset($column['NULL']) && ($column['NULL'] ? 'YES' : 'NO') != $arr['Null']) 917 || (((!isset($column['unsigned']) || !$column['unsigned']) && isset($arr['unsigned'])) 918 || (isset($column['unsigned']) && $column['unsigned'] && !isset($arr['unsigned']))) 919 || (isset($column['auto_increment']) && ($column['auto_increment'] ? 'auto_increment' : '') != $arr['Extra'])) { 920 $sql = 'ALTER TABLE `' . $table . '` CHANGE `' . $column['name'] . '` `' . $column['name'] . '`'; 921 if (isset($column['type'])) { 922 $sql .= ' ' . $column['type']; 923 } 924 925 if (isset($column['unsigned'])) { 926 $sql .= ' unsigned'; 927 } 928 929 if (isset($column['NULL']) && $column['NULL'] == false) { 930 $sql .= ' NOT NULL'; 931 } 932 933 if (isset($column['NULL']) && $column['NULL'] == true && !isset($column['default'])) { 934 $sql .= ' default NULL'; 935 } 936 937 if (isset($column['default'])) { 938 if (strtolower($column['type']) == 'timestamp' && $column['default'] === 'CURRENT_TIMESTAMP') { 939 $sql .= ' default CURRENT_TIMESTAMP'; 940 } else { 941 $sql .= ' default ' . (is_numeric($column['default']) ? $column['default'] : "'" . $column['default'] . "'"); 942 } 943 } 944 945 if (isset($column['on_update'])) { 946 $sql .= ' ON UPDATE ' . $column['on_update']; 947 } 948 949 if (isset($column['auto_increment'])) { 950 $sql .= ' auto_increment'; 951 } 952 953 if (isset($column['comment'])) { 954 $sql .= " COMMENT '" . $column['comment'] . "'"; 955 } 956 957 if (!db_execute($sql, $log, $db_conn)) { 958 return false; 959 } 960 } 961 } 962 } 963 964 if ($removecolumns) { 965 $result = db_fetch_assoc('SHOW columns FROM `' . $table . '`', $log, $db_conn); 966 foreach($result as $arr) { 967 if (!in_array($arr['Field'], $allcolumns)) { 968 if (!db_remove_column($table, $arr['Field'], $log, $db_conn)) { 969 return false; 970 } 971 } 972 } 973 } 974 975 $info = db_fetch_row("SELECT ENGINE, TABLE_COMMENT 976 FROM information_schema.TABLES 977 WHERE TABLE_SCHEMA = SCHEMA() 978 AND TABLE_NAME = '$table'", $log, $db_conn); 979 980 if (isset($info['TABLE_COMMENT']) && isset($data['comment']) && str_replace("'", '', $info['TABLE_COMMENT']) != str_replace("'", '', $data['comment'])) { 981 if (!db_execute("ALTER TABLE `$table` COMMENT '" . str_replace("'", '', $data['comment']) . "'", $log, $db_conn)) { 982 return false; 983 } 984 } 985 986 if (isset($info['ENGINE']) && isset($data['type']) && strtolower($info['ENGINE']) != strtolower($data['type'])) { 987 if (!db_execute("ALTER TABLE `$table` ENGINE = " . $data['type'], $log, $db_conn)) { 988 return false; 989 } 990 } 991 992 // Correct any indexes 993 $indexes = db_fetch_assoc("SHOW INDEX FROM `$table`", $log, $db_conn); 994 $allindexes = array(); 995 996 foreach ($indexes as $index) { 997 $allindexes[$index['Key_name']][$index['Seq_in_index']-1] = $index['Column_name']; 998 } 999 1000 foreach ($allindexes as $n => $index) { 1001 if ($n != 'PRIMARY' && isset($data['keys'])) { 1002 $removeindex = true; 1003 foreach ($data['keys'] as $k) { 1004 if ($k['name'] == $n) { 1005 $removeindex = false; 1006 $add = array_diff($k['columns'], $index); 1007 $del = array_diff($index, $k['columns']); 1008 if (!empty($add) || !empty($del)) { 1009 if (!db_execute("ALTER TABLE `$table` DROP INDEX `$n`", $log, $db_conn) || 1010 !db_execute("ALTER TABLE `$table` ADD INDEX `$n` (" . $k['name'] . '` (' . db_format_index_create($k['columns']) . ')', $log, $db_conn)) { 1011 return false; 1012 } 1013 } 1014 break; 1015 } 1016 } 1017 1018 if ($removeindex) { 1019 if (!db_execute("ALTER TABLE `$table` DROP INDEX `$n`", $log, $db_conn)) { 1020 return false; 1021 } 1022 } 1023 } 1024 } 1025 1026 // Add any indexes 1027 if (isset($data['keys'])) { 1028 foreach ($data['keys'] as $k) { 1029 if (!isset($allindexes[$k['name']])) { 1030 if (!db_execute("ALTER TABLE `$table` ADD INDEX `" . $k['name'] . '` (' . db_format_index_create($k['columns']) . ')', $log, $db_conn)) { 1031 return false; 1032 } 1033 } 1034 } 1035 } 1036 1037 // FIXME: It won't allow us to drop a primary key that is set to auto_increment 1038 1039 // Check Primary Key 1040 if (!isset($data['primary']) && isset($allindexes['PRIMARY'])) { 1041 if (!db_execute("ALTER TABLE `$table` DROP PRIMARY KEY", $log, $db_conn)) { 1042 return false; 1043 } 1044 unset($allindexes['PRIMARY']); 1045 } 1046 1047 if (isset($data['primary'])) { 1048 if (!isset($allindexes['PRIMARY'])) { 1049 // No current primary key, so add it 1050 if (!db_execute("ALTER TABLE `$table` ADD PRIMARY KEY(" . db_format_index_create($data['primary']) . ')', $log, $db_conn)) { 1051 return false; 1052 } 1053 } else { 1054 $add = array_diff($data['primary'], $allindexes['PRIMARY']); 1055 $del = array_diff($allindexes['PRIMARY'], $data['primary']); 1056 if (!empty($add) || !empty($del)) { 1057 if (!db_execute("ALTER TABLE `$table` DROP PRIMARY KEY", $log, $db_conn) || 1058 !db_execute("ALTER TABLE `$table` ADD PRIMARY KEY(" . db_format_index_create($data['primary']) . ')', $log, $db_conn)) { 1059 return false; 1060 } 1061 } 1062 } 1063 } 1064 1065 if (isset($data['row_format']) && db_get_global_variable('innodb_file_format', $db_conn) == 'Barracuda') { 1066 db_execute("ALTER TABLE `$table` ROW_FORMAT = " . $data['row_format'], $log, $db_conn); 1067 } 1068 1069 if (isset($data['charset'])) { 1070 $charset .= ' DEFAULT CHARSET = ' . $data['charset']; 1071 db_execute("ALTER TABLE `$table` " . $charset, $log, $db_conn); 1072 } 1073 1074 if (isset($data['collate'])) { 1075 $charset = ' COLLATE = ' . $data['collate']; 1076 db_execute("ALTER TABLE `$table` " . $charset, $log, $db_conn); 1077 } 1078 1079 return true; 1080} 1081 1082function db_format_index_create($indexes) { 1083 if (is_array($indexes)) { 1084 $outindex = ''; 1085 foreach($indexes as $index) { 1086 $index = trim($index); 1087 if (substr($index, -1) == ')') { 1088 $outindex .= ($outindex != '' ? ',':'') . $index; 1089 } else { 1090 $outindex .= ($outindex != '' ? ',':'') . '`' . $index . '`'; 1091 } 1092 } 1093 1094 return $outindex; 1095 } else { 1096 $indexes = trim($indexes); 1097 if (substr($indexes, -1) == ')') { 1098 return $indexes; 1099 } else { 1100 return '`' . trim($indexes, ' `') . '`'; 1101 } 1102 } 1103} 1104 1105/* db_table_create - checks whether a table exists 1106 @param $table - the name of the table 1107 @param $data - data array 1108 @param $log - whether to log error messages, defaults to true 1109 @returns - (bool) the output of the sql query as a single variable */ 1110function db_table_create($table, $data, $log = true, $db_conn = false) { 1111 global $database_sessions, $database_default, $database_hostname, $database_port; 1112 1113 /* check for a connection being passed, if not use legacy behavior */ 1114 if (!is_object($db_conn)) { 1115 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1116 1117 if (!is_object($db_conn)) { 1118 return false; 1119 } 1120 } 1121 1122 if (!db_table_exists($table, $log, $db_conn)) { 1123 $c = 0; 1124 $sql = 'CREATE TABLE `' . $table . "` (\n"; 1125 foreach ($data['columns'] as $column) { 1126 if (isset($column['name'])) { 1127 if ($c > 0) { 1128 $sql .= ",\n"; 1129 } 1130 1131 $sql .= '`' . $column['name'] . '`'; 1132 1133 if (isset($column['type'])) { 1134 $sql .= ' ' . $column['type']; 1135 } 1136 1137 if (isset($column['unsigned'])) { 1138 $sql .= ' unsigned'; 1139 } 1140 1141 if (isset($column['NULL']) && $column['NULL'] == false) { 1142 $sql .= ' NOT NULL'; 1143 } 1144 1145 if (isset($column['NULL']) && $column['NULL'] == true && !isset($column['default'])) { 1146 $sql .= ' default NULL'; 1147 } 1148 1149 if (isset($column['default'])) { 1150 if (strtolower($column['type']) == 'timestamp' && $column['default'] === 'CURRENT_TIMESTAMP') { 1151 $sql .= ' default CURRENT_TIMESTAMP'; 1152 } else { 1153 $sql .= ' default ' . (is_numeric($column['default']) ? $column['default'] : "'" . $column['default'] . "'"); 1154 } 1155 } 1156 1157 if (isset($column['on_update'])) { 1158 $sql .= ' ON UPDATE ' . $column['on_update']; 1159 } 1160 1161 if (isset($column['comment'])) { 1162 $sql .= " COMMENT '" . $column['comment'] . "'"; 1163 } 1164 1165 if (isset($column['auto_increment'])) { 1166 $sql .= ' auto_increment'; 1167 } 1168 1169 $c++; 1170 } 1171 } 1172 1173 if (isset($data['primary'])) { 1174 if (is_array($data['primary'])) { 1175 $sql .= ",\n PRIMARY KEY (`" . implode('`,`'. $data['primary']) . '`)'; 1176 } else { 1177 $sql .= ",\n PRIMARY KEY (`" . $data['primary'] . '`)'; 1178 } 1179 } 1180 1181 if (isset($data['keys']) && cacti_sizeof($data['keys'])) { 1182 foreach ($data['keys'] as $key) { 1183 if (isset($key['name'])) { 1184 if (is_array($key['columns'])) { 1185 $sql .= ",\n KEY `" . $key['name'] . '` (`' . implode('`,`', $key['columns']) . '`)'; 1186 } else { 1187 $sql .= ",\n KEY `" . $key['name'] . '` (`' . $key['columns'] . '`)'; 1188 } 1189 } 1190 } 1191 } 1192 $sql .= ') ENGINE = ' . $data['type']; 1193 1194 if (isset($data['comment'])) { 1195 $sql .= " COMMENT = '" . $data['comment'] . "'"; 1196 } 1197 1198 if (isset($data['row_format']) && db_get_global_variable('innodb_file_format', $db_conn) == 'Barracuda') { 1199 $sql .= ' ROW_FORMAT = ' . $data['row_format']; 1200 } 1201 1202 if (db_execute($sql, $log, $db_conn)) { 1203 if (isset($data['charset'])) { 1204 db_execute("ALLTER TABLE `$table` CHARSET = " . $data['charset']); 1205 } 1206 1207 if (isset($data['collate'])) { 1208 db_execute("ALTER TABLE `$table` COLLATE = " . $data['collate']); 1209 } 1210 1211 return true; 1212 } else { 1213 return false; 1214 } 1215 } 1216} 1217 1218/* db_get_global_variable - get the value of a global variable 1219 @param $variable - the variable to obtain 1220 @param $db_conn - the database connection to use 1221 @returns - (string) the value of the variable if found */ 1222function db_get_global_variable($variable, $db_conn = false) { 1223 global $database_sessions, $database_default, $database_hostname, $database_port; 1224 1225 /* check for a connection being passed, if not use legacy behavior */ 1226 if (!is_object($db_conn)) { 1227 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1228 1229 if (!is_object($db_conn)) { 1230 return false; 1231 } 1232 } 1233 1234 $data = db_fetch_row("SHOW GLOBAL VARIABLES LIKE '$variable'", true, $db_conn); 1235 1236 if (cacti_sizeof($data)) { 1237 return $data['Value']; 1238 } else { 1239 return false; 1240 } 1241} 1242 1243/* db_get_session_variable - get the value of a session variable 1244 @param $variable - the variable to obtain 1245 @param $db_conn - the database connection to use 1246 @returns - (string) the value of the variable if found */ 1247function db_get_session_variable($variable, $db_conn = false) { 1248 global $database_sessions, $database_default, $database_hostname, $database_port; 1249 1250 /* check for a connection being passed, if not use legacy behavior */ 1251 if (!is_object($db_conn)) { 1252 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1253 1254 if (!is_object($db_conn)) { 1255 return false; 1256 } 1257 } 1258 1259 $data = db_fetch_row("SHOW SESSION VARIABLES LIKE '$variable'", true, $db_conn); 1260 1261 if (cacti_sizeof($data)) { 1262 return $data['Value']; 1263 } else { 1264 return false; 1265 } 1266} 1267 1268/* db_begin_transaction - start a transaction 1269 @param $db_conn - the database connection to use 1270 @returns - (bool) if the begin transaction was successful */ 1271function db_begin_transaction($db_conn = false) { 1272 global $database_sessions, $database_default, $database_hostname, $database_port; 1273 1274 /* check for a connection being passed, if not use legacy behavior */ 1275 if (!is_object($db_conn)) { 1276 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1277 1278 if (!is_object($db_conn)) { 1279 return false; 1280 } 1281 } 1282 1283 return $db_conn->beginTransaction(); 1284} 1285 1286/* db_commit_transaction - commit a transaction 1287 @param $db_conn - the database connection to use 1288 @returns - (bool) if the commit transaction was successful */ 1289function db_commit_transaction($db_conn = false) { 1290 global $database_sessions, $database_default, $database_hostname, $database_port; 1291 1292 /* check for a connection being passed, if not use legacy behavior */ 1293 if (!is_object($db_conn)) { 1294 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1295 1296 if (!is_object($db_conn)) { 1297 return false; 1298 } 1299 } 1300 1301 if (db_fetch_cell('SELECT @@in_transaction') > 0) { 1302 return $db_conn->commit(); 1303 } 1304} 1305 1306/* db_rollback_transaction - rollback a transaction 1307 @param $db_conn - the database connection to use 1308 @returns - (bool) if the rollback transaction was successful */ 1309function db_rollback_transaction($db_conn = false) { 1310 global $database_sessions, $database_default, $database_hostname, $database_port; 1311 1312 /* check for a connection being passed, if not use legacy behavior */ 1313 if (!is_object($db_conn)) { 1314 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1315 1316 if (!is_object($db_conn)) { 1317 return false; 1318 } 1319 } 1320 1321 return $db_conn->rollBack(); 1322} 1323 1324/* array_to_sql_or - loops through a single dimentional array and converts each 1325 item to a string that can be used in the OR portion of an sql query in the 1326 following form: 1327 column=item1 OR column=item2 OR column=item2 ... 1328 @param $array - the array to convert 1329 @param $sql_column - the column to set each item in the array equal to 1330 @returns - a string that can be placed in a SQL OR statement */ 1331function array_to_sql_or($array, $sql_column) { 1332 /* if the last item is null; pop it off */ 1333 if (end($array) === null) { 1334 array_pop($array); 1335 } 1336 1337 if (cacti_sizeof($array)) { 1338 $sql_or = "($sql_column IN('" . implode("','", $array) . "'))"; 1339 1340 return $sql_or; 1341 } 1342} 1343 1344/* db_replace - replaces the data contained in a particular row 1345 @param $table_name - the name of the table to make the replacement in 1346 @param $array_items - an array containing each column -> value mapping in the row 1347 @param $keyCols - a string or array of primary keys 1348 @param $autoQuote - whether to use intelligent quoting or not 1349 @returns - the auto incriment id column (if applicable) */ 1350function db_replace($table_name, $array_items, $keyCols, $db_conn = false) { 1351 global $database_sessions, $database_default, $database_hostname, $database_port; 1352 1353 /* check for a connection being passed, if not use legacy behavior */ 1354 if (!is_object($db_conn)) { 1355 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1356 } 1357 1358 cacti_log("DEVEL: SQL Replace on table '$table_name': '" . serialize($array_items) . "'", false, 'DBCALL', POLLER_VERBOSITY_DEVDBG); 1359 1360 _db_replace($db_conn, $table_name, $array_items, $keyCols); 1361 1362 return db_fetch_insert_id($db_conn); 1363} 1364 1365 1366// FIXME: Need to Rename and cleanup a bit 1367 1368function _db_replace($db_conn, $table, $fieldArray, $keyCols) { 1369 global $database_sessions, $database_default, $database_hostname, $database_port; 1370 1371 /* check for a connection being passed, if not use legacy behavior */ 1372 if (!is_object($db_conn)) { 1373 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1374 1375 if (!is_object($db_conn)) { 1376 return false; 1377 } 1378 } 1379 1380 if (!is_array($keyCols)) { 1381 $keyCols = array($keyCols); 1382 } 1383 1384 $sql = "INSERT INTO $table ("; 1385 $sql2 = ''; 1386 $sql3 = ''; 1387 1388 $first = true; 1389 $first3 = true; 1390 foreach($fieldArray as $k => $v) { 1391 if (!$first) { 1392 $sql .= ', '; 1393 $sql2 .= ', '; 1394 } 1395 $sql .= "`$k`"; 1396 $sql2 .= $v; 1397 $first = false; 1398 1399 if (in_array($k, $keyCols)) continue; // skip UPDATE if is key 1400 1401 if (!$first3) { 1402 $sql3 .= ', '; 1403 } 1404 1405 $sql3 .= "`$k`=VALUES(`$k`)"; 1406 1407 $first3 = false; 1408 } 1409 1410 $sql .= ") VALUES ($sql2)" . ($sql3 != '' ? " ON DUPLICATE KEY UPDATE $sql3" : ''); 1411 1412 $return_code = db_execute($sql, true, $db_conn); 1413 1414 if (!$return_code) { 1415 cacti_log("ERROR: SQL Save Failed for Table '$table'. SQL:'" . clean_up_lines($sql) . "'", false, 'DBCALL'); 1416 } 1417 1418 return db_fetch_insert_id($db_conn); 1419} 1420 1421/* sql_save - saves data to an sql table 1422 @param $array_items - an array containing each column -> value mapping in the row 1423 @param $table_name - the name of the table to make the replacement in 1424 @param $key_cols - the primary key(s) 1425 @returns - the auto incriment id column (if applicable) */ 1426function sql_save($array_items, $table_name, $key_cols = 'id', $autoinc = true, $db_conn = false) { 1427 global $database_sessions, $database_default, $database_hostname, $database_port, $database_last_error; 1428 1429 /* check for a connection being passed, if not use legacy behavior */ 1430 if (!is_object($db_conn)) { 1431 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1432 } 1433 1434 $log = true; 1435 if (!db_table_exists($table_name, $log, $db_conn)) { 1436 $error_message = "SQL Save on table '$table_name': Table does not exist, unable to save!"; 1437 raise_message('sql_save_table', $error_message, MESSAGE_LEVEL_ERROR); 1438 cacti_log('ERROR: ' . $error_message, false, 'DBCALL'); 1439 cacti_debug_backtrace('SQL', false, true, 0, 1); 1440 return false; 1441 } 1442 1443 $cols = db_get_table_column_types($table_name, $db_conn); 1444 1445 cacti_log("DEVEL: SQL Save on table '$table_name': '" . serialize($array_items) . "'", false, 'DBCALL', POLLER_VERBOSITY_DEVDBG); 1446 1447 foreach ($array_items as $key => $value) { 1448 if (!isset($cols[$key])) { 1449 $error_message = "SQL Save on table '$table_name': Column '$key' does not exist, unable to save!"; 1450 raise_message('sql_save_key', $error_message, MESSAGE_LEVEL_ERROR); 1451 cacti_log('ERROR: ' . $error_message, false, 'DBCALL'); 1452 cacti_debug_backtrace('SQL', false, true, 0, 1); 1453 return false; 1454 } 1455 1456 if (strstr($cols[$key]['type'], 'int') !== false || 1457 strstr($cols[$key]['type'], 'float') !== false || 1458 strstr($cols[$key]['type'], 'double') !== false || 1459 strstr($cols[$key]['type'], 'decimal') !== false) { 1460 if ($value == '') { 1461 if ($cols[$key]['null'] == 'YES') { 1462 // TODO: We should make 'NULL', but there are issues that need to be addressed first 1463 $array_items[$key] = 0; 1464 } elseif (strpos($cols[$key]['extra'], 'auto_increment') !== false) { 1465 $array_items[$key] = 0; 1466 } elseif ($cols[$key]['default'] == '') { 1467 // TODO: We should make 'NULL', but there are issues that need to be addressed first 1468 $array_items[$key] = 0; 1469 } else { 1470 $array_items[$key] = $cols[$key]['default']; 1471 } 1472 } elseif (empty($value)) { 1473 $array_items[$key] = 0; 1474 } else { 1475 $array_items[$key] = $value; 1476 } 1477 } else { 1478 $array_items[$key] = db_qstr($value); 1479 } 1480 } 1481 1482 $replace_result = _db_replace($db_conn, $table_name, $array_items, $key_cols); 1483 1484 /* get the last AUTO_ID and return it */ 1485 if (!$replace_result || db_fetch_insert_id($db_conn) == '0') { 1486 if (!is_array($key_cols)) { 1487 if (isset($array_items[$key_cols])) { 1488 return str_replace('"', '', $array_items[$key_cols]); 1489 } 1490 } 1491 1492 return false; 1493 } else { 1494 return $replace_result; 1495 } 1496} 1497 1498function db_qstr($s, $db_conn = false) { 1499 global $database_sessions, $database_default, $database_hostname, $database_port; 1500 1501 /* check for a connection being passed, if not use legacy behavior */ 1502 if (!is_object($db_conn)) { 1503 $db_conn = $database_sessions["$database_hostname:$database_port:$database_default"]; 1504 } 1505 1506 if (is_null($s)) { 1507 return 'NULL'; 1508 } 1509 1510 if (is_object($db_conn)) { 1511 return $db_conn->quote($s); 1512 } 1513 1514 $s = str_replace(array('\\', "\0", "'"), array('\\\\', "\\\0", "\\'"), $s); 1515 1516 return "'" . $s . "'"; 1517} 1518 1519function db_strip_control_chars($sql) { 1520 return trim(clean_up_lines($sql), ';'); 1521} 1522 1523function db_get_column_attributes($table, $columns) { 1524 if (empty($columns) || empty($table)) { 1525 return false; 1526 } 1527 1528 if (!is_array($columns)) { 1529 $columns = explode(',', $columns); 1530 } 1531 1532 $sql = 'SELECT * FROM information_schema.columns 1533 WHERE table_schema = SCHEMA() 1534 AND table_name = ? 1535 AND column_name IN ('; 1536 1537 $column_names = array(); 1538 foreach ($columns as $column) { 1539 if (!empty($column)) { 1540 $sql .= (cacti_sizeof($column_names) ? ',' : '') . '?'; 1541 $column_names[] = $column; 1542 } 1543 } 1544 $sql .= ')'; 1545 1546 $params = array_merge(array($table), $column_names); 1547 1548 return db_fetch_assoc_prepared($sql, $params); 1549} 1550 1551function db_get_columns_length($table, $columns) { 1552 $column_data = db_get_column_attributes($table, $columns); 1553 1554 if (!empty($column_data)) { 1555 return array_rekey($column_data, 'COLUMN_NAME','CHARACTER_MAXIMUM_LENGTH'); 1556 } 1557 1558 return false; 1559} 1560 1561function db_get_column_length($table, $column) { 1562 $column_data = db_get_columns_length($table, $column); 1563 1564 if (!empty($column_data) && isset($column_data[$column])) { 1565 return $column_data[$column]; 1566 } 1567 1568 return false; 1569} 1570 1571function db_check_password_length() { 1572 $len = db_get_column_length('user_auth', 'password'); 1573 1574 if ($len === false) { 1575 die(__('Failed to determine password field length, can not continue as may corrupt password')); 1576 } else if ($len < 80) { 1577 /* Ensure that the password length is increased before we start updating it */ 1578 db_execute("ALTER TABLE user_auth MODIFY COLUMN password varchar(256) NOT NULL default ''"); 1579 $len = db_get_column_length('user_auth','password'); 1580 if ($len < 80) { 1581 die(__('Failed to alter password field length, can not continue as may corrupt password')); 1582 } 1583 } 1584} 1585 1586function db_echo_sql($line, $force = false) { 1587 global $config; 1588 1589 file_put_contents(sys_get_temp_dir() . '/cacti-sql.log', get_debug_prefix() . $line, FILE_APPEND); 1590} 1591 1592/* db_error - return the last error from the database 1593 @returns - string - the last database error if any */ 1594function db_error() { 1595 global $database_last_error; 1596 1597 return $database_last_error; 1598} 1599 1600/* db_get_default_database - Get the database name of the current database or return the default database name 1601 @returns - string - either current db name or configuration default if no connection/name */ 1602function db_get_default_database($db_conn = false) { 1603 global $database_default; 1604 1605 $database = db_fetch_cell('SELECT DATABASE()', '', true, $db_conn); 1606 if (empty($database)) { 1607 $database = $database_default; 1608 } 1609} 1610 1611/* db_force_remote_cnn - force the remote collector to use main data collector connection 1612 @returns - null */ 1613function db_force_remote_cnn() { 1614 global $database_default, $database_hostname, $database_username, $database_password; 1615 global $database_port, $database_ssl, $database_ssl_key, $database_ssl_cert, $database_ssl_ca; 1616 1617 global $rdatabase_default, $rdatabase_hostname, $rdatabase_username, $rdatabase_password; 1618 global $rdatabase_port, $rdatabase_ssl, $rdatabase_ssl_key, $rdatabase_ssl_cert, $rdatabase_ssl_ca; 1619 1620 // Connection worked, so now override the default settings so that it will always utilize the remote connection 1621 $database_default = $rdatabase_default; 1622 $database_hostname = $rdatabase_hostname; 1623 $database_username = $rdatabase_username; 1624 $database_password = $rdatabase_password; 1625 $database_port = $rdatabase_port; 1626 $database_ssl = $rdatabase_ssl; 1627 $database_ssl_key = $rdatabase_ssl_key; 1628 $database_ssl_cert = $rdatabase_ssl_cert; 1629 $database_ssl_ca = $rdatabase_ssl_ca; 1630} 1631 1632/* db_dump_data - dump data into a file by mysqldump, minimize password be caught. 1633 @param $database - default $database_default 1634 @param $tables - default all tables 1635 @param $credentials - array($name => value, ...) for user, password, host, port, ssl ... 1636 @param $output_file - dump file name, default /tmp/cacti.dump.sql 1637 @param $options - option strings for mysqldump, if --defaults-extra-file set, dump the data directly 1638 @returns - returnl status of the executed command */ 1639function db_dump_data($database = '', $tables = '', $credentials = array(), $output_file = false, $options = '--extended-insert=FALSE') { 1640 global $database_default, $database_username, $database_password; 1641 $credentials_string = ''; 1642 1643 if ($database == '') { 1644 $database = $database_default; 1645 } 1646 if (cacti_sizeof($credentials)) { 1647 foreach ($credentials as $key => $value) { 1648 $name = trim($key); 1649 if (strstr($name, '--') !== false) { //name like --host 1650 if($name == '--password') { 1651 $password = $value; 1652 } else if ($name == '--user') { 1653 $username = $value; 1654 } else { 1655 $credentials_string .= $name . '=' . $value . ' '; 1656 } 1657 } else if(strstr($name, '-') !== false) { //name like -h 1658 if($name == '-p') { 1659 $password = $value; 1660 } else if ($name == '-u') { 1661 $username = $value; 1662 } else { 1663 $credentials_string .= $name . $value . ' '; 1664 } 1665 } else { //name like host 1666 if($name == 'password') { 1667 $password = $value; 1668 } else if ($name == 'user') { 1669 $username = $value; 1670 } else { 1671 $credentials_string .= '--' . $name . '=' . $value . ' '; 1672 } 1673 } 1674 } 1675 } 1676 if (!isset($password)) { 1677 $password = $database_password; 1678 } 1679 if (!isset($username)) { 1680 $username = $database_username; 1681 } 1682 if (strstr($options, '--defaults-extra-file') !== false) { 1683 exec("mysqldump $options $credentials_string $database $tables > " . $output_file, $output, $retval); 1684 } else { 1685 exec("mysqldump $options $credentials_string " . $database . ' version >/dev/null 2>&1', $output, $retval); 1686 if ($retval) { 1687 exec("mysqldump $options $credentials_string -u" . $username . ' -p' . $password . ' ' . $database . " $tables > " . $output_file, $output, $retval); 1688 } else { 1689 exec("mysqldump $options $credentials_string $database $tables > " . $output_file, $output, $retval); 1690 } 1691 } 1692 return $retval; 1693} 1694 1695