/**
* Get table data
*
* @return array
*/
function reseller_getDatatable()
{
$columns = array('alias_name', 'alias_mount', 'url_forward', 'admin_name', 'alias_status');
$nbColumns = count($columns);
/* Paging */
$limit = '';
if (isset($_GET['iDisplayStart']) && isset($_GET['iDisplayLength']) && $_GET['iDisplayLength'] != '-1') {
$limit = 'LIMIT ' . intval($_GET['iDisplayStart']) . ', ' . intval($_GET['iDisplayLength']);
}
/* Ordering */
$order = '';
if (isset($_GET['iSortCol_0'])) {
$order = 'ORDER BY ';
if (isset($_GET['iSortingCols'])) {
$iSortingCols = intval($_GET['iSortingCols']);
for ($i = 0; $i < $iSortingCols; $i++) {
if (isset($_GET['iSortCol_' . $i]) && isset($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])]) && $_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == 'true' && isset($_GET['sSortDir_' . $i])) {
$order .= $columns[intval($_GET['iSortCol_' . $i])] . ' ' . $_GET['sSortDir_' . $i] . ', ';
}
}
}
$order = substr_replace($order, '', -2);
if ($order == 'ORDER BY') {
$order = '';
}
}
/* Filtering */
$where = 'WHERE created_by = ' . quoteValue($_SESSION['user_id'], PDO::PARAM_INT);
if (isset($_GET['sSearch']) && $_GET['sSearch'] != '') {
$where .= ' AND (';
for ($i = 0; $i < $nbColumns; $i++) {
$where .= "{$columns[$i]} LIKE " . quoteValue("%{$_GET['sSearch']}%") . ' OR ';
}
$where = substr_replace($where, '', -3);
$where .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < $nbColumns; $i++) {
if (isset($_GET["bSearchable_{$i}"]) && $_GET["bSearchable_{$i}"] == 'true' && isset($_GET["sSearch_{$i}"]) && $_GET["sSearch_{$i}"] != '') {
$where .= "AND {$columns[$i]} LIKE " . quoteValue("%{$_GET["sSearch_{$i}"]}%");
}
}
/* Get data to display */
$rResult = execute_query("\n\t\t\tSELECT\n\t\t\t\tSQL_CALC_FOUND_ROWS alias_id, " . implode(', ', $columns) . "\n\t\t\tFROM\n\t\t\t\tdomain_aliasses\n\t\t\tINNER JOIN\n\t\t\t\tdomain USING(domain_id)\n\t\t\tINNER JOIN\n\t\t\t\tadmin ON(admin_id = domain_admin_id)\n\t\t\t{$where}\n\t\t\t{$order}\n\t\t\t{$limit}\n\t\t");
/* Total records after filtering (without limit) */
$stmt = execute_query('SELECT FOUND_ROWS()');
$iTotalDisplayRecords = $stmt->fetchRow(PDO::FETCH_NUM);
$iTotalDisplayRecords = $iTotalDisplayRecords[0];
/* Total record before any filtering */
$stmt = exec_query("\n\t\t\tSELECT\n\t\t\t\tCOUNT(alias_id)\n\t\t\tFROM\n\t\t\t\tdomain_aliasses\n\t\t\tINNER JOIN\n\t\t\t\tdomain USING(domain_id)\n\t\t\tINNER JOIN\n\t\t\t\tadmin ON(admin_id = domain_admin_id)\n\t\t\tWHERE\n\t\t\t\tcreated_by = ?\n\t\t", $_SESSION['user_id']);
$iTotalRecords = $stmt->fetchRow(PDO::FETCH_NUM);
$iTotalRecords = $iTotalRecords[0];
/* Output */
$output = array('sEcho' => intval($_GET['sEcho']), 'iTotalDisplayRecords' => $iTotalDisplayRecords, 'iTotalRecords' => $iTotalRecords, 'aaData' => array());
$trDelete = tr('Delete');
$trEdit = tr('Edit');
$trActivate = tr('Activate');
while ($data = $rResult->fetchRow(PDO::FETCH_ASSOC)) {
$row = array();
$aliasName = decode_idna($data['alias_name']);
for ($i = 0; $i < $nbColumns; $i++) {
if ($columns[$i] == 'alias_name') {
if ($data['alias_status'] == 'ok') {
$row[$columns[$i]] = "<a href=\"http://www.{$aliasName}\" target=\"_blank\" class=\"icon i_domain_icon\">" . $aliasName . '</a>';
} else {
$row[$columns[$i]] = '<span class="icon i_domain_icon">' . decode_idna($data[$columns[$i]]) . '</span>';
}
} elseif ($columns[$i] == 'admin_name') {
$row[$columns[$i]] = tohtml(decode_idna($data[$columns[$i]]));
} elseif ($columns[$i] == 'alias_status') {
$row[$columns[$i]] = translate_dmn_status($data[$columns[$i]]);
} else {
$row[$columns[$i]] = tohtml($data[$columns[$i]]);
}
}
$aliasId = $data['alias_id'];
switch ($data['alias_status']) {
case 'ok':
$actions = "<a href=\"alias_edit.php?id={$aliasId}\" class=\"icon i_edit\" " . "title=\"{$trEdit}\">{$trEdit}</a>";
$actions .= "\n<a href=\"alias_delete.php?id={$aliasId}\" onclick=\"return delete_alias('{$aliasName}')\" " . "class=\"icon i_close\" title=\"{$trDelete}\">{$trDelete}</a>";
break;
case 'ordered':
$actions = "<a href=\"alias_order.php?action=activate&act_id={$aliasId}\" class=\"icon i_open\" " . "title=\"{$trActivate}\">{$trActivate}</a>";
$actions .= "\n<a href=\"alias_order.php?action=delete&del_id={$aliasId}\" " . "onclick=\"return delete_alias_order('{$aliasName}')\" class=\"icon i_close\" " . "title=\"{$trDelete}\">{$trDelete}</a>";
break;
default:
$actions = tr('n\\a');
}
$row['actions'] = $actions;
$output['aaData'][] = $row;
}
return $output;
}
/**
* Makes the PHP mail function disableable
* - Adds reseller_props.php_ini_al_mail_function permission column
* - Adds domain.phpini_perm_mail_function permission column
* - Adds PHP mail permission property in hosting plans if any
*
* @throws iMSCP_Exception
*/
protected function r212()
{
$sqlUpd = array();
// Add permission column for resellers
$sqlUpd[] = $this->addColumn('reseller_props', 'php_ini_al_mail_function', "VARCHAR(15) NOT NULL DEFAULT 'yes' AFTER `php_ini_al_disable_functions`");
# Add permission column for clients
$sqlUpd[] = $this->addColumn('domain', 'phpini_perm_mail_function', "VARCHAR(20) NOT NULL DEFAULT 'yes' AFTER `phpini_perm_disable_functions`");
// Add PHP mail permission property in hosting plans if any
$stmt = exec_query('SELECT id, props FROM hosting_plans');
while ($row = $stmt->fetchRow()) {
$id = quoteValue($row['id'], PDO::PARAM_INT);
$props = explode(';', $row['props']);
if (sizeof($props) < 26) {
array_splice($props, 18, 0, 'yes');
// Insert new property at position 18
$sqlUpd[] = 'UPDATE hosting_plans SET props = ' . quoteValue(implode(';', $props)) . 'WHERE id = ' . $id;
}
}
return $sqlUpd;
}
/**
* Get logs
*
* @throws iMSCP_Exception
*/
function admin_getLogs()
{
try {
// Filterable / orderable columns
$columns = array('log_time', 'log_message');
$nbColumns = count($columns);
$indexColumn = 'log_id';
/* DB table to use */
$table = 'log';
/* Paging */
$limit = '';
if (isset($_GET['iDisplayStart']) && isset($_GET['iDisplayLength']) && $_GET['iDisplayLength'] !== '-1') {
$limit = 'LIMIT ' . intval($_GET['iDisplayStart']) . ', ' . intval($_GET['iDisplayLength']);
}
/* Ordering */
$order = '';
if (isset($_GET['iSortCol_0']) && isset($_GET['iSortingCols'])) {
$order = 'ORDER BY ';
for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] === 'true') {
$sortDir = isset($_GET['sSortDir_' . $i]) && in_array($_GET['sSortDir_' . $i], array('asc', 'desc')) ? $_GET['sSortDir_' . $i] : 'asc';
$order .= $columns[intval($_GET['iSortCol_' . $i])] . ' ' . $sortDir . ', ';
}
}
$order = substr_replace($order, '', -2);
if ($order == 'ORDER BY ') {
$order = '';
}
}
/* Filtering */
$where = '';
if (isset($_GET['sSearch']) && $_GET['sSearch'] != '') {
$where .= 'WHERE (';
for ($i = 0; $i < $nbColumns; $i++) {
$where .= $columns[$i] . ' LIKE ' . quoteValue('%' . $_GET['sSearch'] . '%') . ' OR ';
}
$where = substr_replace($where, '', -3);
$where .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < $nbColumns; $i++) {
if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] === 'true' && $_GET['sSearch_' . $i] !== '') {
$where .= "AND {$columns[$i]} LIKE " . quoteValue('%' . $_GET['sSearch_' . $i] . '%');
}
}
/* Get data to display */
$rResult = execute_query('
SELECT
SQL_CALC_FOUND_ROWS ' . str_replace(' , ', ' ', implode(', ', $columns)) . "\n\t\t\t\tFROM\n\t\t\t\t\t{$table}\n\t\t\t\t{$where}\n\t\t\t\t{$order}\n\t\t\t\t{$limit}\n\t\t\t");
/* Data set length after filtering */
$resultFilterTotal = execute_query('SELECT FOUND_ROWS()');
$resultFilterTotal = $resultFilterTotal->fetchRow(\PDO::FETCH_NUM);
$filteredTotal = $resultFilterTotal[0];
/* Total data set length */
$resultTotal = exec_query("SELECT COUNT({$indexColumn}) FROM {$table}");
$resultTotal = $resultTotal->fetchRow(\PDO::FETCH_NUM);
$total = $resultTotal[0];
/* Output */
$output = array('sEcho' => intval($_GET['sEcho']), 'iTotalRecords' => $total, 'iTotalDisplayRecords' => $filteredTotal, 'aaData' => array());
/** @var $cfg iMSCP_Config_Handler_File */
$cfg = iMSCP_Registry::get('config');
$dateFormat = $cfg['DATE_FORMAT'] . ' H:i:s';
while ($data = $rResult->fetchRow(PDO::FETCH_ASSOC)) {
$row = array();
for ($i = 0; $i < $nbColumns; $i++) {
if ($columns[$i] == 'log_time') {
$row[$columns[$i]] = date($dateFormat, strtotime($data[$columns[$i]]));
} else {
$replaces = array('/\\b(deactivated|delete[sd]?|deletion|deactivation|failed)\\b/i' => '<strong style="color:#FF0000">\\1</strong>', '/\\b(remove[sd]?)\\b/i' => '<strong style="color:#FF0000">\\1</strong>', '/\\b(unable)\\b/i' => ' <strong style="color:#FF0000">\\1</strong>', '/\\b(activated|activation|addition|add(s|ed)?|switched)\\b/i' => '<strong style="color:#33CC66">\\1</strong>', '/\\b(created|ordered)\\b/i' => '<strong style="color:#3300FF">\\1</strong>', '/\\b(update[sd]?)\\b/i' => '<strong style="color:#3300FF">\\1</strong>', '/\\b(edit(s|ed)?)\\b/i' => '<strong style="color:#33CC66">\\1</strong>', '/\\b(unknown)\\b/i' => '<strong style="color:#CC00FF">\\1</strong>', '/\\b(logged)\\b/i' => '<strong style="color:#336600">\\1</strong>', '/\\b(Warning[\\!]?)\\b/i' => '<strong style="color:#FF0000">\\1</strong>');
foreach ($replaces as $pattern => $replacement) {
$data[$columns[$i]] = preg_replace($pattern, $replacement, $data[$columns[$i]]);
}
$row[$columns[$i]] = $data[$columns[$i]];
}
}
$output['aaData'][] = $row;
}
admin_sendJsonResponse(200, $output);
} catch (iMSCP_Exception_Database $e) {
write_log(sprintf('Unable to get logs: %s', $e->getMessage()), E_USER_ERROR);
admin_sendJsonResponse(500, array('message' => tr('An unexpected error occurred: %s', $e->getMessage())));
}
admin_sendJsonResponse(400, array('message' => tr('Bad request.')));
}
/**
* 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');
}
/**
* Deletes an SQL user
*
* @throws iMSCP_Exception_Database
* @param int $dmnId Domain unique identifier
* @param int $userId Sql user unique identifier
* @return bool TRUE on success, FALSE otherwise
*/
function sql_delete_user($dmnId, $userId)
{
$stmt = exec_query('
SELECT sqlu_name, sqlu_host, sqld_name FROM sql_user INNER JOIN sql_database USING(sqld_id)
WHERE sqlu_id = ? AND domain_id = ?
', array($userId, $dmnId));
if (!$stmt->rowCount()) {
return false;
}
$row = $stmt->fetchRow(PDO::FETCH_ASSOC);
$user = $row['sqlu_name'];
$host = $row['sqlu_host'];
$dbName = $row['sqld_name'];
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onBeforeDeleteSqlUser, array('sqlUserId' => $userId, 'sqlUsername' => $user, 'sqlUserhost' => $host));
$stmt = exec_query('SELECT COUNT(sqlu_id) AS cnt FROM sql_user WHERE sqlu_name = ? AND sqlu_host = ?', array($user, $host));
$row = $stmt->fetchRow(PDO::FETCH_ASSOC);
if ($row['cnt'] < 2) {
exec_query('DROP USER ?@?', array($user, $host));
} else {
// SQL user has privileges on many databases. We remove its privileges for the involved database only
exec_query(sprintf('REVOKE ALL PRIVILEGES ON %s.* FROM %s@%s', quoteIdentifier($dbName), quoteValue($user), quoteValue($host)));
}
exec_query('DELETE FROM sql_user WHERE sqlu_id = ?', $userId);
iMSCP_Events_Aggregator::getInstance()->dispatch(iMSCP_Events::onAfterDeleteSqlUser, array('sqlUserId' => $userId, 'sqlUsername' => $user, 'sqlUserhost' => $host));
return true;
}
