From 381520c8866a5f3be7e743e3ae16b6fb2988c495 Mon Sep 17 00:00:00 2001 From: mcramer <m.cramer@pixcept.de> Date: Tue, 21 Aug 2012 13:51:27 -0400 Subject: [PATCH] Implemented FS#1448 - one database user name and multiple databases Bugfix on db-Class (datalog Update) --- interface/web/sites/database_edit.php | 158 ++++++++++++++++++++++++++++++++++++++++++++-------- 1 files changed, 132 insertions(+), 26 deletions(-) diff --git a/interface/web/sites/database_edit.php b/interface/web/sites/database_edit.php index 7f675fa..d098625 100644 --- a/interface/web/sites/database_edit.php +++ b/interface/web/sites/database_edit.php @@ -114,20 +114,16 @@ $app->uses('getconf'); $global_config = $app->getconf->get_global_config('sites'); $dbname_prefix = replacePrefix($global_config['dbname_prefix'], $this->dataRecord); - $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord); if ($this->dataRecord['database_name'] != ""){ /* REMOVE the restriction */ $app->tpl->setVar("database_name", str_replace($dbname_prefix , '', $this->dataRecord['database_name'])); - $app->tpl->setVar("database_user", str_replace($dbuser_prefix , '', $this->dataRecord['database_user'])); } if($_SESSION["s"]["user"]["typ"] == 'admin' || $app->auth->has_clients($_SESSION['s']['user']['userid'])) { $app->tpl->setVar("database_name_prefix", $global_config['dbname_prefix']); - $app->tpl->setVar("database_user_prefix", $global_config['dbuser_prefix']); } else { $app->tpl->setVar("database_name_prefix", $dbname_prefix); - $app->tpl->setVar("database_user_prefix", $dbuser_prefix); } if($this->id > 0) { @@ -187,8 +183,7 @@ $app->uses('getconf'); $global_config = $app->getconf->get_global_config('sites'); $dbname_prefix = replacePrefix($global_config['dbname_prefix'], $this->dataRecord); - $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord); - + //* Prevent that the database name and charset is changed $old_record = $app->tform->getDataRecord($this->id); if($old_record["database_name"] != $dbname_prefix . $this->dataRecord["database_name"]) { @@ -200,8 +195,7 @@ //* Database username and database name shall not be empty if($this->dataRecord['database_name'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_name_error_empty"].'<br />'; - if($this->dataRecord['database_user'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_user_error_empty"].'<br />'; - + //* Check if the server has been changed // We do this only for the admin or reseller users, as normal clients can not change the server ID anyway if($_SESSION["s"]["user"]["typ"] == 'admin' || $app->auth->has_clients($_SESSION['s']['user']['userid'])) { @@ -214,7 +208,6 @@ unset($old_record); if(strlen($dbname_prefix . $this->dataRecord['database_name']) > 64) $app->tform->errorMessage .= str_replace('{db}',$dbname_prefix . $this->dataRecord['database_name'],$app->tform->wordbook["database_name_error_len"]).'<br />'; - if(strlen($dbuser_prefix . $this->dataRecord['database_user']) > 16) $app->tform->errorMessage .= str_replace('{user}',$dbuser_prefix . $this->dataRecord['database_user'],$app->tform->wordbook["database_user_error_len"]).'<br />'; //* Check database name and user against blacklist $dbname_blacklist = array($conf['db_database'],'mysql'); @@ -222,16 +215,10 @@ $app->tform->errorMessage .= $app->lng('Database name not allowed.').'<br />'; } - $dbuser_blacklist = array($conf['db_user'],'mysql','root'); - if(in_array($dbname_prefix . $this->dataRecord['database_user'],$dbname_blacklist)) { - $app->tform->errorMessage .= $app->lng('Database user not allowed.').'<br />'; - } - if ($app->tform->errorMessage == ''){ /* restrict the names if there is no error */ /* crop user and db names if they are too long -> mysql: user: 16 chars / db: 64 chars */ $this->dataRecord['database_name'] = substr($dbname_prefix . $this->dataRecord['database_name'], 0, 64); - $this->dataRecord['database_user'] = substr($dbuser_prefix . $this->dataRecord['database_user'], 0, 16); } //* Check for duplicates @@ -244,8 +231,11 @@ // we need remote access rights for this server, so get it's ip address $server_config = $app->getconf->get_server_config($tmp['server_id'], 'server'); if($server_config['ip_address']!='') { + if($this->dataRecord['remote_access'] != 'y') $this->dataRecord['remote_ips'] = ''; $this->dataRecord['remote_access'] = 'y'; - $this->dataRecord['remote_ips'] .= ($this->dataRecord['remote_ips'] != '' ? ',' : '') . $server_config['ip_address']; + if(preg_match('/(^|,)' . preg_quote($server_config['ip_address'], '/') . '(,|$)/', $this->dataRecord['remote_ips']) == false) { + $this->dataRecord['remote_ips'] .= ($this->dataRecord['remote_ips'] != '' ? ',' : '') . $server_config['ip_address']; + } } } @@ -261,16 +251,13 @@ //* Database username and database name shall not be empty if($this->dataRecord['database_name'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_name_error_empty"].'<br />'; - if($this->dataRecord['database_user'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_user_error_empty"].'<br />'; //* Get the database name and database user prefix $app->uses('getconf'); $global_config = $app->getconf->get_global_config('sites'); $dbname_prefix = replacePrefix($global_config['dbname_prefix'], $this->dataRecord); - $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord); if(strlen($dbname_prefix . $this->dataRecord['database_name']) > 64) $app->tform->errorMessage .= str_replace('{db}',$dbname_prefix . $this->dataRecord['database_name'],$app->tform->wordbook["database_name_error_len"]).'<br />'; - if(strlen($dbuser_prefix . $this->dataRecord['database_user']) > 16) $app->tform->errorMessage .= str_replace('{user}',$dbuser_prefix . $this->dataRecord['database_user'],$app->tform->wordbook["database_user_error_len"]).'<br />'; //* Check database name and user against blacklist $dbname_blacklist = array($conf['db_database'],'mysql'); @@ -278,16 +265,10 @@ $app->tform->errorMessage .= $app->lng('Database name not allowed.').'<br />'; } - $dbuser_blacklist = array($conf['db_user'],'mysql','root'); - if(in_array($dbname_prefix . $this->dataRecord['database_user'],$dbname_blacklist)) { - $app->tform->errorMessage .= $app->lng('Database user not allowed.').'<br />'; - } - /* restrict the names */ /* crop user and db names if they are too long -> mysql: user: 16 chars / db: 64 chars */ if ($app->tform->errorMessage == ''){ $this->dataRecord['database_name'] = substr($dbname_prefix . $this->dataRecord['database_name'], 0, 64); - $this->dataRecord['database_user'] = substr($dbuser_prefix . $this->dataRecord['database_user'], 0, 16); } //* Check for duplicates @@ -300,14 +281,139 @@ // we need remote access rights for this server, so get it's ip address $server_config = $app->getconf->get_server_config($tmp['server_id'], 'server'); if($server_config['ip_address']!='') { + if($this->dataRecord['remote_access'] != 'y') $this->dataRecord['remote_ips'] = ''; $this->dataRecord['remote_access'] = 'y'; - $this->dataRecord['remote_ips'] .= (trim($this->dataRecord['remote_ips']) != '' ? ',' : '') . $server_config['ip_address']; + if(preg_match('/(^|,)' . preg_quote($server_config['ip_address'], '/') . '(,|$)/', $this->dataRecord['remote_ips']) == false) { + $this->dataRecord['remote_ips'] .= ($this->dataRecord['remote_ips'] != '' ? ',' : '') . $server_config['ip_address']; + } } } parent::onBeforeInsert(); } + function onInsertSave($sql) { + global $app, $conf; + + if($this->dataRecord["parent_domain_id"] > 0) { + $web = $app->db->queryOneRecord("SELECT * FROM web_domain WHERE domain_id = ".intval($this->dataRecord["parent_domain_id"])); + + //* The Database user shall be owned by the same group then the website + $sys_groupid = $web['sys_groupid']; + } else { + $sys_groupid = $this->dataRecord['sys_groupid']; + } + + + if($this->dataRecord['database_user_id']) { + // check if there has already been a database on this server with that user + $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($this->dataRecord['database_user_id']) . "' OR `database_ro_user_id` = '" . intval($this->dataRecord['database_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + + if($check && $check['cnt'] < 1) { + // we need to make a datalog insert for the database users that are connected to this database + $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($this->dataRecord['database_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + if($db_user) { + $db_user['server_id'] = $this->dataRecord['server_id']; + $app->db->datalogSave('web_database_user', 'INSERT', 'database_user_id', $db_user['database_user_id'], array(), $db_user); + } + } + } + + if($this->dataRecord['database_ro_user_id']) { + // check if there has already been a database on this server with that user + $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "' OR `database_ro_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + + if($check && $check['cnt'] < 1) { + // we need to make a datalog insert for the database users that are connected to this database + $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + if($db_user) { + $db_user['server_id'] = $this->dataRecord['server_id']; + $app->db->datalogSave('web_database_user', 'INSERT', 'database_user_id', $db_user['database_user_id'], array(), $db_user); + } + } + } + + $app->db->query($sql); + if($app->db->errorMessage != '') die($app->db->errorMessage); + $new_id = $app->db->insertID(); + + return $new_id; + } + + function onUpdateSave($sql) { + global $app; + if(!empty($sql) && !$app->tform->isReadonlyTab($app->tform->getCurrentTab(),$this->id)) { + $old_record = $app->tform->getDataRecord($this->id); + + if($this->dataRecord["parent_domain_id"] > 0) { + $web = $app->db->queryOneRecord("SELECT * FROM web_domain WHERE domain_id = ".intval($this->dataRecord["parent_domain_id"])); + + //* The Database user shall be owned by the same group then the website + $sys_groupid = $web['sys_groupid']; + } else { + $sys_groupid = $this->dataRecord['sys_groupid']; + } + + // check if database user has changed + if($old_record['database_user_id'] && $old_record['database_user_id'] != $this->dataRecord['database_user_id'] && $old_record['database_user_id'] != $this->dataRecord['database_ro_user_id']) { + // check if any database on the server still uses this one + $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($old_record['database_user_id']) . "' OR `database_ro_user_id` = '" . intval($old_record['database_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "' AND `database_id` != '" . intval($this->id) . "'"); + if($check['cnt'] < 1) { + // send a datalog delete + $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($old_record['database_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + if($db_user) { + $db_user['server_id'] = $this->dataRecord['server_id']; + $app->db->datalogSave('web_database_user', 'DELETE', 'database_user_id', $db_user['database_user_id'], $db_user, array()); + } + } + } + // check if readonly database user has changed + if($old_record['database_ro_user_id'] && $old_record['database_ro_user_id'] != $this->dataRecord['database_ro_user_id'] && $old_record['database_ro_user_id'] != $this->dataRecord['database_user_id']) { + // check if any database on the server still uses this one + $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($old_record['database_ro_user_id']) . "' OR `database_ro_user_id` = '" . intval($old_record['database_ro_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "' AND `database_id` != '" . intval($this->id) . "'"); + if($check['cnt'] < 1) { + // send a datalog delete + $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($old_record['database_ro_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + if($db_user) { + $db_user['server_id'] = $this->dataRecord['server_id']; + $app->db->datalogSave('web_database_user', 'DELETE', 'database_user_id', $db_user['database_user_id'], $db_user, array()); + } + } + } + + if($this->dataRecord['database_user_id']) { + // check if there has already been a database on this server with that user + $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($this->dataRecord['database_user_id']) . "' OR `database_ro_user_id` = '" . intval($this->dataRecord['database_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + + if($check && $check['cnt'] < 1) { + // we need to make a datalog insert for the database users that are connected to this database + $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($this->dataRecord['database_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + if($db_user) { + $db_user['server_id'] = $this->dataRecord['server_id']; + $app->db->datalogSave('web_database_user', 'INSERT', 'database_user_id', $db_user['database_user_id'], array(), $db_user); + } + } + } + + if($this->dataRecord['database_ro_user_id']) { + // check if there has already been a database on this server with that user + $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "' OR `database_ro_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + + if($check && $check['cnt'] < 1) { + // we need to make a datalog insert for the database users that are connected to this database + $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'"); + if($db_user) { + $db_user['server_id'] = $this->dataRecord['server_id']; + $app->db->datalogSave('web_database_user', 'INSERT', 'database_user_id', $db_user['database_user_id'], array(), $db_user); + } + } + } + + $app->db->query($sql); + if($app->db->errorMessage != '') die($app->db->errorMessage); + } + } + function onAfterInsert() { global $app, $conf; -- Gitblit v1.9.1