Warning: Non-US characters in user name (General)

by Auge ⌂, Wednesday, May 03, 2017, 20:10 (2538 days ago) @ danielb987


I suggest that the field "user_name" should have a unique index, but if doing so, we might have other forums with duplicate user names.

There is a simple way to see if this is a problem. This SQL query will show any dublicate user names.

SELECT `user_name`, COUNT(`user_name`) FROM `mlf2_userdata` GROUP BY `user_name` HAVING COUNT(`user_name`) > 1

I build a (until now) non functional prototype of the check for duplicates/non-unique names.

if (empty($update['errors']) && in_array($settings['version'],array('2.3.5', '2.3.6', '', '2.3.7', '', '', '', '2.4', '2.4.1', '2.4.2'))) {
 $resCountNames = mysqli_query($connid, "SELECT `user_name`, COUNT(`user_name`) AS cnt FROM `mlf2_userdata` GROUP BY `user_name` HAVING COUNT(`user_name`) > 1");
 if ($resCountNames === false) {
  $update['errors'][] = 'Database error in line '.__LINE__.': ' . mysqli_error($connid);
 if (empty($update['errors'])) {
  if (mysqli_num_rows($connid, $resCountNames) > 0) {
   # list the non-unique user names
   $update['errors'][]  = '<h3><strong>Attention</strong>: Found non-unique user names!</h3>';
   $update['errors'][] .= '<p>Please make the names unique and inform the users in question about the changes.</p>';
   $update['errors'][] .= '<pre>';
   while ($row = mysqli_fetch_assoc($resCountNames)) {
    $update['errors'][] .= htmlspecialchars($row['user_name']) .": ". htmlspecialchars($row['cnt']) ."\n";
   $update['errors'][] .= '</pre>';
  } else {
   if(!@mysqli_query($connid, "ALTER TABLE `".$db_settings['userdata_table']."` MODIFY `user_name` varchar(255) COLLATE utf8_bin;")) {
    $update['errors'][] = 'Database error in line '.__LINE__.': ' . mysqli_error($connid);
   /* a few queries to add indexex to a few fields in a few tables */
   if(!@mysqli_query($connid, "ALTER TABLE `".$db_settings['userdata_table']."` ADD INDEX(`user_type`);")) {
    $update['errors'][] = 'Database error in line '.__LINE__.': ' . mysqli_error($connid);
   if(!@mysqli_query($connid, "ALTER TABLE `".$db_settings['userdata_table']."` ADD INDEX(`user_name`);")) {
    $update['errors'][] = 'Database error in line '.__LINE__.': ' . mysqli_error($connid);

In the block if (mysqli_num_rows($connid, $resCountNames) > 0), where I want to list the questionable names, we should add an additional info about names, that can be listed as identic but are not. This can be a case like you described it ("Daniel" vs. "Däniel" (wrote it with German "ä")) or similar. Do you or anyone else have a proposal for a few suitable sentences?

If an upgrade script is adding a uniqe index to a text field in the database, I strongly recommends that the query above is used before to check if the field is uniqe. It is much better to get an error before the upgrade script does any changes than to get the error in the middle of the upgrade.

FACK, thatswhy I defined the check as the first step of the upgrade process.

Tschö, Auge

Trenne niemals Müll, denn er hat nur eine Silbe!

Complete thread:

 RSS Feed of thread