Home > Computer > Easily Delete All WordPress Spam Comments Using phpMyAdmin
Donation Goal
Donate Now Goal amount for this year: 799 USD, Received: 100 USD (13%)
Please donate to help support this website. The funds will be used to purchase owned license of LiteSpeed Web Server Enterprise (2-CPU). It provides superior performance in terms of raw speed, scalability and anti-DDoS capabilities.

Easily Delete All WordPress Spam Comments Using phpMyAdmin

Posted By Raymond In Category: Computer

Jul
4
2009

For the frequent comment posters in Raymond.CC Blog, you might have noticed an addition of random words to type in before your comment gets in. Well this blog site has been attacked by some people that has nothing else better to do in their life. Yesterday while I was writing an article, I noticed that the pending comments awaiting for moderation shows about 800+! I normally check for pending comments every few hours a day and it’s impossible for the comments to grow that much in such a short while. At first I thought those spam bots has found a new way to bypass Akismet but seeing those comments, it looked like it had been done by some script kiddie.
delete wordpress spam
The WordPress spam comment attack is pretty smart because it is definitely automated and all the IP address were different. I assume the wordpress comment spamming tool uses a list of open proxy and tries to post comments in every article. Fortunately I have configured all comments to be moderated so that inappropriate comments doesn’t get posted out and create unnecessary drama.

If your WordPress site has been attacked by similar comments spamming tool, here’s what you can do to easily delete all WordPress spam comments using phpMyAdmin and implement a simple CAPTCHA system to make the spammer’s life difficult.


Since the spam comments keeps in coming in, I had to do something to stop it first. Log in to your WordPress Admin site, go to Settings > Discussion and CHECK “Users must be registered and logged in to comment“. Now the spam comments should stop since it requires an account. To be on the safe side, disable registration by going to Settings > General and UNCHECK “Anyone can register“.

When you’ve done both of the steps above, no comments will get in.

Now to clean up the spam that’s in moderation. Log in to phpMyAdmin, select the database that has WordPress installed and go to SQL tab. Type the following command below and click the Go button.

delete from wp_comments where comment_approved = '0'

phpMyAdmin delete wordpress spam comments
As you can see at the screenshot above, phpMyAdmin took less than 0.1 seconds to clean up 886 spam comments.

There are a few WordPress plugins that can help you implement CAPTCHA in your comment form. As for me, I installed reCAPTCHA because other than preventing easy comment spamming, it also helps to digitize books, newspapers and old time radio shows. Simply download wp-reCAPTCHA, drop it in plugins folder and activate. Sign up for a free reCAPTCHA account to get public and private key. Enter both keys in your Wordrpess’ Settings > reCAPTCHA.

Finally you will need to allow your visitors to post comments. Go back to Settings > Discussion and UNCHECK “Users must be registered and logged in to comment“.

The spammer took hours to spam the comment form but it took me less than 1 second to clean up the spam and another 3 minutes to install reCAPTCHA in my comment form. Do the math and the spammer sure looks silly. reCAPTCHA is not impossible to crack but the spammers would have to spend even more of their precious time and effort to do something that they don’t even gain anything from it.


Related posts:
  • Nasty Spam on WordPress Comment box
  • How To Turn Off Comments For All Post In WordPress
  • Stop Guestbook Spam, Blog Spam, Wiki Spam, Comment Spam with CAPTCHA Service
  • Gmail Marks Legitimate Emails as SPAM! How To Correct This?
  • Bounce Email Spam Messages Back to Spammers with Non-Delivery Notifications
    • Mark

      Great post Ray!

    • http://techuse.net Ali Hussain

      Great. Lesser spam more useful

    • Hi

      I LOL’d

      or as gamers say “pwnt”

    • TCzzz

      Nice work quick actions save the rest of us from these little kids.its a shame they waste their time with these sort of things especially on this kind of site which is just informative and causing no problems anywhere.

      shame on them and good on you ray.

    • Mattehw Maltby

      Good on you Raymond. To all spammer of Raymond.cc blog – Get a life, bother some one that deserves it! I’m not gonna get political, but be cool, Raymond.cc is on the people’s side!

    • http://www.technostarry.com Saqib

      Nice trick there Ray.

    • Decent60

      Unfortunately, it’s those spammers that make it harder for those who actually enjoy your articles and want to comment easily. There are times when even ReCAPTCHA is hard to read for a actual people. I know there is a refresh button but it’s just an annoyance that I don’t like to deal with. Most of the time I’d rather just not post than have to deal with those silly things.
      Oh well, with popularity comes annoying kids who have nothing better to do with their lives.

      Good luck Raymond and thanks for the tip.

    • Samuel

      Thanks Ray! I am searching for a good Captcha plugin and luckily I found this article. Re-captcha is compatible upto 2.7.1. Are you this plugin? Are there any issues with it when using on WP 2.8? Because I have had some troubles with plugins on WP 2.8 and it takes lot of time to find the solution. Any suggestions?

    • John

      Thanks for that Raymond. a very informative piece for someone like myself that is just starting out with WordPress.

    • Steveo

      My PhPbb Forum is being targetted and is creating 20 User accounts per day. I have no idea what to do man.

    • brayden

      Thanks ray, another great article as usual :P, anyway steveo there are CAPTCHA additions on phpBB, just search their site for some. It should slow down the registration process on the spam. oh and disable accounts from the same e-mail and IP.

    • http://www.virtualmallorca.com max

      thanks, i had 1500 unread comments after a holiday. using php myadmin did the trick.

    • http://carameltoffee.net Adlina

      Gosh, this made my day. I installed Akismet on one of my sites running WP as a CMS quite late and now I have over 1,200 comments awaiting moderation. This makes me breathe easy again. Thanks for the tips!

    • http://www.soitscometothis.com SoItsComeToThis

      Ray,

      I’ve gone in and deleted over 29,000 spam comments by removing them from the database. But comment count on the sidebar still says I have 29000 comments to review.

      Is there some other function of WordPress that calculates the comment count on the sidebar?

    • http://developdaly.com/ Patrick Daly

      Thanks for the idea. I had a client with 7,000 spam comments. No way was I going to delete all of those by hand, plus the front-end timed out when trying to list more than 20 comments.

    • http://techbutt.com Azian

      thanks a lot! i got 3000++ spam messages. now it all gone. thanks!

    • http://www.eface.in Aravind

      Thankyou verymuch ….. i have deleted 2328 spam pending comments !!!

    • http://martin.saulis.com Martin

      WordPress stores comment count in a separate field in the database for each post (that is an understandable way of optimizing the system, meaning that there is no need to count comments upon every page load), and automatically updates the comment count field on each new comment.

      So, do not forget, that after deleting the comments like that manually from the database, you will have to manually update the appropriate comment count field in the database for each affected post, which might be a headache.

    • http://webenk.net EnKrypt

      Good on you Buddy! That worked out like… SWEET!
      Unlike others who’ve had the problem, I only had 2,100 spammin’ crammers on my site.

      Thanks for sharing your brain cells with the rest of us!

      EnK

    • one

      nice trick, nice work

    • http://www.cayyolu-bilgisayar.com ali veli

      Nice Shoot Guy :)
      Thank you very very much !

    • http://www.thescube.com Shovan

      But wont this all un-moderated comments?

    • kamilla

      hvordan sletter man wordpress.com ? kan du hjelpe meg ? ta kontakt på e-post :)

      - trenger virkelig hjelp ;)

    • http://gloriasetiadi.com/ gloriasetiadi

      nice info, thanks for sharing ;)

    • http://www.howtosmellawhale.com Kerry

      I was beginning to think I was doomed to forever have 17,000+ unapproved comments in my database. Thank you for this article!

    • http://richskibum.com Willie

      Man you saved me with this. I just updated some themes and when I did things reset and the next day there was 500 comments.
      I also had the same ip break in to one site. They used admin in wp some how. They set up admin credentials and put a script on my server. So check your sites guys and gals

    • http://www.jimcarson.com jim

      Thanks, man, that saved a lot of time. Had 1,453 this morning from a bot in the Netherlands.

    • http://www.evocreative.com Fernando Davila

      Thank you! Just what I wanted… I had more than 5,000 unapproved comments! All spam!

    • http://www.change-is-refreshing.com nikhil

      But how do i know if one of the bulk messages is from a friend? it helped anyways thank you so much

    • http://godlikeproductions.info Q

      Thank you! That it only took 0.0891 seconds to do it. I had over 1300 pending. Yikes.

    • fraser

      This is so great, had a ton of spam, and now none.
      wonderful

    • Fernanda Prevedello

      Thank you very much! Works pretty fine! =)

    • http://www.facebook.com/marygeitner Mary Geitner

      New to dbase modification. I keep getting a message in SQL saying:
      #1146 – Table ‘spaceliftcolorado_dreamh.wp_comments’ doesn’t exist.I changed this dbase password earlier today — not sure if that has any bearing, but I can’t get further. Thanks

    Copyright © 2005-2012 - Raymond.CC Blog