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.