<?php
/**
* i-MSCP SpamAssassin plugin
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$database = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_spamassassin');
return array('up' => "CREATE DATABASE IF NOT EXISTS {$database} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
<?php
/**
* i-MSCP SpamAssassin plugin
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$database = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_spamassassin');
$table = quoteIdentifier('bayes_global_vars');
return array('up' => "\n\t\tCREATE TABLE IF NOT EXISTS {$database}.{$table} (\n\t\t\t`variable` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\t`value` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\tPRIMARY KEY (`variable`)\n\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\n\t");
/**
* Deletes the given SQL database
*
* @param int $dmnId Domain unique identifier
* @param int $dbId Databse unique identifier
* @return bool TRUE on success, false otherwise
*/
function delete_sql_database($dmnId, $dbId)
{
$stmt = exec_query('SELECT sqld_name FROM sql_database WHERE domain_id = ? AND sqld_id = ?', array($dmnId, $dbId));
if (!$stmt->rowCount()) {
return false;
}
$row = $stmt->fetchRow(PDO::FETCH_ASSOC);
$dbName = $row['sqld_name'];
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onBeforeDeleteSqlDb, array('sqlDbId' => $dbId, 'sqlDatabaseName' => $dbName));
$stmt = exec_query('SELECT sqlu_id FROM sql_user INNER JOIN sql_database USING(sqld_id) WHERE sqld_id = ? AND domain_id = ?', array($dbId, $dmnId));
while ($row = $stmt->fetchRow(PDO::FETCH_ASSOC)) {
if (!sql_delete_user($dmnId, $row['sqlu_id'])) {
return false;
}
}
exec_query(sprintf('DROP DATABASE IF EXISTS %s', quoteIdentifier($dbName)));
exec_query('DELETE FROM sql_database WHERE domain_id = ? AND sqld_id = ?', array($dmnId, $dbId));
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onAfterDeleteSqlDb, array('sqlDbId' => $dbId, 'sqlDatabaseName' => $dbName));
return true;
}
/**
* Deletes an admin or reseller user
*
* @throws iMSCP_Exception_Database
* @param int $userId User unique identifier
*/
function admin_deleteUser($userId)
{
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onBeforeDeleteUser, array('userId' => $userId));
$userId = (int) $userId;
/** @var $cfg iMSCP_Config_Handler_File */
$cfg = iMSCP_Registry::get('config');
/** @var $db iMSCP_Database */
$db = iMSCP_Database::getInstance();
$stmt = exec_query('
SELECT
a.admin_type, b.logo
FROM
admin a
LEFT JOIN
user_gui_props b ON (b.user_id = a.admin_id)
WHERE
admin_id = ?
', $userId);
$row = $stmt->fetchRow(PDO::FETCH_ASSOC);
$userType = $row['admin_type'];
if (empty($userType) || $userType == 'user') {
showBadRequestErrorPage();
}
// Users (admins/resellers) common items to delete
$itemsToDelete = array('admin' => 'admin_id = ?', 'email_tpls' => 'owner_id = ?', 'tickets' => 'ticket_from = ? OR ticket_to = ?', 'user_gui_props' => 'user_id = ?');
// Note: Admin can also have they own hosting_plans bug must not be considerated
// as common item since first admin must be never removed
if ($userType == 'reseller') {
// Getting reseller's software packages to remove if any
$stmt = exec_query('SELECT software_id, software_archive FROM web_software WHERE reseller_id = ?', $userId);
$swPackages = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Getting custom reseller isp logo if set
$resellerLogo = $row['logo'];
// Add specific reseller items to remove
$itemsToDelete = array_merge(array('hosting_plans' => 'reseller_id = ?', 'reseller_props' => 'reseller_id = ?', 'web_software' => 'reseller_id = ?'), $itemsToDelete);
}
// We are using transaction to ensure data consistency and prevent any garbage in
// the database. If one query fail, the whole process is reverted.
try {
// Cleanup database
$db->beginTransaction();
foreach ($itemsToDelete as $table => $where) {
// Build the DELETE statement
$query = "DELETE FROM " . quoteIdentifier($table) . ($where ? " WHERE {$where}" : '');
exec_query($query, array_fill(0, substr_count($where, '?'), $userId));
}
$db->commit();
// Cleanup files system
// We are safe here. We don't stop the process even if files cannot be removed. That can result in garbages but
// the sysadmin can easily delete them through ssh.
// Deleting reseller software instaler local repository
if (isset($swPackages) && !empty($swPackages)) {
_admin_deleteResellerSwPackages($userId, $swPackages);
} elseif ($userType == 'reseller' && is_dir($cfg['GUI_APS_DIR'] . '/' . $userId) && @rmdir($cfg['GUI_APS_DIR'] . '/' . $userId) == false) {
write_log('Unable to remove reseller software directory: ' . $cfg['GUI_APS_DIR'] . '/' . $userId, E_USER_ERROR);
}
// Deleting user logo
if (isset($resellerLogo) && !empty($resellerLogo)) {
$logoPath = $cfg['GUI_ROOT_DIR'] . '/data/persistent/ispLogos/' . $resellerLogo;
if (file_exists($logoPath) && @unlink($logoPath) == false) {
write_log('Unable to remove user logo ' . $logoPath, E_USER_ERROR);
}
}
$userTr = $userType == 'reseller' ? tr('Reseller') : tr('Admin');
set_page_message(tr('%s account successfully deleted.', $userTr), 'success');
write_log($_SESSION['user_logged'] . ": deletes user " . $userId, E_USER_NOTICE);
} catch (iMSCP_Exception_Database $e) {
$db->rollBack();
throw $e;
}
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onAfterDeleteUser, array('userId' => $userId));
redirectTo('manage_users.php');
}
<?php
/**
* i-MSCP SpamAssassin plugin
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$database = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_spamassassin');
$table = quoteIdentifier('bayes_seen');
return array('up' => "\n\t\tCREATE TABLE IF NOT EXISTS {$database}.{$table} (\n\t\t\t`id` int(11) NOT NULL DEFAULT '0',\n\t\t\t`msgid` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',\n\t\t\t`flag` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\tPRIMARY KEY (`id`,`msgid`)\n\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\n\t");
/**
* Converts all tables to InnoDB engine
*
* @return array SQL statements to be executed
*/
protected function r60()
{
$sqlUpd = array();
/** @var $db iMSCP_Database */
$db = iMSCP_Registry::get('db');
foreach ($db->getTables() as $table) {
$table = quoteIdentifier($table);
$sqlUpd[] = "ALTER TABLE {$table} ENGINE=InnoDB";
}
return $sqlUpd;
}
/**
* i-MSCP - internet Multi Server Control Panel
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$roundcubeDbName = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_roundcube');
# We must check column existence before add it due error made at commit 6c17b88771f0586ec0949c838b091dc0f4b1d4cc, in
# which that column has been added in an existent migration file instead of a new one.
$addColumn = function ($dbName, $table, $column, $def) {
$table = quoteIdentifier($table);
$stmt = exec_query("SHOW COLUMNS FROM {$dbName}.{$table} LIKE ?", $column);
if (!$stmt->rowCount()) {
return sprintf('ALTER TABLE %s.%s ADD %s %s;', $dbName, $table, quoteIdentifier($column), $def);
}
return '';
};
return array('up' => $addColumn($roundcubeDbName, 'events', 'status', 'VARCHAR(32) NOT NULL AFTER sensitivity') . " \n REPLACE INTO {$roundcubeDbName}.system (name, value) VALUES ('calendar-database-version', '2014040900');\n ", 'down' => "\n ALTER TABLE {$roundcubeDbName}.events DROP status;\n REPLACE INTO {$roundcubeDbName}.system (name, value) VALUES ('calendar-database-version', '2013051600');\n ");
/**
* Add SQL user for the given database
*
* @throws Exception
* @throws iMSCP_Exception_Database
* @param int $customerId Customer unique identifier
* @param int $dbId
* @return void
*/
function client_addSqlUser($customerId, $dbId)
{
if (empty($_POST)) {
return;
}
if (!isset($_POST['uaction'])) {
showBadRequestErrorPage();
}
$dmnId = get_user_domain_id($customerId);
if (!isset($_POST['Add_Exist'])) {
$needUserCreate = true;
if (!isset($_POST['user_name']) || !isset($_POST['user_host']) || !isset($_POST['pass']) || !isset($_POST['pass_rep'])) {
showBadRequestErrorPage();
}
$user = clean_input($_POST['user_name']);
$host = clean_input($_POST['user_host']);
$password = clean_input($_POST['pass']);
$passwordConf = clean_input($_POST['pass_rep']);
if ($user === '') {
set_page_message(tr('Please enter an username.'), 'error');
return;
}
if (preg_match('/[%|\\?]+/', $user)) {
set_page_message(tr("Wildcards such as '%s' and '%s' are not allowed in username.", '%', '?'), 'error');
return;
}
if ($host === '') {
set_page_message(tr('Please enter an SQL user host.'), 'error');
return;
}
$host = encode_idna(clean_input($_POST['user_host']));
if ($host !== '%' && $host !== 'localhost' && !iMSCP_Validate::getInstance()->hostname($host, array('allow' => Zend_Validate_Hostname::ALLOW_DNS | Zend_Validate_Hostname::ALLOW_IP))) {
set_page_message(tr('Invalid SQL user host: %s', iMSCP_Validate::getInstance()->getLastValidationMessages()), 'error');
return;
}
if ($password === '') {
set_page_message(tr('Please enter a password.'), 'error');
return;
}
if ($password !== $passwordConf) {
set_page_message(tr("Passwords do not match."), 'error');
return;
}
if (strlen($password) > 32) {
set_page_message(tr('Password is too long.'), 'error');
return;
}
if (!checkPasswordSyntax($password)) {
set_page_message(tr('Only printable characters from the ASCII table (not extended), excepted the space, are allowed.'), 'error');
return;
}
if (isset($_POST['use_dmn_id']) && $_POST['use_dmn_id'] == 'on' && isset($_POST['id_pos']) && $_POST['id_pos'] == 'start') {
$user = $dmnId . '_' . clean_input($_POST['user_name']);
} elseif (isset($_POST['use_dmn_id']) && $_POST['use_dmn_id'] == 'on' && isset($_POST['id_pos']) && $_POST['id_pos'] == 'end') {
$user = clean_input($_POST['user_name']) . '_' . $dmnId;
} else {
$user = clean_input($_POST['user_name']);
}
if (strlen($user) > 16) {
set_page_message(tr('Username is too long.'), 'error');
return;
}
if (client_isSqlUser($user, $host)) {
set_page_message(tr('SQL user %s already exits.', $user . '@' . decode_idna($host)), 'error');
return;
}
} elseif (isset($_POST['sqluser_id'])) {
// Using existing SQL user as specified in input data
$needUserCreate = false;
$userId = intval($_POST['sqluser_id']);
$stmt = exec_query('SELECT sqlu_name, sqlu_host, sqlu_pass FROM sql_user WHERE sqlu_id = ?', $userId);
if (!$stmt->rowCount()) {
showBadRequestErrorPage();
}
$row = $stmt->fetchRow(PDO::FETCH_ASSOC);
$user = $row['sqlu_name'];
$host = $row['sqlu_host'];
$password = $row['sqlu_pass'];
} else {
showBadRequestErrorPage();
return;
}
# Retrieve database to which SQL user should be assigned
$stmt = exec_query('SELECT sqld_name FROM sql_database WHERE sqld_id = ? AND domain_id = ?', array($dbId, $dmnId));
if (!$stmt->rowCount()) {
showBadRequestErrorPage();
}
$row = $stmt->fetchRow(PDO::FETCH_ASSOC);
$dbName = $row['sqld_name'];
$dbName = preg_replace('/([_%\\?\\*])/', '\\\\$1', $dbName);
$config = iMSCP_Registry::get('config');
$mysqlConfig = new iMSCP_Config_Handler_File($config['CONF_DIR'] . '/mysql/mysql.data');
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onBeforeAddSqlUser);
// Here we cannot use transaction due to statements that cause an implicit commit. Thus we execute
// those statements first to let the i-MSCP database in clean state if one of them fails.
// See https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html for more details
if ($needUserCreate) {
if (strpos('mariadb', $config['SQL_SERVER']) !== false || version_compare($mysqlConfig['SQLD_VERSION'], '5.7.6', '<')) {
exec_query('CREATE USER ?@? IDENTIFIED BY ?', array($user, $host, $password));
} else {
exec_query('CREATE USER ?@? IDENTIFIED BY ? PASSWORD EXPIRE NEVER', array($user, $host, $password));
}
}
execute_query(sprintf('GRANT ALL PRIVILEGES ON %s.* to %s@%s', quoteIdentifier($dbName), quoteValue($user), quoteValue($host)));
exec_query('INSERT INTO sql_user (sqld_id, sqlu_name, sqlu_host, sqlu_pass) VALUES (?, ?, ?, ?)', array($dbId, $user, $host, $password));
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onAfterAddSqlUser);
set_page_message(tr('SQL user successfully added.'), 'success');
write_log(sprintf("%s added new SQL user: %s", $_SESSION['user_logged'], tohtml($user)), E_USER_NOTICE);
redirectTo('sql_manage.php');
}
/**
* Add SQL database
*
* @param int $userId
* @return void
*/
function client_addSqlDb($userId)
{
if (!isset($_POST['uaction'])) {
return;
}
if (!isset($_POST['db_name'])) {
showBadRequestErrorPage();
}
$dbName = clean_input($_POST['db_name']);
if ($_POST['db_name'] === '') {
set_page_message(tr('Please type database name.'), 'error');
return;
}
$mainDmnId = get_user_domain_id($userId);
if (isset($_POST['use_dmn_id']) && $_POST['use_dmn_id'] === 'on') {
if (isset($_POST['id_pos']) && $_POST['id_pos'] === 'start') {
$dbName = $mainDmnId . '_' . $dbName;
} elseif (isset($_POST['id_pos']) && $_POST['id_pos'] === 'end') {
$dbName = $dbName . '_' . $mainDmnId;
}
}
if (strlen($dbName) > 64) {
set_page_message(tr('Database name is too long.'), 'error');
return;
}
if ($dbName === 'test' || client_isDatabase($dbName)) {
set_page_message(tr('Database name is unavailable.'), 'error');
return;
}
if (preg_match('/[%|\\?]+/', $dbName)) {
set_page_message(tr("Wildcards such as 's%' and 's%' are not allowed.", '%', '?'), 'error');
return;
}
$responses = iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onBeforeAddSqlDb, array('dbName' => $dbName));
if (!$responses->isStopped()) {
execute_query(sprintf('CREATE DATABASE IF NOT EXISTS %s', quoteIdentifier($dbName)));
exec_query('INSERT INTO sql_database (domain_id, sqld_name) VALUES (?, ?)', array($mainDmnId, $dbName));
set_page_message(tr('SQL database successfully created.'), 'success');
write_log(sprintf('%s added new SQL database: %s', decode_idna($_SESSION['user_logged']), $dbName), E_USER_NOTICE);
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onAfterAddSqlDb, array('dbName' => $dbName));
}
redirectTo('sql_manage.php');
}
function add_sql_database(&$sql, $user_id)
{
global $cfg;
if (!isset($_POST['uaction'])) {
return;
}
$root_sql =& ADONewConnection('mysql');
if (!@$root_sql->Connect($cfg['DB_HOST'], $cfg['DB_USER'], $cfg['DB_PASS'])) {
set_page_message(tr('Can not connect as MySQL administrator!'));
return;
}
//
// let's generate database name.
//
if ($_POST['db_name'] === '') {
set_page_message(tr('Please type database name!'));
return;
}
$dmn_id = get_user_domain_id($sql, $user_id);
if (isset($_POST['use_dmn_id']) && $_POST['use_dmn_id'] === 'on') {
//
// we'll use domain_id in the name of the database;
//
if (isset($_POST['id_pos']) && $_POST['id_pos'] === 'start') {
$db_name = $dmn_id . "_" . $_POST['db_name'];
} else {
if (isset($_POST['id_pos']) && $_POST['id_pos'] === 'end') {
$db_name = $_POST['db_name'] . "_" . $dmn_id;
}
}
} else {
$db_name = $_POST['db_name'];
}
if (strlen($db_name) > $cfg['MAX_SQL_DATABASE_LENGTH']) {
set_page_message(tr('Too long database name!'));
return;
}
//
// have we such database in the system!?
//
if (check_db_name($root_sql, $db_name)) {
set_page_message(tr('Specified database name already exists!'));
return;
}
// are wildcards used?
//
if (ereg("\\%|\\?", $db_name)) {
set_page_message(tr('Wildcards as % and ? are not allowed!'));
return;
}
$query = 'create database ' . quoteIdentifier($db_name);
$rs = exec_query($root_sql, $query, array());
$query = <<<SQL_QUERY
insert into sql_database
(domain_id, sqld_name)
values
(?, ?)
SQL_QUERY;
$rs = exec_query($sql, $query, array($dmn_id, $db_name));
write_log($_SESSION['user_logged'] . " : add new SQL database -> " . $db_name);
set_page_message(tr('SQL database created successfully!'));
user_goto('manage_sql.php');
}
function add_sql_user(&$sql, $user_id, $db_id)
{
global $cfg;
if (!isset($_POST['uaction'])) {
return;
}
//
// let's check user input;
//
if ($_POST['user_name'] === '' && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Please type user name!'));
return;
}
if ($_POST['pass'] === '' && $_POST['pass_rep'] === '' && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Please type user password!'));
return;
}
if ($_POST['pass'] !== $_POST['pass_rep'] && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Entered passwords does not match!'));
return;
}
if (strlen($_POST['pass']) > $cfg['MAX_SQL_PASS_LENGTH'] && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Too user long password!'));
return;
}
if (isset($_POST['Add_Exist'])) {
$query = "SELECT sqlu_pass FROM sql_user WHERE sqlu_id = ?";
$rs = exec_query($sql, $query, array($_POST['sqluser_id']));
if ($rs->RecordCount() == 0) {
set_page_message(tr('SQL-user not found! Maybe it was deleted by another user!'));
return;
}
$user_pass = $rs->fields['sqlu_pass'];
} else {
$user_pass = $_POST['pass'];
}
$dmn_id = get_user_domain_id($sql, $user_id);
if (!isset($_POST['Add_Exist'])) {
//
// we'll use domain_id in the name of the database;
//
if (isset($_POST['use_dmn_id']) && $_POST['use_dmn_id'] === 'on' && isset($_POST['id_pos']) && $_POST['id_pos'] === 'start') {
$db_user = $dmn_id . "_" . $_POST['user_name'];
} else {
if (isset($_POST['use_dmn_id']) && $_POST['use_dmn_id'] === 'on' && isset($_POST['id_pos']) && $_POST['id_pos'] === 'end') {
$db_user = $_POST['user_name'] . "_" . $dmn_id;
} else {
$db_user = $_POST['user_name'];
}
}
} else {
if (isset($_POST['Add_Exist'])) {
$query = "SELECT sqlu_name FROM sql_user WHERE sqlu_id = ?";
$rs = exec_query($sql, $query, array($_POST['sqluser_id']));
$db_user = $rs->fields['sqlu_name'];
}
}
if (strlen($db_user) > $cfg['MAX_SQL_USER_LENGTH']) {
set_page_message(tr('User name too long!'));
return;
}
// are wildcards used?
//
if (ereg("\\%|\\?", $db_user)) {
set_page_message(tr('Wildcards as % and ? are not allowed!'));
return;
}
//
// have we such sql user in the system?!
//
if (check_db_user($sql, $db_user) && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Specified SQL username name already exists!'));
return;
}
//
// add user in the vhcs table;
//
$query = <<<SQL_QUERY
insert into sql_user
(sqld_id, sqlu_name, sqlu_pass)
values
(?, ?, ?)
SQL_QUERY;
$rs = exec_query($sql, $query, array($db_id, $db_user, $user_pass));
$query = <<<SQL_QUERY
select
sqld_name as db_name
from
sql_database
where
sqld_id = ?
and
domain_id = ?
SQL_QUERY;
$rs = exec_query($sql, $query, array($db_id, $dmn_id));
$db_name = $rs->fields['db_name'];
//
// add user in the mysql system tables;
//
$new_db_name = ereg_replace("_", "\\_", $db_name);
$query = 'grant all on ' . quoteIdentifier($new_db_name) . '.* to ?@\'localhost\' identified by ?';
$rs = exec_query($sql, $query, array($db_user, $user_pass));
$query = 'grant all on ' . quoteIdentifier($new_db_name) . '.* to ?@\'%\' identified by ?';
$rs = exec_query($sql, $query, array($db_user, $user_pass));
write_log($_SESSION['user_logged'] . " : add SQL user" . $db_name);
set_page_message(tr('SQL user successfully added!'));
user_goto('manage_sql.php');
}
<?php
/**
* i-MSCP SpamAssassin plugin
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$database = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_spamassassin');
$table = quoteIdentifier('awl');
return array('up' => "\n\t\tCREATE TABLE IF NOT EXISTS {$database}.{$table} (\n\t\t\t`username` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\t`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\t`ip` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\t`count` int(11) NOT NULL DEFAULT '0',\n\t\t\t`totscore` float NOT NULL DEFAULT '0',\n\t\t\t`signedby` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\t`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\t\t\tPRIMARY KEY (`username`,`email`,`signedby`,`ip`)\n\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\n\t");
<?php
/**
* i-MSCP SpamAssassin plugin
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$database = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_spamassassin');
$table = quoteIdentifier('bayes_expire');
return array('up' => "\n CREATE TABLE IF NOT EXISTS {$database}.{$table} (\n `id` int(11) NOT NULL DEFAULT '0',\n `runtime` int(11) NOT NULL DEFAULT '0',\n KEY `bayes_expire_idx1` (`id`)\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\n ");
<?php
/**
* i-MSCP - internet Multi Server Control Panel
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$roundcubeDbName = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_roundcube');
return array('up' => "\n CREATE TABLE IF NOT EXISTS {$roundcubeDbName}.tasks (\n task_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n tasklist_id int(10) unsigned NOT NULL,\n parent_id int(10) unsigned DEFAULT NULL,\n uid varchar(255) NOT NULL,\n created datetime NOT NULL,\n changed datetime NOT NULL,\n del tinyint(1) unsigned NOT NULL DEFAULT '0',\n title varchar(255) NOT NULL,\n description text,\n tags text,\n date varchar(10) DEFAULT NULL,\n time varchar(5) DEFAULT NULL,\n startdate varchar(10) DEFAULT NULL,\n starttime varchar(5) DEFAULT NULL,\n flagged tinyint(4) NOT NULL DEFAULT '0',\n complete float NOT NULL DEFAULT '0',\n alarms varchar(255) DEFAULT NULL,\n recurrence varchar(255) DEFAULT NULL,\n organizer varchar(255) DEFAULT NULL,\n attendees text,\n notify datetime DEFAULT NULL,\n PRIMARY KEY (task_id),\n KEY tasklisting (tasklist_id,del,date),\n KEY uid (uid),\n CONSTRAINT fk_tasks_tasklist_id FOREIGN KEY (tasklist_id)\n REFERENCES tasklists(tasklist_id) ON DELETE CASCADE ON UPDATE CASCADE\n ) /*!40000 ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci */;\n ", 'down' => "\n DROP TABLE IF EXISTS {$roundcubeDbName}.tasks;\n ");
/**
* @todo
* * Database user with same name can be added several times
* * If creation of database user fails in MySQL-Table, database user is already
* in loclal EasySCP table -> Error handling
*/
function add_sql_user($sql, $user_id, $db_id)
{
$cfg = EasySCP_Registry::get('Config');
if (!isset($_POST['uaction'])) {
return;
}
// let's check user input
if (empty($_POST['user_name']) && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Please type user name!'), 'warning');
return;
}
if (empty($_POST['pass']) && empty($_POST['pass_rep']) && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Please type user password!'), 'warning');
return;
}
if (isset($_POST['pass']) && isset($_POST['pass_rep']) && $_POST['pass'] !== $_POST['pass_rep'] && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Entered passwords do not match!'), 'warning');
return;
}
if (isset($_POST['pass']) && strlen($_POST['pass']) > $cfg->MAX_SQL_PASS_LENGTH && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Too long user password!'), 'warning');
return;
}
if (isset($_POST['pass']) && !preg_match('/^[[:alnum:]:!*+#_.-]+$/', $_POST['pass']) && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Don\'t use special chars like "@, $, %..." in the password!'), 'warning');
return;
}
if (isset($_POST['pass']) && !chk_password($_POST['pass']) && !isset($_POST['Add_Exist'])) {
if ($cfg->PASSWD_STRONG) {
set_page_message(sprintf(tr('The password must be at least %s chars long and contain letters and numbers to be valid.'), $cfg->PASSWD_CHARS), 'warning');
} else {
set_page_message(sprintf(tr('Password data is shorter than %s signs or includes not permitted signs!'), $cfg->PASSWD_CHARS), 'warning');
}
return;
}
if (isset($_POST['Add_Exist'])) {
$query = "SELECT `sqlu_pass` FROM `sql_user` WHERE `sqlu_id` = ?";
$rs = exec_query($sql, $query, $_POST['sqluser_id']);
if ($rs->recordCount() == 0) {
set_page_message(tr('SQL-user not found! It might has been deleted by another user.'), 'warning');
return;
}
$user_pass = decrypt_db_password($rs->fields['sqlu_pass']);
} else {
$user_pass = $_POST['pass'];
}
$dmn_id = get_user_domain_id($user_id);
if (!isset($_POST['Add_Exist'])) {
// we'll use domain_id in the name of the database;
if (isset($_POST['use_dmn_id']) && $_POST['use_dmn_id'] === 'on' && isset($_POST['id_pos']) && $_POST['id_pos'] === 'start') {
$db_user = $dmn_id . "_" . clean_input($_POST['user_name']);
} else {
if (isset($_POST['use_dmn_id']) && $_POST['use_dmn_id'] === 'on' && isset($_POST['id_pos']) && $_POST['id_pos'] === 'end') {
$db_user = clean_input($_POST['user_name']) . "_" . $dmn_id;
} else {
$db_user = clean_input($_POST['user_name']);
}
}
} else {
$query = "SELECT `sqlu_name` FROM `sql_user` WHERE `sqlu_id` = ?";
$rs = exec_query($sql, $query, $_POST['sqluser_id']);
$db_user = $rs->fields['sqlu_name'];
}
if (strlen($db_user) > $cfg->MAX_SQL_USER_LENGTH) {
set_page_message(tr('User name too long!'), 'warning');
return;
}
// are wildcards used?
if (preg_match("/[%|\\?]+/", $db_user)) {
set_page_message(tr('Wildcards such as %% and ? are not allowed!'), 'warning');
return;
}
// have we such sql user in the system?!
if (check_db_user($sql, $db_user) && !isset($_POST['Add_Exist'])) {
set_page_message(tr('Specified SQL username name already exists!'), 'warning');
return;
}
// add user in the EasySCP table;
$query = "\n\t\tINSERT INTO `sql_user`\n\t\t\t(`sqld_id`, `sqlu_name`, `sqlu_pass`)\n\t\tVALUES\n\t\t\t(?, ?, ?)\n\t";
exec_query($sql, $query, array($db_id, $db_user, encrypt_db_password($user_pass)));
update_reseller_c_props(get_reseller_id($dmn_id));
$query = "\n\t\tSELECT\n\t\t\t`sqld_name` AS `db_name`\n\t\tFROM\n\t\t\t`sql_database`\n\t\tWHERE\n\t\t\t`sqld_id` = ?\n\t\tAND\n\t\t\t`domain_id` = ?\n\t";
$rs = exec_query($sql, $query, array($db_id, $dmn_id));
$db_name = $rs->fields['db_name'];
$db_name = preg_replace("/([_%\\?\\*])/", '\\\\$1', $db_name);
// add user in the mysql system tables
$query = "GRANT ALL PRIVILEGES ON " . quoteIdentifier($db_name) . ".* TO ?@? IDENTIFIED BY ?";
exec_query($sql, $query, array($db_user, "localhost", $user_pass));
exec_query($sql, $query, array($db_user, "%", $user_pass));
write_log($_SESSION['user_logged'] . ": add SQL user: " . tohtml($db_user));
set_page_message(tr('SQL user successfully added!'), 'info');
user_goto('sql_manage.php');
}
<?php
/**
* i-MSCP SpamAssassin plugin
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$database = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_spamassassin');
$table = quoteIdentifier('bayes_vars');
return array('up' => "\n\t\tCREATE TABLE IF NOT EXISTS {$database}.{$table} (\n\t\t\t`id` int(11) NOT NULL AUTO_INCREMENT,\n\t\t\t`username` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\t`spam_count` int(11) NOT NULL DEFAULT '0',\n\t\t\t`ham_count` int(11) NOT NULL DEFAULT '0',\n\t\t\t`token_count` int(11) NOT NULL DEFAULT '0',\n\t\t\t`last_expire` int(11) NOT NULL DEFAULT '0',\n\t\t\t`last_atime_delta` int(11) NOT NULL DEFAULT '0',\n\t\t\t`last_expire_reduce` int(11) NOT NULL DEFAULT '0',\n\t\t\t`oldest_token_age` int(11) NOT NULL DEFAULT '2147483647',\n\t\t\t`newest_token_age` int(11) NOT NULL DEFAULT '0',\n\t\t\tPRIMARY KEY (`id`),\n\t\t\tUNIQUE KEY `bayes_vars_idx1` (`username`)\n\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\n\t");
<?php
/**
* i-MSCP SpamAssassin plugin
* Copyright (C) 2013-2015 Sascha Bay <*****@*****.**>
* Copyright (C) 2013-2015 Rene Schuster <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$database = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_spamassassin');
$table = quoteIdentifier('userpref');
return array('up' => "\n\t\tINSERT IGNORE INTO {$database}.{$table}\n\t\t\t(`username`, `preference`, `value`)\n\t\tVALUES\n\t\t\t('\$GLOBAL', 'required_score', '5'),\n\t\t\t('\$GLOBAL', 'rewrite_header Subject', '*****SPAM*****'),\n\t\t\t('\$GLOBAL', 'report_safe', '1'),\n\t\t\t('\$GLOBAL', 'use_bayes', '1'),\n\t\t\t('\$GLOBAL', 'use_bayes_rules', '1'),\n\t\t\t('\$GLOBAL', 'bayes_auto_learn', '1'),\n\t\t\t('\$GLOBAL', 'bayes_auto_learn_threshold_nonspam', '0.1'),\n\t\t\t('\$GLOBAL', 'bayes_auto_learn_threshold_spam', '12.0'),\n\t\t\t('\$GLOBAL', 'use_auto_whitelist', '0'),\n\t\t\t('\$GLOBAL', 'skip_rbl_checks', '1'),\n\t\t\t('\$GLOBAL', 'use_razor2', '0'),\n\t\t\t('\$GLOBAL', 'use_pyzor', '0'),\n\t\t\t('\$GLOBAL', 'use_dcc', '0'),\n\t\t\t('\$GLOBAL', 'score USER_IN_BLACKLIST', '10'),\n\t\t\t('\$GLOBAL', 'score USER_IN_WHITELIST', '-6');\n\t");
<?php
/**
* i-MSCP SpamAssassin plugin
* Copyright (C) 2013-2016 Sascha Bay <*****@*****.**>
* Copyright (C) 2013-2016 Rene Schuster <*****@*****.**>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
$database = quoteIdentifier(iMSCP_Registry::get('config')->DATABASE_NAME . '_spamassassin');
$table = quoteIdentifier('bayes_token');
return array('up' => "\n\t\tCREATE TABLE IF NOT EXISTS {$database}.{$table} (\n\t\t\t`id` int(11) NOT NULL DEFAULT '0',\n\t\t\t`token` char(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n\t\t\t`spam_count` int(11) NOT NULL DEFAULT '0',\n\t\t\t`ham_count` int(11) NOT NULL DEFAULT '0',\n\t\t\t`atime` int(11) NOT NULL DEFAULT '0',\n\t\t\tPRIMARY KEY (`id`,`token`),\n\t\t\tKEY `bayes_token_idx1` (`id`,`atime`)\n\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\n\t");
function delete_sql_database(&$sql, $dmn_id, $db_id)
{
$query = <<<SQL_QUERY
select
sqld_name as db_name
from
sql_database
where
domain_id = ?
and
sqld_id = ?
SQL_QUERY;
$rs = exec_query($sql, $query, array($dmn_id, $db_id));
if ($rs->RecordCount() == 0) {
user_goto('manage_sql.php');
}
$db_name = quoteIdentifier($rs->fields['db_name']);
//
// have we any users assigned to this database;
//
$query = <<<SQL_QUERY
select
t2.sqlu_id as db_user_id,
t2.sqlu_name as db_user_name
from
sql_database as t1,
sql_user as t2
where
t1.sqld_id = t2.sqld_id
and
t1.domain_id = ?
and
t1.sqld_id = ?
SQL_QUERY;
$rs = exec_query($sql, $query, array($dmn_id, $db_id));
if ($rs->RecordCount() != 0) {
while (!$rs->EOF) {
$db_user_id = $rs->fields['db_user_id'];
$db_user_name = $rs->fields['db_user_name'];
sql_delete_user($sql, $dmn_id, $db_user_id);
$rs->MoveNext();
}
}
//
// drop desired database;
//
$query = <<<SQL_QUERY
drop database {$db_name}
SQL_QUERY;
$rs = exec_query($sql, $query);
write_log($_SESSION['user_logged'] . " : delete SQL database -> " . $db_name);
//
// delete desired database from the vhcs sql_database table;
//
$query = <<<SQL_QUERY
delete from
sql_database
where
domain_id = ?
and
sqld_id = ?
SQL_QUERY;
$rs = exec_query($sql, $query, array($dmn_id, $db_id));
}
