Is there a way to bulk-change user 'names' (note: not usernames)

We have a few hundred users on our forum whose names (not usernames, names) inadvertently got set to their email address as a result of how the external SSO provider (through wordpress) was set up when they created their accounts.

Is there a relatively simple way to bulk change those names from the_name@example.com to the_name (i.e. truncate everything from ‘@’ onward) without manually going into each profile and changing them one by one?

1 Like

You can do it from the rails console. It’s more complicated than can be explained here, especially since emails addresses are no longer in the user record. If you have a budget you can post in #marketplace or contact me directly.

Or but you don’t care about the actual email address…

Something like

users = User.where ('username like'%@%''")

Then you’d loop through and use gsub to strip everything from the @ to the end. That’s the best I can do on my phone.

2 Likes

I appreciate the response @pfaffman. I can write the SQL, but working in a rails console is a little outside my wheelhouse. How can I get into the discourse python console :slightly_smiling_face:?

Would be nice - though I understand also potentially dangerous - if there was a tool like the data explorer that also let you write an UPDATE query.

Anyone know if there is a way to connect a third party SQL tool (like Sequel Pro or equiv) to the running DB in self-hosted docker discourse?

1 Like

Yeah, but doing it in Rails makes it much harder to screw up your database, like if you do it with Rails, you’ll know that you didn’t make usernames that are, say, illegal (and if some other table needs to be updated when you change the one your messing with, Rails will do it, SQL won’t).

cd /var/discourse
./launcher enter app
rails c

Now you’re in rails.

users=User.where("username like '%@%'")
users.each do |user|
  user.name.gsub!(/@(.*)/,"")
  puts "New name: #{user.name}"
  # user.save
end

I think that’ll do what you want. If it seems like it, you can uncomment the user.save and try your luck.

Do a backup first, OK? (Or, better, on a staging server.)

3 Likes

Wow, thanks for the code!

I don’t have a staging server for this discourse instance, but i can take a backup and then run the code very early tomorrow morning when there is likely not much (or any) traffic so i can restore immediately if there’s an issue.

Thanks so much @pfaffman.

1 Like

You should be pretty safe if the puts look ok. Cheers!

1 Like

Truncating at the @ still leaves personal information (email address) largely visible to the Big Bad Internet (given that most people are @gmail.com or a few other big providers). You’d probably be better off setting them to the same value as username.

2 Likes

Thanks @lionel-rowe, that’s a good point. The usernames already are that same value though (username was created by taking everything prior to ‘@’ from the user’s email address).

The horror.

I had to make a minor change in the code and add a little procedure to make it work. Here’s the modified code:

users=User.where("name like '%@%'")

Then the rails console starts “more-ing” you through the user data so i needed to q out of that. Then:

users.each do |user|
  user.name.gsub!(/@(.*)/,"")
  puts "New name: #{user.name}"
  # user.save
end

Then the rails console showed me the list of new names (correct!), and then put me back into a “more” of the users. q again.

Then:

users.each do |user|
  user.name.gsub!(/@(.*)/,"")
  puts "New name: #{user.name}"
  user.save
end

…and no more email address user names and everything looking good on the forum front end.

Thanks again @pfaffman for your generous help.

4 Likes