470 lines
16 KiB
PHP
470 lines
16 KiB
PHP
<?php
|
|
/**
|
|
* Observium
|
|
*
|
|
* This file is part of Observium.
|
|
*
|
|
* @package observium
|
|
* @subpackage db
|
|
* @copyright (C) 2006-2013 Adam Armstrong, (C) 2013-2021 Observium Limited
|
|
*
|
|
*/
|
|
|
|
/* Specific mysqli function calls, uses procedural style. */
|
|
|
|
// Set to TRUE if MySQLnd driver is being used
|
|
define('OBS_DB_MYSQLND', function_exists('mysqli_get_client_stats'));
|
|
|
|
/**
|
|
* Get MySQL client info
|
|
*
|
|
* @return string $info
|
|
*/
|
|
function dbClientInfo() {
|
|
return mysqli_get_client_info();
|
|
}
|
|
|
|
/**
|
|
* Returns a string representing the type of connection used
|
|
*
|
|
* @return string $info
|
|
*/
|
|
function dbHostInfo() {
|
|
return isset($GLOBALS[OBS_DB_LINK]) ? mysqli_get_host_info($GLOBALS[OBS_DB_LINK]) : '';
|
|
}
|
|
|
|
/**
|
|
* Open connection to mysql server
|
|
*
|
|
* @param string $database Database name
|
|
* @param string $user Username for connect to mysql server
|
|
* @param string $password Username password
|
|
* @param string $host Hostname for mysql server, default 'localhost'
|
|
* @param string $charset Charset used for mysql connection, default 'utf8'
|
|
*
|
|
* @return object $connection
|
|
*/
|
|
function dbOpen($host, $user, $password, $database, $charset = 'utf8') {
|
|
// Check host params
|
|
$host_array = explode(':', $host);
|
|
if (count($host_array) > 1) {
|
|
if ($host_array[0] === 'p') {
|
|
// p:example.com
|
|
// p:::1
|
|
array_shift($host_array);
|
|
$host = implode(':', $host_array);
|
|
$GLOBALS['config']['db_persistent'] = TRUE;
|
|
} elseif (count($host_array) === 2) {
|
|
// This is for compatibility with old style host option (from mysql extension)
|
|
// IPv6 host not possible here
|
|
$host = $host_array[0];
|
|
if (is_numeric($host_array[1])) {
|
|
// example.com:3306
|
|
$port = $host_array[1];
|
|
} else {
|
|
// example.com:/tmp/mysql.sock
|
|
$socket = $host_array[1];
|
|
}
|
|
}
|
|
}
|
|
|
|
// Server port
|
|
if (isset($GLOBALS['config']['db_port']) && is_numeric($GLOBALS['config']['db_port'])) {
|
|
$port = $GLOBALS['config']['db_port'];
|
|
} elseif (!isset($port)) {
|
|
$port = ini_get("mysqli.default_port");
|
|
}
|
|
|
|
// Server socket
|
|
if (isset($GLOBALS['config']['db_socket']) && !safe_empty($GLOBALS['config']['db_socket'])) {
|
|
$socket = $GLOBALS['config']['db_socket'];
|
|
} elseif (!isset($socket)) {
|
|
$socket = ini_get("mysqli.default_socket");
|
|
}
|
|
|
|
// Prepending host by p: for open a persistent connection.
|
|
if (isset($GLOBALS['config']['db_persistent']) && $GLOBALS['config']['db_persistent'] &&
|
|
ini_get('mysqli.allow_persistent')) {
|
|
$host = 'p:' . $host;
|
|
}
|
|
|
|
// Init new connection
|
|
$time_start = utime();
|
|
$GLOBALS['db_stats']['connect'] = 1;
|
|
$connection = mysqli_init();
|
|
if ($connection === (object)$connection) {
|
|
$client_flags = 0;
|
|
// Optionally compress connection
|
|
if ($GLOBALS['config']['db_compress'] && defined('MYSQLI_CLIENT_COMPRESS')) {
|
|
$client_flags |= MYSQLI_CLIENT_COMPRESS;
|
|
}
|
|
|
|
// Optionally enable SSL
|
|
if ($GLOBALS['config']['db_ssl']) {
|
|
$client_flags |= MYSQLI_CLIENT_SSL;
|
|
|
|
if (!empty($GLOBALS['config']['db_ssl_key']) || !empty($GLOBALS['config']['db_ssl_cert']) ||
|
|
!empty($GLOBALS['config']['db_ssl_ca']) || !empty($GLOBALS['config']['db_ssl_ca_path']) ||
|
|
!empty($GLOBALS['config']['db_ssl_ciphers'])) {
|
|
$db_ssl_key = $GLOBALS['config']['db_ssl_key'] ? $GLOBALS['config']['db_ssl_key'] : '';
|
|
$db_ssl_cert = $GLOBALS['config']['db_ssl_cert'] ? $GLOBALS['config']['db_ssl_cert'] : '';
|
|
$db_ssl_ca = $GLOBALS['config']['db_ssl_ca'] ? $GLOBALS['config']['db_ssl_ca'] : '';
|
|
$db_ssl_ca_path = $GLOBALS['config']['db_ssl_ca_path'] ? $GLOBALS['config']['db_ssl_ca_path'] : '';
|
|
$db_ssl_ciphers = $GLOBALS['config']['db_ssl_ciphers'] ? $GLOBALS['config']['db_ssl_ciphers'] : '';
|
|
mysqli_ssl_set($connection, $db_ssl_key, $db_ssl_cert, $db_ssl_ca, $db_ssl_ca_path, $db_ssl_ciphers);
|
|
}
|
|
|
|
// Disables SSL certificate validation on mysqlnd for MySQL 5.6 or later
|
|
// https://bugs.php.net/bug.php?id=68344
|
|
if (!$GLOBALS['config']['db_ssl_verify']) {
|
|
$client_flags |= MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT;
|
|
|
|
mysqli_options($connection, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, $GLOBALS['config']['db_ssl_verify']);
|
|
}
|
|
}
|
|
|
|
// Connection timeout
|
|
//$timeout = (isset($GLOBALS['config']['db_timeout']) && $GLOBALS['config']['db_timeout'] >= 1) ? (int) $GLOBALS['config']['db_timeout'] : 30;
|
|
//mysqli_options($connection, MYSQLI_OPT_CONNECT_TIMEOUT, $timeout);
|
|
|
|
// Convert integer and float columns back to PHP numbers. Boolean returns as int. Only valid for mysqlnd.
|
|
/*
|
|
if (defined('OBS_DB_MYSQLND') && OBS_DB_MYSQLND)
|
|
{
|
|
mysqli_options($connection, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);
|
|
}
|
|
*/
|
|
// Report if no index or bad index was used in a query
|
|
//mysqli_report(MYSQLI_REPORT_INDEX);
|
|
|
|
// Set default ignore errors on php8.1
|
|
// https://php.watch/versions/8.1/mysqli-error-mode
|
|
mysqli_report(MYSQLI_REPORT_OFF);
|
|
|
|
if (!mysqli_real_connect($connection, $host, $user, $password, $database, (int)$port, $socket, $client_flags)) {
|
|
$error_num = @mysqli_connect_errno();
|
|
if (defined('OBS_DEBUG') && OBS_DEBUG) {
|
|
echo('MySQLi connection error ' . $error_num . ': ' . @mysqli_connect_error() . PHP_EOL);
|
|
}
|
|
if ($error_num == 2006 && PHP_VERSION_ID >= 70400 && PHP_VERSION_ID < 70402) {
|
|
print_error("PHP version less than 7.4.2 have multiple issues with MySQL 8.0, please update your PHP to latest.");
|
|
}
|
|
|
|
$time_end = utime();
|
|
$GLOBALS['db_stats']['connect_sec'] = $time_end - $time_start;
|
|
|
|
return NULL;
|
|
}
|
|
|
|
$time_end = utime();
|
|
$GLOBALS['db_stats']['connect_sec'] = $time_end - $time_start;
|
|
//print_vars($GLOBALS['db_stats']['connect_sec']);
|
|
|
|
if ($charset) {
|
|
mysqli_set_charset($connection, $charset);
|
|
/*
|
|
if (version_compare(PHP_VERSION, '5.3.6', '<') && $charset == 'utf8')
|
|
{
|
|
// Seem as problem to set default charset on php < 5.3.6
|
|
mysqli_query($connection, "SET NAMES 'utf8' COLLATE 'utf8_general_ci';");
|
|
}
|
|
*/
|
|
}
|
|
}
|
|
|
|
return $connection;
|
|
}
|
|
|
|
/**
|
|
* Closes a previously opened database connection
|
|
*
|
|
* @param mysqli $connection Link to resource with mysql connection, default last used connection
|
|
*
|
|
* @return bool Returns TRUE on success or FALSE on failure.
|
|
*/
|
|
function dbClose($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
if (!OBS_DB_SKIP) { print_error("Call to function mysqli_close() without link identifier."); }
|
|
return;
|
|
}
|
|
|
|
return mysqli_close($connection);
|
|
}
|
|
|
|
/**
|
|
* Returns the text of the error message from last MySQL operation
|
|
*
|
|
* @param mysqli $connection Link to resource with mysql connection, default last used connection
|
|
*
|
|
* @return string $return
|
|
*/
|
|
function dbError($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
//if (!OBS_DB_SKIP) { print_error("Call to function mysqli_error() without link identifier."); }
|
|
return mysqli_connect_error();
|
|
}
|
|
|
|
return mysqli_error($connection);
|
|
}
|
|
|
|
/**
|
|
* Returns the numerical value of the error message from last MySQL operation
|
|
*
|
|
* @param mysqli $connection Link to resource with mysql connection, default last used connection
|
|
*
|
|
* @return int $return
|
|
*/
|
|
function dbErrorNo($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
//if (!OBS_DB_SKIP) { print_error("Call to function mysqli_errno() without link identifier."); }
|
|
return mysqli_connect_errno();
|
|
}
|
|
|
|
return mysqli_errno($connection);
|
|
}
|
|
|
|
function dbWarnings($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
return;
|
|
}
|
|
|
|
$warning = [];
|
|
if (mysqli_warning_count($connection)) {
|
|
$e = mysqli_get_warnings($connection);
|
|
do {
|
|
//echo "Warning: $e->errno: $e->message\n";
|
|
$warning[] = "$e->errno: $e->message";
|
|
} while ($e->next());
|
|
}
|
|
return $warning;
|
|
}
|
|
|
|
function dbPing($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
}
|
|
|
|
return mysqli_ping($connection);
|
|
}
|
|
|
|
function dbAffectedRows($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
if (!OBS_DB_SKIP) { print_error("Call to function mysqli_affected_rows() without link identifier."); }
|
|
return;
|
|
}
|
|
|
|
return mysqli_affected_rows($connection);
|
|
}
|
|
|
|
function dbCallQuery($fullSql, $connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
if (!OBS_DB_SKIP) { print_error("Call to function mysqli_query() without link identifier."); }
|
|
return;
|
|
}
|
|
|
|
return mysqli_query($connection, $fullSql);
|
|
//return mysqli_query($connection, $fullSql, MYSQLI_USE_RESULT); // Unbuffered results, for speedup!
|
|
}
|
|
|
|
/**
|
|
* Returns escaped string
|
|
*
|
|
* @param string $string Input string for escape in mysql query
|
|
* @param mysqli $connection Link to resource with mysql connection, default last used connection
|
|
*
|
|
* @return string
|
|
*/
|
|
function dbEscape($string, $connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
if (!OBS_DB_SKIP) { print_error("Call to function mysqli_real_escape_string() without link identifier."); }
|
|
return;
|
|
}
|
|
|
|
$return = mysqli_real_escape_string($connection, $string);
|
|
if (!isset($return[0]) && isset($string[0])) {
|
|
// If character set empty, use escape alternative
|
|
// FIXME. I really not know why, but in unittests $connection object is lost!
|
|
print_debug("Mysql connection lost, in dbEscape() used escape alternative!");
|
|
$search = array("\\", "\x00", "\n", "\r", "'", '"', "\x1a");
|
|
$replace = array("\\\\","\\0","\\n", "\\r", "\'", '\"', "\\Z");
|
|
$return = str_replace($search, $replace, $string);
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
/**
|
|
* Returns the auto generated id used in the last query
|
|
*
|
|
* @param mysqli $connection Link to resource with mysql connection, default last used connection
|
|
*
|
|
* @return string
|
|
*/
|
|
function dbLastID($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
if (!OBS_DB_SKIP) { print_error("Call to function mysqli_insert_id() without link identifier."); }
|
|
return;
|
|
}
|
|
|
|
if ($id = mysqli_insert_id($connection)) {
|
|
//print_debug("DEBUG ID by function");
|
|
//var_dump($id);
|
|
return $id;
|
|
} else {
|
|
// mysqli_insert_id() not return last id, after non empty warnings
|
|
//print_debug("DEBUG ID by query");
|
|
//var_dump($id);
|
|
return dbFetchCell('SELECT last_insert_id();');
|
|
}
|
|
//return mysqli_insert_id($connection);
|
|
}
|
|
|
|
/*
|
|
* Fetches all of the rows (associatively) from the last performed query.
|
|
* Most other retrieval functions build off this
|
|
* */
|
|
function dbFetchRows($sql, $parameters = [], $print_query = FALSE) {
|
|
$time_start = utime();
|
|
$result = dbQuery($sql, $parameters, $print_query);
|
|
|
|
$rows = [];
|
|
if ($result instanceof mysqli_result) {
|
|
if (OBS_DB_MYSQLND) {
|
|
// MySQL Native Driver
|
|
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
|
|
} else {
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$rows[] = $row;
|
|
}
|
|
}
|
|
mysqli_free_result($result);
|
|
|
|
$time_end = utime();
|
|
$GLOBALS['db_stats']['fetchrows_sec'] += $time_end - $time_start;
|
|
$GLOBALS['db_stats']['fetchrows']++;
|
|
}
|
|
|
|
// no records, thus return empty array
|
|
// which should evaluate to false, and will prevent foreach notices/warnings
|
|
return $rows;
|
|
}
|
|
|
|
/*
|
|
* Like fetch(), accepts any number of arguments
|
|
* The first argument is an sprintf-ready query stringTypes
|
|
* */
|
|
function dbFetchRow($sql = NULL, $parameters = array(), $print_query = FALSE) {
|
|
$time_start = utime();
|
|
$result = dbQuery($sql, $parameters, $print_query);
|
|
if ($result) {
|
|
$row = mysqli_fetch_assoc($result);
|
|
mysqli_free_result($result);
|
|
$time_end = utime();
|
|
|
|
$GLOBALS['db_stats']['fetchrow_sec'] += $time_end - $time_start;
|
|
$GLOBALS['db_stats']['fetchrow']++;
|
|
|
|
return $row;
|
|
}
|
|
return [];
|
|
}
|
|
|
|
/*
|
|
* Fetches the first call from the first row returned by the query
|
|
* */
|
|
function dbFetchCell($sql, $parameters = array(), $print_query = FALSE) {
|
|
$time_start = utime();
|
|
//$row = dbFetchRow($sql, $parameters);
|
|
$result = dbQuery($sql, $parameters, $print_query);
|
|
if ($result) {
|
|
$row = mysqli_fetch_assoc($result);
|
|
mysqli_free_result($result);
|
|
$time_end = utime();
|
|
|
|
$GLOBALS['db_stats']['fetchcell_sec'] += $time_end - $time_start;
|
|
$GLOBALS['db_stats']['fetchcell']++;
|
|
|
|
if (is_array($row)) {
|
|
return array_shift($row); // shift first field off first row
|
|
}
|
|
}
|
|
|
|
return NULL; // or ''?
|
|
}
|
|
|
|
function dbBeginTransaction($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
if (!OBS_DB_SKIP) { print_error("Call to begin db transaction without link identifier."); }
|
|
return;
|
|
}
|
|
|
|
mysqli_autocommit($connection, FALSE); // Set autocommit to off
|
|
}
|
|
|
|
function dbCommitTransaction($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
if (!OBS_DB_SKIP) { print_error("Call to commmit db transaction without link identifier."); }
|
|
return;
|
|
}
|
|
|
|
mysqli_commit($connection);
|
|
mysqli_autocommit($connection, TRUE); // Restore autocommit to on
|
|
}
|
|
|
|
function dbRollbackTransaction($connection = NULL) {
|
|
// Observium uses $observium_link global variable name for db link
|
|
if ($connection === (object)$connection) {}
|
|
elseif (isset($GLOBALS[OBS_DB_LINK]) && $GLOBALS[OBS_DB_LINK] === (object)$GLOBALS[OBS_DB_LINK]) {
|
|
$connection = $GLOBALS[OBS_DB_LINK];
|
|
} else {
|
|
if (!OBS_DB_SKIP) { print_error("Call to rollback db transaction without link identifier."); }
|
|
return;
|
|
}
|
|
|
|
mysqli_rollback($connection);
|
|
mysqli_autocommit($connection, TRUE); // Restore autocommit to on
|
|
}
|
|
|
|
// EOF
|