From e1ceb050e19c7574bca146a8da7047ee4ff456b5 Mon Sep 17 00:00:00 2001 From: Marius Burkard <m.burkard@pixcept.de> Date: Sun, 10 Jul 2016 05:02:35 -0400 Subject: [PATCH] Merge branch 'stable-3.1' --- interface/lib/classes/db_mysql.inc.php | 1479 ++++++++++++++++++++++++++++++++++++++++++---------------- 1 files changed, 1,070 insertions(+), 409 deletions(-) diff --git a/interface/lib/classes/db_mysql.inc.php b/interface/lib/classes/db_mysql.inc.php index 8354be6..c74e6e0 100644 --- a/interface/lib/classes/db_mysql.inc.php +++ b/interface/lib/classes/db_mysql.inc.php @@ -29,202 +29,591 @@ class db extends mysqli { - private $dbHost = ''; // hostname 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 - 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 - private $errorNumber = 0; // last error number - public $errorMessage = ''; // last error message - private $errorLocation = '';// last error location - public $show_error_messages = true; // false in server, true in interface + /**#@+ + * @access private + */ + private $_iQueryId; + private $_iConnId; - // constructor - public function __construct($prefix = '') { - global $conf; - if($prefix != '') $prefix .= '_'; - $this->dbHost = $conf[$prefix.'db_host']; - $this->dbName = $conf[$prefix.'db_database']; - $this->dbUser = $conf[$prefix.'db_user']; - $this->dbPass = $conf[$prefix.'db_password']; - $this->dbCharset = $conf[$prefix.'db_charset']; - $this->dbNewLink = $conf[$prefix.'db_new_link']; - $this->dbClientFlags = $conf[$prefix.'db_client_flags']; - parent::__construct($conf[$prefix.'db_host'], $conf[$prefix.'db_user'],$conf[$prefix.'db_password'],$conf[$prefix.'db_database']); - if ($this->connect_error) { - $this->updateError('DB::__construct'); - return false; - } - parent::query( 'SET NAMES '.$this->dbCharset); - parent::query( "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 $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 function __destruct() { - $this->close(); // helps avoid memory leaks, and persitent connections that don't go away. - } - - // error handler - public function updateError($location) { - global $app; - - if($this->connect_error) { - $this->errorNumber = $this->connect_errno; - $this->errorMessage = $this->connect_error; - } else { - $this->errorNumber = $this->errno; - $this->errorMessage = $this->error; - } - - $this->errorLocation = $location; - if($this->errorNumber) { - $error_msg = $this->errorLocation .' '. $this->errorMessage; - // This right here will allow us to use the samefile for server & interface - if($this->show_error_messages) { - echo $error_msg; - } else if(method_exists($app, 'log')) { - $app->log($error_msg, LOGLEVEL_WARN); - } - } - } - - public function query($queryString) { - $this->queryId = parent::query($queryString); - $this->updateError('DB::query('.$queryString.') -> mysqli_query'); - if($this->errorNumber) debug_print_backtrace(); - if(!$this->queryId) { - return false; - } - $this->currentRow = 0; - return $this->queryId; - } - - // returns all records in an array - public function queryAllRecords($queryString) { - if(!$this->query($queryString)) - { - return false; - } - $ret = array(); - while($line = $this->nextRecord()) - { - $ret[] = $line; - } - return $ret; - } - - // returns one record in an array - public function queryOneRecord($queryString) { - if(!$this->query($queryString) || $this->numRows() == 0) - { - return false; - } - return $this->nextRecord(); - } - - // returns the next record in an array - public function nextRecord() { - $this->record = $this->queryId->fetch_assoc(); - $this->updateError('DB::nextRecord()-> mysql_fetch_array'); - if(!$this->record || !is_array($this->record)) - { - return false; - } - $this->currentRow++; - return $this->record; - } - - // returns number of rows returned by the last select query - public function numRows() { - return $this->queryId->num_rows; - } - - public function affectedRows() { - return $this->queryId->affected_rows; - } - - // returns mySQL insert id - public function insertID() { - return $this->insert_id; - } + public $show_error_messages = false; // false in server, true in interface - //* Function to quote strings - public function quote($formfield) { - return $this->escape_string($formfield); - } + /* 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 + private $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 + //// + */ - //* Function to unquotae strings - public function unquote($formfield) { - return stripslashes($formfield); - } + // constructor + public function __construct($prefix = '') { + global $conf; + if($prefix != '') $prefix .= '_'; + $this->dbHost = $conf[$prefix.'db_host']; + $this->dbPort = $conf[$prefix.'db_port']; + $this->dbName = $conf[$prefix.'db_database']; + $this->dbUser = $conf[$prefix.'db_user']; + $this->dbPass = $conf[$prefix.'db_password']; + $this->dbCharset = $conf[$prefix.'db_charset']; + $this->dbNewLink = $conf[$prefix.'db_new_link']; + $this->dbClientFlags = $conf[$prefix.'db_client_flags']; -public function toLower($record) { - if(is_array($record)) { - foreach($record as $key => $val) { - $key = strtolower($key); - $out[$key] = $val; - } - } - return $out; - } + $this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, '', (int)$this->dbPort); + $try = 0; + while((!is_object($this->_iConnId) || mysqli_connect_error()) && $try < 5) { + if($try > 0) sleep(1); - public function diffrec($record_old, $record_new) { - $diffrec_full = array(); - $diff_num = 0; + $try++; + $this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, '', (int)$this->dbPort); + } - if(is_array($record_old) && count($record_old) > 0) { - foreach($record_old as $key => $val) { - // if(!isset($record_new[$key]) || $record_new[$key] != $val) { - if($record_new[$key] != $val) { - // Record has changed - $diffrec_full['old'][$key] = $val; - $diffrec_full['new'][$key] = $record_new[$key]; - $diff_num++; - } else { - $diffrec_full['old'][$key] = $val; - $diffrec_full['new'][$key] = $val; + if(!is_object($this->_iConnId) || mysqli_connect_error()) { + $this->_iConnId = null; + $this->_sqlerror('Zugriff auf Datenbankserver fehlgeschlagen! / Database server not accessible!'); + return false; + } + if(!((bool)mysqli_query( $this->_iConnId, 'USE `' . $this->dbName . '`'))) { + $this->close(); + $this->_sqlerror('Datenbank nicht gefunden / Database not found'); + return false; + } + + $this->_setCharset(); } - } - } elseif(is_array($record_new)) { - foreach($record_new as $key => $val) { - if(isset($record_new[$key]) && @$record_old[$key] != $val) { - // Record has changed - $diffrec_full['new'][$key] = $val; - $diffrec_full['old'][$key] = @$record_old[$key]; - $diff_num++; - } else { - $diffrec_full['new'][$key] = $val; - $diffrec_full['old'][$key] = $val; - } + + public function __destruct() { + if($this->_iConnId) mysqli_close($this->_iConnId); } - } - return array('diff_num' => $diff_num, 'diff_rec' => $diffrec_full); + public function close() { + if($this->_iConnId) mysqli_close($this->_iConnId); + $this->_iConnId = null; + } - } + public function _build_query_string($sQuery = '') { + $iArgs = func_num_args(); + if($iArgs > 1) { + $aArgs = func_get_args(); - //** Function to fill the datalog with a full differential record. - public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) { - global $app,$conf; + 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! + } - // Insert backticks only for incomplete table names. - if(stristr($db_table,'.')) { - $escape = ''; - } else { - $escape = '`'; - } + $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)) { + 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 + $string = trim($string); + if(substr($string,-1) == ';') $string = substr($string,0,-1); + + // Save original string + $string_orig = $string; + + //echo $string; + $chars = array(';', '#', '/*', '*/', '--', '\\\'', '\\"'); + + $string = str_replace('\\\\', '', $string); + $string = preg_replace('/(^|[^\\\])([\'"])\\2/is', '$1', $string); + $string = preg_replace('/(^|[^\\\])([\'"])(.*?[^\\\])\\2/is', '$1', $string); + $ok = true; + + if(substr_count($string, "`") % 2 != 0 || substr_count($string, "'") % 2 != 0 || substr_count($string, '"') % 2 != 0) { + $app->log("SQL injection warning (" . $string_orig . ")",2); + $ok = false; + } else { + foreach($chars as $char) { + if(strpos($string, $char) !== false) { + $ok = false; + $app->log("SQL injection warning (" . $string_orig . ")",2); + break; + } + } + } + if($ok == true) { + return true; + } else { + if($ids_config['sql_scan_action'] == 'warn') { + // we return false in warning level. + return false; + } else { + // if sql action = 'block' or anything else then stop here. + $app->error('Possible SQL injection. All actions have been logged.'); + } + } + } + } + } + + private function _query($sQuery = '') { + global $app; + + if ($sQuery == '') { + $this->_sqlerror('Keine Anfrage angegeben / No query given'); + return false; + } + + $try = 0; + do { + $try++; + $ok = mysqli_ping($this->_iConnId); + if(!$ok) { + if(!mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, $this->dbName, (int)$this->dbPort)) { + if($try > 4) { + $this->_sqlerror('DB::query -> reconnect'); + return false; + } else { + sleep(1); + } + } else { + $this->_setCharset(); + $ok = true; + } + } + } while($ok == false); + + $aArgs = func_get_args(); + $sQuery = call_user_func_array(array(&$this, '_build_query_string'), $aArgs); + $this->securityScan($sQuery); + + $this->_iQueryId = @mysqli_query($this->_iConnId, $sQuery); + if (!$this->_iQueryId) { + $this->_sqlerror('Falsche Anfrage / Wrong Query', 'SQL-Query = ' . $sQuery); + return false; + } + + return is_bool($this->_iQueryId) ? $this->_iQueryId : new db_result($this->_iQueryId, $this->_iConnId); + } + + /**#@-*/ + + + + + + /** + * Executes a query + * + * Executes a given query string, has a variable amount of parameters: + * - 1 parameter + * executes the given query + * - 2 parameters + * executes the given query, replaces the first ? in the query with the second parameter + * - 3 parameters + * 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 + * - 4 or more parameters + * all ? in the query are replaced from left to right by the parameters 2 to x + * + * @access public + * @param string $sQuery query string + * @param mixed ... one or more parameters + * @return db_result the result object of the query + */ + + + public function query($sQuery = '') { + $aArgs = func_get_args(); + return call_user_func_array(array(&$this, '_query'), $aArgs); + } + + /** + * Execute a query and get first result array + * + * Executes a query and returns the first result row as an array + * This is like calling $result = $db->query(), $result->get(), $result->free() + * Use of this function @see query + * + * @access public + * @param string $sQuery query to execute + * @param ... further params (see query()) + * @return array result row or NULL if none found + */ + public function queryOneRecord($sQuery = '') { + if(!preg_match('/limit \d+\s*,\s*\d+$/i', $sQuery)) $sQuery .= ' LIMIT 0,1'; + + $aArgs = func_get_args(); + $oResult = call_user_func_array(array(&$this, 'query'), $aArgs); + if(!$oResult) return null; + + $aReturn = $oResult->get(); + $oResult->free(); + + return $aReturn; + } + + public function queryOne($sQuery = '') { + return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args()); + } + + public function query_one($sQuery = '') { + return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args()); + } + + /** + * Execute a query and return all rows + * + * Executes a query and returns all result rows in an array + * <strong>Use this with extreme care!!!</strong> Uses lots of memory on big result sets. + * + * @access public + * @param string $sQuery query to execute + * @param ... further params (see query()) + * @return array all the rows in the result set + */ + public function queryAllRecords($sQuery = '') { + $aArgs = func_get_args(); + $oResult = call_user_func_array(array(&$this, 'query'), $aArgs); + if(!$oResult) return array(); + + $aResults = array(); + while($aRow = $oResult->get()) { + $aResults[] = $aRow; + } + $oResult->free(); + + return $aResults; + } + + public function queryAll($sQuery = '') { + return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args()); + } + + public function query_all($sQuery = '') { + return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args()); + } + + /** + * Execute a query and return all rows as simple array + * + * Executes a query and returns all result rows in an array with elements + * <strong>Only first column is returned</strong> Uses lots of memory on big result sets. + * + * @access public + * @param string $sQuery query to execute + * @param ... further params (see query()) + * @return array all the rows in the result set + */ + public function queryAllArray($sQuery = '') { + $aArgs = func_get_args(); + $oResult = call_user_func_array(array(&$this, 'query'), $aArgs); + if(!$oResult) return array(); + + $aResults = array(); + while($aRow = $oResult->get()) { + $aResults[] = reset($aRow); + } + $oResult->free(); + + return $aResults; + } + + public function query_all_array($sQuery = '') { + return $this->queryAllArray($sQuery); + } + + + + /** + * Get id of last inserted row + * + * Gives you the id of the last inserted row in a table with an auto-increment primary key + * + * @access public + * @return int id of last inserted row or 0 if none + */ + public function insert_id() { + $iRes = mysqli_query($this->_iConnId, 'SELECT LAST_INSERT_ID() as `newid`'); + if(!is_object($iRes)) return false; + + $aReturn = mysqli_fetch_assoc($iRes); + mysqli_free_result($iRes); + + return $aReturn['newid']; + } + + + + /** + * get affected row count + * + * Gets the amount of rows affected by the previous query + * + * @access public + * @return int affected rows + */ + public function affected() { + if(!is_object($this->_iConnId)) return 0; + $iRows = mysqli_affected_rows($this->_iConnId); + if(!$iRows) $iRows = 0; + return $iRows; + } + + + /** + * check if a utf8 string is valid + * + * @access public + * @param string $string the string to check + * @return bool true if it is valid utf8, false otherwise + */ + private function check_utf8($str) { + $len = strlen($str); + for($i = 0; $i < $len; $i++){ + $c = ord($str[$i]); + if ($c > 128) { + if (($c > 247)) return false; + elseif ($c > 239) $bytes = 4; + elseif ($c > 223) $bytes = 3; + elseif ($c > 191) $bytes = 2; + else return false; + if (($i + $bytes) > $len) return false; + while ($bytes > 1) { + $i++; + $b = ord($str[$i]); + if ($b < 128 || $b > 191) return false; + $bytes--; + } + } + } + return true; + } // end of check_utf8 + + /** + * Escape a string for usage in a query + * + * @access public + * @param string $sString query string to escape + * @return string escaped string + */ + public function escape($sString) { + global $app; + if(!is_string($sString) && !is_numeric($sString)) { + $app->log('NON-String given in escape function! (' . gettype($sString) . ')', LOGLEVEL_INFO); + //$sAddMsg = getDebugBacktrace(); + $app->log($sAddMsg, LOGLEVEL_DEBUG); + $sString = ''; + } + + $cur_encoding = mb_detect_encoding($sString); + if($cur_encoding != "UTF-8") { + if($cur_encoding != 'ASCII') { + if(is_object($app) && method_exists($app, 'log')) $app->log('String ' . substr($sString, 0, 25) . '... is ' . $cur_encoding . '.', LOGLEVEL_INFO); + if($cur_encoding) $sString = mb_convert_encoding($sString, 'UTF-8', $cur_encoding); + else $sString = mb_convert_encoding($sString, 'UTF-8'); + } + } elseif(!$this->check_utf8($sString)) { + $sString = utf8_encode($sString); + } + + if($this->_iConnId) return mysqli_real_escape_string($this->_iConnId, $sString); + else return addslashes($sString); + } + + /** + * + * + * @access private + */ + private function _sqlerror($sErrormsg = 'Unbekannter Fehler', $sAddMsg = '') { + global $app, $conf; + + $mysql_error = (is_object($this->_iConnId) ? mysqli_error($this->_iConnId) : mysqli_connect_error()); + $mysql_errno = (is_object($this->_iConnId) ? mysqli_errno($this->_iConnId) : mysqli_connect_errno()); + $this->errorMessage = $mysql_error; + + //$sAddMsg .= getDebugBacktrace(); + + if($this->show_error_messages && $conf['demo_mode'] === false) { + echo $sErrormsg . $sAddMsg; + } else if(is_object($app) && method_exists($app, 'log')) { + $app->log($sErrormsg . $sAddMsg . ' -> ' . $mysql_errno . ' (' . $mysql_error . ')', LOGLEVEL_WARN); + } + } + + public function affectedRows() { + return $this->affected(); + } + + // returns mySQL insert id + public function insertID() { + return $this->insert_id(); + } + + + //* Function to quote strings + public function quote($formfield) { + return $this->escape($formfield); + } + + //* Function to unquotae strings + public function unquote($formfield) { + return stripslashes($formfield); + } + + public function toLower($record) { + if(is_array($record)) { + foreach($record as $key => $val) { + $key = strtolower($key); + $out[$key] = $val; + } + } + return $out; + } + + public function insertFromArray($tablename, $data) { + if(!is_array($data)) return false; + + $k_query = ''; + $v_query = ''; + + $params = array($tablename); + $v_params = array(); + + foreach($data as $key => $value) { + $k_query .= ($k_query != '' ? ', ' : '') . '??'; + $v_query .= ($v_query != '' ? ', ' : '') . '?'; + $params[] = $key; + $v_params[] = $value; + } + + $query = 'INSERT INTO ?? (' . $k_query . ') VALUES (' . $v_query . ')'; + return $this->query($query, true, array_merge($params, $v_params)); + } + + public function diffrec($record_old, $record_new) { + $diffrec_full = array(); + $diff_num = 0; + + if(is_array($record_old) && count($record_old) > 0) { + foreach($record_old as $key => $val) { + // if(!isset($record_new[$key]) || $record_new[$key] != $val) { + if(@$record_new[$key] != $val) { + // Record has changed + $diffrec_full['old'][$key] = $val; + $diffrec_full['new'][$key] = @$record_new[$key]; + $diff_num++; + } else { + $diffrec_full['old'][$key] = $val; + $diffrec_full['new'][$key] = $val; + } + } + } elseif(is_array($record_new)) { + foreach($record_new as $key => $val) { + if(isset($record_new[$key]) && @$record_old[$key] != $val) { + // Record has changed + $diffrec_full['new'][$key] = $val; + $diffrec_full['old'][$key] = @$record_old[$key]; + $diff_num++; + } else { + $diffrec_full['new'][$key] = $val; + $diffrec_full['old'][$key] = $val; + } + } + } + + return array('diff_num' => $diff_num, 'diff_rec' => $diffrec_full); + + } + + //** Function to fill the datalog with a full differential record. + public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) { + global $app, $conf; + + // Check fields + if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$db_table)) $app->error('Invalid table name '.$db_table); + if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$primary_field)) $app->error('Invalid primary field '.$primary_field.' in table '.$db_table); + + $primary_id = intval($primary_id); if($force_update == true) { //* We force a update even if no record has changed - $diffrec_full = array('new' => $record_new,'old' => $record_old); + $diffrec_full = array('new' => $record_new, 'old' => $record_old); $diff_num = count($record_new); } else { //* get the difference record between old and new record @@ -234,113 +623,141 @@ unset($tmp); } - // Insert the server_id, if the record has a server_id - $server_id = (isset($record_old['server_id']) && $record_old['server_id'] > 0)?$record_old['server_id']:0; - if(isset($record_new['server_id'])) $server_id = $record_new['server_id']; + // Insert the server_id, if the record has a server_id + $server_id = (isset($record_old['server_id']) && $record_old['server_id'] > 0)?$record_old['server_id']:0; + if(isset($record_new['server_id'])) $server_id = $record_new['server_id']; - if($diff_num > 0) { - //print_r($diff_num); - //print_r($diffrec_full); - $diffstr = $app->db->quote(serialize($diffrec_full)); - $username = $app->db->quote($_SESSION['s']['user']['username']); - $dbidx = $primary_field.':'.$primary_id; + if($diff_num > 0) { + //print_r($diff_num); + //print_r($diffrec_full); + $diffstr = serialize($diffrec_full); + $username = $_SESSION['s']['user']['username']; + $dbidx = $primary_field.':'.$primary_id; - if($action == 'INSERT') $action = 'i'; - if($action == 'UPDATE') $action = 'u'; - if($action == 'DELETE') $action = 'd'; - $sql = "INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES ('".$db_table."','$dbidx','$server_id','$action','".time()."','$username','$diffstr')"; - $app->db->query($sql); - } + if($action == 'INSERT') $action = 'i'; + if($action == 'UPDATE') $action = 'u'; + if($action == 'DELETE') $action = 'd'; + $sql = "INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES (?, ?, ?, ?, ?, ?, ?)"; + $app->db->query($sql, $db_table, $dbidx, $server_id, $action, time(), $username, $diffstr); + } - return true; - } + return true; + } - //** Inserts a record and saves the changes into the datalog - public function datalogInsert($tablename, $insert_data, $index_field) { - global $app; - - if(is_array($insert_data)) { + //** Inserts a record and saves the changes into the datalog + public function datalogInsert($tablename, $insert_data, $index_field) { + global $app; + + // Check fields + if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename); + if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename); + + if(is_array($insert_data)) { $key_str = ''; $val_str = ''; + $params = array($tablename); + $v_params = array(); foreach($insert_data as $key => $val) { - $key_str .= "`".$key ."`,"; - $val_str .= "'".$this->quote($val)."',"; + $key_str .= '??,'; + $params[] = $key; + + $val_str .= '?,'; + $v_params[] = $val; } - $key_str = substr($key_str,0,-1); - $val_str = substr($val_str,0,-1); + $key_str = substr($key_str, 0, -1); + $val_str = substr($val_str, 0, -1); $insert_data_str = '('.$key_str.') VALUES ('.$val_str.')'; + $this->query("INSERT INTO ?? $insert_data_str", true, array_merge($params, $v_params)); } else { + /* TODO: deprecate this method! */ $insert_data_str = $insert_data; - } - - $old_rec = array(); - $this->query("INSERT INTO $tablename $insert_data_str"); - $index_value = $this->insertID(); - $new_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'"); - $this->datalogSave($tablename, 'INSERT', $index_field, $index_value, $old_rec, $new_rec); - - return $index_value; - } - - //** Updates a record and saves the changes into the datalog - public function datalogUpdate($tablename, $update_data, $index_field, $index_value, $force_update = false) { - global $app; - - $old_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'"); - - if(is_array($update_data)) { - $update_data_str = ''; - foreach($update_data as $key => $val) { - $update_data_str .= "`".$key ."` = '".$this->quote($val)."',"; - } - $update_data_str = substr($update_data_str,0,-1); - } else { - $update_data_str = $update_data; + $this->query("INSERT INTO ?? $insert_data_str", $tablename); + $app->log("deprecated use of passing values to datalogInsert() - table " . $tablename, 1); } - $this->query("UPDATE $tablename SET $update_data WHERE $index_field = '$index_value'"); - $new_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'"); - $this->datalogSave($tablename, 'UPDATE', $index_field, $index_value, $old_rec, $new_rec, $force_update); + $old_rec = array(); + $index_value = $this->insertID(); + $new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value); + $this->datalogSave($tablename, 'INSERT', $index_field, $index_value, $old_rec, $new_rec); - return true; - } + return $index_value; + } - //** Deletes a record and saves the changes into the datalog - public function datalogDelete($tablename, $index_field, $index_value) { - global $app; + //** Updates a record and saves the changes into the datalog + public function datalogUpdate($tablename, $update_data, $index_field, $index_value, $force_update = false) { + global $app; - $old_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'"); - $this->query("DELETE FROM $tablename WHERE $index_field = '$index_value'"); - $new_rec = array(); - $this->datalogSave($tablename, 'DELETE', $index_field, $index_value, $old_rec, $new_rec); + // Check fields + if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename); + if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename); + + $old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value); - return true; - } + if(is_array($update_data)) { + $params = array($tablename); + $update_data_str = ''; + foreach($update_data as $key => $val) { + $update_data_str .= '?? = ?,'; + $params[] = $key; + $params[] = $val; + } + $params[] = $index_field; + $params[] = $index_value; + $update_data_str = substr($update_data_str, 0, -1); + $this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", true, $params); + } else { + /* TODO: deprecate this method! */ + $update_data_str = $update_data; + $this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", $tablename, $index_field, $index_value); + $app->log("deprecated use of passing values to datalogUpdate() - table " . $tablename, 1); + } + $new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value); + $this->datalogSave($tablename, 'UPDATE', $index_field, $index_value, $old_rec, $new_rec, $force_update); - public function freeResult($query) - { - if(is_object($query) && (get_class($query) == "mysqli_result")) { - $query->free(); - return true; - } else { - return false; - } - } + return true; + } - /* TODO: Does anything use this? */ - public function delete() { + //** Deletes a record and saves the changes into the datalog + public function datalogDelete($tablename, $index_field, $index_value) { + global $app; - } + // Check fields + if(!preg_match('/^[a-zA-Z0-9\-\_\.]{1,64}$/',$tablename)) $app->error('Invalid table name '.$tablename); + if(!preg_match('/^[a-zA-Z0-9\-\_]{1,64}$/',$index_field)) $app->error('Invalid index field '.$index_field.' in table '.$tablename); + + $old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value); + $this->query("DELETE FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value); + $new_rec = array(); + $this->datalogSave($tablename, 'DELETE', $index_field, $index_value, $old_rec, $new_rec); - /* TODO: Does anything use this? */ - public function Transaction($action) { - //action = begin, commit oder rollback + return true; + } - } + //* get the current datalog status for the specified login (or currently logged in user) + public function datalogStatus($login = '') { + global $app; - /* + $return = array('count' => 0, 'entries' => array()); + if($_SESSION['s']['user']['typ'] == 'admin') return $return; // these information should not be displayed to admin users + + if($login == '' && isset($_SESSION['s']['user'])) { + $login = $_SESSION['s']['user']['username']; + } + + $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); + foreach($result as $row) { + 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 + $return['entries'][] = array('table' => $row['dbtable'], 'action' => $row['action'], 'count' => $row['cnt'], 'text' => $app->lng('datalog_status_' . $row['action'] . '_' . $row['dbtable'])); + $return['count'] += $row['cnt']; + } + unset($result); + + return $return; + } + + /* $columns = array(action => add | alter | drop name => Spaltenname name_new => neuer Spaltenname, nur bei 'alter' belegt @@ -354,33 +771,34 @@ */ - public function createTable($table_name,$columns) { - $index = ''; - $sql = "CREATE TABLE $table_name ("; - foreach($columns as $col){ - $sql .= $col['name'].' '.$this->mapType($col['type'],$col['typeValue']).' '; + public function createTable($table_name, $columns) { + $index = ''; + $sql = "CREATE TABLE ?? ("; + foreach($columns as $col){ + $sql .= $col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' '; - if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' "; - if($col['notNull'] == true) { - $sql .= 'NOT NULL '; - } else { - $sql .= 'NULL '; + if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' "; + if($col['notNull'] == true) { + $sql .= 'NOT NULL '; + } else { + $sql .= 'NULL '; + } + if($col['autoInc'] == true) $sql .= 'auto_increment '; + $sql.= ','; + // key Definitionen + if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),'; + if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),'; + if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),'; + } + $sql .= $index; + $sql = substr($sql, 0, -1); + $sql .= ')'; + /* TODO: secure parameters */ + $this->query($sql, $table_name); + return true; } - if($col['autoInc'] == true) $sql .= 'auto_increment '; - $sql.= ','; - // key Definitionen - if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),'; - if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),'; - if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),'; - } - $sql .= $index; - $sql = substr($sql,0,-1); - $sql .= ')'; - $this->query($sql); - return true; - } - /* + /* $columns = array(action => add | alter | drop name => Spaltenname name_new => neuer Spaltenname, nur bei 'alter' belegt @@ -393,59 +811,56 @@ */ - public function alterTable($table_name,$columns) { - $index = ''; - $sql = "ALTER TABLE $table_name "; - foreach($columns as $col){ - if($col['action'] == 'add') { - $sql .= 'ADD '.$col['name'].' '.$this->mapType($col['type'],$col['typeValue']).' '; - } elseif ($col['action'] == 'alter') { - $sql .= 'CHANGE '.$col['name'].' '.$col['name_new'].' '.$this->mapType($col['type'],$col['typeValue']).' '; - } elseif ($col['action'] == 'drop') { - $sql .= 'DROP '.$col['name'].' '; + public function alterTable($table_name, $columns) { + $index = ''; + $sql = "ALTER TABLE ?? "; + foreach($columns as $col){ + if($col['action'] == 'add') { + $sql .= 'ADD '.$col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' '; + } elseif ($col['action'] == 'alter') { + $sql .= 'CHANGE '.$col['name'].' '.$col['name_new'].' '.$this->mapType($col['type'], $col['typeValue']).' '; + } elseif ($col['action'] == 'drop') { + $sql .= 'DROP '.$col['name'].' '; + } + if($col['action'] != 'drop') { + if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' "; + if($col['notNull'] == true) { + $sql .= 'NOT NULL '; + } else { + $sql .= 'NULL '; + } + if($col['autoInc'] == true) $sql .= 'auto_increment '; + $sql.= ','; + // Index definitions + if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),'; + if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),'; + if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),'; + } + } + $sql .= $index; + $sql = substr($sql, 0, -1); + /* TODO: secure parameters */ + //die($sql); + $this->query($sql, $table_name); + return true; } - if($col['action'] != 'drop') { - if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' "; - if($col['notNull'] == true) { - $sql .= 'NOT NULL '; - } else { - $sql .= 'NULL '; - } - if($col['autoInc'] == true) $sql .= 'auto_increment '; - $sql.= ','; - // Index definitions - if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),'; - if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),'; - if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),'; + + public function dropTable($table_name) { + $this->check($table_name); + $sql = "DROP TABLE ??"; + return $this->query($sql, $table_name); } - } - $sql .= $index; - $sql = substr($sql,0,-1); - //die($sql); - $this->query($sql); - return true; - } + // gibt Array mit Tabellennamen zur�ck + public function getTables($database_name = '') { + if(!is_object($this->_iConnId)) return false; + if($database_name == '') $database_name = $this->dbName; + $tb_names = $this->queryAllArray("SHOW TABLES FROM ??", $database_name); + return $tb_names; + } - public function dropTable($table_name) { - $this->check($table_name); - $sql = "DROP TABLE '". $table_name."'"; - return $this->query($sql); - } - - // gibt Array mit Tabellennamen zur�ck - public function getTables($database_name = '') { - - if($database_name == '') $database_name = $this->dbName; - $result = parent::query("SHOW TABLES FROM $database_name"); - for ($i = 0; $i < $result->num_rows; $i++) { - $tb_names[$i] = (($result->data_seek( $i) && (($___mysqli_tmp = $result->fetch_row()) !== NULL)) ? array_shift($___mysqli_tmp) : false); - } - return $tb_names; - } - - // gibt Feldinformationen zur Tabelle zur�ck - /* + // gibt Feldinformationen zur Tabelle zur�ck + /* $columns = array(action => add | alter | drop name => Spaltenname name_new => neuer Spaltenname, nur bei 'alter' belegt @@ -459,67 +874,76 @@ */ - function tableInfo($table_name) { + function tableInfo($table_name) { - global $go_api,$go_info; - // Tabellenfelder einlesen + global $go_api, $go_info, $app; + // Tabellenfelder einlesen - if($rows = $go_api->db->queryAllRecords('SHOW FIELDS FROM '.$table_name)){ - foreach($rows as $row) { + if($rows = $app->db->queryAllRecords('SHOW FIELDS FROM ??', $table_name)){ + foreach($rows as $row) { + /* $name = $row[0]; $default = $row[4]; $key = $row[3]; $extra = $row[5]; $isnull = $row[2]; $type = $row[1]; + */ + + $name = $row['Field']; + $default = $row['Default']; + $key = $row['Key']; + $extra = $row['Extra']; + $isnull = $row['Null']; + $type = $row['Type']; - $column = array(); + $column = array(); - $column['name'] = $name; - //$column['type'] = $type; - $column['defaultValue'] = $default; - if(stristr($key,'PRI')) $column['option'] = 'primary'; - if(stristr($isnull,'YES')) { - $column['notNull'] = false; - } else { - $column['notNull'] = true; - } - if($extra == 'auto_increment') $column['autoInc'] = true; + $column['name'] = $name; + //$column['type'] = $type; + $column['defaultValue'] = $default; + if(stristr($key, 'PRI')) $column['option'] = 'primary'; + if(stristr($isnull, 'YES')) { + $column['notNull'] = false; + } else { + $column['notNull'] = true; + } + if($extra == 'auto_increment') $column['autoInc'] = true; - // Type in Metatype umsetzen + // Type in Metatype umsetzen - if(stristr($type,'int(')) $metaType = 'int32'; - if(stristr($type,'bigint')) $metaType = 'int64'; - if(stristr($type,'char')) { - $metaType = 'char'; - $tmp_typeValue = explode('(',$type); - $column['typeValue'] = substr($tmp_typeValue[1],0,-1); - } - if(stristr($type,'varchar')) { - $metaType = 'varchar'; - $tmp_typeValue = explode('(',$type); - $column['typeValue'] = substr($tmp_typeValue[1],0,-1); - } - if(stristr($type,'text')) $metaType = 'text'; - if(stristr($type,'double')) $metaType = 'double'; - if(stristr($type,'blob')) $metaType = 'blob'; + if(stristr($type, 'int(')) $metaType = 'int32'; + if(stristr($type, 'bigint')) $metaType = 'int64'; + if(stristr($type, 'char')) { + $metaType = 'char'; + $tmp_typeValue = explode('(', $type); + $column['typeValue'] = substr($tmp_typeValue[1], 0, -1); + } + if(stristr($type, 'varchar')) { + $metaType = 'varchar'; + $tmp_typeValue = explode('(', $type); + $column['typeValue'] = substr($tmp_typeValue[1], 0, -1); + } + if(stristr($type, 'text')) $metaType = 'text'; + if(stristr($type, 'double')) $metaType = 'double'; + if(stristr($type, 'blob')) $metaType = 'blob'; - $column['type'] = $metaType; + $column['type'] = $metaType; - $columns[] = $column; - } - return $columns; - } else { - return false; - } + $columns[] = $column; + } + return $columns; + } else { + return false; + } - //$this->createTable('tester',$columns); + //$this->createTable('tester',$columns); - /* + /* $result = mysql_list_fields($go_info["server"]["db_name"],$table_name); $fields = mysql_num_fields ($result); $i = 0; @@ -544,40 +968,277 @@ - } + } - public function mapType($metaType,$typeValue) { - global $go_api; - $metaType = strtolower($metaType); - switch ($metaType) { - case 'int16': - return 'smallint'; - break; - case 'int32': - return 'int'; - break; - case 'int64': - return 'bigint'; - break; - case 'double': - return 'double'; - break; - case 'char': - return 'char'; - break; - case 'varchar': - if($typeValue < 1) die('Database failure: Lenght required for these data types.'); - return 'varchar('.$typeValue.')'; - break; - case 'text': - return 'text'; - break; - case 'blob': - return 'blob'; - break; - } - } + public function mapType($metaType, $typeValue) { + global $go_api; + $metaType = strtolower($metaType); + switch ($metaType) { + case 'int16': + return 'smallint'; + break; + case 'int32': + return 'int'; + break; + case 'int64': + return 'bigint'; + break; + case 'double': + return 'double'; + break; + case 'char': + return 'char'; + break; + case 'varchar': + if($typeValue < 1) die('Database failure: Lenght required for these data types.'); + return 'varchar('.$typeValue.')'; + break; + case 'text': + return 'text'; + break; + case 'blob': + return 'blob'; + break; + case 'date': + return 'date'; + break; + } + } - } +} - ?> \ No newline at end of file +/** + * database query result class + * + * @package pxFramework + * + */ +class db_result { + + /** + * + * + * @access private + */ + private $_iResId = null; + private $_iConnection = null; + + + + /** + * + * + * @access private + */ + public function db_result($iResId, $iConnection) { + $this->_iResId = $iResId; + $this->_iConnection = $iConnection; + } + + + + /** + * get count of result rows + * + * Returns the amount of rows in the result set + * + * @access public + * @return int amount of rows + */ + public function rows() { + if(!is_object($this->_iResId)) return 0; + $iRows = mysqli_num_rows($this->_iResId); + if(!$iRows) $iRows = 0; + return $iRows; + } + + + + /** + * Get number of affected rows + * + * Returns the amount of rows affected by the previous query + * + * @access public + * @return int amount of affected rows + */ + public function affected() { + if(!is_object($this->_iConnection)) return 0; + $iRows = mysqli_affected_rows($this->_iConnection); + if(!$iRows) $iRows = 0; + return $iRows; + } + + + + /** + * Frees the result set + * + * @access public + */ + public function free() { + if(!is_object($this->_iResId)) return; + + mysqli_free_result($this->_iResId); + return; + } + + + + /** + * Get a result row (associative) + * + * Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... } + * + * @access public + * @return array result row + */ + public function get() { + $aItem = null; + + if(is_object($this->_iResId)) { + $aItem = mysqli_fetch_assoc($this->_iResId); + if(!$aItem) $aItem = null; + } + return $aItem; + } + + + + /** + * Get a result row (array with numeric index) + * + * @access public + * @return array result row + */ + public function getAsRow() { + $aItem = null; + + if(is_object($this->_iResId)) { + $aItem = mysqli_fetch_row($this->_iResId); + if(!$aItem) $aItem = null; + } + return $aItem; + } + +} + +/** + * database query result class + * + * emulates a db result set out of an array so you can use array results and db results the same way + * + * @package pxFramework + * @see db_result + * + * + */ +class fakedb_result { + + /** + * + * + * @access private + */ + private $aResultData = array(); + + /** + * + * + * @access private + */ + private $aLimitedData = array(); + + + + /** + * + * + * @access private + */ + public function fakedb_result($aData) { + $this->aResultData = $aData; + $this->aLimitedData = $aData; + reset($this->aLimitedData); + } + + + + /** + * get count of result rows + * + * Returns the amount of rows in the result set + * + * @access public + * @return int amount of rows + */ + // Gibt die Anzahl Zeilen zurück + public function rows() { + return count($this->aLimitedData); + } + + + + /** + * Frees the result set + * + * @access public + */ + // Gibt ein Ergebnisset frei + public function free() { + $this->aResultData = array(); + $this->aLimitedData = array(); + return; + } + + + + /** + * Get a result row (associative) + * + * Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... } + * + * @access public + * @return array result row + */ + // Gibt eine Ergebniszeile zurück + public function get() { + $aItem = null; + + if(!is_array($this->aLimitedData)) return $aItem; + + if(list($vKey, $aItem) = each($this->aLimitedData)) { + if(!$aItem) $aItem = null; + } + return $aItem; + } + + + + /** + * Get a result row (array with numeric index) + * + * @access public + * @return array result row + */ + public function getAsRow() { + return $this->get(); + } + + + + /** + * Limit the result (like a LIMIT x,y in a SQL query) + * + * @access public + * @param int $iStart offset to start read + * @param int iLength amount of datasets to read + */ + public function limit_result($iStart, $iLength) { + $this->aLimitedData = array_slice($this->aResultData, $iStart, $iLength, true); + } + +} + + +?> -- Gitblit v1.9.1