Clean up unconfirmed users

Question

On my Q2A site I wanted to automatically clean up unconfirmed (spammer) accounts. My naïve approach was to just delete the accounts from the database. However, pupi1985 explained to me that direct manipulation in the database backend is not recommended, as it might leave the data in an inconsistent state.

So how do I use qa_delete_user() to accomplish the task?

Answer 1

After some fiddling around I came up with the following script:

#!/usr/bin/php
<?php
$include_dir = '/path/to/q2a/qa-include';

$timeout = 8;  # hours

require_once "${include_dir}/qa-base.php";
require_once "${include_dir}/app/users.php";
require_once "${include_dir}/app/users-edit.php";

$query = "SELECT userid,handle,email FROM ^users WHERE level = 0 AND date_sub(now(), INTERVAL ${timeout} HOUR) > created AND NOT flags & 1";

$unconfirmed_users = qa_db_read_all_assoc(qa_db_query_sub($query));
foreach($unconfirmed_users as $user) {
  qa_delete_user($user['userid']);
}

The query retrieves all normal users (level = 0) that don't have the "confirmed" flag set, and then deletes the accounts with the given user IDs. The additional fields fetched from the database can be used for logging or keeping a record of deleted accounts (so that you can analyze them for patterns that can be used in fending off spammers).

Set up a cron job to run the script every hour or so, and cleanup should happen automatically without risking inconsistencies in the database.

Comments

But one may confirm his email after 8 hours right? I think setting the email confirmation time to 48 hours (including in the confirmation email sent to users) would be a cleaner way. Also, clicking of the confirmation link after the expiry should show the error.

---

Well, of course you can adjust the deletion time in whichever way you like. Personally I think 8 hours is a sane default, since in my experience most normal users will register and confirm immediately (so they can post a question/answer/comment). Accounts that weren't confirmed after 8 hours were practically always spammers.

Answer 2

I unfortunately deleted all the fake users directly from the database using the following query:

DELETE FROM `qa_users` WHERE `qa_users`.`userid` != 1;

As a result, I am now seeing 11,000 pages marked as 404 errors in Google Search Console. Additionally, every time I add a new question, the number of 404 errors increases.

The issue is compounded by the fact that manually removing each 404 error from Google Search Console is very time-consuming, especially with around 12,000 error links to address.

Could anyone assist me in resolving this issue and dealing with these missing pages properly? Any advice or solutions would be greatly appreciated. Thank you!

Comments

this query will delete all users that are not the admin (user id 1). I don't think you will ever want to do that...

---

Please do not post questions as answers, even if they may be related to an existing question. There's an "ask new related question" button under every answer. Use that. Or post an entirely new question.

Answer 3

I use the Usermanager plugin for this.

Comments

What version of  Q2A and PHP are you using with Usermanager? I am not able to get it to work. I get a white screen when I try to access the plugin.  I don’t know of anyone who is using it with PHP 8.X.X

---

I use php version 8.03 and the latest Q2A version. The plugin works for me, but unfortunately only partially. The data can no longer be sorted, for example by user with 0 points. Perhaps someone can update the plugin? Then it would be very useful.

---

The User-Manager plugin is for manual management of users, not for scheduled automatic cleanups (which is what this question was about).