| | |
| | | private $_iConnId; |
| | | |
| | | private $dbHost = ''; // hostname of the MySQL server |
| | | private $dbPort = ''; // port of the MySQL server |
| | | private $dbName = ''; // logical database name on that server |
| | | private $dbUser = ''; // database authorized user |
| | | private $dbPass = ''; // user's password |
| | |
| | | private $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 |
| | | //// |
| | |
| | | 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->dbNewLink = $conf[$prefix.'db_new_link']; |
| | | $this->dbClientFlags = $conf[$prefix.'db_client_flags']; |
| | | |
| | | $this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass); |
| | | $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); |
| | | |
| | | $try++; |
| | | $this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass); |
| | | $this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, '', (int)$this->dbPort); |
| | | } |
| | | |
| | | if(!is_object($this->_iConnId) || mysqli_connect_error()) { |
| | |
| | | $this->_sqlerror('Zugriff auf Datenbankserver fehlgeschlagen! / Database server not accessible!'); |
| | | return false; |
| | | } |
| | | if(!((bool)mysqli_query( $this->_iConnId, "USE $this->dbName"))) { |
| | | if(!((bool)mysqli_query( $this->_iConnId, 'USE `' . $this->dbName . '`'))) { |
| | | $this->close(); |
| | | $this->_sqlerror('Datenbank nicht gefunden / Database not found'); |
| | | return false; |
| | |
| | | |
| | | if($iPos2 !== false && ($iPos === false || $iPos2 <= $iPos)) { |
| | | $sTxt = $this->escape($sValue); |
| | | |
| | | if(strpos($sTxt, '.') !== false) $sTxt = preg_replace('/^(.+)\.(.+)$/', '`$1`.`$2`', $sTxt); |
| | | else $sTxt = '`' . $sTxt . '`'; |
| | | |
| | | $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); |
| | |
| | | } else { |
| | | if(is_int($sValue) || is_float($sValue)) { |
| | | $sTxt = $sValue; |
| | | } elseif(is_string($sValue) && (strcmp($sValue, '#NULL#') == 0)) { |
| | | } elseif(is_null($sValue) || (is_string($sValue) && (strcmp($sValue, '#NULL#') == 0))) { |
| | | $sTxt = 'NULL'; |
| | | } elseif(is_array($sValue)) { |
| | | $sTxt = ''; |
| | | foreach($sValue as $sVal) $sTxt .= ',\'' . $this->escape($sVal) . '\''; |
| | | $sTxt = '(' . substr($sTxt, 1) . ')'; |
| | | if($sTxt == '()') $sTxt = '(0)'; |
| | | 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) . '\''; |
| | | } |
| | |
| | | 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; |
| | |
| | | $try++; |
| | | $ok = mysqli_ping($this->_iConnId); |
| | | if(!$ok) { |
| | | if(!mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, $this->dbName)) { |
| | | if(!mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, $this->dbName, (int)$this->dbPort)) { |
| | | if($try > 4) { |
| | | $this->_sqlerror('DB::query -> reconnect'); |
| | | return 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); |
| | | $this->_iQueryId = @mysqli_query($this->_iConnId, $sQuery); |
| | | if (!$this->_iQueryId) { |
| | | $this->_sqlerror('Falsche Anfrage / Wrong Query', false, 'SQL-Query = ' . $sQuery); |
| | | $this->_sqlerror('Falsche Anfrage / Wrong Query', 'SQL-Query = ' . $sQuery); |
| | | return false; |
| | | } |
| | | |
| | |
| | | } |
| | | |
| | | public function queryOne($sQuery = '') { |
| | | return $this->query_one($sQuery); |
| | | return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args()); |
| | | } |
| | | |
| | | public function query_one($sQuery = '') { |
| | | return $this->queryOneRecord($sQuery); |
| | | return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args()); |
| | | } |
| | | |
| | | /** |
| | |
| | | } |
| | | |
| | | public function queryAll($sQuery = '') { |
| | | return $this->queryAllRecords($sQuery); |
| | | return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args()); |
| | | } |
| | | |
| | | public function query_all($sQuery = '') { |
| | | return $this->queryAllRecords($sQuery); |
| | | return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args()); |
| | | } |
| | | |
| | | /** |
| | |
| | | } |
| | | |
| | | |
| | | /** |
| | | * 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 |
| | |
| | | $sString = ''; |
| | | } |
| | | |
| | | /*$cur_encoding = mb_detect_encoding($sString); |
| | | $cur_encoding = mb_detect_encoding($sString); |
| | | if($cur_encoding != "UTF-8") { |
| | | if($cur_encoding != 'ASCII') { |
| | | $app->log('String ' . substr($sString, 0, 25) . '... is ' . $cur_encoding . '.', LOGLEVEL_WARN); |
| | | 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(!PXBase::check_utf8($sString)) { |
| | | } elseif(!$this->check_utf8($sString)) { |
| | | $sString = utf8_encode($sString); |
| | | }*/ |
| | | } |
| | | |
| | | if($this->_iConnId) return mysqli_real_escape_string($this->_iConnId, $sString); |
| | | else return addslashes($sString); |
| | |
| | | |
| | | $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, LOGLEVEL_WARN); |
| | | $app->log($sErrormsg . $sAddMsg . ' -> ' . $mysql_errno . ' (' . $mysql_error . ')', LOGLEVEL_WARN); |
| | | } |
| | | } |
| | | |
| | |
| | | } |
| | | 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, $params + $v_params); |
| | | } |
| | | |
| | | public function diffrec($record_old, $record_new) { |
| | | $diffrec_full = array(); |
| | | $diff_num = 0; |
| | |
| | | public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) { |
| | | global $app, $conf; |
| | | |
| | | // Insert backticks only for incomplete table names. |
| | | if(stristr($db_table, '.')) { |
| | | $escape = ''; |
| | | } else { |
| | | $escape = '`'; |
| | | } |
| | | // 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 |
| | |
| | | //** 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->escape($val)."',"; |
| | | $key_str .= '??,'; |
| | | $params[] = $key; |
| | | |
| | | $val_str .= '?,'; |
| | | $v_params[] = $val; |
| | | } |
| | | $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; |
| | | $this->query("INSERT INTO ?? $insert_data_str", $tablename); |
| | | $app->log("deprecated use of passing values to datalogInsert() - table " . $tablename, 1); |
| | | } |
| | | /* TODO: reduce risk of insert_data_str! */ |
| | | |
| | | |
| | | $old_rec = array(); |
| | | $this->query("INSERT INTO ?? $insert_data_str", $tablename); |
| | | $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); |
| | |
| | | public function datalogUpdate($tablename, $update_data, $index_field, $index_value, $force_update = false) { |
| | | 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); |
| | | |
| | | if(is_array($update_data)) { |
| | | $params = array($tablename); |
| | | $update_data_str = ''; |
| | | foreach($update_data as $key => $val) { |
| | | $update_data_str .= "`".$key ."` = '".$this->escape($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); |
| | | } |
| | | /* TODO: reduce risk of update_data_str */ |
| | | |
| | | $this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", $tablename, $index_field, $index_value); |
| | | $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 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(); |
| | |
| | | case 'blob': |
| | | return 'blob'; |
| | | break; |
| | | case 'date': |
| | | return 'date'; |
| | | break; |
| | | } |
| | | } |
| | | |