Getting rid of spammers/sploggers (fake users) and their comments in WordPress

One of my other sites, Vision of Earth, has done well enough over the years that it has attracted attention from spammers. The problem I’ve been facing recently was that tens of thousands of “people” were subscribing to the site, receiving welcome emails, and attempting to post comments.

I use Akismet, so these comments weren’t actually getting through to the blog itself, but their sheer number made them a problem.

First, auto-delete spam comments

This section assumes that you have the Akismet WordPress Plugin installed. Since it comes by default with most installations of WordPress, I think this is the easiest approach that is also very effective.

To make Akismet automatically delete (most) spam comments, go to Settings -> Akismet and click the option that says “Silently discard the worst and most pervasive spam so I never see it.” Once you do that, Akismet will start deleting the vast majority of the spam you get. In my case, the number appears to be over 90%.

Akismet Spam Deletion Setting

If 90% isn’t good enough for you, or you’d like assurance that every spam comment is deleted, I suggest that you use Spam Comments Cleaner. The setup is ridiculously simple and intuitive, and I can verify that it indeed works.

Second, stop spammers from registering

There are a variety of plugins out there that will help you with this. The one I decided to go with today was Stop Spammers. When you install it, you can optionally set it up to link up with StopForumSpam, Project Honey Pot, and BotScout via API keys (free). This was a pretty easy process, and one that I’d recommend for the future health of the Internet. By linking up with these tools, we both empower them and empower ourselves.

Third, clean spammers/sploggers out of the database

Warning: I didn’t find an easy way to do this. The plugins WangGuard and Inactive User Deleter purport to be able to automatically delete these accounts. I tried both of them and they both failed. For my tests, I had about 46 thousand inactive/spam accounts on my server.

WangGuard can’t batch delete more users than you can view at one time on the “Users” menu in WordPress. If I raise the number of viewable Users (by clicking “Screen Options” in the top-right) to more than a few hundred, the plugin fails with an out-of-memory problem.

Inactive User Deleter purports to be able to filter users, but it’s selective power is very weak and it also crashes with an out-of-memory error when I try to run it.

I, unfortunately, had to do this manually. Luckily I know what I’m doing in a MySQL database and was able to find enough help online to finish the task. If you want to follow the rest of this guide, you’ll need to have command-line access to your server. Similar actions might be possible on various server management systems, but I won’t deal with them here.

Before I could start deleting accounts, I needed to be absolutely sure that I was going to be able to:

  1. recover from this effort in case it fails and
  2. delete only those Users that I specifically want to delete.

Make a backup of certain database tables

On the command line, I ran:

mysqldump <wordpress_database_name> wp_users wp_usermeta -u <wordpress_username> -p > ~/2014-10-05-database_cleanup_backup_of_users_and_usermeta.sql

The intent of this command is to backup the wp_users and wp_usermeta tables from the <wordpress_database_name> database. For me this created a 28 megabyte file. Note that the ‘<‘ and ‘>’ symbols shouldn’t be present in your command (except for the final one after ‘-p’).

If you don’t know your <wordpress_database_name> or <wordpress_username> (or its associated password, which you’ll need to type in after you run the command), you can find them in the wp-config.php file in the base directory of your WordPress installation.

You will see lines like:

define('DB_NAME', 'mydatabasename'); // The name of the database
define('DB_USER', 'mydbusername'); // Your MySQL username
define('DB_PASSWORD', 'mypassword123abc'); // ...and password

You’ll need all of these pieces of information in the next section as well.

Note that you can restore these tables by running:

mysql <wordpress_database_name> -u <wordpress_username> -p < ~/2014-10-05-database_cleanup_backup_of_users_and_usermeta.sql

Beware, if you do this at a later date, you’ll destroy any information that was added to these tables since you made the backup. This is a backup that you don’t want to have to use in the distant future. You can use more advanced MySQL queries to load these things without losing what you have, but that is far beyond the scope of this post.

Manually delete the bad user accounts

I log into mysql using this command:

mysql <wordpress_database_name> -u <wordpress_username> -p

You’ll need to enter the password to get in. Once in, I tested a bunch of queries to ensure that I understood how many users existed and how many I wanted to delete, and of what types. In my case, I wanted to delete everyone who was a “Subscriber” who had not created a comment or a post. These are pretty intense criteria. Don’t blindly apply what I did here unless you want to delete exactly that set of people.

I deleted the user entries:

delete from wp_users WHERE ID NOT IN (SELECT post_author FROM wp_posts UNION SELECT user_id FROM wp_comments UNION select distinct user_id from wp_usermeta where meta_key = 'wp_capabilities' and meta_value not like '%subscriber%' );

When the command is successfully completed, it will tell you something like:

Query OK, 45421 rows affected (8 min 36.24 sec)

Now to get rid of the records in wp_usermeta, we run:

FROM wp_usermeta
WHERE user_id NOT IN (SELECT ID FROM wp_users);

When it is successful, it will show you the number of rows deleted, like so:

Query OK, 547468 rows affected (20.43 sec)

Note that wp_usermeta will probably contain more than ten times as many entries as wp-users.

And, you’re done. Your WordPress interface should update with these new numbers. Congrats, you’ve cleaned out all the inactive / spammer user accounts on your server.

Happy blogging.

Turn off touchpad while typing on Linux

I installed Linux Mint 16 a few days ago on my Netbook. The touchpad didn’t turn off while I was typing, so I dug around for a way to make it do so.

It turns out you can enable this feature on the “Mouse and Touchpad” configuration app (just search for that string among your applications) on the Touchpad tab. However, this default setting didn’t work for me, because it turned off the touchpad for two seconds every time I typed something. For me, that was way too long.

(Note, in the following setup, I set my delay to 0.25 seconds. Feel free to change the 0.25 in the command in step 5 to whatever you want.)

Thus, it was a more manual configuration for me. Here goes:

  1. Run “Session and Startup”. Again, you can just search for this from the “Start Menu” for Linux Mint.
  2. Go to the tab “Application Autostart”.
  3. Click “Add”
  4. Type a name like “Synaptic Daemon”, add in some comments about what you are doing in the description field, like: “Turn of touchpad while typing”
  5. In the command window, put:

    syndaemon -d -i 0.25 -K -R

  6. Click OK
  7. Make sure that the entry you just created has a little “x” in the box to the left of it.
  8. Click close.
  9. Restart your system.

Now your touchpad should not accept input while you are typing. Almost immediately after you stop typing, it should again respond to your movements.

Changing the order of entries in Grub 2 bootloader

A while back I had to solve the problem of removing an operating system entry from the Grub 2 bootloader.

Today I solved a related problem: changing the order in which the OS entries appear on the boot menu. It seems there are several solutions to this problem. The one I will show here was the most effective for me because I have already used the approach in the link above to remove an OS from Grub 2.

Here’s how you do it:

  1. Log in as root, or execute the following mv commands using “su” before your commands.

    sudo su

  2. Go to the “/etc/grub.d” directory.

    $ cd /etc/grub.d

  3. Look at the directory:

    $ ls

  4. Which will give you a result that look something like this (numbers and names may vary).

    00_header 10_linux 30_os-prober 41_custom
    05_debian_theme 20_linux_xen 40_custom README

  5. Basically, the order of these numbers dictate what the order will be on the Grub menu. You can rename these files to change the order. In my case, I wanted to make one of my “custom” entries the first entry (because it was my Windows 7 partition).

    $ mv 10_linux 15_linux
    $ mv 40_custom 10_custom

  6. Now I just re-run update-grub to re-generate the boot menu table.

    $ update-grub

  7. We can check that things are ok by looking through the generated file. In my case, I checked that my Windows 7 entry was present in the file. Interpreting this file deeply requires a lot of knowledge, so don’t worry if it is mostly gibberish.

    $ vi /boot/grub/grub.cfg

  8. Restart your computer to see the new boot menu order.

Manually removing an OS from Grub 2 bootloader in Ubuntu 11.10

This post is here partially because I have had to go through this action several times in the last few months.

I recently bought a Samsung NF-210 Netbook and I have been fighting with setting it up since then. The netbook comes preloaded with Windows 7 Starter. I added in Ubuntu as a dual-boot option. I started with Ubuntu 10.10, but that proved to be difficult to use because it did not correctly handle the function keys for changing brightness or volume.

This same solution worked on Ubuntu 10.10 as 11.10.

My goal is to remove the Windows Recovery Environment from the automatic listing on the grub boot menu. This is more than just a cosmetic change because if you accidentally run this partition, it will fubar your boot partition causing you to lose the ability to boot using grub. Thus you will have to reinstall grub or possibly an entire OS (either linux or windows) depending on how you have set up your hard drive.

  1. Fire up a terminal, switch to root via “sudo su”.
  2. “cd /etc/grub.d” – changing to the directory where we do most of the work.
  3. “chmod -x 30_os-prober” – turning off the automated ability of grub to find new non-linux OSes.
  4. “vi /boot/grub/grub.cfg” – going to see the current profile of OSes detected on the computer.
  5. Scroll down until you spot the entries for your windows partitions. Select the one(s) that you do want to show up on the menu. Copy them to the clipboard. Copy this text to somewhere so that you can paste it into the next file…
  6. “vi 40_custom” – This custom file lets you add your own OS profiles to the grub menu.
  7. Paste in the text that you copied earlier (below the comments).
  8. “chmod +x 40_custom” – The file needs to be executable in order to be included in the grub menu.
  9. “update-grub” – Grub needs to re-create its menu.

Now when you reboot you should see that only the OSes that you selected and moved will show up. In my case I moved my Windows 7 profile, but not the Recovery Environment profile. Thus when I boot I see all of my menu items except the recovery profile. Yey!

LaTeX on Ubuntu 10.10

When working in LaTeX on Ubuntu in the past, I was reasonably impressed with the development environment called “kile”.

To install it, I fired up the System->Synaptic Package Manager and searched for “kile”. I marked it for installation and applied the changes.

After several minutes, it had downloaded and installed itself. I then right-clicked on my menu bar in a blank area. A small menu popped up, I clicked “Add to Panel”. A dialog pops up. I clicked “Custom Application Launcher”. Another dialog shows up. In both the name and command fields I typed “kile”. As soon as I completed typing it in the command textbox, the icon visible on the left side of the dialog changed to the kile icon. I hit OK.

Now I have a Kile button on my main menu bar. I fired it up.

At the top of the screen is had a button that said “ViewHTML”. I want to be working with PDFs not, HTML. So I clicked the little down arrow to the right of the button. A menu popped up. I selected “ViewPDF” from the menu.

This is when I found out that I do not have “okular” installed. It is a viewer that kile integrates well with. I go into Synaptic Package Manager again and search for “okular”. I download and install it.

Now when I load up a .tex file, such as my blank tex that I created, I can click “PDFLatex” to build the tex into a pdf. Then I can click ViewPDF to view it.