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.

Yuck.

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.

screen-shot-2016-09-09-at-12-22-19-pm

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.

screen-shot-2016-09-09-at-12-21-58-pm

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.

Robert Reich on the public option

Robert Reich was President Clinton’s Secretary of Labor and is now a professor at UC Berkeley. I’ve seen him on TRMS a few times, and I’ve always been impressed.

He has made this video explaining in drop-dead (no, not from the “death panels”) simple terms exactly what the public option is, and how important it is to fight for, right now.

I can take some comfort in knowing that all three people who are in Washington to represent me in Congress — Representative Keith Ellison and Senators Amy Klobuchar and Al Franken — truly do represent me on these issues, and will vote accordingly. (Well, sounds like Sen. Klobuchar might be on the fence, but I’m hopeful she’ll come around after the persuasive and heartfelt email I just sent her.) And I don’t expect many of the Republicans in Congress to do anything more than continue to dig in their heels, bury their heads, spew hatred and lies, and spur liberals like me on to a frothing, cliché-ridden rage. But there are conservative Democrats who will potentially sabotage the entire enterprise for some unknown reason — oh wait, yeah… money — instead of voting with their party and the will of the majority of the American public.

CakePHP Auth component, Flash and Internet Explorer… a deadly combination

OK, it’s not really deadly at all… other than that it will kill your CakePHP session and log you out.

My CakePHP-based CMS uses YUI Uploader, a Flash-based file uploader utility. It’s much better than the default HTML file uploader, because it supports a fully CSS-customizable progress bar and multiple file uploads.

It’s pretty slick, even though I did tear some hair out earlier in the year trying to get it integrated into the CMS. All went well for several months, until one particular client, using Windows Vista and Internet Explorer 8, discovered a showstopper of a problem: whenever you uploaded a file, all would seem well until you went to save your changes and you’d get kicked back to the login screen, without the changes being saved. Bad news!

I did some diagnostics and determined that, yes indeed, the CakePHP session was in fact being dropped as soon as the Flash process finished queuing the file uploads (an AJAX-based process), before you actually click the “Save” button… but since there’s nothing else happening dynamically on the page, it wasn’t obvious that the session had been killed in the background.

Anyway, some research led me to a perfect explanation of the problem, and an equally perfect solution: Flash is sending a different user agent string, which was resetting the CakePHP session. I’m still not sure why it was only affecting Internet Explorer, but at any rate, a simple change to the app/config/core.php file solved the problem in a snap. The critical line:

Configure::write('Session.checkAgent', false);

I suppose by removing this line, the application is ever-so-slightly less secure, but there should be enough other precautions in place that removing the user agent check as part of the process of validating a session should not pose a significant security risk.

I’m in love

Don’t tell SLP, but I’m in love. It happened this afternoon at the new Best Buy at the Mall of America. No, it’s not an affair. It’s a Jaguar Bass.

I’ve been planning to buy a new bass for several months now. My standard Fender Jazz is OK but it’s just not cutting it. The pickups are the big problem. Too buzzy. And one of the volume pots slips. I took off the cover plate and tried to tighten it, but I couldn’t. It’s a fine bass, but I just want something better. I got spoiled when I had an American Jazz Bass a few years ago. So I’ve been planning to upgrade to the Deluxe Jazz. Not quite as nice (or as expensive) as the American, but it has the same Made-in-USA pickups, plus active electronics, at a more reasonable price. And of course it is a 5-string, which has become second nature for me.

So as much as I was dazzled from the first time I saw it a few years ago by the Jaguar’s overwhelming array of switches, knobs and dials, the fact that it doesn’t come in a 5-string model was always a deal breaker for me.

But as long as I was there at Best Buy today, and it was too, I figured, why not just pick it up and try it? Big mistake. Before I even plugged it in, I could tell what it was going to sound like, and that I was, like, totally going to want it! And I did.

The sales dude really knew how to work it too. He was friendly and helpful from the get-go, and then he backed off and just let me play. After a couple of minutes of noodling around and testing the intonation across the fretboard (the next most critical thing about a bass after good pickups), I started playing a bit of the bass line from my favorite Yes song, “Heart of the Sunrise.” A minute later, “Roundabout” started playing on one of the PA demo units across the room. Wrong song, but right album. Kudos to him. If I do get the Jag (which seems more likely with every passing second as I write this), I’ll probably go back there for it. And he doesn’t even get a commission!

It doesn’t have 5 strings, but it sounds great and it looks great (and it comes from Fender’s Japanese shop, which is second only to the one in Corona, California for quality, at a significantly lower price), and it’s pretty much the same price as what I was planning to pay for the Deluxe Jazz. Good deal! (Of course, I also want to get a microKORG, but maybe I can kill two birds with one stone.)