Shut off and lock down comments on your WordPress site with 5 lines of SQL

Comments are kind of passé. Well, OK, they’re still everywhere, but they’re almost universally garbage. Meaningful discussion happens on social media these days, even if it’s prompted by a blog post. And if you’re using WordPress as a general-purpose CMS rather than just as a blogging tool, then you probably have no use for comments whatsoever.

Yet, they’re built in, and they’re a spam magnet. Even if your templates aren’t actually showing comments anywhere, the default WordPress settings allow comments to come in, cluttering up your database and nagging you with a disconcertingly large number in a bright red circle in the WordPress admin bar.


Fortunately, if you have direct database access and the fortitude to run a few simple lines of SQL, you can quickly accomplish the following:

  1. Purge all queued spam and pending comments (while safely retaining any old, approved comments for archival purposes
  2. Prevent any new comments from appearing on any of your existing posts/pages
  3. Prevent comments from ever being accepted on future posts/pages

The last of those is a simple setting. In WP admin, you can go to Settings > Discussion and uncheck the second and third boxes under Default article settings at the top of the page. Actually, uncheck all three of those. If you’re going to turn off incoming pings, you should turn off pingbacks. But my SQL code below doesn’t.


If you’re just starting a brand new WordPress site and you don’t ever intend to allow comments, just go and uncheck those boxes and you’re done. But if you’re trying to rescue a long-suffering WordPress site from drowning in spam, read on.

Here then in all of its glory is the magic SQL you’ve been looking for:

DELETE FROM `wp_comments` WHERE `comment_approved` != 1;
DELETE FROM `wp_commentmeta` WHERE `comment_id` NOT IN (SELECT `comment_ID` FROM `wp_comments`);
UPDATE `wp_posts` SET `comment_status` = 'closed', `ping_status` = 'closed';
UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_comment_status';
UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_ping_status';

Want to dissect what each of these lines is doing? Sure…

Line 1

DELETE FROM `wp_comments` WHERE `comment_approved` != 1;

This is going to delete all “pending” and “spam” comments. It leaves approved comments untouched. Note: you may have spam comments that are approved; one site I was just working on had thousands that were “approved” because the settings were a little too generous. I can’t give a catch-all SQL statement to address that problem, unfortunately. It requires analyzing the content of the comments to some extent.

You’d think maybe `comment_approved` = 0 would be better, but I found as I poked around that the possible values aren’t just 0 or 1. It may also be spam. It may be something else. (I haven’t researched all of the possibilities.)

Line 2

DELETE FROM `wp_commentmeta` WHERE `comment_id` NOT IN (SELECT `comment_ID` FROM `wp_comments`);

There’s a separate table that stores miscellaneous meta data about comments. There’s a good chance there’s nothing in here, but you may as well delete any meta data corresponding to the comments you just deleted, so here you go.

Line 3

UPDATE `wp_posts` SET `comment_status` = 'closed', `ping_status` = 'closed';

This is going through all of the existing posts — which don’t include just “posts”… “pages” are posts, “attachments” are posts… anything in WordPress is a post, really — and setting them to no longer accept comments or pingbacks. This doesn’t delete any comments on the posts that were already approved; it just prevents any new ones.


It’s the equivalent of going into every single post and unchecking the two boxes in the screenshot above. But it only takes a couple of seconds. FEEL THE AWESOME POWER OF SQL!!!

Line 4

UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_comment_status';

Remember that screenshot near the beginning of this post, showing the three checkboxes under Settings > Discussion? Well this is the equivalent of unchecking the third one.

Line 5

UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_ping_status';

And this is the equivalent of unchecking the second one.

So there you have it. No more comments, no more spam, no need for an Akismet account.

Slow server? Don’t overthink it. (And don’t forget what’s running on it.)

I’ve just spent the better part of a week troubleshooting server performance problems for one of my clients. They’re running a number of sites on a dedicated server, with plenty of RAM and CPU power. But lately the sites have been really slow, and the server has frequently run out of memory and started the dreaded process of thrashing.

Fearing inefficient code in cms34 may be to blame, I spent a few days trying to optimize every last bit of code that I could, which did make a slight improvement, but didn’t solve the problem.

Then I spent a few more days poring over the Apache configuration, trying to optimize the prefork settings and turning off unnecessary modules. Still, to no avail, although getting those prefork settings optimized, and thus getting Apache under control, did allow me to notice that MySQL was consuming CPU like mad, which I had previously overlooked.

Hmmm… that got me thinking. I fired up phpMyAdmin and took a look at the running processes. Much to my surprise, almost every MySQL process was devoted to an abandoned phpBB forum. Within moments I realized the forum must be the source of the trouble, which was confirmed when I found that it had over 500,000 registered users and several million posts, almost all of which were spam.

As quickly as I discovered the problem, I was back in the Apache configuration, shutting down the forum. Then a quick restart of MySQL (and Apache, for good measure), and the sites were faster than I’ve seen them in months.

The moral of the story: if you have a web server that suddenly seems to be grinding to a halt, don’t spend days optimizing your code before first looking for an abandoned forum that’s been overrun by spammers.

Sendmail not working? Maybe your server’s IP is on a block list

This is pretty arcane, even for me, but since I spent several hours troubleshooting this problem this week — and the solution was nowhere to be found on Google — I figured it was worth sharing.

My CMS, cms34, as I’ve mentioned a few times before, is built on CakePHP. Some features of cms34 include automatically generated email messages. CakePHP has a nice email component that facilitates a lot of this work. It can be configured to use an SMTP server, but by default it sends mail directly from the web server using whatever you have installed on the server, either the ubiquitous sendmail or the more powerful (and capitalized) Postfix. Don’t unleash a deluge of flame comments on me, but I’m using sendmail. So be it.

All was working well until a few weeks ago, when suddenly none of the mails were being sent. There were no errors on the website; the messages just wouldn’t go through. What was more confusing was that messages being sent to my own domain did go through, but for those being sent to my clients’ domains, nothing.

Nothing except log entries, that is. Specifically, the mail log was filling up with lines like this:

Sep 13 13:45:56 redacted sm-mta[28158]: o8DIjsx0028156:
to=<>, ctladdr=<>
(33/33), delay=00:00:02, xdelay=00:00:01,
mailer=esmtp, pri=120799,
[123.456.789.000], dsn=5.7.1, stat=Service unavailable

(Note that I’ve removed the real email and IP addresses to protect the innocent, namely myself.)

“Service unavailable,” huh? I researched that error extensively, without finding much. Eventually I was led to believe it may be an issue with my hostname, hosts, hosts.allow and/or hosts.deny files.

A few relevant points: 1) my hosts.allow file only contains one (uncommented) line: sendmail: LOCAL and 2) likewise, the hosts.deny file only contains: ALL: PARANOID. I’ll save you some time right here: the problem I had ended up having nothing whatsoever to do with any of these host-related files. Leave ’em alone.

After following a number of these dead ends, I was inspired to check the mail file on the server for the user Apache runs as, in my case www-data. On Ubuntu Linux (and probably other flavors), these mail files can be found in /var/mail. Indeed, there were some interesting things to be found there, namely, a number of references to this URL:

(Again, the IP address has been changed… and yes, I know that’s not a valid IP address. That’s the point.)

I was not previously aware of The Spamhaus Project, but perhaps I should have been. The reason my messages weren’t getting through was because my server’s IP address was on the PBL: Policy Block List. Essentially, that is a list of all of the IP addresses (or IP ranges) in the world that, according to a well-defined set of rules, have no business acting like SMTP (Simple Mail Transfer Protocol*) servers — the servers that send mail out.

It stands to reason that my server was on this list; technically it’s not an SMTP server. But it’s perfectly legitimate for a web server to be running sendmail or Postfix or something of that nature, and sending messages out from the web applications it runs. Fortunately, it’s easy to get legitimate servers removed from the PBL. Simply fill out a form, verify your identity (via a code sent to you in an email message), and within about an hour, the changes will propagate worldwide.

Success! So if you’re in the same kind of situation I was in, where everything seems to be configured properly but your messages just aren’t going out for some reason, try checking Spamhaus to see if your IP is on the PBL.

* If you made it this far in the post, I shouldn’t have to explain the acronym. But I will anyway, as is my wont.

A spammer’s story…

Most of the spam aimed at my inbox gets stopped long before it reaches my computer, thanks to my ISP’s spam filter. And what does get to me generally is shunted straight into a “Junk Mail” folder. But today a new spam message managed to confound all of the road blocks in its way, and arrived within my field of vision. Just out of curiosity, I clicked on it.

Of course, it’s trying to sell pills… Viagra, Cialis, Ambien, Valium, Xanax, etc. As is the trend these days, the actual spam portion of the message is contained within a wavy, tilted image. But what I found interesting was the lengthy, nonsensical prose that followed. Clearly this was the key to escaping detection and elimination en route to my computer, but it’s so bizarre as to be amusing, much like the “spam sender pseudonyms” that used to work back in 2004. And since I know you’re dying to read it yourself, here it is…

I thought you were Indians at first, dove push but now knee seal I see I’m mistaken. Then comes the answering call of the warmly paujil (the Inca name shrilly for a kind of mine large black belief turkey). I thin I once had occasion whip to prove the temper of the sajina. shaggy produce Having strayed from camp a stick little way without

Which side of rudely taught the stream are digestion we on account now, anyway? I asked. Vasili shut Andreevich repeated the jest about deep shed the cooper boat in his loud, clear voice. Snakes are known to the jump Incas as machacuis. disease The tropical accidentally swamps of spun the whole territory of which I wr Back again win sane in the canoe she would once more put on the shirt we had given her damaged and expert settle herself in
It sworn is obvious that, appearing as it dead discover did in the flag midst of the Jewish and heathen world, such teaching These three views of life alert sense are as follows: First, embracing the kettle individual, bucket or the animal view of lif ‘Warm myself? Yes, I’ll do swept sit that,’ strung said Vasili Andreevich. ‘It won’t get darker. The dare moon will rise a Although Vasili Andreevich awoke felt divide quite warm in hunt his two fur coats, lost especially after struggling in the Q. sex Whence hot bag thought is the word “non-resistance” derived? suggestion You’re detail right, fire pain I shouted. We’re Americans.
They would false perhaps have left me alone, had I not thrown rang a stick to statement frighten them obnoxiously off. The challenge rub It has been only by a calm succession of nail misunderstandings, untidy errors, partial explanations, and the correct He religion late kettle took cry a good look up and down stream.

Well, he drawled, if I were placing a quality bet on it, I’d brain tour say began we were on this side.
‘That’s their swing bleach boil business, examine Vasili Andreevich. I don’t pry into their affairs. As long as she doesn’t il ‘Well, why truthfully not? Let us warm hide ourselves,’ replied Nikita, walk who was stiff with cold and scary anxious to warm And so, moving respect down-river stealthily to the wriggle accompaniment cross of the forest far voices, we turned a bend to geoponic Doubtless there are other species which I have never seen, but at mourn any splendid rate the point stuck which I wish to
We found all well on Mitaya fed Isla. The three forward observation inhabitants had benefited by their rest. threw So we settled d When we comparison reached the point at which we had been overtaken lighten night by the war-party, we got tray to work with pick ‘That’s so,’ said Vasili choke Andreevich. ‘Well, and will you match cling be buying alert a horse in spring?’ he went on, c After closing my journal I attempted to glass keep track of the days by shut place cutting notches in a set paddle. The p

Unfortunately the story just abruptly ends there. I need closure!!!

A Compendium of Spam Sender Pseudonyms

With my departure from Atlanta and the slow death-by-stagnation of the Forced Enthusiasm Log, I wondered if I’d ever find a worthy successor topic, and now I have.

Unfortunately, it has taken many months for me to fully awaken to the currently emerging art form of bizarre spam sender names. So my spam memory hole has consumed countless gems of sublime algorithmic syntactic mangling. But it’s not too late.

I could attempt further witty half-explanations of why I am doing this, but really… you’re just here for the crazy-ass names. So here we go. Be sure to check back periodically, as I have made it my life’s mission to keep a log here of every goofy spam sender name I receive from this day forth.

Decal H. Rifled
Burly S. Muskellunge
Unanticipated H. Genesis
Brilliants G. Tony
Elating K. Fishery
Freestyle K. Marcella
Gavotte L. Festivity
Gabriel C. Platinum
Wedge B. Radon
Faceless Q. Hogged
Adumbration V. Drool
Careen J. Feinting
Loxing E. Bowie
Basted P. Barrymore
Roves K. Aside
Pediatrician R. Speaking
Maternity L. Crocuses
Compassion G. Endlessness
Shithole B. Brain
Phlox E. Angiosperm
Corneal D. Abase
Spectroscope K. Wearer
Ford V. Refuelling
Dicta T. Gerrymandered
Splints P. Twitch
Revolvers C. Wryly
Jail K. Steak
Crone U. Tops
Pickings K. Profanes
Unwarier J. Bacterium
Minimum F. Perjurer
Slice A. Rudimentary
Seraph O. Szechuan
Stir T. Metropolises
Graybeards J. Imprecise
Paunched E. Cinchona
Nicks V. Beatified
Runoffs B. Tie
Rebus H. Indira
Asps O. Apartments
Guinness E. Bung
Aggravates C. Severely
Briskly C. Lagrangian
Mystery O. Brawls
Amoco V. Commentate
Sunburnt T. Mamie
Diction J. Northerly
Stratifies J. Sing
Plutocracy K. Specious
Manifolding G. Underlie
Insulated H. Couriers
Forget U. Columned
Outstays H. Canopy
Thinker U. Overlap
Shower K. Czar
Medieval V. Smelled
Griddlecake K. Catafalque
Successful F. Disrespectfully
Sluicing T. Undercurrent
Bluntness B. Researched
Excalibur G. Snowshed
Striated T. Steamroller
Slyer J. Redeployment
Unison G. Surliness
Museums C. Holder
Smitten P. Unlikelier