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.