416 lines
16 KiB
PHP
416 lines
16 KiB
PHP
<?php
|
|
|
|
/* ----------------------------------------------------------------------------
|
|
NagiosQL
|
|
-------------------------------------------------------------------------------
|
|
(c) 2005-2022 by Martin Willisegger
|
|
|
|
Project : NagiosQL
|
|
Component : MySQLi data processing class
|
|
Website : https://sourceforge.net/projects/nagiosql/
|
|
Version : 3.5.0
|
|
GIT Repo : https://gitlab.com/wizonet/NagiosQL
|
|
-----------------------------------------------------------------------------*/
|
|
|
|
/* ----------------------------------------------------------------------------
|
|
Class: Common database functions for MySQL (mysqli database module)
|
|
-------------------------------------------------------------------------------
|
|
Includes any functions to communicate with an MySQL database server
|
|
Name: MysqliDbClass
|
|
Class variables: $arrParams Array including the server settings
|
|
$strErrorMessage Database error string
|
|
$error Boolean - Error true/false
|
|
$strDBId Database connection id
|
|
$intLastId ID of last dataset
|
|
$intAffectedRows Counter variable of all affected data dows
|
|
$booSSLuse Use SSL connection
|
|
|
|
Parameters: $arrParams['server'] -> DB server name
|
|
$arrParams['port'] -> DB server port
|
|
$arrParams['user'] -> DB server username
|
|
$arrParams['password'] -> DB server password
|
|
$arrParams['database'] -> DB server database name
|
|
-----------------------------------------------------------------------------*/
|
|
|
|
namespace functions;
|
|
|
|
class MysqliDbClass
|
|
{
|
|
/* Define class variables */
|
|
public $error = false; /* Will be filled in functions */
|
|
public $strDBId; /* Will be filled in functions */
|
|
public $intLastId = 0; /* Will be filled in functions */
|
|
public $intAffectedRows = 0; /* Will be filled in functions */
|
|
public $strErrorMessage = ''; /* Will be filled in functions */
|
|
public $booSSLuse = false; /* Defines if SSL is used or not */
|
|
public $arrParams = array(); /* Must be filled in while initialization */
|
|
|
|
/**
|
|
* MysqliDbClass constructor.
|
|
*/
|
|
public function __construct()
|
|
{
|
|
$this->arrParams['server'] = '';
|
|
$this->arrParams['port'] = 0;
|
|
$this->arrParams['username'] = '';
|
|
$this->arrParams['password'] = '';
|
|
$this->arrParams['database'] = '';
|
|
}
|
|
|
|
/**
|
|
* MysqliDbClass destructor.
|
|
*/
|
|
public function __destruct()
|
|
{
|
|
$this->dbDisconnect();
|
|
}
|
|
|
|
/**
|
|
* Close database server connectuon
|
|
* @return bool true = successful / false = error
|
|
* @noinspection PhpReturnValueOfMethodIsNeverUsedInspection
|
|
*/
|
|
private function dbDisconnect(): bool
|
|
{
|
|
return mysqli_close($this->strDBId);
|
|
}
|
|
|
|
/**
|
|
* Opens a connection to the database server and select a database
|
|
* @param int $intMode 1 = connect only / 0 = connect + dbselect
|
|
* @return bool true = successful / false = error
|
|
* Status messages are stored in class variable
|
|
*/
|
|
public function hasDBConnection(int $intMode = 0): bool
|
|
{
|
|
$booReturn = true;
|
|
$this->dbconnect();
|
|
if ($this->error === true) {
|
|
$booReturn = false;
|
|
}
|
|
if (($booReturn === true) && ($intMode === 0)) {
|
|
$this->dbselect();
|
|
if ($this->error === true) {
|
|
$booReturn = false;
|
|
}
|
|
}
|
|
return $booReturn;
|
|
}
|
|
|
|
/**
|
|
* Connect to database server
|
|
* @param string $dbserver Server name
|
|
* @param int $dbport TCP port
|
|
* @param string $dbuser Database user
|
|
* @param string $dbpasswd Database password
|
|
* @return void true = successful / false = error
|
|
* Status messages are stored in class variable
|
|
* @noinspection PhpMissingParamTypeInspection
|
|
* @noinspection PhpSameParameterValueInspection
|
|
*/
|
|
private function dbconnect($dbserver = null, int $dbport = 0, $dbuser = null, $dbpasswd = null): void
|
|
{
|
|
/* Reset error variables */
|
|
$this->strErrorMessage = '';
|
|
$this->error = false;
|
|
$booReturn = true;
|
|
/* Get parameters */
|
|
if ($dbserver === null) {
|
|
$dbserver = $this->arrParams['server'];
|
|
}
|
|
if ($dbport === 0) {
|
|
$dbport = $this->arrParams['port'];
|
|
}
|
|
if ($dbuser === null) {
|
|
$dbuser = $this->arrParams['username'];
|
|
}
|
|
if ($dbpasswd === null) {
|
|
$dbpasswd = $this->arrParams['password'];
|
|
}
|
|
/* Not all parameters available */
|
|
if (($dbserver === '') || ($dbuser === '') || ($dbpasswd === '')) {
|
|
$this->strErrorMessage .= gettext('Missing server connection parameter!') . '::';
|
|
$this->error = true;
|
|
$booReturn = false;
|
|
}
|
|
if ($booReturn === true) {
|
|
$this->dbinit();
|
|
/* if ($this->booSSLuse === true) {
|
|
TODO: TO BE DEFINED
|
|
}*/
|
|
$intErrorReporting = error_reporting();
|
|
error_reporting(0);
|
|
if ($dbport === 0) {
|
|
$booReturn = mysqli_real_connect($this->strDBId, $dbserver, $dbuser, $dbpasswd);
|
|
} else {
|
|
$booReturn = mysqli_real_connect($this->strDBId, $dbserver, $dbuser, $dbpasswd, null, $dbport);
|
|
}
|
|
error_reporting($intErrorReporting);
|
|
// Connection fails
|
|
if ($booReturn === false) {
|
|
$this->strErrorMessage = '[' . $dbserver . '] ' . gettext('Connection to the database server has failed '
|
|
. 'by reason:') . ' ::';
|
|
$strError = mysqli_connect_error();
|
|
$this->strErrorMessage .= $strError . '::';
|
|
$this->error = true;
|
|
}
|
|
/** PHP8 exception handling */
|
|
mysqli_report(MYSQLI_REPORT_ERROR);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Initialize a mysql database connection
|
|
* @return bool true = successful / false = error
|
|
* @noinspection PhpReturnValueOfMethodIsNeverUsedInspection
|
|
*/
|
|
private function dbinit(): bool
|
|
{
|
|
$this->strDBId = mysqli_init();
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Select a database
|
|
* @param string $database Database name
|
|
* @return bool true = successful / false = error
|
|
* Status messages are stored in class variable
|
|
* @noinspection PhpReturnValueOfMethodIsNeverUsedInspection
|
|
* @noinspection PhpMissingParamTypeInspection
|
|
* @noinspection PhpSameParameterValueInspection
|
|
*/
|
|
private function dbselect($database = null): bool
|
|
{
|
|
/* Reset error variables */
|
|
$this->strErrorMessage = '';
|
|
$this->error = false;
|
|
$booReturn = true;
|
|
/* Get parameters */
|
|
if ($database === null) {
|
|
$database = $this->arrParams['database'];
|
|
}
|
|
/* Not all parameters available */
|
|
if ($database === '') {
|
|
$this->strErrorMessage .= gettext('Missing database connection parameter!') . '::';
|
|
$this->error = true;
|
|
$booReturn = false;
|
|
}
|
|
if ($booReturn === true) {
|
|
$intErrorReporting = error_reporting();
|
|
error_reporting(0);
|
|
$bolConnect = mysqli_select_db($this->strDBId, $database);
|
|
error_reporting($intErrorReporting);
|
|
/* Session cannot be etablished */
|
|
if (!$bolConnect) {
|
|
$this->strErrorMessage .= '[' . $database . '] ' .
|
|
gettext('Connection to the database has failed by reason:') . ' ::';
|
|
$this->strErrorMessage .= mysqli_error($this->strDBId) . '::';
|
|
$this->error = true;
|
|
$booReturn = false;
|
|
}
|
|
}
|
|
if ($booReturn === true) {
|
|
mysqli_query($this->strDBId, "set names 'utf8'");
|
|
if (mysqli_error($this->strDBId) !== '') {
|
|
$this->strErrorMessage .= mysqli_error($this->strDBId) . '::';
|
|
$this->error = true;
|
|
$booReturn = false;
|
|
}
|
|
}
|
|
if ($booReturn === true) {
|
|
mysqli_query($this->strDBId, "set session sql_mode = 'NO_ENGINE_SUBSTITUTION'");
|
|
if (mysqli_error($this->strDBId) !== '') {
|
|
$this->strErrorMessage .= mysqli_error($this->strDBId) . '::';
|
|
$this->error = true;
|
|
$booReturn = false;
|
|
}
|
|
}
|
|
return $booReturn;
|
|
}
|
|
|
|
/**
|
|
* Sends an SQL statement to the server and returns the result of the first data field
|
|
* @param string $strSQL SQL Statement
|
|
* @return string <data> = successful / <empty> = error
|
|
* Status messages are stored in class variable
|
|
*/
|
|
public function getFieldData(string $strSQL): string
|
|
{
|
|
/* Reset error variables */
|
|
$this->strErrorMessage = '';
|
|
$this->error = false;
|
|
$strReturn = '';
|
|
/* Send the SQL statement to the server */
|
|
$resQuery = mysqli_query($this->strDBId, $strSQL);
|
|
/* Error processing */
|
|
if ($resQuery && (mysqli_num_rows($resQuery) !== 0) && (mysqli_error($this->strDBId) === '')) {
|
|
/* Return the field value from position 0/0 */
|
|
$arrDataset = mysqli_fetch_array($resQuery, MYSQLI_NUM);
|
|
$strReturn = $arrDataset[0];
|
|
if ($strReturn === null) {
|
|
$strReturn = '';
|
|
}
|
|
} elseif (mysqli_error($this->strDBId) !== '') {
|
|
$this->strErrorMessage .= mysqli_error($this->strDBId) . '::';
|
|
$this->error = true;
|
|
}
|
|
return $strReturn;
|
|
}
|
|
|
|
/**
|
|
* Sends an SQL statement to the server and returns the result of the first data set
|
|
* @param string $strSQL SQL Statement
|
|
* @param array|null $arrDataset Result array (by reference)
|
|
* @return bool true = successful / false = error
|
|
* Status messages are stored in class variable
|
|
*/
|
|
public function hasSingleDataset(string $strSQL, ?array &$arrDataset): bool
|
|
{
|
|
$arrDataset = array();
|
|
$booReturn = true;
|
|
/* Reset error variables */
|
|
$this->strErrorMessage = '';
|
|
$this->error = false;
|
|
/* Send the SQL statement to the server */
|
|
$resQuery = mysqli_query($this->strDBId, $strSQL);
|
|
/* Error processing */
|
|
if ($resQuery && (mysqli_num_rows($resQuery) !== 0) && (mysqli_error($this->strDBId) === '')) {
|
|
/* Put the values into the array */
|
|
$arrDataset = mysqli_fetch_array($resQuery, MYSQLI_ASSOC);
|
|
} elseif (mysqli_error($this->strDBId) !== '') {
|
|
$this->strErrorMessage .= mysqli_error($this->strDBId) . '::';
|
|
$this->error = true;
|
|
$booReturn = false;
|
|
}
|
|
return $booReturn;
|
|
}
|
|
|
|
/**
|
|
* Sends an SQL statement to the server and returns the result of all dataset in a data array
|
|
* @param string $strSQL SQL Statement
|
|
* @param array|null $arrDataset Result array (by reference)
|
|
* @param int|null $intDataCount Number of data result sets
|
|
* @return bool true = successful / false = error
|
|
* Status messages are stored in class variable
|
|
*/
|
|
public function hasDataArray(string $strSQL, array &$arrDataset = null, int &$intDataCount = null): bool
|
|
{
|
|
$arrDataset = array();
|
|
$intDataCount = 0;
|
|
$booReturn = true;
|
|
/* Reset error variables */
|
|
$this->strErrorMessage = '';
|
|
$this->error = false;
|
|
/* Send the SQL statement to the server */
|
|
$resQuery = mysqli_query($this->strDBId, $strSQL);
|
|
/* Error processing */
|
|
if ($resQuery && (mysqli_num_rows($resQuery) !== 0) && (mysqli_error($this->strDBId) === '')) {
|
|
$intDataCount = (int)mysqli_num_rows($resQuery);
|
|
$intCount = 0;
|
|
/* Put the values into the array */
|
|
while ($arrDataTemp = mysqli_fetch_array($resQuery, MYSQLI_ASSOC)) {
|
|
foreach ($arrDataTemp as $key => $value) {
|
|
$arrDataset[$intCount][$key] = $value;
|
|
}
|
|
$intCount++;
|
|
}
|
|
} elseif (mysqli_error($this->strDBId) !== '') {
|
|
$this->strErrorMessage .= mysqli_error($this->strDBId) . '::';
|
|
$this->error = true;
|
|
$booReturn = false;
|
|
}
|
|
return $booReturn;
|
|
}
|
|
|
|
/**
|
|
* Insert/update or delete data
|
|
* @param string $strSQL SQL Statement
|
|
* @return bool true = successful / false = error
|
|
* Status messages are stored in class variable
|
|
*/
|
|
public function insertData(string $strSQL): bool
|
|
{
|
|
/* Reset error variables */
|
|
$this->strErrorMessage = '';
|
|
$this->error = false;
|
|
$booReturn = false;
|
|
/* Send the SQL statement to the server */
|
|
if ($strSQL !== '') {
|
|
mysqli_query($this->strDBId, $strSQL);
|
|
/* Error processing */
|
|
if (mysqli_error($this->strDBId) === '') {
|
|
$this->intLastId = mysqli_insert_id($this->strDBId);
|
|
$this->intAffectedRows = mysqli_affected_rows($this->strDBId);
|
|
$booReturn = true;
|
|
} else {
|
|
$this->strErrorMessage .= mysqli_error($this->strDBId) . '::';
|
|
$this->error = true;
|
|
}
|
|
}
|
|
return $booReturn;
|
|
}
|
|
|
|
/**
|
|
* Count the sum of data records
|
|
* @param string $strSQL SQL Statement
|
|
* @return int <number> = successful / 0 = no dataset or error
|
|
* Status messages are stored in class variable
|
|
*/
|
|
public function countRows(string $strSQL): int
|
|
{
|
|
/* Reset error variables */
|
|
$this->strErrorMessage = '';
|
|
$this->error = false;
|
|
$intReturn = 0;
|
|
/* Send the SQL statement to the server */
|
|
$resQuery = mysqli_query($this->strDBId, $strSQL);
|
|
/* Error processing */
|
|
if ($resQuery && (mysqli_error($this->strDBId) === '')) {
|
|
$intReturn = mysqli_num_rows($resQuery);
|
|
} else {
|
|
$this->strErrorMessage .= mysqli_error($this->strDBId);
|
|
$this->error = true;
|
|
}
|
|
return $intReturn;
|
|
}
|
|
|
|
/**
|
|
* Returns a safe insert string for database manipulations
|
|
* @param string $strInput Input String
|
|
* @return string Output String
|
|
*/
|
|
public function realEscape(string $strInput): string
|
|
{
|
|
return mysqli_real_escape_string($this->strDBId, $strInput);
|
|
}
|
|
|
|
/**
|
|
* Set SSL connection parameters
|
|
* @param string $sslkey SSL key
|
|
* @param string $sslcert SSL certificate
|
|
* @param string|null $sslca SSL CA file (optional)
|
|
* @param string|null $sslpath SSL certificate path (optional)
|
|
* @param string|null $sslcypher SSL cypher (optional)
|
|
* @return bool true = successful
|
|
* Status messages are stored in class variable
|
|
* @noinspection PhpSameParameterValueInspection
|
|
* @noinspection PhpUnusedPrivateMethodInspection
|
|
*/
|
|
private function dbsetssl(string $sslkey, string $sslcert, string $sslca = null, string $sslpath = null, string $sslcypher = null): bool
|
|
{
|
|
/* Reset error variables */
|
|
$this->strErrorMessage = "";
|
|
$this->error = false;
|
|
$booReturn = true;
|
|
/* Values are missing */
|
|
if (($sslkey === "") || ($sslcert === "")) {
|
|
$this->strErrorMessage = gettext("Missing MySQL SSL parameter!") . "::";
|
|
$this->error = true;
|
|
$booReturn = false;
|
|
}
|
|
if ($booReturn === true) {
|
|
mysqli_ssl_set($this->strDBId, $sslkey, $sslcert, $sslca, $sslpath, $sslcypher);
|
|
}
|
|
return ($booReturn);
|
|
}
|
|
} |