gitlabFork/ISPConfig3.git - Solinfo Gitblit

- Fixed problem with .* table info (GRANT X ON Y.*)

Marius Cramer

2015-04-16 d7fe4dc96dcd2319a56216cdf683d2963f3ef110

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	}
ccfc84	89	if(!((bool)mysqli_query( $this->_iConnId, 'USE `' . $this->dbName . '`'))) {
cd8bb8	90	$this->close();
MC	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);
d7fe4d	131	if(strpos($sTxt, '.') !== false) {
MC	132	$sTxt = preg_replace('/^(.+)\.(.+)$/', '`$1`.`$2`', $sTxt);
	133	$sTxt = str_replace('.``', '.', $sTxt);
	134	} else $sTxt = '`' . $sTxt . '`';
cd8bb8	135
MC	136	$sQuery = substr_replace($sQuery, $sTxt, $iPos2, 2);
	137	$iPos2 += strlen($sTxt);
	138	$iPos = $iPos2;
	139	} else {
	140	if(is_int($sValue) \|\| is_float($sValue)) {
	141	$sTxt = $sValue;
3a11d2	142	} elseif(is_null($sValue) \|\| (is_string($sValue) && (strcmp($sValue, '#NULL#') == 0))) {
cd8bb8	143	$sTxt = 'NULL';
MC	144	} elseif(is_array($sValue)) {
3a11d2	145	if(isset($sValue['SQL'])) {
MC	146	$sTxt = $sValue['SQL'];
	147	} else {
	148	$sTxt = '';
	149	foreach($sValue as $sVal) $sTxt .= ',\'' . $this->escape($sVal) . '\'';
	150	$sTxt = '(' . substr($sTxt, 1) . ')';
	151	if($sTxt == '()') $sTxt = '(0)';
	152	}
cd8bb8	153	} else {
MC	154	$sTxt = '\'' . $this->escape($sValue) . '\'';
	155	}
	156
	157	$sQuery = substr_replace($sQuery, $sTxt, $iPos, 1);
	158	$iPos += strlen($sTxt);
	159	$iPos2 = $iPos;
7fe908	160	}
cd8bb8	161	}
7fe908	162	}
cd8bb8	163
MC	164	return $sQuery;
f5b0ca	165	}
7fe908	166
cd8bb8	167	/*#@-/
MC	168
	169
	170	/**#@+
	171	* @access private
	172	*/
	173	private function _setCharset() {
	174	mysqli_query($this->_iConnId, 'SET NAMES '.$this->dbCharset);
	175	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	176	}
cb1221	177
TB	178	private function securityScan($string) {
	179	global $app, $conf;
	180
	181	// get security config
	182	if(isset($app)) {
	183	$app->uses('getconf');
	184	$ids_config = $app->getconf->get_security_config('ids');
	185
	186	if($ids_config['sql_scan_enabled'] == 'yes') {
	187
0e34ba	188	// Remove whitespace
TB	189	$string = trim($string);
	190	if(substr($string,-1) == ';') $string = substr($string,0,-1);
	191
	192	// Save original string
cb1221	193	$string_orig = $string;
TB	194
	195	//echo $string;
68b146	196	$chars = array(';', '#', '/', '/', '--', '\\\'', '\\"');
cb1221	197
TB	198	$string = str_replace('\\\\', '', $string);
f986a3	199	$string = preg_replace('/(^\|[^\\\])([\'"])\\2/is', '$1', $string);
MC	200	$string = preg_replace('/(^\|[^\\\])([\'"])(.*?[^\\\])\\2/is', '$1', $string);
cb1221	201	$ok = true;
TB	202
	203	if(substr_count($string, "`") % 2 != 0 \|\| substr_count($string, "'") % 2 != 0 \|\| substr_count($string, '"') % 2 != 0) {
	204	$app->log("SQL injection warning (" . $string_orig . ")",2);
	205	$ok = false;
	206	} else {
	207	foreach($chars as $char) {
	208	if(strpos($string, $char) !== false) {
	209	$ok = false;
	210	$app->log("SQL injection warning (" . $string_orig . ")",2);
	211	break;
	212	}
	213	}
	214	}
	215	if($ok == true) {
	216	return true;
	217	} else {
	218	if($ids_config['sql_scan_action'] == 'warn') {
	219	// we return false in warning level.
	220	return false;
	221	} else {
	222	// if sql action = 'block' or anything else then stop here.
	223	$app->error('Possible SQL injection. All actions have been logged.');
	224	}
	225	}
	226	}
	227	}
	228	}
f5b0ca	229
cd8bb8	230	private function _query($sQuery = '') {
MC	231	global $app;
	232
	233	if ($sQuery == '') {
	234	$this->_sqlerror('Keine Anfrage angegeben / No query given');
	235	return false;
	236	}
	237
7fe908	238	$try = 0;
MC	239	do {
	240	$try++;
cd8bb8	241	$ok = mysqli_ping($this->_iConnId);
7fe908	242	if(!$ok) {
cd8bb8	243	if(!mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, $this->dbName)) {
7fe908	244	if($try > 4) {
cd8bb8	245	$this->_sqlerror('DB::query -> reconnect');
7fe908	246	return false;
MC	247	} else {
	248	sleep(1);
	249	}
	250	} else {
cd8bb8	251	$this->_setCharset();
7fe908	252	$ok = true;
MC	253	}
	254	}
	255	} while($ok == false);
cd8bb8	256
MC	257	$aArgs = func_get_args();
	258	$sQuery = call_user_func_array(array(&$this, '_build_query_string'), $aArgs);
797215	259	$this->securityScan($sQuery);
cd8bb8	260
8173c6	261	$this->_iQueryId = @mysqli_query($this->_iConnId, $sQuery);
cd8bb8	262	if (!$this->_iQueryId) {
MC	263	$this->_sqlerror('Falsche Anfrage / Wrong Query', false, 'SQL-Query = ' . $sQuery);
7fe908	264	return false;
MC	265	}
cd8bb8	266
MC	267	return is_bool($this->_iQueryId) ? $this->_iQueryId : new db_result($this->_iQueryId, $this->_iConnId);
7fe908	268	}
f5b0ca	269
cd8bb8	270	/*#@-/
MC	271
	272
	273
	274
	275
	276	/**
	277	* Executes a query
	278	*
	279	* Executes a given query string, has a variable amount of parameters:
	280	* - 1 parameter
	281	* executes the given query
	282	* - 2 parameters
	283	* executes the given query, replaces the first ? in the query with the second parameter
	284	* - 3 parameters
	285	* 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
	286	* - 4 or more parameters
	287	* all ? in the query are replaced from left to right by the parameters 2 to x
	288	*
	289	* @access public
	290	* @param string $sQuery query string
	291	* @param mixed ... one or more parameters
	292	* @return db_result the result object of the query
	293	*/
	294
	295
	296	public function query($sQuery = '') {
	297	$aArgs = func_get_args();
	298	return call_user_func_array(array(&$this, '_query'), $aArgs);
7fe908	299	}
f5b0ca	300
cd8bb8	301	/**
MC	302	* Execute a query and get first result array
	303	*
	304	* Executes a query and returns the first result row as an array
	305	* This is like calling $result = $db->query(), $result->get(), $result->free()
	306	* Use of this function @see query
	307	*
	308	* @access public
	309	* @param string $sQuery query to execute
	310	* @param ... further params (see query())
	311	* @return array result row or NULL if none found
	312	*/
	313	public function queryOneRecord($sQuery = '') {
	314	if(!preg_match('/limit \d+\s,\s\d+$/i', $sQuery)) $sQuery .= ' LIMIT 0,1';
	315
	316	$aArgs = func_get_args();
	317	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	318	if(!$oResult) return null;
	319
	320	$aReturn = $oResult->get();
	321	$oResult->free();
	322
	323	return $aReturn;
7fe908	324	}
f5b0ca	325
cd8bb8	326	public function queryOne($sQuery = '') {
7ae5b0	327	return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args());
7fe908	328	}
MC	329
cd8bb8	330	public function query_one($sQuery = '') {
7ae5b0	331	return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args());
cd8bb8	332	}
MC	333
	334	/**
	335	* Execute a query and return all rows
	336	*
	337	* Executes a query and returns all result rows in an array
	338	* <strong>Use this with extreme care!!!</strong> Uses lots of memory on big result sets.
	339	*
	340	* @access public
	341	* @param string $sQuery query to execute
	342	* @param ... further params (see query())
	343	* @return array all the rows in the result set
	344	*/
	345	public function queryAllRecords($sQuery = '') {
	346	$aArgs = func_get_args();
	347	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	348	if(!$oResult) return array();
	349
	350	$aResults = array();
	351	while($aRow = $oResult->get()) {
	352	$aResults[] = $aRow;
	353	}
	354	$oResult->free();
	355
	356	return $aResults;
	357	}
	358
	359	public function queryAll($sQuery = '') {
7ae5b0	360	return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args());
cd8bb8	361	}
MC	362
	363	public function query_all($sQuery = '') {
7ae5b0	364	return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args());
cd8bb8	365	}
MC	366
	367	/**
	368	* Execute a query and return all rows as simple array
	369	*
	370	* Executes a query and returns all result rows in an array with elements
	371	* <strong>Only first column is returned</strong> Uses lots of memory on big result sets.
	372	*
	373	* @access public
	374	* @param string $sQuery query to execute
	375	* @param ... further params (see query())
	376	* @return array all the rows in the result set
	377	*/
	378	public function queryAllArray($sQuery = '') {
	379	$aArgs = func_get_args();
	380	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	381	if(!$oResult) return array();
	382
	383	$aResults = array();
	384	while($aRow = $oResult->get()) {
	385	$aResults[] = reset($aRow);
	386	}
	387	$oResult->free();
	388
	389	return $aResults;
	390	}
	391
	392	public function query_all_array($sQuery = '') {
	393	return $this->queryAllArray($sQuery);
	394	}
	395
	396
	397
	398	/**
	399	* Get id of last inserted row
	400	*
	401	* Gives you the id of the last inserted row in a table with an auto-increment primary key
	402	*
	403	* @access public
	404	* @return int id of last inserted row or 0 if none
	405	*/
	406	public function insert_id() {
	407	$iRes = mysqli_query($this->_iConnId, 'SELECT LAST_INSERT_ID() as `newid`');
	408	if(!is_object($iRes)) return false;
	409
	410	$aReturn = mysqli_fetch_assoc($iRes);
	411	mysqli_free_result($iRes);
	412
	413	return $aReturn['newid'];
	414	}
	415
	416
	417
	418	/**
	419	* get affected row count
	420	*
	421	* Gets the amount of rows affected by the previous query
	422	*
	423	* @access public
	424	* @return int affected rows
	425	*/
	426	public function affected() {
	427	if(!is_object($this->_iConnId)) return 0;
	428	$iRows = mysqli_affected_rows($this->_iConnId);
	429	if(!$iRows) $iRows = 0;
	430	return $iRows;
	431	}
	432
	433
385f54	434	/**
MC	435	* check if a utf8 string is valid
	436	*
	437	* @access public
	438	* @param string $string the string to check
	439	* @return bool true if it is valid utf8, false otherwise
	440	*/
	441	private function check_utf8($str) {
	442	$len = strlen($str);
	443	for($i = 0; $i < $len; $i++){
	444	$c = ord($str[$i]);
	445	if ($c > 128) {
	446	if (($c > 247)) return false;
	447	elseif ($c > 239) $bytes = 4;
	448	elseif ($c > 223) $bytes = 3;
	449	elseif ($c > 191) $bytes = 2;
	450	else return false;
	451	if (($i + $bytes) > $len) return false;
	452	while ($bytes > 1) {
	453	$i++;
	454	$b = ord($str[$i]);
	455	if ($b < 128 \|\| $b > 191) return false;
	456	$bytes--;
	457	}
	458	}
	459	}
	460	return true;
	461	} // end of check_utf8
cd8bb8	462
MC	463	/**
	464	* Escape a string for usage in a query
	465	*
	466	* @access public
	467	* @param string $sString query string to escape
	468	* @return string escaped string
	469	*/
	470	public function escape($sString) {
	471	global $app;
	472	if(!is_string($sString) && !is_numeric($sString)) {
	473	$app->log('NON-String given in escape function! (' . gettype($sString) . ')', LOGLEVEL_INFO);
563649	474	//$sAddMsg = getDebugBacktrace();
cd8bb8	475	$app->log($sAddMsg, LOGLEVEL_DEBUG);
MC	476	$sString = '';
	477	}
	478
385f54	479	$cur_encoding = mb_detect_encoding($sString);
cd8bb8	480	if($cur_encoding != "UTF-8") {
MC	481	if($cur_encoding != 'ASCII') {
385f54	482	$app->log('String ' . substr($sString, 0, 25) . '... is ' . $cur_encoding . '.', LOGLEVEL_INFO);
cd8bb8	483	if($cur_encoding) $sString = mb_convert_encoding($sString, 'UTF-8', $cur_encoding);
MC	484	else $sString = mb_convert_encoding($sString, 'UTF-8');
	485	}
385f54	486	} elseif(!$this->check_utf8($sString)) {
cd8bb8	487	$sString = utf8_encode($sString);
385f54	488	}
cd8bb8	489
MC	490	if($this->_iConnId) return mysqli_real_escape_string($this->_iConnId, $sString);
	491	else return addslashes($sString);
	492	}
	493
	494	/**
	495	*
	496	*
	497	* @access private
	498	*/
	499	private function _sqlerror($sErrormsg = 'Unbekannter Fehler', $sAddMsg = '') {
	500	global $app, $conf;
	501
	502	$mysql_error = (is_object($this->_iConnId) ? mysqli_error($this->_iConnId) : mysqli_connect_error());
	503	$mysql_errno = (is_object($this->_iConnId) ? mysqli_errno($this->_iConnId) : mysqli_connect_errno());
	504
563649	505	//$sAddMsg .= getDebugBacktrace();
cd8bb8	506
MC	507	if($this->show_error_messages && $conf['demo_mode'] === false) {
	508	echo $sErrormsg . $sAddMsg;
	509	} else if(is_object($app) && method_exists($app, 'log')) {
a1918e	510	$app->log($sErrormsg . $sAddMsg . ' -> ' . $mysql_errno . ' (' . $mysql_error . ')', LOGLEVEL_WARN);
cd8bb8	511	}
7fe908	512	}
MC	513
	514	public function affectedRows() {
cd8bb8	515	return $this->affected();
7fe908	516	}
MC	517
	518	// returns mySQL insert id
	519	public function insertID() {
cd8bb8	520	return $this->insert_id();
7fe908	521	}
MC	522
	523
	524	//* Function to quote strings
	525	public function quote($formfield) {
cd8bb8	526	return $this->escape($formfield);
7fe908	527	}
MC	528
	529	//* Function to unquotae strings
	530	public function unquote($formfield) {
	531	return stripslashes($formfield);
	532	}
	533
	534	public function toLower($record) {
	535	if(is_array($record)) {
	536	foreach($record as $key => $val) {
	537	$key = strtolower($key);
	538	$out[$key] = $val;
	539	}
	540	}
	541	return $out;
	542	}
a6a094	543
MC	544	public function insertFromArray($tablename, $data) {
	545	if(!is_array($data)) return false;
	546
	547	$k_query = '';
	548	$v_query = '';
	549
	550	$params = array($tablename);
	551	$v_params = array();
	552
	553	foreach($data as $key => $value) {
	554	$k_query .= ($k_query != '' ? ', ' : '') . '??';
	555	$v_query .= ($v_query != '' ? ', ' : '') . '?';
	556	$params[] = $key;
	557	$v_params[] = $value;
	558	}
	559
	560	$query = 'INSERT INTO ?? (' . $k_query . ') VALUES (' . $v_query . ')';
	561	return $this->query($query, true, $params + $v_params);
	562	}
	563
7fe908	564	public function diffrec($record_old, $record_new) {
MC	565	$diffrec_full = array();
	566	$diff_num = 0;
	567
	568	if(is_array($record_old) && count($record_old) > 0) {
	569	foreach($record_old as $key => $val) {
	570	// if(!isset($record_new[$key]) \|\| $record_new[$key] != $val) {
	571	if(@$record_new[$key] != $val) {
	572	// Record has changed
	573	$diffrec_full['old'][$key] = $val;
	574	$diffrec_full['new'][$key] = @$record_new[$key];
	575	$diff_num++;
	576	} else {
	577	$diffrec_full['old'][$key] = $val;
	578	$diffrec_full['new'][$key] = $val;
	579	}
	580	}
	581	} elseif(is_array($record_new)) {
	582	foreach($record_new as $key => $val) {
	583	if(isset($record_new[$key]) && @$record_old[$key] != $val) {
	584	// Record has changed
	585	$diffrec_full['new'][$key] = $val;
	586	$diffrec_full['old'][$key] = @$record_old[$key];
	587	$diff_num++;
	588	} else {
	589	$diffrec_full['new'][$key] = $val;
	590	$diffrec_full['old'][$key] = $val;
	591	}
	592	}
	593	}
	594
	595	return array('diff_num' => $diff_num, 'diff_rec' => $diffrec_full);
	596
	597	}
	598
	599	//** Function to fill the datalog with a full differential record.
	600	public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) {
	601	global $app, $conf;
	602
94b44c	603	// Check fields
7272e4	604	if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$db_table)) $app->error('Invalid table name '.$db_table);
cd48c7	605	if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$primary_field)) $app->error('Invalid primary field '.$primary_field.' in table '.$db_table);
94b44c	606
TB	607	$primary_id = intval($primary_id);
f5b0ca	608
b0eb45	609	if($force_update == true) {
T	610	//* We force a update even if no record has changed
7fe908	611	$diffrec_full = array('new' => $record_new, 'old' => $record_old);
b0eb45	612	$diff_num = count($record_new);
T	613	} else {
	614	//* get the difference record between old and new record
	615	$tmp = $this->diffrec($record_old, $record_new);
	616	$diffrec_full = $tmp['diff_rec'];
	617	$diff_num = $tmp['diff_num'];
	618	unset($tmp);
	619	}
f5b0ca	620
7fe908	621	// Insert the server_id, if the record has a server_id
MC	622	$server_id = (isset($record_old['server_id']) && $record_old['server_id'] > 0)?$record_old['server_id']:0;
	623	if(isset($record_new['server_id'])) $server_id = $record_new['server_id'];
cd8bb8	624
f5b0ca	625
7fe908	626	if($diff_num > 0) {
MC	627	//print_r($diff_num);
	628	//print_r($diffrec_full);
cd8bb8	629	$diffstr = serialize($diffrec_full);
MC	630	$username = $_SESSION['s']['user']['username'];
7fe908	631	$dbidx = $primary_field.':'.$primary_id;
f5b0ca	632
7fe908	633	if($action == 'INSERT') $action = 'i';
MC	634	if($action == 'UPDATE') $action = 'u';
	635	if($action == 'DELETE') $action = 'd';
cd8bb8	636	$sql = "INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES (?, ?, ?, ?, ?, ?, ?)";
MC	637	$app->db->query($sql, $db_table, $dbidx, $server_id, $action, time(), $username, $diffstr);
7fe908	638	}
f5b0ca	639
7fe908	640	return true;
MC	641	}
f5b0ca	642
7fe908	643	//** Inserts a record and saves the changes into the datalog
MC	644	public function datalogInsert($tablename, $insert_data, $index_field) {
	645	global $app;
e9a57d	646
94b44c	647	// Check fields
7272e4	648	if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename);
cd48c7	649	if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename);
94b44c	650
7fe908	651	if(is_array($insert_data)) {
ee260d	652	$key_str = '';
T	653	$val_str = '';
2af58c	654	$params = array($tablename);
MC	655	$v_params = array();
ee260d	656	foreach($insert_data as $key => $val) {
a6e3ae	657	$key_str .= '??,';
2af58c	658	$params[] = $key;
MC	659
	660	$val_str .= '?,';
	661	$v_params[] = $val;
ee260d	662	}
7fe908	663	$key_str = substr($key_str, 0, -1);
MC	664	$val_str = substr($val_str, 0, -1);
ee260d	665	$insert_data_str = '('.$key_str.') VALUES ('.$val_str.')';
2af58c	666	$this->query("INSERT INTO ?? $insert_data_str", true, $params + $v_params);
ee260d	667	} else {
2af58c	668	/* TODO: deprecate this method! */
ee260d	669	$insert_data_str = $insert_data;
2af58c	670	$this->query("INSERT INTO ?? $insert_data_str", $tablename);
3a11d2	671	$app->log("deprecated use of passing values to datalogInsert() - table " . $tablename, 1);
ee260d	672	}
2af58c	673
7fe908	674	$old_rec = array();
MC	675	$index_value = $this->insertID();
cd8bb8	676	$new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ? = ?", $tablename, $index_field, $index_value);
7fe908	677	$this->datalogSave($tablename, 'INSERT', $index_field, $index_value, $old_rec, $new_rec);
f5b0ca	678
7fe908	679	return $index_value;
MC	680	}
f5b0ca	681
7fe908	682	//** Updates a record and saves the changes into the datalog
MC	683	public function datalogUpdate($tablename, $update_data, $index_field, $index_value, $force_update = false) {
ee260d	684	global $app;
7fe908	685
94b44c	686	// Check fields
7272e4	687	if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename);
cd48c7	688	if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename);
94b44c	689
cd8bb8	690	$old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
7fe908	691
MC	692	if(is_array($update_data)) {
2af58c	693	$params = array($tablename);
ee260d	694	$update_data_str = '';
T	695	foreach($update_data as $key => $val) {
2af58c	696	$update_data_str .= '?? = ?,';
MC	697	$params[] = $key;
	698	$params[] = $val;
ee260d	699	}
2af58c	700	$params[] = $index_field;
MC	701	$params[] = $index_value;
7fe908	702	$update_data_str = substr($update_data_str, 0, -1);
2af58c	703	$this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", true, $params);
ee260d	704	} else {
2af58c	705	/* TODO: deprecate this method! */
ee260d	706	$update_data_str = $update_data;
2af58c	707	$this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", $tablename, $index_field, $index_value);
3a11d2	708	$app->log("deprecated use of passing values to datalogUpdate() - table " . $tablename, 1);
ee260d	709	}
f5b0ca	710
cd8bb8	711	$new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
7fe908	712	$this->datalogSave($tablename, 'UPDATE', $index_field, $index_value, $old_rec, $new_rec, $force_update);
f5b0ca	713
7fe908	714	return true;
MC	715	}
f5b0ca	716
7fe908	717	//** Deletes a record and saves the changes into the datalog
MC	718	public function datalogDelete($tablename, $index_field, $index_value) {
	719	global $app;
f5b0ca	720
94b44c	721	// Check fields
7272e4	722	if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename);
cd48c7	723	if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename);
94b44c	724
cd8bb8	725	$old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
MC	726	$this->query("DELETE FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
7fe908	727	$new_rec = array();
MC	728	$this->datalogSave($tablename, 'DELETE', $index_field, $index_value, $old_rec, $new_rec);
	729
	730	return true;
	731	}
	732
	733	//* get the current datalog status for the specified login (or currently logged in user)
	734	public function datalogStatus($login = '') {
	735	global $app;
	736
	737	$return = array('count' => 0, 'entries' => array());
	738	if($_SESSION['s']['user']['typ'] == 'admin') return $return; // these information should not be displayed to admin users
	739
	740	if($login == '' && isset($_SESSION['s']['user'])) {
	741	$login = $_SESSION['s']['user']['username'];
	742	}
	743
cd8bb8	744	$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	745	foreach($result as $row) {
MC	746	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
	747	$return['entries'][] = array('table' => $row['dbtable'], 'action' => $row['action'], 'count' => $row['cnt'], 'text' => $app->lng('datalog_status_' . $row['action'] . '_' . $row['dbtable']));
	748	$return['count'] += $row['cnt'];
	749	}
	750	unset($result);
	751
	752	return $return;
	753	}
f5b0ca	754
7fe908	755	/*
f5b0ca	756	$columns = array(action => add \| alter \| drop
N	757	name => Spaltenname
	758	name_new => neuer Spaltenname, nur bei 'alter' belegt
	759	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	760	typeValue => Wert z.B. bei Varchar
	761	defaultValue => Default Wert
	762	notNull => true \| false
	763	autoInc => true \| false
	764	option => unique \| primary \| index)
	765
	766
	767	*/
	768
7fe908	769	public function createTable($table_name, $columns) {
MC	770	$index = '';
cd8bb8	771	$sql = "CREATE TABLE ?? (";
7fe908	772	foreach($columns as $col){
MC	773	$sql .= $col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
f5b0ca	774
7fe908	775	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
MC	776	if($col['notNull'] == true) {
	777	$sql .= 'NOT NULL ';
	778	} else {
	779	$sql .= 'NULL ';
	780	}
	781	if($col['autoInc'] == true) $sql .= 'auto_increment ';
	782	$sql.= ',';
	783	// key Definitionen
	784	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	785	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	786	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
	787	}
	788	$sql .= $index;
	789	$sql = substr($sql, 0, -1);
	790	$sql .= ')';
cd8bb8	791	/* TODO: secure parameters */
MC	792	$this->query($sql, $table_name);
7fe908	793	return true;
f5b0ca	794	}
N	795
7fe908	796	/*
f5b0ca	797	$columns = array(action => add \| alter \| drop
N	798	name => Spaltenname
	799	name_new => neuer Spaltenname, nur bei 'alter' belegt
	800	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	801	typeValue => Wert z.B. bei Varchar
	802	defaultValue => Default Wert
	803	notNull => true \| false
	804	autoInc => true \| false
	805	option => unique \| primary \| index)
	806
	807
	808	*/
7fe908	809	public function alterTable($table_name, $columns) {
MC	810	$index = '';
cd8bb8	811	$sql = "ALTER TABLE ?? ";
7fe908	812	foreach($columns as $col){
MC	813	if($col['action'] == 'add') {
	814	$sql .= 'ADD '.$col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	815	} elseif ($col['action'] == 'alter') {
	816	$sql .= 'CHANGE '.$col['name'].' '.$col['name_new'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	817	} elseif ($col['action'] == 'drop') {
	818	$sql .= 'DROP '.$col['name'].' ';
	819	}
	820	if($col['action'] != 'drop') {
	821	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
	822	if($col['notNull'] == true) {
	823	$sql .= 'NOT NULL ';
	824	} else {
	825	$sql .= 'NULL ';
	826	}
	827	if($col['autoInc'] == true) $sql .= 'auto_increment ';
	828	$sql.= ',';
	829	// Index definitions
	830	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	831	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	832	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
	833	}
	834	}
	835	$sql .= $index;
	836	$sql = substr($sql, 0, -1);
cd8bb8	837	/* TODO: secure parameters */
7fe908	838	//die($sql);
cd8bb8	839	$this->query($sql, $table_name);
7fe908	840	return true;
f5b0ca	841	}
7fe908	842
MC	843	public function dropTable($table_name) {
	844	$this->check($table_name);
cd8bb8	845	$sql = "DROP TABLE ??";
MC	846	return $this->query($sql, $table_name);
f5b0ca	847	}
N	848
7fe908	849	// gibt Array mit Tabellennamen zur�ck
MC	850	public function getTables($database_name = '') {
cd8bb8	851	if(!is_object($this->_iConnId)) return false;
7fe908	852	if($database_name == '') $database_name = $this->dbName;
cd8bb8	853	$tb_names = $this->queryAllArray("SHOW TABLES FROM ??", $database_name);
7fe908	854	return $tb_names;
MC	855	}
f5b0ca	856
7fe908	857	// gibt Feldinformationen zur Tabelle zur�ck
MC	858	/*
f5b0ca	859	$columns = array(action => add \| alter \| drop
N	860	name => Spaltenname
	861	name_new => neuer Spaltenname, nur bei 'alter' belegt
	862	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	863	typeValue => Wert z.B. bei Varchar
	864	defaultValue => Default Wert
	865	notNull => true \| false
	866	autoInc => true \| false
	867	option => unique \| primary \| index)
	868
	869
	870	*/
	871
7fe908	872	function tableInfo($table_name) {
f5b0ca	873
7fe908	874	global $go_api, $go_info, $app;
MC	875	// Tabellenfelder einlesen
f5b0ca	876
cd8bb8	877	if($rows = $app->db->queryAllRecords('SHOW FIELDS FROM ??', $table_name)){
7fe908	878	foreach($rows as $row) {
MC	879	/*
f5b0ca	880	$name = $row[0];
N	881	$default = $row[4];
	882	$key = $row[3];
	883	$extra = $row[5];
	884	$isnull = $row[2];
	885	$type = $row[1];
bfcdef	886	*/
7fe908	887
MC	888	$name = $row['Field'];
	889	$default = $row['Default'];
	890	$key = $row['Key'];
	891	$extra = $row['Extra'];
	892	$isnull = $row['Null'];
	893	$type = $row['Type'];
f5b0ca	894
N	895
7fe908	896	$column = array();
f5b0ca	897
7fe908	898	$column['name'] = $name;
MC	899	//$column['type'] = $type;
	900	$column['defaultValue'] = $default;
	901	if(stristr($key, 'PRI')) $column['option'] = 'primary';
	902	if(stristr($isnull, 'YES')) {
	903	$column['notNull'] = false;
	904	} else {
	905	$column['notNull'] = true;
	906	}
	907	if($extra == 'auto_increment') $column['autoInc'] = true;
f5b0ca	908
N	909
7fe908	910	// Type in Metatype umsetzen
f5b0ca	911
7fe908	912	if(stristr($type, 'int(')) $metaType = 'int32';
MC	913	if(stristr($type, 'bigint')) $metaType = 'int64';
	914	if(stristr($type, 'char')) {
	915	$metaType = 'char';
	916	$tmp_typeValue = explode('(', $type);
	917	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
	918	}
	919	if(stristr($type, 'varchar')) {
	920	$metaType = 'varchar';
	921	$tmp_typeValue = explode('(', $type);
	922	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
	923	}
	924	if(stristr($type, 'text')) $metaType = 'text';
	925	if(stristr($type, 'double')) $metaType = 'double';
	926	if(stristr($type, 'blob')) $metaType = 'blob';
f5b0ca	927
N	928
7fe908	929	$column['type'] = $metaType;
f5b0ca	930
7fe908	931	$columns[] = $column;
MC	932	}
	933	return $columns;
	934	} else {
	935	return false;
	936	}
f5b0ca	937
N	938
7fe908	939	//$this->createTable('tester',$columns);
f5b0ca	940
7fe908	941	/*
f5b0ca	942	$result = mysql_list_fields($go_info["server"]["db_name"],$table_name);
N	943	$fields = mysql_num_fields ($result);
	944	$i = 0;
	945	$table = mysql_field_table ($result, $i);
	946	while ($i < $fields) {
	947	$name = mysql_field_name ($result, $i);
	948	$type = mysql_field_type ($result, $i);
	949	$len = mysql_field_len ($result, $i);
	950	$flags = mysql_field_flags ($result, $i);
	951	print_r($flags);
	952
	953	$columns = array(name => $name,
	954	type => "",
	955	defaultValue => "",
	956	isnull => 1,
	957	option => "");
	958	$returnvar[] = $columns;
	959
	960	$i++;
	961	}
	962	*/
	963
	964
	965
7fe908	966	}
f5b0ca	967
7fe908	968	public function mapType($metaType, $typeValue) {
MC	969	global $go_api;
	970	$metaType = strtolower($metaType);
	971	switch ($metaType) {
	972	case 'int16':
	973	return 'smallint';
	974	break;
	975	case 'int32':
	976	return 'int';
	977	break;
	978	case 'int64':
	979	return 'bigint';
	980	break;
	981	case 'double':
	982	return 'double';
	983	break;
	984	case 'char':
	985	return 'char';
	986	break;
	987	case 'varchar':
	988	if($typeValue < 1) die('Database failure: Lenght required for these data types.');
	989	return 'varchar('.$typeValue.')';
	990	break;
	991	case 'text':
	992	return 'text';
	993	break;
	994	case 'blob':
	995	return 'blob';
	996	break;
8748b3	997	case 'date':
TB	998	return 'date';
	999	break;
7fe908	1000	}
MC	1001	}
f5b0ca	1002
7fe908	1003	}
f5b0ca	1004
cd8bb8	1005	/**
MC	1006	* database query result class
	1007	*
	1008	* @package pxFramework
	1009	*
	1010	*/
	1011	class db_result {
	1012
	1013	/**
	1014	*
	1015	*
	1016	* @access private
	1017	*/
	1018	private $_iResId = null;
	1019	private $_iConnection = null;
	1020
	1021
	1022
	1023	/**
	1024	*
	1025	*
	1026	* @access private
	1027	*/
	1028	public function db_result($iResId, $iConnection) {
	1029	$this->_iResId = $iResId;
	1030	$this->_iConnection = $iConnection;
	1031	}
	1032
	1033
	1034
	1035	/**
	1036	* get count of result rows
	1037	*
	1038	* Returns the amount of rows in the result set
	1039	*
	1040	* @access public
	1041	* @return int amount of rows
	1042	*/
	1043	public function rows() {
	1044	if(!is_object($this->_iResId)) return 0;
	1045	$iRows = mysqli_num_rows($this->_iResId);
	1046	if(!$iRows) $iRows = 0;
	1047	return $iRows;
	1048	}
	1049
	1050
	1051
	1052	/**
	1053	* Get number of affected rows
	1054	*
	1055	* Returns the amount of rows affected by the previous query
	1056	*
	1057	* @access public
	1058	* @return int amount of affected rows
	1059	*/
	1060	public function affected() {
	1061	if(!is_object($this->_iConnection)) return 0;
	1062	$iRows = mysqli_affected_rows($this->_iConnection);
	1063	if(!$iRows) $iRows = 0;
	1064	return $iRows;
	1065	}
	1066
	1067
	1068
	1069	/**
	1070	* Frees the result set
	1071	*
	1072	* @access public
	1073	*/
	1074	public function free() {
	1075	if(!is_object($this->_iResId)) return;
	1076
	1077	mysqli_free_result($this->_iResId);
	1078	return;
	1079	}
	1080
	1081
	1082
	1083	/**
	1084	* Get a result row (associative)
	1085	*
	1086	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	1087	*
	1088	* @access public
	1089	* @return array result row
	1090	*/
	1091	public function get() {
	1092	$aItem = null;
	1093
	1094	if(is_object($this->_iResId)) {
	1095	$aItem = mysqli_fetch_assoc($this->_iResId);
	1096	if(!$aItem) $aItem = null;
	1097	}
	1098	return $aItem;
	1099	}
	1100
	1101
	1102
	1103	/**
	1104	* Get a result row (array with numeric index)
	1105	*
	1106	* @access public
	1107	* @return array result row
	1108	*/
	1109	public function getAsRow() {
	1110	$aItem = null;
	1111
	1112	if(is_object($this->_iResId)) {
	1113	$aItem = mysqli_fetch_row($this->_iResId);
	1114	if(!$aItem) $aItem = null;
	1115	}
	1116	return $aItem;
	1117	}
	1118
	1119	}
	1120
	1121	/**
	1122	* database query result class
	1123	*
	1124	* emulates a db result set out of an array so you can use array results and db results the same way
	1125	*
	1126	* @package pxFramework
	1127	* @see db_result
	1128	*
	1129	*
	1130	*/
	1131	class fakedb_result {
	1132
	1133	/**
	1134	*
	1135	*
	1136	* @access private
	1137	*/
	1138	private $aResultData = array();
	1139
	1140	/**
	1141	*
	1142	*
	1143	* @access private
	1144	*/
	1145	private $aLimitedData = array();
	1146
	1147
	1148
	1149	/**
	1150	*
	1151	*
	1152	* @access private
	1153	*/
	1154	public function fakedb_result($aData) {
	1155	$this->aResultData = $aData;
	1156	$this->aLimitedData = $aData;
	1157	reset($this->aLimitedData);
	1158	}
	1159
	1160
	1161
	1162	/**
	1163	* get count of result rows
	1164	*
	1165	* Returns the amount of rows in the result set
	1166	*
	1167	* @access public
	1168	* @return int amount of rows
	1169	*/
	1170	// Gibt die Anzahl Zeilen zurück
	1171	public function rows() {
	1172	return count($this->aLimitedData);
	1173	}
	1174
	1175
	1176
	1177	/**
	1178	* Frees the result set
	1179	*
	1180	* @access public
	1181	*/
	1182	// Gibt ein Ergebnisset frei
	1183	public function free() {
	1184	$this->aResultData = array();
	1185	$this->aLimitedData = array();
	1186	return;
	1187	}
	1188
	1189
	1190
	1191	/**
	1192	* Get a result row (associative)
	1193	*
	1194	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	1195	*
	1196	* @access public
	1197	* @return array result row
	1198	*/
	1199	// Gibt eine Ergebniszeile zurück
	1200	public function get() {
	1201	$aItem = null;
	1202
	1203	if(!is_array($this->aLimitedData)) return $aItem;
	1204
	1205	if(list($vKey, $aItem) = each($this->aLimitedData)) {
	1206	if(!$aItem) $aItem = null;
	1207	}
	1208	return $aItem;
	1209	}
	1210
	1211
	1212
	1213	/**
	1214	* Get a result row (array with numeric index)
	1215	*
	1216	* @access public
	1217	* @return array result row
	1218	*/
	1219	public function getAsRow() {
	1220	return $this->get();
	1221	}
	1222
	1223
	1224
	1225	/**
	1226	* Limit the result (like a LIMIT x,y in a SQL query)
	1227	*
	1228	* @access public
	1229	* @param int $iStart offset to start read
	1230	* @param int iLength amount of datasets to read
	1231	*/
	1232	public function limit_result($iStart, $iLength) {
	1233	$this->aLimitedData = array_slice($this->aResultData, $iStart, $iLength, true);
	1234	}
	1235
	1236	}
	1237
	1238
7fe908	1239	?>