mirror of
https://github.com/ZoneMinder/zoneminder.git
synced 2026-10-29 01:38:37 -04:00
Isaac ConnorandClaude Opus 5.5
0960b2064b
dbQuery()'s two error paths for a prepare() or query() that returns false logged $pdo->errorInfo. $pdo is not defined there, so PHP warned twice and the message ended without the error. They also read dbLastError() from the connection only after ZM\Error() had logged; with a database log target that inserts into Logs through the same connection, which replaces its errorInfo, so dbLastError() came back as "00000" instead of the error. Read the error once, before logging, and use it for both. The query() path also said "Error preparing" for a statement it ran directly. These paths run when PDO's error mode doesn't throw; dbConnect() sets ERRMODE_EXCEPTION, so normally the catch below handles the failure. tests/php/test_database_error_paths.php drives both paths with ERRMODE_SILENT and checks for no PHP warning and the real error text. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
510 lines
16 KiB
PHP
510 lines
16 KiB
PHP
<?php
|
|
//
|
|
// ZoneMinder web database interface file, $Date$, $Revision$
|
|
// Copyright (C) 2001-2008 Philip Coombes
|
|
//
|
|
// This program is free software; you can redistribute it and/or
|
|
// modify it under the terms of the GNU General Public License
|
|
// as published by the Free Software Foundation; either version 2
|
|
// of the License, or (at your option) any later version.
|
|
//
|
|
// This program is distributed in the hope that it will be useful,
|
|
// but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
// GNU General Public License for more details.
|
|
//
|
|
// You should have received a copy of the GNU General Public License
|
|
// along with this program; if not, write to the Free Software
|
|
// Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
|
|
//
|
|
|
|
define('DB_LOG_OFF', 0);
|
|
define('DB_LOG_ONLY', 1);
|
|
define('DB_LOG_DEBUG', 2);
|
|
|
|
$GLOBALS['dbLogLevel'] = DB_LOG_OFF;
|
|
|
|
$GLOBALS['dbConn'] = false;
|
|
require_once('logger.php');
|
|
|
|
function dbConnect() {
|
|
global $dbConn;
|
|
|
|
$dsn = ZM_DB_TYPE;
|
|
if ( ZM_DB_HOST ) {
|
|
if ( strpos(ZM_DB_HOST, ':') ) {
|
|
// Host variable may carry a port or socket.
|
|
list($host, $portOrSocket) = explode(':', ZM_DB_HOST, 2);
|
|
if ( ctype_digit($portOrSocket) ) {
|
|
$dsn .= ':host='.$host.';port='.$portOrSocket.';';
|
|
} else {
|
|
$dsn .= ':unix_socket='.$portOrSocket.';';
|
|
}
|
|
} else {
|
|
$dsn .= ':host='.ZM_DB_HOST.';';
|
|
}
|
|
} else {
|
|
$dsn .= ':host=localhost;';
|
|
}
|
|
$dsn .= 'dbname='.ZM_DB_NAME.';charset=utf8mb4';
|
|
|
|
// From PHP 8.1, pdo_mysql returns integers and floats as PHP ints and floats
|
|
// with emulated prepares too, as it does with server-side ones. There, let
|
|
// PDO bind the parameters itself: one round trip per statement instead of
|
|
// three (prepare, execute, close). Older PHP would return every value as a
|
|
// string, so it keeps server-side prepares. Emulated prepares would accept
|
|
// several statements in one string, so that is turned off.
|
|
$emulatePrepares = PHP_VERSION_ID >= 80100;
|
|
|
|
try {
|
|
$dbOptions = array();
|
|
if ( $emulatePrepares ) {
|
|
$dbOptions[PDO::MYSQL_ATTR_MULTI_STATEMENTS] = false;
|
|
}
|
|
if ( defined('ZM_DB_SSL_CA_CERT') and ZM_DB_SSL_CA_CERT ) {
|
|
$dbOptions += array(
|
|
PDO::MYSQL_ATTR_SSL_CA => ZM_DB_SSL_CA_CERT,
|
|
PDO::MYSQL_ATTR_SSL_KEY => ZM_DB_SSL_CLIENT_KEY,
|
|
PDO::MYSQL_ATTR_SSL_CERT => ZM_DB_SSL_CLIENT_CERT,
|
|
);
|
|
// Identity-verify the server certificate when ZM_DB_SSL_VERIFY_SERVER_CERT
|
|
// is set: truthy verifies, false-y (0/false/no/off) allows a self-signed
|
|
// or non-matching cert. An empty/unset value leaves PDO's default in place
|
|
// so existing installs are not changed on upgrade. Guarded with defined()
|
|
// so an upgraded zm.conf that predates this option does not fatal on PHP 8.
|
|
// Refs #3816.
|
|
if ( defined('ZM_DB_SSL_VERIFY_SERVER_CERT') ) {
|
|
$verify_value = strtolower(trim((string)ZM_DB_SSL_VERIFY_SERVER_CERT));
|
|
if ( $verify_value !== '' ) {
|
|
$dbOptions[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] =
|
|
!in_array($verify_value, array('0', 'false', 'no', 'off'), true);
|
|
}
|
|
}
|
|
}
|
|
$dbConn = new PDO($dsn, ZM_DB_USER, ZM_DB_PASS, $dbOptions);
|
|
|
|
$dbConn->setAttribute(PDO::ATTR_EMULATE_PREPARES, $emulatePrepares);
|
|
$dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
} catch(PDOException $ex) {
|
|
global $error_message;
|
|
$error_message = "Unable to connect to ZM db using dsn $dsn<br/><br/>".$ex->getMessage();
|
|
error_log('Unable to connect to ZM DB ' . $ex->getMessage());
|
|
$dbConn = null;
|
|
}
|
|
return $dbConn;
|
|
} // end function dbConnect
|
|
|
|
// $dbConn is tri-state: false means no connection has been attempted, null means
|
|
// an attempt was made and failed, and a PDO means we are connected.
|
|
//
|
|
// Connecting happens on first use rather than when this file is included. Every
|
|
// model in web/includes requires database.php, so connecting at include time
|
|
// meant that merely loading a class opened a socket - and, on failure, rendered
|
|
// an error page and killed the request from inside a library include. Deferring
|
|
// it means code that only needs the functions here never touches the database.
|
|
|
|
// Return the connection, opening it if needed. A request that cannot reach the
|
|
// database cannot be served, so this renders the error view and stops, which is
|
|
// what including this file used to do - just at the point a query is actually
|
|
// attempted.
|
|
function zmDbConn() {
|
|
global $dbConn;
|
|
if ($dbConn === false) dbConnect();
|
|
if (!$dbConn) {
|
|
// Absolute path: the previous relative include only resolved when the cwd
|
|
// was web/, so it failed for anything served out of web/api/.
|
|
include(__DIR__.'/../views/no_database_connection.php');
|
|
exit();
|
|
}
|
|
return $dbConn;
|
|
}
|
|
|
|
// As above, but returns null instead of ending the request. For callers that
|
|
// have something useful to do without a database - the logger falls back to
|
|
// error_log() - and must not turn a logging call into a fatal error.
|
|
function zmDbConnOrNull() {
|
|
global $dbConn;
|
|
if ($dbConn === false) dbConnect();
|
|
return $dbConn ? $dbConn : null;
|
|
}
|
|
|
|
function dbDisconnect() {
|
|
global $dbConn;
|
|
// false, not null: this is "no longer connected", not "connecting failed", so
|
|
// a later query is free to open a new connection.
|
|
$dbConn = false;
|
|
}
|
|
|
|
function dbLogOff() {
|
|
global $dbLogLevel;
|
|
$dbLogLevel = DB_LOG_OFF;
|
|
}
|
|
|
|
function dbLogOn() {
|
|
global $dbLogLevel;
|
|
$dbLogLevel = DB_LOG_ONLY;
|
|
}
|
|
|
|
function dbLogDebug() {
|
|
global $dbLogLevel;
|
|
$dbLogLevel = DB_LOG_DEBUG;
|
|
}
|
|
|
|
function dbDebug() {
|
|
dbLogDebug();
|
|
}
|
|
|
|
function dbLog($sql, $update=false) {
|
|
global $dbLogLevel;
|
|
$noExecute = $update && ($dbLogLevel >= DB_LOG_DEBUG);
|
|
if ( $dbLogLevel > DB_LOG_OFF )
|
|
ZM\Debug( "SQL-LOG: $sql".($noExecute?' (not executed)':'') );
|
|
return( $noExecute );
|
|
}
|
|
|
|
function dbError($sql) {
|
|
$dbConn = zmDbConnOrNull();
|
|
if (!$dbConn) return '';
|
|
$error = $dbConn->errorInfo();
|
|
if (!$error[0])
|
|
return '';
|
|
|
|
$message = "SQL-ERR '".implode("\n", $dbConn->errorInfo())."', statement was '".$sql."'";
|
|
ZM\Error($message);
|
|
return $message;
|
|
}
|
|
|
|
function dbEscape( $string ) {
|
|
$dbConn = zmDbConn();
|
|
if ( version_compare(phpversion(), '5.4', '<=') and get_magic_quotes_gpc() )
|
|
return $dbConn->quote(stripslashes($string));
|
|
else
|
|
return $dbConn->quote($string);
|
|
}
|
|
|
|
function dbQuery($sql, $params=NULL, $debug = false) {
|
|
$dbConn = zmDbConn();
|
|
if (dbLog($sql, true))
|
|
return;
|
|
$result = NULL;
|
|
try {
|
|
if (isset($params)) {
|
|
if (!$result = $dbConn->prepare($sql)) {
|
|
// Read before logging: a DB log target writes through the same
|
|
// connection, which replaces its errorInfo, and calls dbQuery, which
|
|
// clears dbLastError.
|
|
$error = implode(' ', $dbConn->errorInfo());
|
|
ZM\Error("SQL: Error preparing $sql: $error");
|
|
$GLOBALS['dbLastError'] = $error;
|
|
return NULL;
|
|
}
|
|
|
|
// Integers are bound as integers, so that LIMIT ? still works when
|
|
// prepares are emulated (a string would be quoted).
|
|
foreach ($params as $key => $value) {
|
|
$result->bindValue(is_int($key) ? $key + 1 : $key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
|
|
}
|
|
if (!$result->execute()) {
|
|
ZM\Error("SQL: Error executing $sql: " . print_r($result->errorInfo(), true));
|
|
$GLOBALS['dbLastError'] = implode(' ', $result->errorInfo());
|
|
return NULL;
|
|
}
|
|
} else {
|
|
if ( defined('ZM_DB_DEBUG') or $debug ) {
|
|
ZM\Debug("SQL: $sql values:" . ($params?implode(',',$params):''));
|
|
}
|
|
$result = $dbConn->query($sql);
|
|
if ( ! $result ) {
|
|
// Read before logging, as for prepare() above.
|
|
$error = implode(' ', $dbConn->errorInfo());
|
|
ZM\Error("SQL: Error running $sql: $error");
|
|
$GLOBALS['dbLastError'] = $error;
|
|
return NULL;
|
|
}
|
|
}
|
|
if ( defined('ZM_DB_DEBUG') or $debug ) {
|
|
ZM\Debug('SQL: '.$sql.' '.($params?implode(',',$params):'').' rows: '.$result->rowCount());
|
|
}
|
|
} catch(PDOException $e) {
|
|
ZM\Error("SQL-ERR '".$e->getMessage()."', statement was '".$sql."' params:" . ($params?implode(',',$params):''));
|
|
$GLOBALS['dbLastError'] = $e->getMessage();
|
|
return NULL;
|
|
}
|
|
$GLOBALS['dbLastError'] = null;
|
|
return $result;
|
|
}
|
|
|
|
// Human-readable text of the most recent dbQuery() failure, or '' if the last
|
|
// query succeeded. Only meaningful immediately after dbQuery() returns NULL.
|
|
function dbLastError() {
|
|
return isset($GLOBALS['dbLastError']) ? $GLOBALS['dbLastError'] : '';
|
|
}
|
|
|
|
function dbFetchOne($sql, $col=false, $params=NULL) {
|
|
$result = dbQuery($sql, $params);
|
|
if ( !$result ) {
|
|
ZM\Error("SQL-ERR dbFetchOne no result, statement was '".$sql."'".($params ? 'params: ' . join(',',$params) : ''));
|
|
return false;
|
|
}
|
|
if ( !$result->rowCount() ) {
|
|
# No rows is not an error
|
|
return false;
|
|
}
|
|
|
|
if ( $result && ($dbRow = $result->fetch(PDO::FETCH_ASSOC)) ) {
|
|
if ( $col ) {
|
|
if ( ! array_key_exists($col, $dbRow) ) {
|
|
ZM\Warning("$col does not exist in the returned row " . print_r($dbRow, true));
|
|
return false;
|
|
}
|
|
return $dbRow[$col];
|
|
}
|
|
return $dbRow;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
function dbFetchAll($sql, $col=false, $params=NULL) {
|
|
$dbRows = array();
|
|
$result = dbQuery($sql, $params);
|
|
if ( ! $result ) {
|
|
ZM\Error("SQL-ERR dbFetchAll no result, statement was '".$sql."'".($params ? 'params: '.join(',', $params) : ''));
|
|
return $dbRows;
|
|
}
|
|
|
|
while ( $dbRow = $result->fetch(PDO::FETCH_ASSOC) )
|
|
$dbRows[] = $col ? $dbRow[$col] : $dbRow;
|
|
return $dbRows;
|
|
}
|
|
|
|
function dbFetchAssoc($sql, $indexCol, $dataCol=false) {
|
|
$result = dbQuery($sql);
|
|
|
|
$dbRows = array();
|
|
while( $dbRow = $result->fetch(PDO::FETCH_ASSOC) )
|
|
$dbRows[$dbRow[$indexCol]] = $dataCol ? $dbRow[$dataCol] : $dbRow;
|
|
return $dbRows;
|
|
}
|
|
|
|
function dbFetch($sql, $col=false) {
|
|
return dbFetchAll($sql, $col);
|
|
}
|
|
|
|
function dbFetchNext($result, $col=false) {
|
|
if ( !$result ) {
|
|
ZM\Error("dbFetchNext called on null result.");
|
|
return false;
|
|
}
|
|
if ( $dbRow = $result->fetch(PDO::FETCH_ASSOC) )
|
|
return $col ? $dbRow[$col] : $dbRow;
|
|
return false;
|
|
}
|
|
|
|
function dbNumRows($sql, $params=NULL) {
|
|
$result = dbQuery($sql, $params);
|
|
return $result->rowCount();
|
|
}
|
|
|
|
function dbInsertId() {
|
|
$dbConn = zmDbConn();
|
|
return $dbConn->lastInsertId();
|
|
}
|
|
|
|
function getEnumValues($table, $column) {
|
|
$row = dbFetchOne("DESCRIBE `$table` `$column`");
|
|
preg_match_all("/'([^']+)'/", $row['Type'], $matches);
|
|
return $matches[1];
|
|
}
|
|
|
|
function getSetValues($table, $column) {
|
|
return getEnumValues($table, $column);
|
|
}
|
|
|
|
function getUniqueValues($table, $column, $asString=1) {
|
|
$values = array();
|
|
$sql = "SELECT DISTINCT `$column` FROM `$table` WHERE (NOT isnull(`$column`) AND `$column` != '') ORDER BY `$column`";
|
|
foreach ( dbFetchAll($sql) as $row ) {
|
|
if ( $asString )
|
|
$values[$row[$column]] = $row[$column];
|
|
else
|
|
$values[] = $row[$column];
|
|
}
|
|
return $values;
|
|
}
|
|
|
|
function getTableColumns( $table, $asString=1 ) {
|
|
$columns = array();
|
|
$sql = "DESCRIBE `$table`";
|
|
foreach ( dbFetchAll($sql) as $row ) {
|
|
if ( $asString )
|
|
$columns[$row['Field']] = $row['Type'];
|
|
else
|
|
$columns[] = $row['Type'];
|
|
}
|
|
return $columns;
|
|
}
|
|
|
|
function getTableAutoInc( $table ) {
|
|
$row = dbFetchOne('SHOW TABLE status WHERE Name=?', NULL, array($table));
|
|
return $row['Auto_increment'];
|
|
}
|
|
|
|
function getTableDescription( $table, $asString=1 ) {
|
|
$columns = array();
|
|
foreach( dbFetchAll("DESCRIBE `$table`") as $row ) {
|
|
$desc = array(
|
|
'name' => $row['Field'],
|
|
'required' => ($row['Null']=='NO')?true:false,
|
|
'default' => $row['Default'],
|
|
'db' => $row,
|
|
);
|
|
if ( preg_match('/^varchar\((\d+)\)$/', $row['Type'], $matches) ) {
|
|
$desc['type'] = 'text';
|
|
$desc['typeAttrib'] = 'varchar';
|
|
$desc['maxLength'] = $matches[1];
|
|
} elseif ( preg_match('/^(\w+)?text$/', $row['Type'], $matches) ) {
|
|
$desc['type'] = 'text';
|
|
if ( !empty($matches[1]) )
|
|
$desc['typeAttrib'] = $matches[1];
|
|
switch ( $matches[1] ) {
|
|
case 'tiny' :
|
|
$desc['maxLength'] = 255;
|
|
break;
|
|
case 'medium' :
|
|
$desc['maxLength'] = 32768;
|
|
break;
|
|
case '' :
|
|
case 'big' :
|
|
//$desc['minLength'] = -128;
|
|
break;
|
|
default :
|
|
ZM\Error("Unexpected text qualifier '".$matches[1]."' found for field '".$row['Field']."' in table '".$table."'");
|
|
break;
|
|
}
|
|
} elseif ( preg_match('/^(enum|set)\((.*)\)$/', $row['Type'], $matches) ) {
|
|
$desc['type'] = 'text';
|
|
$desc['typeAttrib'] = $matches[1];
|
|
preg_match_all("/'([^']+)'/", $matches[2], $matches);
|
|
$desc['values'] = $matches[1];
|
|
} elseif ( preg_match('/^(\w+)?int\(\d+\)(?:\s+(unsigned))?$/', $row['Type'], $matches) ) {
|
|
$desc['type'] = 'integer';
|
|
switch ( $matches[1] ) {
|
|
case 'tiny' :
|
|
$desc['minValue'] = -128;
|
|
$desc['maxValue'] = 127;
|
|
break;
|
|
case 'small' :
|
|
$desc['minValue'] = -32768;
|
|
$desc['maxValue'] = 32767;
|
|
break;
|
|
case 'medium' :
|
|
$desc['minValue'] = -8388608;
|
|
$desc['maxValue'] = 8388607;
|
|
break;
|
|
case '' :
|
|
$desc['minValue'] = -2147483648;
|
|
$desc['maxValue'] = 2147483647;
|
|
break;
|
|
case 'big' :
|
|
//$desc['minValue'] = -128;
|
|
//$desc['maxValue'] = 127;
|
|
break;
|
|
default :
|
|
ZM\Error("Unexpected integer qualifier '".$matches[1]."' found for field '".$row['Field']."' in table '".$table."'");
|
|
break;
|
|
}
|
|
if ( !empty($matches[1]) )
|
|
$desc['typeAttrib'] = $matches[1];
|
|
if ( $desc['unsigned'] = ( isset($matches[2]) && $matches[2] == 'unsigned' ) ) {
|
|
$desc['maxValue'] += (-$desc['minValue']);
|
|
$desc['minValue'] = 0;
|
|
}
|
|
} elseif ( preg_match('/^(?:decimal|numeric)\((\d+)(?:,(\d+))?\)(?:\s+(unsigned))?$/', $row['Type'], $matches) ) {
|
|
$desc['type'] = 'fixed';
|
|
$desc['range'] = $matches[1];
|
|
if ( isset($matches[2]) )
|
|
$desc['precision'] = $matches[2];
|
|
else
|
|
$desc['precision'] = 0;
|
|
$desc['unsigned'] = ( isset($matches[3]) && $matches[3] == 'unsigned' );
|
|
} elseif ( preg_match('/^(datetime|timestamp|date|time)$/', $row['Type'], $matches) ) {
|
|
$desc['type'] = 'datetime';
|
|
switch ( $desc['typeAttrib'] = $matches[1] ) {
|
|
case 'datetime' :
|
|
case 'timestamp' :
|
|
$desc['hasDate'] = true;
|
|
$desc['hasTime'] = true;
|
|
break;
|
|
case 'date' :
|
|
$desc['hasDate'] = true;
|
|
$desc['hasTime'] = false;
|
|
break;
|
|
case 'time' :
|
|
$desc['hasDate'] = false;
|
|
$desc['hasTime'] = true;
|
|
break;
|
|
}
|
|
} else {
|
|
ZM\Error("Can't parse database type '".$row['Type']."' found for field '".$row['Field']."' in table '".$table."'");
|
|
}
|
|
|
|
if ( $asString )
|
|
$columns[$row['Field']] = $desc;
|
|
else
|
|
$columns[] = $desc;
|
|
}
|
|
return $columns;
|
|
}
|
|
|
|
function db_version() {
|
|
return dbFetchOne('SELECT VERSION()', 'VERSION()');
|
|
}
|
|
|
|
function db_supports_feature($feature) {
|
|
$version = db_version();
|
|
if ($feature == 'skip_locks') {
|
|
$just_the_version = strstr($version, '-MariaDB', true);
|
|
if (false === $just_the_version) {
|
|
# Is MYSQL
|
|
return version_compare($version, '8.0.1', '>=');
|
|
} else {
|
|
return version_compare($just_the_version, '10.6', '>=');
|
|
}
|
|
} else {
|
|
ZM\Warning("Unknown feature requested $feature");
|
|
}
|
|
}
|
|
|
|
function dbInsert($table, $fieldArray) {
|
|
$query = 'INSERT INTO `' . $table . '` SET ';
|
|
$fields = [];
|
|
$conditions = [];
|
|
$values = [];
|
|
foreach ($fieldArray as $fieldName => $fieldValue) {
|
|
$fields[] = '`' . $fieldName.'`=?';
|
|
$values[] = $fieldValue;
|
|
}
|
|
$query .= implode(', ', $fields);
|
|
return dbQuery($query, $values);
|
|
}
|
|
|
|
function dbUpdate($table, $fieldArray, $conditionArray) {
|
|
$query = 'UPDATE `' . $table . '` SET ';
|
|
$fields = [];
|
|
$conditions = [];
|
|
$values = [];
|
|
foreach ($fieldArray as $fieldName => $fieldValue) {
|
|
$fields[] = '`' . $fieldName.'`=?';
|
|
$values[] = $fieldValue;
|
|
}
|
|
$query .= implode(', ', $fields);
|
|
$query .= ' WHERE ';
|
|
foreach ($conditionArray as $fieldName => $fieldValue) {
|
|
$conditions[] = '`' . $fieldName . '` = ?';
|
|
$values[] = $fieldValue;
|
|
}
|
|
$query .= implode(' AND ', $conditions);
|
|
return dbQuery($query, $values);
|
|
}
|
|
?>
|