Search code examples
phpwordpressmariadbspamusermetadata

How to delete WordPress users with links in their profile description or other profile fields?

I have a lot of spam profiles on my WordPress website. They're linking to their spam sites using their profiles (homepage and description).

That is a good method to recognize them. Normal users don't write long AI generated texts with links on their profiles.

How can I bulk delete them (or first find them) with sql?

The data can be found in the wp_usermeta table in the meta_key description.

I tried this:

SELECT meta_key FROM `wp_usermeta` WHERE description != '';

but got this error message:

#1054 - Unknown column 'description' in 'where clause'

Seems that I don't understand the structure of this table at the moment.

Can you help me?

Thanks.

Solution

  • The correct working SQL query is:

    SELECT * FROM `wp_usermeta` WHERE `meta_key` = 'description' AND `meta_value` != '';

    Instead of searching in the database, you could filter users that have a description from admin users list, and then Bulk delete them.

    The code displays a new button called "Spam" in admin users list, that filter users that have a description (see the screenshot below).

    Try the following:

    add_action( 'restrict_manage_users', 'add_filter_users_has_description', 20 );
function add_filter_users_has_description() {
    echo '<input id="post-query-submit" type="submit" class="button" value="'.__('Spam').'" name="has_description">';
}

add_filter( 'pre_get_users', 'filter_users_with_description' );
function filter_users_with_description( $query ) {
    global $pagenow;

    if ( is_admin() && 'users.php' == $pagenow && isset($_GET['has_description']) && !empty($_GET['has_description']) ) {
        $meta_query = array(
            array(
                'key'     => 'description',
                'value'   => '',
                'compare' => '!=',
            )
        );
        $query->set( 'meta_query', $meta_query );
    }
}

    Code goes in functions.php file of your child theme (or in a plugin). Tested and works.

    enter image description here

    Addition: Display trimmed description in WordPress Users List custom column

    // add a custom column admin users list
add_filter( 'manage_users_columns', 'add_spam_column_to_users_list' );
function add_spam_column_to_users_list( $columns ) {
    $columns['description'] = __('Spam');
    return $columns;
}

// Users custom column: Display user description (trimmed)
add_filter( 'manage_users_custom_column', 'add_description_to_users_list_spam_column', 10, 3 );
function add_description_to_users_list_spam_column( $output, $column, $user_id ) {
    if ( 'description' === $column ) {
        if ( $description = get_user_meta( $user_id, 'description', true ) ) {
            $output = '<small style="word-break:break-word;">'.wp_trim_words( $description, 5 ).' ...</small>';
        }
    }
    return $output;
}

    Code goes in functions.php file of your child theme (or in a plugin). Tested and works.