Delete All Spam Pingbacks And Trackbacks From WordPress Database – The Hard Way
WordPress has a feature which lets you notify other blogs when you link to their content. This is called Trackback and it allows you to “Ping” other blogs and proactively tell them that you’ve linked to their page(s).
This is useful in specific scenarios. For example, when you “ping” a website and they have chosen to display pingbacks in their comments section, you get a free backlink to your post that linked to their content. Mostly, these links are all nofollowed (external no follow), so it doesn’t add to your SEO. But at times, this is useful and helps you gain some extra referral traffic.
However, there is a serious drawback of WordPress pingbacks.
Spam sites may use this feature to gain links from your website, if you are not careful. If you have chosen to show pingbacks and trackbacks in your WordPress theme, spam sites will continue to link to your site’s random pages, in hope of the return “trackback” or “pingback” link that is shown in your theme.
Result is that your site ends up having thousands of unnecessary pingbacks over a course of time.
Another major disadvantage of Pingbacks is that it greatly hogs down your WordPress database and increases its file size.
How I deleted more than 1,50,000 unnecessary Pingbacks from my WordPress Database
The other day while I was inspeting the source code of this website, I saw something really weird.
I was surprised to see that my site was allegedly spammed by thousands of spam pingbacks with spammy keywords. The worst part is that the number was quite high – 150,000 and all these spam pingbacks were shown in different pages, which is a bad practice.
Now I had to get rid of all these spam pingbacks and completely delete pingbacks and trackbacks from my WordPress site. I had two options
- Run an SQL query through PHPmyadmin, sort and separate Pingbacks from legitimate comments, and delete all pingbacks through another SQL query.
;I tried the above SQL query but unfortunately, the pingbacks were all there. I tried with other queries but nothing work. I am a little paranoid when it comes to playing with the site’s Database and thought of dropping this option for security reasons.
- Next, I logged into the site and tried deleting spam pingbacks and trackbacks manually. After some time, I realized its going to take ages and I must automate the whole process.
Here is what I did, it might be useful if you are in the same boat and want an easier way to get rid of all pingbacks and trackbacks from your WordPress database and theme.
1. Install the iMacros addon for Firefox. This is a superb tool which lets you automate tasks in the browser. We are going to need this to automate the deletion of spam pingbacks from phpmyadmin.
2. Login to your web hosting account, open the phpmyadmin tool and select your site’s database. I highly recommend backing up your website’s Database before proceeding.
3. Select the wp_comments table
4. Click on the comment_type table and sort comments by type. You should see all the “trackbacks” listed one after another.
Note: It is extremely important to sort all the trackback comments first, because in the next step we are going to automate their deletion with a Macro script in Firefox.
5. Hit the iMacro button in Firefox to open the iMacro sidebar. You are now ready to record your Macro script. Do not close your phpmyadmin window or navigayte away.
6. Now do these steps exactly in the same order as mentioned
- Hit the “record” button (this will start recording your mouse action).
- Scroll down to the bottom of the page and hit the button “Check All” (this will select all the comments visible on the page).
- Hit the “Delete” button.
- This will take you to a new page where you need to confirm whether you really want to delete the selected comments. Hit the “Yes” button.
- Click “Stop” in the Macro sidebar.
- Click “Save” and save the Macro script which you just finished recording.
Now here is how the magic happens.
I checked that one iteration of this operation deletes 30 comments. I checked that there are more than 150,000 spam pingbacks. So 150,000/30 = 5000, which means I need to repeat this process 5000 times.
Here is when the iMacro script that I recorded saves the day. All I have to do is to play the script and let it loop 5000 times.
You might be thinking this is weird. But there is a reason why I prefer deleting comments manually over an SQL query. First, I don’t have to worry if anything goes wrong and import the DB and fix stuff. Second, I can let Firefox run in the background and keep an eye on the script as it clean up all the spam comments one by one. It took me an entire day but then I wasn’t doing it anyway. The script was doing it and I could use that time on my routine work.
Prevent Pingbacks In WordPress
One last step.
You surely don’t want to do this clean up over and over again, so a good way to ensure that this mess never happens is to disable pingbacks in WordPress. Install the No Self Pings WordPress plugin and go to your website’s WordPress administration area > Settings > Discussions and uncheck the box “Allow link notifications from other blogs (pingbacks and trackbacks).
That way, if spam sites link to you or send your site a ping, it won’t be displayed in your website’s comment section and neither your site’s Database will be overloaded with spam comments.