Fix UniqueUsernameValidator comparisonv2.3.3

Comparison was downcasing only one side, therefore if previously
existing account had a non-lowercase spelling, it would be ignored
when checking for duplicates.

New rake task `mastodon:maintenance:find_duplicate_usernames` will
help find constraint violations that might have occured from the
presence of this bug.

Bump version to 2.3.3

4 files changed, 21 insertions, 3 deletions

diff --git a/app/models/concerns/account_finder_concern.rb b/app/models/concerns/account_finder_concern.rb
index 2e8a7fb3799..6b7237e89d5 100644
--- a/app/models/concerns/account_finder_concern.rb
+++ b/app/models/concerns/account_finder_concern.rb
@@ -30,7 +30,7 @@ module AccountFinderConcern
     end
 
     def account
-      scoped_accounts.take
+      scoped_accounts.order(id: :asc).take
     end
 
     private
diff --git a/app/validators/unique_username_validator.rb b/app/validators/unique_username_validator.rb
index c76407b16fc..fb67105dd6b 100644
--- a/app/validators/unique_username_validator.rb
+++ b/app/validators/unique_username_validator.rb
@@ -6,7 +6,7 @@ class UniqueUsernameValidator < ActiveModel::Validator
 
     normalized_username = account.username.downcase.delete('.')
 
-    scope = Account.where(domain: nil, username: normalized_username)
+    scope = Account.where(domain: nil).where('lower(username) = ?', normalized_username)
     scope = scope.where.not(id: account.id) if account.persisted?
 
     account.errors.add(:username, :taken) if scope.exists?
diff --git a/lib/mastodon/version.rb b/lib/mastodon/version.rb
index 121c5c693dc..a6927eec316 100644
--- a/lib/mastodon/version.rb
+++ b/lib/mastodon/version.rb
@@ -13,7 +13,7 @@ module Mastodon
     end
 
     def patch
-      2
+      3
     end
 
     def pre
diff --git a/lib/tasks/mastodon.rake b/lib/tasks/mastodon.rake
index cf32b149545..18bdc347fc4 100644
--- a/lib/tasks/mastodon.rake
+++ b/lib/tasks/mastodon.rake
@@ -740,6 +740,24 @@ namespace :mastodon do
       LinkCrawlWorker.push_bulk status_ids
     end
 
+    desc 'Find case-insensitive username duplicates of local users'
+    task find_duplicate_usernames: :environment do
+      include RoutingHelper
+
+      disable_log_stdout!
+
+      duplicate_masters = Account.find_by_sql('SELECT * FROM accounts WHERE id IN (SELECT min(id) FROM accounts WHERE domain IS NULL GROUP BY lower(username) HAVING count(*) > 1)')
+      pastel = Pastel.new
+
+      duplicate_masters.each do |account|
+        puts pastel.yellow("First of their name: ") + pastel.bold(account.username) + " (#{admin_account_url(account.id)})"
+
+        Account.where('lower(username) = ?', account.username.downcase).where.not(id: account.id).each do |duplicate|
+          puts "  " + pastel.red("Duplicate: ") + admin_account_url(duplicate.id)
+        end
+      end
+    end
+
     desc 'Remove all home feed regeneration markers'
     task remove_regeneration_markers: :environment do
       keys = Redis.current.keys('account:*:regeneration')