db_mysql.inc.php

<?php
/*
   Copyright (c) 2005, Till Brehm, projektfarm Gmbh
   All rights reserved.

   Redistribution and use in source and binary forms, with or without modification,
   are permitted provided that the following conditions are met:

 * Redistributions of source code must retain the above copyright notice,
 this list of conditions and the following disclaimer.
 * Redistributions in binary form must reproduce the above copyright notice,
 this list of conditions and the following disclaimer in the documentation
 and/or other materials provided with the distribution.
 * Neither the name of ISPConfig nor the names of its contributors
 may be used to endorse or promote products derived from this software without
 specific prior written permission.

 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
 ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
 IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
 INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
 BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
 OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
 NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
 EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

class db
{
	/**#@+
     * @access private
     */
	private $_iQueryId;
	private $_iConnId;

	private $_Prefix = ''; // config variable name prefix

	private $dbHost = '';  // hostname of the MySQL server
	private $dbPort = '';  // port of the MySQL server
	private $dbName = '';  // logical database name on that server
	private $dbUser = '';  // database authorized user
	private $dbPass = '';  // user's password
	private $dbCharset = 'utf8';// Database charset
	private $dbNewLink = false; // Return a new linkID when connect is called again
	private $dbClientFlags = 0; // MySQL Client falgs
	/**#@-*/

	public $show_error_messages = false; // false in server, true in interface


	/* old things - unused now ////
	private $linkId = 0;  // last result of mysqli_connect()
	private $queryId = 0;  // last result of mysqli_query()
	private $record = array(); // last record fetched
	private $autoCommit = 1;    // Autocommit Transactions
	private $currentRow;  // current row number
	public $errorNumber = 0; // last error number
	*/
	public $errorMessage = ''; // last error message
	/*
	private $errorLocation = '';// last error location
	private $isConnected = false; // needed to know if we have a valid mysqli object from the constructor
	////
	*/

	// constructor
	public function __construct($host = NULL , $user = NULL, $pass = NULL, $database = NULL, $port = NULL, $flags = NULL, $confPrefix = '') {
		global $app, $conf;

		if($confPrefix != '') $this->_Prefix = $confPrefix . '_';
		$this->dbHost = $host ? $host  : $conf[$this->_Prefix.'db_host'];
		$this->dbPort = $port ? $port : $conf[$this->_Prefix.'db_port'];
		$this->dbName = $database ? $database : $conf[$this->_Prefix.'db_database'];
		$this->dbUser = $user ? $user : $conf[$this->_Prefix.'db_user'];
		$this->dbPass = $pass ? $pass : $conf[$this->_Prefix.'db_password'];
		$this->dbCharset = $conf[$this->_Prefix.'db_charset'];
		$this->dbNewLink = $conf[$this->_Prefix.'db_new_link'];
		$this->dbClientFlags = $flags ? $flags : $conf[$this->_Prefix.'db_client_flags'];
		$this->_iConnId = mysqli_init();

		mysqli_real_connect($this->_iConnId, $this->dbHost, $this->dbUser, $this->dbPass, '', (int)$this->dbPort, NULL, $this->dbClientFlags);
		for($try=0;(!is_object($this->_iConnId) || mysqli_connect_errno()) && $try < 5;++$try) {
			sleep($try);
			if(!is_object($this->_iConnId)) {
				$this->_iConnId = mysqli_init();
			}
			mysqli_real_connect($this->_iConnId, $this->dbHost, $this->dbUser, $this->dbPass, '', (int)$this->dbPort, NULL, $this->dbClientFlags);
		}

		if(!is_object($this->_iConnId) || mysqli_connect_errno()) {
			$this->_iConnId = null;
			$this->_sqlerror('Zugriff auf Datenbankserver fehlgeschlagen! / Database server not accessible!', '', true);
			return false;
		}
		if(!((bool)mysqli_query( $this->_iConnId, 'USE `' . $this->dbName . '`'))) {
			$this->close();
			$this->_sqlerror('Datenbank nicht gefunden / Database not found', '', true);
			return false;
		}

		$this->_setCharset();
	}

	public function __destruct() {
		if($this->_iConnId) mysqli_close($this->_iConnId);
	}

	public function close() {
		if($this->_iConnId) mysqli_close($this->_iConnId);
		$this->_iConnId = null;
	}

	/* This allows our private variables to be "read" out side of the class */
	public function __get($var) {
		return isset($this->$var) ? $this->$var : NULL;
	}

	public function _build_query_string($sQuery = '') {
		$iArgs = func_num_args();
		if($iArgs > 1) {
			$aArgs = func_get_args();

			if($iArgs == 3 && $aArgs[1] === true && is_array($aArgs[2])) {
				$aArgs = $aArgs[2];
				$iArgs = count($aArgs);
			} else {
				array_shift($aArgs); // delete the query string that is the first arg!
			}

			$iPos = 0;
			$iPos2 = 0;
			foreach($aArgs as $sKey => $sValue) {
				$iPos2 = strpos($sQuery, '??', $iPos2);
				$iPos = strpos($sQuery, '?', $iPos);

				if($iPos === false && $iPos2 === false) break;

				if($iPos2 !== false && ($iPos === false || $iPos2 <= $iPos)) {
					$sTxt = $this->escape($sValue);

					if(strpos($sTxt, '.') !== false) {
						$sTxt = preg_replace('/^(.+)\.(.+)$/', '`$1`.`$2`', $sTxt);
						$sTxt = str_replace('.`*`', '.*', $sTxt);
					} else $sTxt = '`' . $sTxt . '`';

					$sQuery = substr_replace($sQuery, $sTxt, $iPos2, 2);
					$iPos2 += strlen($sTxt);
					$iPos = $iPos2;
				} else {
					if(is_int($sValue) || is_float($sValue)) {
						$sTxt = $sValue;
					} elseif(is_null($sValue) || (is_string($sValue) && (strcmp($sValue, '#NULL#') == 0))) {
						$sTxt = 'NULL';
					} elseif(is_array($sValue)) {
						if(isset($sValue['SQL'])) {
							$sTxt = $sValue['SQL'];
						} else {
							$sTxt = '';
							foreach($sValue as $sVal) $sTxt .= ',\'' . $this->escape($sVal) . '\'';
							$sTxt = '(' . substr($sTxt, 1) . ')';
							if($sTxt == '()') $sTxt = '(0)';
						}
					} else {
						$sTxt = '\'' . $this->escape($sValue) . '\'';
					}

					$sQuery = substr_replace($sQuery, $sTxt, $iPos, 1);
					$iPos += strlen($sTxt);
					$iPos2 = $iPos;
				}
			}
		}

		return $sQuery;
	}

	/**#@-*/


	/**#@+
     * @access private
     */
	private function _setCharset() {
		mysqli_query($this->_iConnId, 'SET NAMES '.$this->dbCharset);
		mysqli_query($this->_iConnId, "SET character_set_results = '".$this->dbCharset."', character_set_client = '".$this->dbCharset."', character_set_connection = '".$this->dbCharset."', character_set_database = '".$this->dbCharset."', character_set_server = '".$this->dbCharset."'");
	}

	private function securityScan($string) {
		global $app, $conf;

		// get security config
		if(isset($app)) {
			$app->uses('getconf');
			$ids_config = $app->getconf->get_security_config('ids');

			if($ids_config['sql_scan_enabled'] == 'yes') {

				// Remove whitespace