1<?php 2/////////////////////////////////////////////////////////////////////////////// 3// 4// Common utilities 5// 6/////////////////////////////////////////////////////////////////////////////// 7// 8// (c) 2005-2020 by Martin Willisegger 9// 10// Project : Common scripts 11// Component : MySQLi data processing class 12// Website : https://sourceforge.net/projects/nagiosql/ 13// Version : 3.4.1 14// GIT Repo : https://gitlab.com/wizonet/NagiosQL 15// 16/////////////////////////////////////////////////////////////////////////////////////////////// 17// 18/////////////////////////////////////////////////////////////////////////////////////////////// 19// 20// Class: Common database functions for MySQL (mysqli database module) 21// 22/////////////////////////////////////////////////////////////////////////////////////////////// 23// 24// Includes any functions to communicate with an MySQL database server 25// 26// Name: MysqliDbClass 27// 28// Class variables: $arrParams Array including the server settings 29// ---------------- $strErrorMessage Database error string 30// $error Boolean - Error true/false 31// $strDBId Database connection id 32// $intLastId ID of last dataset 33// $intAffectedRows Counter variable of all affected data dows 34// $booSSLuse Use SSL connection 35// 36// Parameters: $arrParams['server'] -> DB server name 37// ----------- $arrParams['port'] -> DB server port 38// $arrParams['user'] -> DB server username 39// $arrParams['password'] -> DB server password 40// $arrParams['database'] -> DB server database name 41// 42/////////////////////////////////////////////////////////////////////////////////////////////// 43namespace functions; 44 45class MysqliDbClass 46{ 47 // Define class variables 48 public $error = false; // Will be filled in functions 49 public $strDBId; // Will be filled in functions 50 public $intLastId = 0; // Will be filled in functions 51 public $intAffectedRows = 0; // Will be filled in functions 52 public $strErrorMessage = ''; // Will be filled in functions 53 public $booSSLuse = false; // Defines if SSL is used or not 54 public $arrParams = array(); // Must be filled in while initialization 55 56 /** 57 * MysqliDbClass constructor. 58 */ 59 public function __construct() 60 { 61 $this->arrParams['server'] = ''; 62 $this->arrParams['port'] = 0; 63 $this->arrParams['username'] = ''; 64 $this->arrParams['password'] = ''; 65 $this->arrParams['database'] = ''; 66 } 67 68 /** 69 * MysqliDbClass destructor. 70 */ 71 public function __destruct() 72 { 73 $this->dbDisconnect(); 74 } 75 76 /** 77 * Opens a connection to the database server and select a database 78 * @param int $intMode 1 = connect only / 0 = connect + dbselect 79 * @return bool true = successful / false = error 80 * Status messages are stored in class variable 81 */ 82 public function hasDBConnection($intMode = 0) 83 { 84 $booReturn = true; 85 $this->dbconnect(); 86 if ($this->error == true) { 87 $booReturn = false; 88 } 89 if (($booReturn == true) && ($intMode == 0)) { 90 $this->dbselect(); 91 if ($this->error == true) { 92 $booReturn = false; 93 } 94 } 95 return $booReturn; 96 } 97 98 /** 99 * Sends an SQL statement to the server and returns the result of the first data field 100 * @param string $strSQL SQL Statement 101 * @return string <data> = successful / <empty> = error 102 * Status messages are stored in class variable 103 */ 104 public function getFieldData($strSQL) 105 { 106 // Reset error variables 107 $this->strErrorMessage = ''; 108 $this->error = false; 109 $strReturn = ''; 110 // Send the SQL statement to the server 111 $resQuery = mysqli_query($this->strDBId, $strSQL); 112 // Error processing 113 if ($resQuery && (mysqli_num_rows($resQuery) != 0) && (mysqli_error($this->strDBId) == '')) { 114 // Return the field value from position 0/0 115 $arrDataset = mysqli_fetch_array($resQuery, MYSQLI_NUM); 116 $strReturn = $arrDataset[0]; 117 } elseif (mysqli_error($this->strDBId) != '') { 118 $this->strErrorMessage .= mysqli_error($this->strDBId). '::'; 119 $this->error = true; 120 } 121 return $strReturn; 122 } 123 124 /** 125 * Sends an SQL statement to the server and returns the result of the first data set 126 * @param string $strSQL SQL Statement 127 * @param array|null $arrDataset Result array (by reference) 128 * @return bool true = successful / false = error 129 * Status messages are stored in class variable 130 */ 131 public function hasSingleDataset($strSQL, &$arrDataset) 132 { 133 //$arrDataset = array(); 134 $booReturn = true; 135 // Reset error variables 136 $this->strErrorMessage = ''; 137 $this->error = false; 138 // Send the SQL statement to the server 139 $resQuery = mysqli_query($this->strDBId, $strSQL); 140 // Error processing 141 if ($resQuery && (mysqli_num_rows($resQuery) != 0) && (mysqli_error($this->strDBId) == '')) { 142 // Put the values into the array 143 $arrDataset = mysqli_fetch_array($resQuery, MYSQLI_ASSOC); 144 } elseif (mysqli_error($this->strDBId) != '') { 145 $this->strErrorMessage .= mysqli_error($this->strDBId). '::'; 146 $this->error = true; 147 $booReturn = false; 148 } 149 return $booReturn; 150 } 151 152 /** 153 * Sends an SQL statement to the server and returns the result of all dataset in a data array 154 * @param string $strSQL SQL Statement 155 * @param array $arrDataset Result array (by reference) 156 * @param int $intDataCount Number of data result sets 157 * @return bool true = successful / false = error 158 * Status messages are stored in class variable 159 */ 160 public function hasDataArray($strSQL, &$arrDataset, &$intDataCount) 161 { 162 $arrDataset = array(); 163 $intDataCount = 0; 164 $booReturn = true; 165 // Reset error variables 166 $this->strErrorMessage = ''; 167 $this->error = false; 168 // Send the SQL statement to the server 169 $resQuery = mysqli_query($this->strDBId, $strSQL); 170 // Error processing 171 if ($resQuery && (mysqli_num_rows($resQuery) != 0) && (mysqli_error($this->strDBId) == '')) { 172 $intDataCount = mysqli_num_rows($resQuery); 173 $intCount = 0; 174 // Put the values into the array 175 while ($arrDataTemp = mysqli_fetch_array($resQuery, MYSQLI_ASSOC)) { 176 foreach ($arrDataTemp as $key => $value) { 177 $arrDataset[$intCount][$key] = $value; 178 } 179 $intCount++; 180 } 181 } elseif (mysqli_error($this->strDBId) != '') { 182 $this->strErrorMessage .= mysqli_error($this->strDBId). '::'; 183 $this->error = true; 184 $booReturn = false; 185 } 186 return $booReturn; 187 } 188 189 /** 190 * Insert/update or delete data 191 * @param string $strSQL SQL Statement 192 * @return bool true = successful / false = error 193 * Status messages are stored in class variable 194 */ 195 public function insertData($strSQL) 196 { 197 // Reset error variables 198 $this->strErrorMessage = ''; 199 $this->error = false; 200 $booReturn = false; 201 // Send the SQL statement to the server 202 if ($strSQL != '') { 203 mysqli_query($this->strDBId, $strSQL); 204 // Error processing 205 if (mysqli_error($this->strDBId) == '') { 206 $this->intLastId = mysqli_insert_id($this->strDBId); 207 $this->intAffectedRows = mysqli_affected_rows($this->strDBId); 208 $booReturn = true; 209 } else { 210 $this->strErrorMessage .= mysqli_error($this->strDBId) . '::'; 211 $this->error = true; 212 } 213 } 214 return $booReturn; 215 } 216 217 /** 218 * Count the sum of data records 219 * @param string $strSQL SQL Statement 220 * @return int <number> = successful / 0 = no dataset or error 221 * Status messages are stored in class variable 222 */ 223 public function countRows($strSQL) 224 { 225 // Reset error variables 226 $this->strErrorMessage = ''; 227 $this->error = false; 228 $intReturn = 0; 229 // Send the SQL statement to the server 230 $resQuery = mysqli_query($this->strDBId, $strSQL); 231 // Error processing 232 if ($resQuery && (mysqli_error($this->strDBId) == '')) { 233 $intReturn = mysqli_num_rows($resQuery); 234 } else { 235 $this->strErrorMessage .= mysqli_error($this->strDBId); 236 $this->error = true; 237 } 238 return $intReturn; 239 } 240 241 /** 242 * Returns a safe insert string for database manipulations 243 * @param string $strInput Input String 244 * @return string Output String 245 */ 246 public function realEscape($strInput) 247 { 248 return mysqli_real_escape_string($this->strDBId, $strInput); 249 } 250 251 /** 252 * Initialize a mysql database connection 253 * @return bool true = successful / false = error 254 */ 255 private function dbinit() 256 { 257 $this->strDBId = mysqli_init(); 258 return true; 259 } 260 261 /** 262 * Connect to database server 263 * @param string $dbserver Server name 264 * @param int $dbport TCP port 265 * @param string $dbuser Database user 266 * @param string $dbpasswd Database password 267 * @return bool true = successful / false = error 268 * Status messages are stored in class variable 269 */ 270 private function dbconnect($dbserver = null, $dbport = null, $dbuser = null, $dbpasswd = null) 271 { 272 // Reset error variables 273 $this->strErrorMessage = ''; 274 $this->error = false; 275 $booReturn = true; 276 // Get parameters 277 if ($dbserver == null) { 278 $dbserver = $this->arrParams['server']; 279 } 280 if ($dbport == null) { 281 $dbport = $this->arrParams['port']; 282 } 283 if ($dbuser == null) { 284 $dbuser = $this->arrParams['username']; 285 } 286 if ($dbpasswd == null) { 287 $dbpasswd = $this->arrParams['password']; 288 } 289 // Not all parameters available 290 if (($dbserver == '') || ($dbuser == '') || ($dbpasswd == '')) { 291 $this->strErrorMessage .= gettext('Missing server connection parameter!'). '::'; 292 $this->error = true; 293 $booReturn = false; 294 } 295 if ($booReturn == true) { 296 $this->dbinit(); 297 //if ($this->booSSLuse == true) { 298 // TO BE DEFINED 299 //} 300 $intErrorReporting = error_reporting(); 301 error_reporting(0); 302 if ($dbport == 0) { 303 $booReturn = mysqli_real_connect($this->strDBId, $dbserver, $dbuser, $dbpasswd); 304 } else { 305 $booReturn = mysqli_real_connect($this->strDBId, $dbserver, $dbuser, $dbpasswd, null, $dbport); 306 } 307 error_reporting($intErrorReporting); 308 // Connection fails 309 if ($booReturn == false) { 310 $this->strErrorMessage = '[' .$dbserver. '] ' .gettext('Connection to the database server has failed ' 311 . 'by reason:'). ' ::'; 312 $strError = mysqli_connect_error(); 313 $this->strErrorMessage .= $strError. '::'; 314 $this->error = true; 315 } 316 } 317 return $booReturn; 318 } 319 320 /** 321 * Select a database 322 * @param string $database Database name 323 * @return bool true = successful / false = error 324 * Status messages are stored in class variable 325 */ 326 private function dbselect($database = null) 327 { 328 // Reset error variables 329 $this->strErrorMessage = ''; 330 $this->error = false; 331 $booReturn = true; 332 // Get parameters 333 if ($database == null) { 334 $database = $this->arrParams['database']; 335 } 336 // Not all parameters available 337 if ($database == '') { 338 $this->strErrorMessage .= gettext('Missing database connection parameter!'). '::'; 339 $this->error = true; 340 $booReturn = false; 341 } 342 if ($booReturn == true) { 343 $bolConnect = mysqli_select_db($this->strDBId, $database); 344 // Session cannot be etablished 345 if (!$bolConnect) { 346 $this->strErrorMessage .= '[' .$database. '] ' . 347 gettext('Connection to the database has failed by reason:'). ' ::'; 348 $this->strErrorMessage .= mysqli_error($this->strDBId). '::'; 349 $this->error = true; 350 $booReturn = false; 351 } 352 } 353 if ($booReturn == true) { 354 mysqli_query($this->strDBId, "set names 'utf8'"); 355 if (mysqli_error($this->strDBId) != '') { 356 $this->strErrorMessage .= mysqli_error($this->strDBId). '::'; 357 $this->error = true; 358 $booReturn = false; 359 } 360 } 361 if ($booReturn == true) { 362 mysqli_query($this->strDBId, "set session sql_mode = 'NO_ENGINE_SUBSTITUTION'"); 363 if (mysqli_error($this->strDBId) != '') { 364 $this->strErrorMessage .= mysqli_error($this->strDBId). '::'; 365 $this->error = true; 366 $booReturn = false; 367 } 368 } 369 return $booReturn; 370 } 371 372 /* 373 /** 374 * Set SSL connection parameters 375 * @param string $sslkey SSL key 376 * @param string $sslcert SSL certificate 377 * @param string $sslca SSL CA file (optional) 378 * @param string $sslpath SSL certificate path (optional) 379 * @param string $sslcypher SSL cypher (optional) 380 * @return bool true = successful 381 * Status messages are stored in class variable 382 */ 383 /* 384 private function dbsetssl($sslkey, $sslcert, $sslca = null, $sslpath = null, $sslcypher = null) 385 { 386 // Reset error variables 387 $this->strErrorMessage = ""; 388 $this->error = false; 389 $booReturn = true; 390 // Values are missing 391 if (($sslkey == "") || ($sslcert == "")) { 392 $this->strErrorMessage = gettext("Missing MySQL SSL parameter!")."::"; 393 $this->error = true; 394 $booReturn = false; 395 } 396 if ($booReturn == true) { 397 mysqli_ssl_set($this->strDBId, $sslkey, $sslcert, $sslca, $sslpath, $sslcypher); 398 } 399 return($booReturn); 400 } 401 */ 402 403 /** 404 * Close database server connectuon 405 * @return bool true = successful / false = error 406 */ 407 private function dbDisconnect() 408 { 409 mysqli_close($this->strDBId); 410 return true; 411 } 412} 413