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 | 225 +++++++++++++++++++++++++++++++++++++++++++++----------
1 files changed, 183 insertions(+), 42 deletions(-)
diff --git a/interface/lib/classes/db_mysql.inc.php b/interface/lib/classes/db_mysql.inc.php
index b18d583..c74e6e0 100644
--- a/interface/lib/classes/db_mysql.inc.php
+++ b/interface/lib/classes/db_mysql.inc.php
@@ -36,6 +36,7 @@
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
@@ -54,7 +55,9 @@
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
////
@@ -65,6 +68,7 @@
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'];
@@ -72,13 +76,13 @@
$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()) {
@@ -86,7 +90,7 @@
$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;
@@ -126,9 +130,12 @@
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);
@@ -136,13 +143,17 @@
} 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) . '\'';
}
@@ -167,6 +178,58 @@
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;
@@ -181,7 +244,7 @@
$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;
@@ -197,10 +260,11 @@
$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;
}
@@ -264,11 +328,11 @@
}
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());
}
/**
@@ -297,11 +361,11 @@
}
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());
}
/**
@@ -371,6 +435,34 @@
}
+ /**
+ * 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
@@ -383,21 +475,21 @@
global $app;
if(!is_string($sString) && !is_numeric($sString)) {
$app->log('NON-String given in escape function! (' . gettype($sString) . ')', LOGLEVEL_INFO);
- $sAddMsg = getDebugBacktrace();
+ //$sAddMsg = getDebugBacktrace();
$app->log($sAddMsg, LOGLEVEL_DEBUG);
$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);
@@ -413,13 +505,14 @@
$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();
+ //$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);
}
}
@@ -452,7 +545,27 @@
}
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;
@@ -492,12 +605,11 @@
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
@@ -536,26 +648,37 @@
//** 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);
+ $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 $index_value;
@@ -565,20 +688,31 @@
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);
@@ -589,6 +723,10 @@
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();
@@ -861,6 +999,9 @@
case 'blob':
return 'blob';
break;
+ case 'date':
+ return 'date';
+ break;
}
}
--
Gitblit v1.9.1