True email confirmation for registration (keeping unvalidated users from user table)

Question

I'm looking for a method to keep users out of the wp_users table until they have confirmed their email address.

WordPress sends new users their password by email, which is a de facto email confirmation. But whether they confirm - and even if the email is fake - they are already added to the table. You can't distinguish those who have confirmed so they fill up your user table and provide confusion if someone later tries to register with that email (if they never got the email, or never responded, or someone else used their email address, etc.). I don't want unconfirmed users/emails in my wp_users table.

I'm looking for an approach that can either

1. scrub unconfirmed accounts after say 48 hours, or better yet
2. put them in an intermediate table until they confirm, then register them, and scrub entries after a 48 hour timeout

No basic plugin seems to do this. One plugin adds "unconfirmed_user_" to the beginning of the username until they confirm, then removes it, but this still leaves them in the user table, and doesn't remove them after a timeout.

BuddyPress does everything I want to do, but has a great deal of bloat, and many extra tables.

What approach would seem most elegant to achieve my goals? I'm thinking it would involve (at the minimum):

1. an intermediate table with the username, email, generated key, and timestamp
2. hooking in to the registration function and intercepting the signup, and stashing the user in the table (EDIT: or more simply, after native registration process, delete from the wp_users table and add them to signup table). Then email a confirmation with the key appended to a wp-login.php url
3. intercepting logins with valid keys and deleting the entry and programmatically registering the user (essentially reversing step 2)
4. running a cron function to delete signups after 48 hours

Is there a bloat-free plugin i missed that accomplishes this, and if not, does my approach sound right? What are the key hooks involved and have I missed any "gotchas" in my process?

Answer 1

It would appear that this is an XY problem, where Y is what you're asking ( an attempt to solve X, your real problem ), and X is the inability to retrieve only confirmed/active users

Firstly, what you wanted to do isn't quite possible in multisites, there's already a table named wp_signups for these users, that tracks the pending confirmation and necessary keys, since a user might be confirmed on one blog and not another. So deleting users that aren't confirmed for your root blog, might delete users that are confirmed on other blogs, destroying data/logins.

So I'm going to answer your original problem, in the context of a singular site, as it's significantly simpler:

SELECT * FROM $wpdb->users WHERE user_status != 2

Taken from the plugin named unconfirmed, but with the logic flipped