413 lines
16 KiB
PHP
413 lines
16 KiB
PHP
|
<?php
|
||
|
|
///////////////////////////////////////////////////////////////////////////////
|
||
|
|
//
|
||
|
|
// Common utilities
|
||
|
|
//
|
||
|
|
///////////////////////////////////////////////////////////////////////////////
|
||
|
|
//
|
||
|
|
// (c) 2005-2018 by Martin Willisegger
|
||
|
|
//
|
||
|
|
// Project : Common scripts
|
||
|
|
// Component : MySQLi data processing class
|
||
|
|
// Website : https://sourceforge.net/projects/nagiosql/
|
||
|
|
// Version : 3.4.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();
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 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($intMode = 0)
|
||
|
|
{
|
||
|
|
$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;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 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($strSQL)
|
||
|
|
{
|
||
|
|
// 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];
|
||
|
|
} 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($strSQL, &$arrDataset)
|
||
|
|
{
|
||
|
|
//$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 $arrDataset Result array (by reference)
|
||
|
|
* @param int $intDataCount Number of data result sets
|
||
|
|
* @return bool true = successful / false = error
|
||
|
|
* Status messages are stored in class variable
|
||
|
|
*/
|
||
|
|
public function hasDataArray($strSQL, &$arrDataset, &$intDataCount)
|
||
|
|
{
|
||
|
|
$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 = 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($strSQL)
|
||
|
|
{
|
||
|
|
// 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($strSQL)
|
||
|
|
{
|
||
|
|
// 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($strInput)
|
||
|
|
{
|
||
|
|
return mysqli_real_escape_string($this->strDBId, $strInput);
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Initialize a mysql database connection
|
||
|
|
* @return bool true = successful / false = error
|
||
|
|
*/
|
||
|
|
private function dbinit()
|
||
|
|
{
|
||
|
|
$this->strDBId = mysqli_init();
|
||
|
|
return true;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 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 bool true = successful / false = error
|
||
|
|
* Status messages are stored in class variable
|
||
|
|
*/
|
||
|
|
private function dbconnect($dbserver = null, $dbport = null, $dbuser = null, $dbpasswd = null)
|
||
|
|
{
|
||
|
|
// Reset error variables
|
||
|
|
$this->strErrorMessage = '';
|
||
|
|
$this->error = false;
|
||
|
|
$booReturn = true;
|
||
|
|
// Get parameters
|
||
|
|
if ($dbserver == null) {
|
||
|
|
$dbserver = $this->arrParams['server'];
|
||
|
|
}
|
||
|
|
if ($dbport == null) {
|
||
|
|
$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) {
|
||
|
|
// 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;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return $booReturn;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Select a database
|
||
|
|
* @param string $database Database name
|
||
|
|
* @return bool true = successful / false = error
|
||
|
|
* Status messages are stored in class variable
|
||
|
|
*/
|
||
|
|
private function dbselect($database = null)
|
||
|
|
{
|
||
|
|
// 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) {
|
||
|
|
$bolConnect = mysqli_select_db($this->strDBId, $database);
|
||
|
|
// 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;
|
||
|
|
}
|
||
|
|
|
||
|
|
/*
|
||
|
|
/**
|
||
|
|
* Set SSL connection parameters
|
||
|
|
* @param string $sslkey SSL key
|
||
|
|
* @param string $sslcert SSL certificate
|
||
|
|
* @param string $sslca SSL CA file (optional)
|
||
|
|
* @param string $sslpath SSL certificate path (optional)
|
||
|
|
* @param string $sslcypher SSL cypher (optional)
|
||
|
|
* @return bool true = successful
|
||
|
|
* Status messages are stored in class variable
|
||
|
|
*/
|
||
|
|
/*
|
||
|
|
private function dbsetssl($sslkey, $sslcert, $sslca = null, $sslpath = null, $sslcypher = null)
|
||
|
|
{
|
||
|
|
// 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);
|
||
|
|
}
|
||
|
|
*/
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Close database server connectuon
|
||
|
|
* @return bool true = successful / false = error
|
||
|
|
*/
|
||
|
|
private function dbDisconnect()
|
||
|
|
{
|
||
|
|
mysqli_close($this->strDBId);
|
||
|
|
return true;
|
||
|
|
}
|
||
|
|
}
|