gitlabFork/ISPConfig3.git - Solinfo Gitblit

Merge remote-tracking branch 'origin/stable-3.0.5'

Marius Cramer

2014-10-31 1102cc886cf42a23cb3145cd9cc8b0d8f6123b61

commit \| author \| age
b5a2f8	1	<?php
T	2	/*
f5b0ca	3	Copyright (c) 2005, Till Brehm, projektfarm Gmbh
N	4	All rights reserved.
b5a2f8	5
f5b0ca	6	Redistribution and use in source and binary forms, with or without modification,
N	7	are permitted provided that the following conditions are met:
b5a2f8	8
f5b0ca	9	* Redistributions of source code must retain the above copyright notice,
N	10	this list of conditions and the following disclaimer.
	11	* Redistributions in binary form must reproduce the above copyright notice,
	12	this list of conditions and the following disclaimer in the documentation
	13	and/or other materials provided with the distribution.
	14	* Neither the name of ISPConfig nor the names of its contributors
	15	may be used to endorse or promote products derived from this software without
	16	specific prior written permission.
b5a2f8	17
f5b0ca	18	THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
N	19	ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
	20	WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
	21	IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
	22	INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
	23	BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
	24	DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
	25	OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
	26	NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
	27	EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
	28	*/
b5a2f8	29
f5b0ca	30	class db extends mysqli
N	31	{
cd8bb8	32	/**#@+
MC	33	* @access private
	34	*/
	35	private $_iQueryId;
	36	private $_iConnId;
	37
7fe908	38	private $dbHost = ''; // hostname of the MySQL server
MC	39	private $dbName = ''; // logical database name on that server
	40	private $dbUser = ''; // database authorized user
	41	private $dbPass = ''; // user's password
	42	private $dbCharset = 'utf8';// Database charset
	43	private $dbNewLink = false; // Return a new linkID when connect is called again
	44	private $dbClientFlags = 0; // MySQL Client falgs
cd8bb8	45	/*#@-/
MC	46
	47	public $show_error_messages = false; // false in server, true in interface
	48
	49
	50	/* old things - unused now ////
7fe908	51	private $linkId = 0; // last result of mysqli_connect()
MC	52	private $queryId = 0; // last result of mysqli_query()
	53	private $record = array(); // last record fetched
	54	private $autoCommit = 1; // Autocommit Transactions
	55	private $currentRow; // current row number
	56	private $errorNumber = 0; // last error number
	57	public $errorMessage = ''; // last error message
	58	private $errorLocation = '';// last error location
	59	private $isConnected = false; // needed to know if we have a valid mysqli object from the constructor
cd8bb8	60	////
MC	61	*/
b5a2f8	62
7fe908	63	// constructor
MC	64	public function __construct($prefix = '') {
	65	global $conf;
	66	if($prefix != '') $prefix .= '_';
	67	$this->dbHost = $conf[$prefix.'db_host'];
	68	$this->dbName = $conf[$prefix.'db_database'];
	69	$this->dbUser = $conf[$prefix.'db_user'];
	70	$this->dbPass = $conf[$prefix.'db_password'];
	71	$this->dbCharset = $conf[$prefix.'db_charset'];
	72	$this->dbNewLink = $conf[$prefix.'db_new_link'];
	73	$this->dbClientFlags = $conf[$prefix.'db_client_flags'];
cd8bb8	74
MC	75	$this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass);
7fe908	76	$try = 0;
cd8bb8	77	while((!is_object($this->_iConnId) \|\| mysqli_connect_error()) && $try < 5) {
7fe908	78	if($try > 0) sleep(1);
f5b0ca	79
7fe908	80	$try++;
cd8bb8	81	$this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass);
7fe908	82	}
f5b0ca	83
cd8bb8	84	if(!is_object($this->_iConnId) \|\| mysqli_connect_error()) {
MC	85	$this->_iConnId = null;
	86	$this->_sqlerror('Zugriff auf Datenbankserver fehlgeschlagen! / Database server not accessible!');
	87	return false;
	88	}
	89	if(!((bool)mysqli_query( $this->_iConnId, "USE $this->dbName"))) {
	90	$this->close();
	91	$this->_sqlerror('Datenbank nicht gefunden / Database not found');
	92	return false;
	93	}
7fe908	94
cd8bb8	95	$this->_setCharset();
7fe908	96	}
MC	97
	98	public function __destruct() {
cd8bb8	99	if($this->_iConnId) mysqli_close($this->_iConnId);
7fe908	100	}
MC	101
cd8bb8	102	public function close() {
MC	103	if($this->_iConnId) mysqli_close($this->_iConnId);
	104	$this->_iConnId = null;
	105	}
7fe908	106
cd8bb8	107	public function _build_query_string($sQuery = '') {
MC	108	$iArgs = func_num_args();
	109	if($iArgs > 1) {
	110	$aArgs = func_get_args();
cc6568	111
cd8bb8	112	if($iArgs == 3 && $aArgs[1] === true && is_array($aArgs[2])) {
MC	113	$aArgs = $aArgs[2];
	114	$iArgs = count($aArgs);
	115	} else {
	116	array_shift($aArgs); // delete the query string that is the first arg!
	117	}
f5b0ca	118
cd8bb8	119	$iPos = 0;
MC	120	$iPos2 = 0;
	121	foreach($aArgs as $sKey => $sValue) {
	122	$iPos2 = strpos($sQuery, '??', $iPos2);
	123	$iPos = strpos($sQuery, '?', $iPos);
	124
	125	if($iPos === false && $iPos2 === false) break;
	126
	127	if($iPos2 !== false && ($iPos === false \|\| $iPos2 <= $iPos)) {
	128	$sTxt = $this->escape($sValue);
0e41de	129
MC	130	$sTxt = str_replace('`', '', $sTxt);
cd8bb8	131	if(strpos($sTxt, '.') !== false) $sTxt = preg_replace('/^(.+)\.(.+)$/', '`$1`.`$2`', $sTxt);
MC	132	else $sTxt = '`' . $sTxt . '`';
	133
	134	$sQuery = substr_replace($sQuery, $sTxt, $iPos2, 2);
	135	$iPos2 += strlen($sTxt);
	136	$iPos = $iPos2;
	137	} else {
	138	if(is_int($sValue) \|\| is_float($sValue)) {
	139	$sTxt = $sValue;
	140	} elseif(is_string($sValue) && (strcmp($sValue, '#NULL#') == 0)) {
	141	$sTxt = 'NULL';
	142	} elseif(is_array($sValue)) {
	143	$sTxt = '';
	144	foreach($sValue as $sVal) $sTxt .= ',\'' . $this->escape($sVal) . '\'';
	145	$sTxt = '(' . substr($sTxt, 1) . ')';
	146	if($sTxt == '()') $sTxt = '(0)';
	147	} else {
	148	$sTxt = '\'' . $this->escape($sValue) . '\'';
	149	}
	150
	151	$sQuery = substr_replace($sQuery, $sTxt, $iPos, 1);
	152	$iPos += strlen($sTxt);
	153	$iPos2 = $iPos;
7fe908	154	}
cd8bb8	155	}
7fe908	156	}
cd8bb8	157
MC	158	return $sQuery;
f5b0ca	159	}
7fe908	160
cd8bb8	161	/*#@-/
MC	162
	163
	164	/**#@+
	165	* @access private
	166	*/
	167	private function _setCharset() {
	168	mysqli_query($this->_iConnId, 'SET NAMES '.$this->dbCharset);
	169	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."'");
f5b0ca	170	}
cb1221	171
TB	172	private function securityScan($string) {
	173	global $app, $conf;
	174
	175	// get security config
	176	if(isset($app)) {
	177	$app->uses('getconf');
	178	$ids_config = $app->getconf->get_security_config('ids');
	179
	180	if($ids_config['sql_scan_enabled'] == 'yes') {
	181
0e34ba	182	// Remove whitespace
TB	183	$string = trim($string);
	184	if(substr($string,-1) == ';') $string = substr($string,0,-1);
	185
	186	// Save original string
cb1221	187	$string_orig = $string;
TB	188
	189	//echo $string;
68b146	190	$chars = array(';', '#', '/', '/', '--', '\\\'', '\\"');
cb1221	191
TB	192	$string = str_replace('\\\\', '', $string);
f986a3	193	$string = preg_replace('/(^\|[^\\\])([\'"])\\2/is', '$1', $string);
MC	194	$string = preg_replace('/(^\|[^\\\])([\'"])(.*?[^\\\])\\2/is', '$1', $string);
cb1221	195	$ok = true;
TB	196
	197	if(substr_count($string, "`") % 2 != 0 \|\| substr_count($string, "'") % 2 != 0 \|\| substr_count($string, '"') % 2 != 0) {
	198	$app->log("SQL injection warning (" . $string_orig . ")",2);
	199	$ok = false;
	200	} else {
	201	foreach($chars as $char) {
	202	if(strpos($string, $char) !== false) {
	203	$ok = false;
	204	$app->log("SQL injection warning (" . $string_orig . ")",2);
	205	break;
	206	}
	207	}
	208	}
	209	if($ok == true) {
	210	return true;
	211	} else {
	212	if($ids_config['sql_scan_action'] == 'warn') {
	213	// we return false in warning level.
	214	return false;
	215	} else {
	216	// if sql action = 'block' or anything else then stop here.
	217	$app->error('Possible SQL injection. All actions have been logged.');
	218	}
	219	}
	220	}
	221	}
	222	}
f5b0ca	223
cd8bb8	224	private function _query($sQuery = '') {
MC	225	global $app;
	226
	227	if ($sQuery == '') {
	228	$this->_sqlerror('Keine Anfrage angegeben / No query given');
	229	return false;
	230	}
	231
7fe908	232	$try = 0;
MC	233	do {
	234	$try++;
cd8bb8	235	$ok = mysqli_ping($this->_iConnId);
7fe908	236	if(!$ok) {
cd8bb8	237	if(!mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, $this->dbName)) {
7fe908	238	if($try > 4) {
cd8bb8	239	$this->_sqlerror('DB::query -> reconnect');
7fe908	240	return false;
MC	241	} else {
	242	sleep(1);
	243	}
	244	} else {
cd8bb8	245	$this->_setCharset();
7fe908	246	$ok = true;
MC	247	}
	248	}
	249	} while($ok == false);
cd8bb8	250
MC	251	$aArgs = func_get_args();
	252	$sQuery = call_user_func_array(array(&$this, '_build_query_string'), $aArgs);
797215	253	$this->securityScan($sQuery);
cd8bb8	254
MC	255	$this->_iQueryId = mysqli_query($this->_iConnId, $sQuery);
	256	if (!$this->_iQueryId) {
	257	$this->_sqlerror('Falsche Anfrage / Wrong Query', false, 'SQL-Query = ' . $sQuery);
7fe908	258	return false;
MC	259	}
cd8bb8	260
MC	261	return is_bool($this->_iQueryId) ? $this->_iQueryId : new db_result($this->_iQueryId, $this->_iConnId);
7fe908	262	}
f5b0ca	263
cd8bb8	264	/*#@-/
MC	265
	266
	267
	268
	269
	270	/**
	271	* Executes a query
	272	*
	273	* Executes a given query string, has a variable amount of parameters:
	274	* - 1 parameter
	275	* executes the given query
	276	* - 2 parameters
	277	* executes the given query, replaces the first ? in the query with the second parameter
	278	* - 3 parameters
	279	* if the 2nd parameter is a boolean true, the 3rd parameter has to be an array containing all the replacements for every occuring ? in the query, otherwise the second parameter replaces the first ?, the third parameter replaces the second ? in the query
	280	* - 4 or more parameters
	281	* all ? in the query are replaced from left to right by the parameters 2 to x
	282	*
	283	* @access public
	284	* @param string $sQuery query string
	285	* @param mixed ... one or more parameters
	286	* @return db_result the result object of the query
	287	*/
	288
	289
	290	public function query($sQuery = '') {
	291	$aArgs = func_get_args();
	292	return call_user_func_array(array(&$this, '_query'), $aArgs);
7fe908	293	}
f5b0ca	294
cd8bb8	295	/**
MC	296	* Execute a query and get first result array
	297	*
	298	* Executes a query and returns the first result row as an array
	299	* This is like calling $result = $db->query(), $result->get(), $result->free()
	300	* Use of this function @see query
	301	*
	302	* @access public
	303	* @param string $sQuery query to execute
	304	* @param ... further params (see query())
	305	* @return array result row or NULL if none found
	306	*/
	307	public function queryOneRecord($sQuery = '') {
	308	if(!preg_match('/limit \d+\s,\s\d+$/i', $sQuery)) $sQuery .= ' LIMIT 0,1';
	309
	310	$aArgs = func_get_args();
	311	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	312	if(!$oResult) return null;
	313
	314	$aReturn = $oResult->get();
	315	$oResult->free();
	316
	317	return $aReturn;
7fe908	318	}
f5b0ca	319
cd8bb8	320	public function queryOne($sQuery = '') {
7ae5b0	321	return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args());
7fe908	322	}
MC	323
cd8bb8	324	public function query_one($sQuery = '') {
7ae5b0	325	return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args());
cd8bb8	326	}
MC	327
	328	/**
	329	* Execute a query and return all rows
	330	*
	331	* Executes a query and returns all result rows in an array
	332	* <strong>Use this with extreme care!!!</strong> Uses lots of memory on big result sets.
	333	*
	334	* @access public
	335	* @param string $sQuery query to execute
	336	* @param ... further params (see query())
	337	* @return array all the rows in the result set
	338	*/
	339	public function queryAllRecords($sQuery = '') {
	340	$aArgs = func_get_args();
	341	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	342	if(!$oResult) return array();
	343
	344	$aResults = array();
	345	while($aRow = $oResult->get()) {
	346	$aResults[] = $aRow;
	347	}
	348	$oResult->free();
	349
	350	return $aResults;
	351	}
	352
	353	public function queryAll($sQuery = '') {
7ae5b0	354	return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args());
cd8bb8	355	}
MC	356
	357	public function query_all($sQuery = '') {
7ae5b0	358	return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args());
cd8bb8	359	}
MC	360
	361	/**
	362	* Execute a query and return all rows as simple array
	363	*
	364	* Executes a query and returns all result rows in an array with elements
	365	* <strong>Only first column is returned</strong> Uses lots of memory on big result sets.
	366	*
	367	* @access public
	368	* @param string $sQuery query to execute
	369	* @param ... further params (see query())
	370	* @return array all the rows in the result set
	371	*/
	372	public function queryAllArray($sQuery = '') {
	373	$aArgs = func_get_args();
	374	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	375	if(!$oResult) return array();
	376
	377	$aResults = array();
	378	while($aRow = $oResult->get()) {
	379	$aResults[] = reset($aRow);
	380	}
	381	$oResult->free();
	382
	383	return $aResults;
	384	}
	385
	386	public function query_all_array($sQuery = '') {
	387	return $this->queryAllArray($sQuery);
	388	}
	389
	390
	391
	392	/**
	393	* Get id of last inserted row
	394	*
	395	* Gives you the id of the last inserted row in a table with an auto-increment primary key
	396	*
	397	* @access public
	398	* @return int id of last inserted row or 0 if none
	399	*/
	400	public function insert_id() {
	401	$iRes = mysqli_query($this->_iConnId, 'SELECT LAST_INSERT_ID() as `newid`');
	402	if(!is_object($iRes)) return false;
	403
	404	$aReturn = mysqli_fetch_assoc($iRes);
	405	mysqli_free_result($iRes);
	406
	407	return $aReturn['newid'];
	408	}
	409
	410
	411
	412	/**
	413	* get affected row count
	414	*
	415	* Gets the amount of rows affected by the previous query
	416	*
	417	* @access public
	418	* @return int affected rows
	419	*/
	420	public function affected() {
	421	if(!is_object($this->_iConnId)) return 0;
	422	$iRows = mysqli_affected_rows($this->_iConnId);
	423	if(!$iRows) $iRows = 0;
	424	return $iRows;
	425	}
	426
	427
	428
	429	/**
	430	* Escape a string for usage in a query
	431	*
	432	* @access public
	433	* @param string $sString query string to escape
	434	* @return string escaped string
	435	*/
	436	public function escape($sString) {
	437	global $app;
	438	if(!is_string($sString) && !is_numeric($sString)) {
	439	$app->log('NON-String given in escape function! (' . gettype($sString) . ')', LOGLEVEL_INFO);
563649	440	//$sAddMsg = getDebugBacktrace();
cd8bb8	441	$app->log($sAddMsg, LOGLEVEL_DEBUG);
MC	442	$sString = '';
	443	}
	444
	445	/*$cur_encoding = mb_detect_encoding($sString);
	446	if($cur_encoding != "UTF-8") {
	447	if($cur_encoding != 'ASCII') {
	448	$app->log('String ' . substr($sString, 0, 25) . '... is ' . $cur_encoding . '.', LOGLEVEL_WARN);
	449	if($cur_encoding) $sString = mb_convert_encoding($sString, 'UTF-8', $cur_encoding);
	450	else $sString = mb_convert_encoding($sString, 'UTF-8');
	451	}
	452	} elseif(!PXBase::check_utf8($sString)) {
	453	$sString = utf8_encode($sString);
	454	}*/
	455
	456	if($this->_iConnId) return mysqli_real_escape_string($this->_iConnId, $sString);
	457	else return addslashes($sString);
	458	}
	459
	460	/**
	461	*
	462	*
	463	* @access private
	464	*/
	465	private function _sqlerror($sErrormsg = 'Unbekannter Fehler', $sAddMsg = '') {
	466	global $app, $conf;
	467
	468	$mysql_error = (is_object($this->_iConnId) ? mysqli_error($this->_iConnId) : mysqli_connect_error());
	469	$mysql_errno = (is_object($this->_iConnId) ? mysqli_errno($this->_iConnId) : mysqli_connect_errno());
	470
563649	471	//$sAddMsg .= getDebugBacktrace();
cd8bb8	472
MC	473	if($this->show_error_messages && $conf['demo_mode'] === false) {
	474	echo $sErrormsg . $sAddMsg;
	475	} else if(is_object($app) && method_exists($app, 'log')) {
a1918e	476	$app->log($sErrormsg . $sAddMsg . ' -> ' . $mysql_errno . ' (' . $mysql_error . ')', LOGLEVEL_WARN);
cd8bb8	477	}
7fe908	478	}
MC	479
	480	public function affectedRows() {
cd8bb8	481	return $this->affected();
7fe908	482	}
MC	483
	484	// returns mySQL insert id
	485	public function insertID() {
cd8bb8	486	return $this->insert_id();
7fe908	487	}
MC	488
	489
	490	//* Function to quote strings
	491	public function quote($formfield) {
cd8bb8	492	return $this->escape($formfield);
7fe908	493	}
MC	494
	495	//* Function to unquotae strings
	496	public function unquote($formfield) {
	497	return stripslashes($formfield);
	498	}
	499
	500	public function toLower($record) {
	501	if(is_array($record)) {
	502	foreach($record as $key => $val) {
	503	$key = strtolower($key);
	504	$out[$key] = $val;
	505	}
	506	}
	507	return $out;
	508	}
	509
	510	public function diffrec($record_old, $record_new) {
	511	$diffrec_full = array();
	512	$diff_num = 0;
	513
	514	if(is_array($record_old) && count($record_old) > 0) {
	515	foreach($record_old as $key => $val) {
	516	// if(!isset($record_new[$key]) \|\| $record_new[$key] != $val) {
	517	if(@$record_new[$key] != $val) {
	518	// Record has changed
	519	$diffrec_full['old'][$key] = $val;
	520	$diffrec_full['new'][$key] = @$record_new[$key];
	521	$diff_num++;
	522	} else {
	523	$diffrec_full['old'][$key] = $val;
	524	$diffrec_full['new'][$key] = $val;
	525	}
	526	}
	527	} elseif(is_array($record_new)) {
	528	foreach($record_new as $key => $val) {
	529	if(isset($record_new[$key]) && @$record_old[$key] != $val) {
	530	// Record has changed
	531	$diffrec_full['new'][$key] = $val;
	532	$diffrec_full['old'][$key] = @$record_old[$key];
	533	$diff_num++;
	534	} else {
	535	$diffrec_full['new'][$key] = $val;
	536	$diffrec_full['old'][$key] = $val;
	537	}
	538	}
	539	}
	540
	541	return array('diff_num' => $diff_num, 'diff_rec' => $diffrec_full);
	542
	543	}
	544
	545	//** Function to fill the datalog with a full differential record.
	546	public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) {
	547	global $app, $conf;
	548
94b44c	549	// Check fields
7272e4	550	if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$db_table)) $app->error('Invalid table name '.$db_table);
cd48c7	551	if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$primary_field)) $app->error('Invalid primary field '.$primary_field.' in table '.$db_table);
94b44c	552
TB	553	$primary_field = $this->quote($primary_field);
	554	$primary_id = intval($primary_id);
f5b0ca	555
b0eb45	556	if($force_update == true) {
T	557	//* We force a update even if no record has changed
7fe908	558	$diffrec_full = array('new' => $record_new, 'old' => $record_old);
b0eb45	559	$diff_num = count($record_new);
T	560	} else {
	561	//* get the difference record between old and new record
	562	$tmp = $this->diffrec($record_old, $record_new);
	563	$diffrec_full = $tmp['diff_rec'];
	564	$diff_num = $tmp['diff_num'];
	565	unset($tmp);
	566	}
f5b0ca	567
7fe908	568	// Insert the server_id, if the record has a server_id
MC	569	$server_id = (isset($record_old['server_id']) && $record_old['server_id'] > 0)?$record_old['server_id']:0;
	570	if(isset($record_new['server_id'])) $server_id = $record_new['server_id'];
cd8bb8	571
f5b0ca	572
7fe908	573	if($diff_num > 0) {
MC	574	//print_r($diff_num);
	575	//print_r($diffrec_full);
cd8bb8	576	$diffstr = serialize($diffrec_full);
MC	577	$username = $_SESSION['s']['user']['username'];
7fe908	578	$dbidx = $primary_field.':'.$primary_id;
f5b0ca	579
7fe908	580	if($action == 'INSERT') $action = 'i';
MC	581	if($action == 'UPDATE') $action = 'u';
	582	if($action == 'DELETE') $action = 'd';
cd8bb8	583	$sql = "INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES (?, ?, ?, ?, ?, ?, ?)";
MC	584	$app->db->query($sql, $db_table, $dbidx, $server_id, $action, time(), $username, $diffstr);
7fe908	585	}
f5b0ca	586
7fe908	587	return true;
MC	588	}
f5b0ca	589
7fe908	590	//** Inserts a record and saves the changes into the datalog
MC	591	public function datalogInsert($tablename, $insert_data, $index_field) {
	592	global $app;
e9a57d	593
94b44c	594	// Check fields
7272e4	595	if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename);
cd48c7	596	if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename);
94b44c	597
7fe908	598	if(is_array($insert_data)) {
ee260d	599	$key_str = '';
T	600	$val_str = '';
	601	foreach($insert_data as $key => $val) {
	602	$key_str .= "`".$key ."`,";
cd8bb8	603	$val_str .= "'".$this->escape($val)."',";
ee260d	604	}
7fe908	605	$key_str = substr($key_str, 0, -1);
MC	606	$val_str = substr($val_str, 0, -1);
ee260d	607	$insert_data_str = '('.$key_str.') VALUES ('.$val_str.')';
T	608	} else {
	609	$insert_data_str = $insert_data;
	610	}
cd8bb8	611	/* TODO: reduce risk of insert_data_str! */
f5b0ca	612
7fe908	613	$old_rec = array();
cd8bb8	614	$this->query("INSERT INTO ?? $insert_data_str", $tablename);
7fe908	615	$index_value = $this->insertID();
cd8bb8	616	$new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ? = ?", $tablename, $index_field, $index_value);
7fe908	617	$this->datalogSave($tablename, 'INSERT', $index_field, $index_value, $old_rec, $new_rec);
f5b0ca	618
7fe908	619	return $index_value;
MC	620	}
f5b0ca	621
7fe908	622	//** Updates a record and saves the changes into the datalog
MC	623	public function datalogUpdate($tablename, $update_data, $index_field, $index_value, $force_update = false) {
ee260d	624	global $app;
7fe908	625
94b44c	626	// Check fields
7272e4	627	if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename);
cd48c7	628	if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename);
94b44c	629
cd8bb8	630	$old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
7fe908	631
MC	632	if(is_array($update_data)) {
ee260d	633	$update_data_str = '';
T	634	foreach($update_data as $key => $val) {
cd8bb8	635	$update_data_str .= "`".$key ."` = '".$this->escape($val)."',";
ee260d	636	}
7fe908	637	$update_data_str = substr($update_data_str, 0, -1);
ee260d	638	} else {
T	639	$update_data_str = $update_data;
	640	}
cd8bb8	641	/* TODO: reduce risk of update_data_str */
f5b0ca	642
cd8bb8	643	$this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", $tablename, $index_field, $index_value);
MC	644	$new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
7fe908	645	$this->datalogSave($tablename, 'UPDATE', $index_field, $index_value, $old_rec, $new_rec, $force_update);
f5b0ca	646
7fe908	647	return true;
MC	648	}
f5b0ca	649
7fe908	650	//** Deletes a record and saves the changes into the datalog
MC	651	public function datalogDelete($tablename, $index_field, $index_value) {
	652	global $app;
f5b0ca	653
94b44c	654	// Check fields
7272e4	655	if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename);
cd48c7	656	if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename);
94b44c	657
cd8bb8	658	$old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
MC	659	$this->query("DELETE FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
7fe908	660	$new_rec = array();
MC	661	$this->datalogSave($tablename, 'DELETE', $index_field, $index_value, $old_rec, $new_rec);
	662
	663	return true;
	664	}
	665
	666	//* get the current datalog status for the specified login (or currently logged in user)
	667	public function datalogStatus($login = '') {
	668	global $app;
	669
	670	$return = array('count' => 0, 'entries' => array());
	671	if($_SESSION['s']['user']['typ'] == 'admin') return $return; // these information should not be displayed to admin users
	672
	673	if($login == '' && isset($_SESSION['s']['user'])) {
	674	$login = $_SESSION['s']['user']['username'];
	675	}
	676
cd8bb8	677	$result = $this->queryAllRecords("SELECT COUNT( * ) AS cnt, sys_datalog.action, sys_datalog.dbtable FROM sys_datalog, server WHERE server.server_id = sys_datalog.server_id AND sys_datalog.user = ? AND sys_datalog.datalog_id > server.updated GROUP BY sys_datalog.dbtable, sys_datalog.action", $login);
7fe908	678	foreach($result as $row) {
MC	679	if(!$row['dbtable'] \|\| in_array($row['dbtable'], array('aps_instances', 'aps_instances_settings', 'mail_access', 'mail_content_filter'))) continue; // ignore some entries, maybe more to come
	680	$return['entries'][] = array('table' => $row['dbtable'], 'action' => $row['action'], 'count' => $row['cnt'], 'text' => $app->lng('datalog_status_' . $row['action'] . '_' . $row['dbtable']));
	681	$return['count'] += $row['cnt'];
	682	}
	683	unset($result);
	684
	685	return $return;
	686	}
f5b0ca	687
7fe908	688	/*
f5b0ca	689	$columns = array(action => add \| alter \| drop
N	690	name => Spaltenname
	691	name_new => neuer Spaltenname, nur bei 'alter' belegt
	692	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	693	typeValue => Wert z.B. bei Varchar
	694	defaultValue => Default Wert
	695	notNull => true \| false
	696	autoInc => true \| false
	697	option => unique \| primary \| index)
	698
	699
	700	*/
	701
7fe908	702	public function createTable($table_name, $columns) {
MC	703	$index = '';
cd8bb8	704	$sql = "CREATE TABLE ?? (";
7fe908	705	foreach($columns as $col){
MC	706	$sql .= $col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
f5b0ca	707
7fe908	708	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
MC	709	if($col['notNull'] == true) {
	710	$sql .= 'NOT NULL ';
	711	} else {
	712	$sql .= 'NULL ';
	713	}
	714	if($col['autoInc'] == true) $sql .= 'auto_increment ';
	715	$sql.= ',';
	716	// key Definitionen
	717	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	718	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	719	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
	720	}
	721	$sql .= $index;
	722	$sql = substr($sql, 0, -1);
	723	$sql .= ')';
cd8bb8	724	/* TODO: secure parameters */
MC	725	$this->query($sql, $table_name);
7fe908	726	return true;
f5b0ca	727	}
N	728
7fe908	729	/*
f5b0ca	730	$columns = array(action => add \| alter \| drop
N	731	name => Spaltenname
	732	name_new => neuer Spaltenname, nur bei 'alter' belegt
	733	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	734	typeValue => Wert z.B. bei Varchar
	735	defaultValue => Default Wert
	736	notNull => true \| false
	737	autoInc => true \| false
	738	option => unique \| primary \| index)
	739
	740
	741	*/
7fe908	742	public function alterTable($table_name, $columns) {
MC	743	$index = '';
cd8bb8	744	$sql = "ALTER TABLE ?? ";
7fe908	745	foreach($columns as $col){
MC	746	if($col['action'] == 'add') {
	747	$sql .= 'ADD '.$col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	748	} elseif ($col['action'] == 'alter') {
	749	$sql .= 'CHANGE '.$col['name'].' '.$col['name_new'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	750	} elseif ($col['action'] == 'drop') {
	751	$sql .= 'DROP '.$col['name'].' ';
	752	}
	753	if($col['action'] != 'drop') {
	754	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
	755	if($col['notNull'] == true) {
	756	$sql .= 'NOT NULL ';
	757	} else {
	758	$sql .= 'NULL ';
	759	}
	760	if($col['autoInc'] == true) $sql .= 'auto_increment ';
	761	$sql.= ',';
	762	// Index definitions
	763	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	764	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	765	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
	766	}
	767	}
	768	$sql .= $index;
	769	$sql = substr($sql, 0, -1);
cd8bb8	770	/* TODO: secure parameters */
7fe908	771	//die($sql);
cd8bb8	772	$this->query($sql, $table_name);
7fe908	773	return true;
f5b0ca	774	}
7fe908	775
MC	776	public function dropTable($table_name) {
	777	$this->check($table_name);
cd8bb8	778	$sql = "DROP TABLE ??";
MC	779	return $this->query($sql, $table_name);
f5b0ca	780	}
N	781
7fe908	782	// gibt Array mit Tabellennamen zur�ck
MC	783	public function getTables($database_name = '') {
cd8bb8	784	if(!is_object($this->_iConnId)) return false;
7fe908	785	if($database_name == '') $database_name = $this->dbName;
cd8bb8	786	$tb_names = $this->queryAllArray("SHOW TABLES FROM ??", $database_name);
7fe908	787	return $tb_names;
MC	788	}
f5b0ca	789
7fe908	790	// gibt Feldinformationen zur Tabelle zur�ck
MC	791	/*
f5b0ca	792	$columns = array(action => add \| alter \| drop
N	793	name => Spaltenname
	794	name_new => neuer Spaltenname, nur bei 'alter' belegt
	795	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	796	typeValue => Wert z.B. bei Varchar
	797	defaultValue => Default Wert
	798	notNull => true \| false
	799	autoInc => true \| false
	800	option => unique \| primary \| index)
	801
	802
	803	*/
	804
7fe908	805	function tableInfo($table_name) {
f5b0ca	806
7fe908	807	global $go_api, $go_info, $app;
MC	808	// Tabellenfelder einlesen
f5b0ca	809
cd8bb8	810	if($rows = $app->db->queryAllRecords('SHOW FIELDS FROM ??', $table_name)){
7fe908	811	foreach($rows as $row) {
MC	812	/*
f5b0ca	813	$name = $row[0];
N	814	$default = $row[4];
	815	$key = $row[3];
	816	$extra = $row[5];
	817	$isnull = $row[2];
	818	$type = $row[1];
bfcdef	819	*/
7fe908	820
MC	821	$name = $row['Field'];
	822	$default = $row['Default'];
	823	$key = $row['Key'];
	824	$extra = $row['Extra'];
	825	$isnull = $row['Null'];
	826	$type = $row['Type'];
f5b0ca	827
N	828
7fe908	829	$column = array();
f5b0ca	830
7fe908	831	$column['name'] = $name;
MC	832	//$column['type'] = $type;
	833	$column['defaultValue'] = $default;
	834	if(stristr($key, 'PRI')) $column['option'] = 'primary';
	835	if(stristr($isnull, 'YES')) {
	836	$column['notNull'] = false;
	837	} else {
	838	$column['notNull'] = true;
	839	}
	840	if($extra == 'auto_increment') $column['autoInc'] = true;
f5b0ca	841
N	842
7fe908	843	// Type in Metatype umsetzen
f5b0ca	844
7fe908	845	if(stristr($type, 'int(')) $metaType = 'int32';
MC	846	if(stristr($type, 'bigint')) $metaType = 'int64';
	847	if(stristr($type, 'char')) {
	848	$metaType = 'char';
	849	$tmp_typeValue = explode('(', $type);
	850	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
	851	}
	852	if(stristr($type, 'varchar')) {
	853	$metaType = 'varchar';
	854	$tmp_typeValue = explode('(', $type);
	855	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
	856	}
	857	if(stristr($type, 'text')) $metaType = 'text';
	858	if(stristr($type, 'double')) $metaType = 'double';
	859	if(stristr($type, 'blob')) $metaType = 'blob';
f5b0ca	860
N	861
7fe908	862	$column['type'] = $metaType;
f5b0ca	863
7fe908	864	$columns[] = $column;
MC	865	}
	866	return $columns;
	867	} else {
	868	return false;
	869	}
f5b0ca	870
N	871
7fe908	872	//$this->createTable('tester',$columns);
f5b0ca	873
7fe908	874	/*
f5b0ca	875	$result = mysql_list_fields($go_info["server"]["db_name"],$table_name);
N	876	$fields = mysql_num_fields ($result);
	877	$i = 0;
	878	$table = mysql_field_table ($result, $i);
	879	while ($i < $fields) {
	880	$name = mysql_field_name ($result, $i);
	881	$type = mysql_field_type ($result, $i);
	882	$len = mysql_field_len ($result, $i);
	883	$flags = mysql_field_flags ($result, $i);
	884	print_r($flags);
	885
	886	$columns = array(name => $name,
	887	type => "",
	888	defaultValue => "",
	889	isnull => 1,
	890	option => "");
	891	$returnvar[] = $columns;
	892
	893	$i++;
	894	}
	895	*/
	896
	897
	898
7fe908	899	}
f5b0ca	900
7fe908	901	public function mapType($metaType, $typeValue) {
MC	902	global $go_api;
	903	$metaType = strtolower($metaType);
	904	switch ($metaType) {
	905	case 'int16':
	906	return 'smallint';
	907	break;
	908	case 'int32':
	909	return 'int';
	910	break;
	911	case 'int64':
	912	return 'bigint';
	913	break;
	914	case 'double':
	915	return 'double';
	916	break;
	917	case 'char':
	918	return 'char';
	919	break;
	920	case 'varchar':
	921	if($typeValue < 1) die('Database failure: Lenght required for these data types.');
	922	return 'varchar('.$typeValue.')';
	923	break;
	924	case 'text':
	925	return 'text';
	926	break;
	927	case 'blob':
	928	return 'blob';
	929	break;
8748b3	930	case 'date':
TB	931	return 'date';
	932	break;
7fe908	933	}
MC	934	}
f5b0ca	935
7fe908	936	}
f5b0ca	937
cd8bb8	938	/**
MC	939	* database query result class
	940	*
	941	* @package pxFramework
	942	*
	943	*/
	944	class db_result {
	945
	946	/**
	947	*
	948	*
	949	* @access private
	950	*/
	951	private $_iResId = null;
	952	private $_iConnection = null;
	953
	954
	955
	956	/**
	957	*
	958	*
	959	* @access private
	960	*/
	961	public function db_result($iResId, $iConnection) {
	962	$this->_iResId = $iResId;
	963	$this->_iConnection = $iConnection;
	964	}
	965
	966
	967
	968	/**
	969	* get count of result rows
	970	*
	971	* Returns the amount of rows in the result set
	972	*
	973	* @access public
	974	* @return int amount of rows
	975	*/
	976	public function rows() {
	977	if(!is_object($this->_iResId)) return 0;
	978	$iRows = mysqli_num_rows($this->_iResId);
	979	if(!$iRows) $iRows = 0;
	980	return $iRows;
	981	}
	982
	983
	984
	985	/**
	986	* Get number of affected rows
	987	*
	988	* Returns the amount of rows affected by the previous query
	989	*
	990	* @access public
	991	* @return int amount of affected rows
	992	*/
	993	public function affected() {
	994	if(!is_object($this->_iConnection)) return 0;
	995	$iRows = mysqli_affected_rows($this->_iConnection);
	996	if(!$iRows) $iRows = 0;
	997	return $iRows;
	998	}
	999
	1000
	1001
	1002	/**
	1003	* Frees the result set
	1004	*
	1005	* @access public
	1006	*/
	1007	public function free() {
	1008	if(!is_object($this->_iResId)) return;
	1009
	1010	mysqli_free_result($this->_iResId);
	1011	return;
	1012	}
	1013
	1014
	1015
	1016	/**
	1017	* Get a result row (associative)
	1018	*
	1019	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	1020	*
	1021	* @access public
	1022	* @return array result row
	1023	*/
	1024	public function get() {
	1025	$aItem = null;
	1026
	1027	if(is_object($this->_iResId)) {
	1028	$aItem = mysqli_fetch_assoc($this->_iResId);
	1029	if(!$aItem) $aItem = null;
	1030	}
	1031	return $aItem;
	1032	}
	1033
	1034
	1035
	1036	/**
	1037	* Get a result row (array with numeric index)
	1038	*
	1039	* @access public
	1040	* @return array result row
	1041	*/
	1042	public function getAsRow() {
	1043	$aItem = null;
	1044
	1045	if(is_object($this->_iResId)) {
	1046	$aItem = mysqli_fetch_row($this->_iResId);
	1047	if(!$aItem) $aItem = null;
	1048	}
	1049	return $aItem;
	1050	}
	1051
	1052	}
	1053
	1054	/**
	1055	* database query result class
	1056	*
	1057	* emulates a db result set out of an array so you can use array results and db results the same way
	1058	*
	1059	* @package pxFramework
	1060	* @see db_result
	1061	*
	1062	*
	1063	*/
	1064	class fakedb_result {
	1065
	1066	/**
	1067	*
	1068	*
	1069	* @access private
	1070	*/
	1071	private $aResultData = array();
	1072
	1073	/**
	1074	*
	1075	*
	1076	* @access private
	1077	*/
	1078	private $aLimitedData = array();
	1079
	1080
	1081
	1082	/**
	1083	*
	1084	*
	1085	* @access private
	1086	*/
	1087	public function fakedb_result($aData) {
	1088	$this->aResultData = $aData;
	1089	$this->aLimitedData = $aData;
	1090	reset($this->aLimitedData);
	1091	}
	1092
	1093
	1094
	1095	/**
	1096	* get count of result rows
	1097	*
	1098	* Returns the amount of rows in the result set
	1099	*
	1100	* @access public
	1101	* @return int amount of rows
	1102	*/
	1103	// Gibt die Anzahl Zeilen zurück
	1104	public function rows() {
	1105	return count($this->aLimitedData);
	1106	}
	1107
	1108
	1109
	1110	/**
	1111	* Frees the result set
	1112	*
	1113	* @access public
	1114	*/
	1115	// Gibt ein Ergebnisset frei
	1116	public function free() {
	1117	$this->aResultData = array();
	1118	$this->aLimitedData = array();
	1119	return;
	1120	}
	1121
	1122
	1123
	1124	/**
	1125	* Get a result row (associative)
	1126	*
	1127	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	1128	*
	1129	* @access public
	1130	* @return array result row
	1131	*/
	1132	// Gibt eine Ergebniszeile zurück
	1133	public function get() {
	1134	$aItem = null;
	1135
	1136	if(!is_array($this->aLimitedData)) return $aItem;
	1137
	1138	if(list($vKey, $aItem) = each($this->aLimitedData)) {
	1139	if(!$aItem) $aItem = null;
	1140	}
	1141	return $aItem;
	1142	}
	1143
	1144
	1145
	1146	/**
	1147	* Get a result row (array with numeric index)
	1148	*
	1149	* @access public
	1150	* @return array result row
	1151	*/
	1152	public function getAsRow() {
	1153	return $this->get();
	1154	}
	1155
	1156
	1157
	1158	/**
	1159	* Limit the result (like a LIMIT x,y in a SQL query)
	1160	*
	1161	* @access public
	1162	* @param int $iStart offset to start read
	1163	* @param int iLength amount of datasets to read
	1164	*/
	1165	public function limit_result($iStart, $iLength) {
	1166	$this->aLimitedData = array_slice($this->aResultData, $iStart, $iLength, true);
	1167	}
	1168
	1169	}
	1170
	1171
7fe908	1172	?>