db_mysql.inc.php

<?php
/*
 * db_mysql.inc.php:  ISPConfig mysql db interface
 *
 * Note!  When making changes to this file, put a copy in both locations:
 *   interface/lib/classes/db_mysql.inc.php
 *      server/lib/classes/db_mysql.inc.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 $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 $dbClientFlags = 0; // MySQL Client falgs
	/**#@-*/

	public $show_error_messages = false; // false in server, interface sets true when generating templates

	/* 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) {
		global $app, $conf;

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

		mysqli_report(MYSQLI_REPORT_OFF);

		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();
			}
			if(!mysqli_real_connect($this->_iConnId, $this->dbHost, $this->dbUser, $this->dbPass, '', (int)$this->dbPort, NULL, $this->dbClientFlags)) {
				$this->_sqlerror('Database connection failed');
			}
		}

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

		$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;
	}

	/*
	 * Test mysql connection.
	 *
	 * @return boolean returns true if db connection is good.
	 */
	public function testConnection() {
		if(mysqli_connect_errno()) {
			return false;
		}
		return (boolean)(is_object($this->_iConnId) && mysqli_ping($this->_iConnId));
	}

	/* 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);

					$sTxt = str_replace('`', '', $sTxt);
					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)) {
						$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() {