Adding existing SQL user does not list all users

MuhKuh · Nov 8th 2014

Tested with i-MSCP 1.1.14, but according to github it also exists in the latest version:

Following users are available:

When trying to add those users to a SQL database, only one of the users is shown.

This is probably because of line 150 in imscp/gui/public/client/sql_user_add.php

Code

if ($prevSeenName != $row['sqlu_name'] && !in_array($row['sqlu_name'], $userList)) {

The if condition should also consider sqlu_host.

**Nuxwin** · Nov 8th 2014

Confirmed

**Nuxwin** · Nov 8th 2014

Please, can you give a try by replacing the function client_generateSqlUserList() in the file gui/public/client/sql_user_add.php with:

PHP

function client_generateSqlUserList($tpl, $customerId, $databaseId)
{
// Select all SQL users of the current domain except those which are already assigned to the database on which we
// want operate on
$stmt = exec_query(
"
SELECT
sqlu_id, sqlu_name, sqlu_host
FROM
sql_user
INNER JOIN
sql_database USING(sqld_id)
WHERE
domain_id = ?
AND
CONCAT(sqlu_name, sqlu_host) NOT IN(SELECT CONCAT(sqlu_name, sqlu_host) FROM sql_user WHERE sqld_id = ?)
ORDER BY
sqlu_name, sqlu_host
",
array(get_user_domain_id($customerId), $databaseId)
);
if($stmt->rowCount()) {
while ($row = $stmt->fetchRow(PDO::FETCH_ASSOC)) {
$tpl->assign(
array(
'SQLUSER_ID' => intval($row['sqlu_id']),
'SQLUSER_NAME' => tohtml($row['sqlu_name']),
'SQLUSER_HOST' => tohtml(decode_idna($row['sqlu_host'])),
)
);
$tpl->parse('SQLUSER_LIST', '.sqluser_list');
}
} else {
$tpl->assign('SHOW_SQLUSER_LIST', '');
}
}

Display More

Thanks

MuhKuh · Nov 8th 2014

Almost

It gives some duplicates now if the user was already used in multiple databases

**Nuxwin** · Nov 8th 2014

Duplicate rows in the resulting HTML select element ? It's normally not possible.

Thanks ;

**Nuxwin** · Nov 8th 2014

Confirmed. I'll review the SQL clause to avoid that

**Nuxwin** · Nov 8th 2014

PHP

function client_generateSqlUserList($tpl, $customerId, $databaseId)
{
$stmt = exec_query(
"
SELECT
sqlu_id, sqlu_name, sqlu_host
FROM
sql_user
INNER JOIN
sql_database USING(sqld_id)
WHERE
domain_id = ?
AND
CONCAT(sqlu_name, sqlu_host) NOT IN(SELECT CONCAT(sqlu_name, sqlu_host) FROM sql_user WHERE sqld_id = ?)
GROUP BY
sqlu_name, sqlu_host
",
array(get_user_domain_id($customerId), $databaseId)
);
if($stmt->rowCount()) {
while ($row = $stmt->fetchRow(PDO::FETCH_ASSOC)) {
$tpl->assign(
array(
'SQLUSER_ID' => intval($row['sqlu_id']),
'SQLUSER_NAME' => tohtml($row['sqlu_name']),
'SQLUSER_HOST' => tohtml(decode_idna($row['sqlu_host'])),
)
);
$tpl->parse('SQLUSER_LIST', '.sqluser_list');
}
} else {
$tpl->assign('SHOW_SQLUSER_LIST', '');
}
}

Display More

Thanks

MuhKuh · Nov 8th 2014

Works

**Nuxwin** · Nov 8th 2014

Share