Regis' Blog

L'informatique, L'amour, Les vaches

Remove Wordpress SPAM by the hand then with a shovel
November 09, 2015
in SPAM SQL Wordpress |
Share this post
| |
Remove Wordpress SPAM by the hand then with a shovel

My work take to me a lot of brain time and when I come home, I don’t have energy. So I took  few days to rest and to take care of my projects.

It take the oportunity to take time for my blog. And then, it’s horrible, I open the WordPress admin page and I have more than 2,200 pending comments. Either my blog has a huge success and I have thousands of visitors each day, either.. well.. OOpps I forgot to activate the anti-spam plugin.

By Hand

Well I have to stay one hour and half in the WordPress admin to click on « Select all » then to click on « Remove » the to click on « Apply ». I didn’t wanted to have such a lovely day. After a page or two, I realize that it’s always the same words that appear, including « Viagra ». Do the spammers have a problem with that?

By shovel

I start MySQL client (use the method of your choice PHPMyAdmin or SSH) and I start to seek:

SELECT COUNT(comment_ID) FROM wp_comments WHERE comment_content LIKE "%viagra%";  

Bam! More than 200 awsers. Well I continue to digg and I can select almost all spams:

SELECT  
    COUNT(comment_ID) 
FROM  
    wp_comments 
WHERE  
       comment_content LIKE "%viagra%" 
    OR comment_content LIKE "%doctor%" 
    OR comment_content LIKE "%canada%" 
    OR comment_content LIKE "%pill%" 
    OR comment_content LIKE "%sex%" 
    OR comment_content LIKE "%xxx%" 
    OR comment_content LIKE "%sale%" 
    OR comment_content LIKE "%cheap%" 
    OR comment_content LIKE "%rolex%" 
    OR comment_content LIKE "%ray ban%" 
    OR comment_content LIKE "%cartier%" 
    OR comment_content LIKE "%necropedosadomaso%" 
    OR comment_content LIKE "%seo%" 
    OR comment_content LIKE "%didas%" 
    OR comment_content LIKE "%nike%"

And then, modify it with:

DELETE FROM  
    wp_comments 
WHERE  
       comment_content LIKE "%viagra%" 
    OR comment_content LIKE "%doctor%" 
    OR comment_content LIKE "%canada%" 
    OR comment_content LIKE "%pill%" 
    OR comment_content LIKE "%sex%" 
    OR comment_content LIKE "%xxx%" 
    OR comment_content LIKE "%sale%" 
    OR comment_content LIKE "%cheap%" 
    OR comment_content LIKE "%rolex%" 
    OR comment_content LIKE "%ray ban%" 
    OR comment_content LIKE "%cartier%" 
    OR comment_content LIKE "%necropedosadomaso%" 
    OR comment_content LIKE "%seo%" 
    OR comment_content LIKE "%didas%" 
    OR comment_content LIKE "%nike%"

to remove all unwanted entries. There’s still stay 100 of comments. It’s hard to say if it’s good comments or bad comments. The comment authors give me more clues. The author URLS too. I don’t know anyone called Google, Yahoo or Bing.

DELETE FROM  
    wp_comments
WHERE  
       comment_author like "%google%" 
    OR comment_author like "%bing%" 
    OR comment_author like "%yahoo%" 
    OR comment_author like "%china%" 
    OR comment_author like "%seo%" 
    OR comment_author like "%google%" 
    OR comment_author like "%skyrim%" 
    OR comment_author like "%nfl%" 
    OR comment_author_url like "%seo%" 
    OR comment_author_url like "%openimagingsystem%" 
    OR comment_author_url like "%rugby%"

At this point, most of spams have disappeared. There’s still 43 comments. I can do it by hand.

Here’s the complete SQL request:

DELETE FROM  
    wp_COMMENT 
WHERE  
       comment_content LIKE "%viagra%" 
    OR comment_content LIKE "%doctor%" 
    OR comment_content LIKE "%canada%" 
    OR comment_content LIKE "%pill%" 
    OR comment_content LIKE "%sex%" 
    OR comment_content LIKE "%xxx%" 
    OR comment_content LIKE "%sale%" 
    OR comment_content LIKE "%cheap%" 
    OR comment_content LIKE "%rolex%" 
    OR comment_content LIKE "%ray ban%" 
    OR comment_content LIKE "%cartier%" 
    OR comment_content LIKE "%necropedosadomaso%" 
    OR comment_content LIKE "%seo%" 
    OR comment_content LIKE "%didas%" 
    OR comment_content LIKE "%nike%" 

    OR comment_author like "%google%" 
    OR comment_author like "%bing%" 
    OR comment_author like "%yahoo%" 
    OR comment_author like "%china%" 
    OR comment_author like "%seo%" 
    OR comment_author like "%skyrim%" 
    OR comment_author like "%nfl%" 

    OR comment_author_url like "%seo%" 
    OR comment_author_url like "%openimagingsystem%" 
    OR comment_author_url like "%rugby%"

Plugin proposal

Now, I use the wordpress plugin Anti-SPAM which is

very simple, which have no configuration and which is based on Javascript injection.  The amount of spam is now near 0.

Please fell free to send me more keyworks to achieve the SQL request.