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.

Postscript to my first experience running WordPress on PHP 7.0

This is a companion piece to my post from earlier today, When switching servers breaks code: a WordPress mystery.

It’s not much of a mystery anymore, but since this was my first, and somewhat unplanned, foray into working with PHP 7.0, I felt it was worthwhile to spend a bit more time today finally exploring which features from earlier versions are gone in PHP 7.0, so I’ll know if I am going to encounter any more challenges in the eventual, inevitable move to 7.0.

The best source I can find on the removals is this RFC from two years ago (!) that lists deprecated features under consideration for removal, all of which did eventually end up being removed. (I assume this is a complete list of the removals, since I can find no other more current list. But suffice to say it is clear that everything listed here was removed, even if it’s not all that was removed.)

I’m happy to see that the only thing in here that I have been using on any recent projects that was removed was the /e modifier for preg_replace(), which is what I uncovered as the source of the “mystery” in my last post. Of course, before this morning I’m not sure I even would have remembered that I was using it.

That said, I’m relatively confident that I am not using any of the other features, because while I had forgotten about the /e modifier, I at least know I use preg_replace() on a regular basis. Most of the other features in the list are things I don’t recall ever having seen before. Such is the nature of being a self-taught user of a language with over 5,000 built-in functions.

There’s only one other removed feature that I know I have used. A lot. It’s the mysql extension. It has been deprecated in favor of the mysqli extension for several years. But my own CMS, cms34, built originally in 2008 on the then-current version 1.2 of CakePHP, has mysql functions everywhere. I did eventually manage to update the platform to version 1.3.21 of CakePHP, but the leap to 2.0 was too arduous, and reworking hundreds of files to use mysqli functions was, likewise, something that never seemed justified. I stopped new development on cms34 in early 2014, but we still have many client sites running on it. I have committed to supporting them as long as necessary, but I am actively encouraging those clients to make the switch to WordPress.

The absolute incompatibility of cms34 with PHP 7.0 makes the merits of that switch a lot more obvious. And now the clock is ticking.

Bottom line for anyone who, like me, is still supporting legacy PHP 5 (or earlier!) code: change is coming.

Update! Well, OK, I was just searching in the wrong way. The official documentation for migrating from PHP 5.6 to 7.0 has lists of both removed extensions and SAPIs and changed functions.

When switching servers breaks code: a WordPress mystery

Earlier this week I launched a brand new WordPress site for a long-time client. Break out the champagne! But of course it’s never that simple, is it?

The client’s live server is a newly configured VPS running Ubuntu 16.04 LTS and PHP 7.0; meanwhile, our staging server is still chugging away on Ubuntu 14.04 LTS and PHP 5.5. So, clearly, a difference there. But I was pleased to find that, for the most part, the site functions perfectly on the new server.

But then the client discovered a problem: on one page, content from a custom post type query wasn’t displaying.

Here’s a short version of the pertinent code:

$people = new WP_Query(array(
  ‘order’ => ‘ASC’,
  ‘orderby’ => ‘menu_order’,
  ‘posts_per_page’ => -1,
  ‘post_type’ => ‘person’,
));

if ($people->have_posts()) {
  while ($people->have_posts()) {
    $people->the_post();
    ?>
    <article>

      <header><h2><?php the_title(); ?></h2></header>
      <div><?php the_content(); ?></div>

    </article>
    <?php
  }
}

Strangely, the_title() was working fine, but the_content() wasn’t. It had been — still is, in fact — working on our staging server, all other things within the WordPress context being equal. (Identical, up-to-date versions of the theme files and all plugins, and WP itself.) And the client confirmed that the content was present in WP admin.

I found, confusingly, that get_the_content() works, even though the_content() doesn’t. But of course you don’t get all of the proper formatting (like paragraph breaks) without some WP filters that the_content() applies, so I tried this:

<?php echo apply_filters(‘the_content’, get_the_content()); ?>

Still didn’t work. After a bit more research I was reminded that the pertinent function that filter runs is wpautop(), so I just called that directly:

<?php echo wpautop(get_the_content()); ?>

Now I have the content displaying nicely, but this is clumsy and I really do not get what might be different. I know the new server is running PHP 7.0 and our staging server is running PHP 5.5… but I’m struggling to understand what kind of changes in PHP could cause this specific problem.

Since get_the_content() works, and the_content() doesn’t, the problem has to lie in something that’s happening with the filters on the_content(). Why? Because the_content() calls get_the_content() right up front. In fact, there’s not a lot to the_content() at all. This function lives in wp-includes/post-template.php (beginning at line 230 in WP 4.6). Here it is in its entirety (reformatted slightly for presentation here):

function the_content( $more_link_text = null, $strip_teaser = false) {
  $content = get_the_content( $more_link_text, $strip_teaser );

  /**
  * Filters the post content.
  *
  * @since 0.71
  *
  * @param string $content Content of the current post.
  */
  $content = apply_filters( ‘the_content’, $content );
  $content = str_replace( ‘]]>’, ‘]]>’, $content );
  echo $content;
}

As you can see, it’s really just 4 lines of actual code. It calls get_the_content() to retrieve the content, applies filters, does an obscure string replacement (which I think I understand but is not really pertinent here), and then echoes the results out to the page.

It’s pretty clear to me that the problem has to lie in one (or more) of the filters in the 'the_content' stack. I have to admit that even after years of working with it, I only have a rather nebulous understanding of how hooks work, so I’m not even sure where to begin dissecting the filter stack here to pinpoint the source of the trouble.

Whenever I know something works in one place and doesn’t work in another place, the first course of action in troubleshooting is to try to identify all of the differences between the two environments. Obviously we have some big differences here as I noted at the top of this post. But I am going to assume that the problem does not lie at the OS layer. Most likely it’s either a difference between PHP 5.5 and 7.0, or, even more likely, a difference between the PHP configurations on the two servers… specifically, modules that are or are not active. See my previous post on The Hierarchy of Coding Errors for my rationale here. Also keep in mind that I personally was responsible for installing LAMP on the server and configuring PHP, and it’s pretty obvious that we’re looking at the sysadmin equivalent of #1 or #2 in that list.

The next step, were I to care to pursue it much further (and if I didn’t have 200 other more important things to do, now that I have the problem “fixed”), would be to run phpinfo() on both servers and identify all of the differences.

That’s one possible path, at least. Another thing to consider is that the_content() actually is working just fine in other parts of the site, so maybe it would be worth digging into that WordPress filter stack first.

At this point, because as I said I have a few other more important things to work on, I will probably leave the mystery unresolved here. But I’d welcome any ideas from readers as to an explanation for all of this.

Update! I just couldn’t leave well enough alone, so a few minutes after I published this post, with the client’s permission, I restored the old version of the template, turned on WP_DEBUG and installed the Debug Bar plugin. Jackpot! Debug Bar returned the following error message when I was calling the_content(), but not when I had my “fixed” code in place:

Screen Shot 2016-09-01 at 9.24.16 AM

Well, how about that? As it turns out, the problem is due to a filter I myself had added, using a previously written function. (That’s #3 on the hierarchy list.) Combine that with deprecated functionality that was removed in PHP 7.0, and problem solved. And I even figured out why the problem is only occurring on this page and not site-wide… because my filter only runs if there’s an email address link in the content.

Rules, Rules, Rules

I think a lot about rules. I’m not a rigid stickler for rules. I believe a lot in taking rules in context. There are times rules matter, and times they really don’t. But I do think it’s important to understand the rules. There are two things to understand about rules: 1) that they exist to keep things running smoothly, and 2) that there is (or at least should be) a reason behind any good rule. Rules that have no clear, broadly agreeable purpose or that are difficult to follow should be reconsidered.

But a lot of rules are pretty simple. Like the rules of the road. And I think a lot about rules of the road, because I’m on the road a lot, in various ways — in a car, on a bike, or on foot.

The rules of the road are simple, but they don’t seem quite as simple to me living in Minneapolis in the 2010s as they did when I was a kid growing up in a small town in the 1980s. Back then, roads were for cars. The only people who biked were kids and adults who had lost their licenses for DUI. And you biked on the sidewalk.

People only walked with their dogs, and generally only in a 2-block radius of their house, the lone exception being the one Vietnam vet with untreated PTSD who refused to wear shoes or use the sidewalk. He could always be seen around town with his dreadlocks, blanket and bare feet, shuffling along in the boulevard grass. I hope things got better for him.

But I digress. That was the 1980s. In contemporary Minneapolis, everyone uses the roads for just about everything. And sometimes it gets messy. There are many places in the park areas of the city where there are three parallel strips of asphalt: a pedestrian path, a bike path, and the road. All clearly marked for their intended purpose. In most of these places, the road is very narrow — two lanes with no shoulder or parking lanes. But you get pedestrians on the bike path (why? who the hell knows?) and bikes on the road (why? to get away from the dumbass pedestrians! or because they think they’re in the Tour de France!) and things get tangled up.

Even when you’re on regular city streets, biking can be a hazardous endeavor. The rule (whether it’s codified as a city ordinance or just a gentle encouragement on road signs) is “SHARE THE ROAD”. But there are cars that nearly run bikers onto the curb, just as there are bikers who ride side-by-side at such a leisurely speed that I wonder how they keep their balance, backing up car traffic for blocks. SHARE THE ROAD goes both ways.

Me? I’m too scared as a biker to ride on major thoroughfares if I can possibly avoid it. I usually stick to those dedicated bike paths when I can. Otherwise, I try to ride on low-traffic residential streets, generally a block or two over from the major thoroughfare. It just seems much, much safer.

But you do encounter clueless drivers. Drivers who will stop for you at an intersection when they don’t have a stop sign and you (the biker) do, and are clearly coming to a stop. Or, drivers who will breeze right through their own stop signs even when you (the biker) have the right-of-way, either because they didn’t see you or because they live a block away and always breeze through that stop sign.

Yes… you may have guessed that I am not just speaking hypothetically here. Both of those situations in the previous paragraph have happened to me. In fact, they happened on the same street, one block apart. Obviously the latter situation (which happened last year) is far more dangerous, and it led to me braking so abruptly I nearly flipped my bike, followed by a loud string of profanity hurled in the semi-apologetic driver’s direction.

The former situation happened to me just this morning, and prompted today’s rant. I was approaching a stop sign, and slowing to a stop. To my left, a white SUV also came to a stop, even though they didn’t have a stop sign. Presumably they didn’t trust that I was going to stop, even though I was vigorously waving them on with my left arm as I braked with my right. Even though I came to a complete stop, got off my bike, and even more vigorously waved them on. Finally they did go and I got the satisfaction of having successfully enforced the rules. (Sort of. I mean, I didn’t actually give the universal “stop” hand signal. Yes, I broke a rule. But I figured my vigorous waving-on covered it.)

But that got me thinking about the rules themselves. You have the official rules of the road, which tell you that you stop at a stop sign and don’t stop when you don’t have one. Bikes are supposed to follow the same road rules as cars, with (as I recently learned) a few exceptions designed to facilitate faster movement, most notable being that it’s OK for a biker to run a red light, if they have first come to a complete stop and verified that there is no other traffic (cross traffic or oncoming left turns, for example). But I doubt a lot of drivers know this rule, and when they see bikers doing it, probably assume (like I would have before) that it’s one more biker breaking the rules.

Which gets me to the second kind of rules — the unwritten, unspoken rules that grow naturally from collective experience. There are so many bikers who completely ignore all of the rules of the road that many drivers either a) assume the worst out of any biker they encounter and exert excessive caution or b) hit the bikers. (Or, as happened last year, they lose their fucking minds and drive around hurling cinder blocks out their car windows.)

I feel like the situation I ran into today was due to the second type of rule. The driver of the white SUV has encountered enough unpredictable bikers — who are best known for their peculiarly selective blindness to red octagons — that they weren’t going to take any chances with me. So the fact that I did follow the rules and stop for a stop sign actually caused a problem. A minor problem, to be sure, but still enough that it lingered with me all morning. (I wonder what that driver is thinking about right now. Almost certainly not me. This is my affliction.)

So, we are living in a society where we have two types of rules: the official rules, and the unspoken ones. Often in direct conflict. Which rules take precedence? Sadly, as much as I want to live in a world where the official rules are logical, reasonable, fair to all, and easy to follow, I fear that we really live in a world where the official rules are so often inconsistent, incomprehensible, unjust or just simply a burden — not to mention out of touch with the realities of human behavior — that the unspoken rules become the ones that people actually follow.

So then what? Should I just give up on the official rules? Should I breeze through stop signs on my bike because “everyone else is doing it”? Should I stubbornly adhere to my way of doing things and get my dander up every time I have to frantically gesture at someone else to get them to accept their own right-of-way?

Or, should I just lighten the hell up?

In that spirit, I come to the third type of rules. The Rules.

The Rules is a tongue-in-cheek book of… rules… written by a former coworker and bandmate who is obsessed with cycling to a level I will never be. I ride a secondhand bike to get around town. I have become quite a fan of watching the Tour de France every July, in part just because there’s an app for it that I feel really does 21st century sportscasting right and I wish every sport were covered this way. But mostly because I enjoy seeing the French countryside, admiring the intensity and endurance of the riders, and, occasionally, moments like riders punching morons on the sidelines.

Anyway… forget about city ordinances or social norms. The real rules of cycling are another matter entirely. And far more entertaining than my rants will ever be.

Reflections on a particularly rough week for race relations in America in 2016

Note: I initially posted this on Facebook. But things on Facebook have a tendency to get lost in the noise. Better to also preserve it here in the musty silence of my blog.

Seeing some pretty extreme responses on social media from some white people in the wake of the past few days’ events. If I could say anything to white people who are scared and/or angry and/or, God forbid, arming themselves for a race war, it would be this:

Social justice is not a zero sum game. You don’t have to do worse for others to do better. To quote the late, great Paul Wellstone, “We all do better when we all do better.”

You may have a vision of what “America” is, or what an “American” is, and that vision may be a particular color. But Americans who aren’t white are still Americans, just as much as you are.

Black Americans who are reacting to their friends and relatives being gunned down by police at routine — far too routine for many of them — traffic stops have a right to be scared, and angry. But Black Lives Matter is not about revenge. It’s not about starting a war. It’s about JUSTICE. About bringing more PEACE to our streets, our cities, our country. It’s about the “American” in “African American.”

At least, that’s how I see it. And living in the city, I’m probably in much closer proximity to BLM than most white Americans who are themselves scared or angry right now. So please, don’t be. Stop. Listen. Think. Feel. Understand.

Our fellow Americans who weren’t born with the inherited privileges conferred by white skin are living under the burden our ancestors placed on them — a burden that we perpetuate every day that we don’t actively acknowledge and work to counteract it. Hear their voices. Amplify them. Don’t silence them.

And when something happens like the shootings in Dallas, wait for the facts. BLM is a peaceful movement. Dallas PD has a good relationship with BLM. Officers were there to PROTECT the BLM marchers. The shooters do not represent Black Lives Matter or its goals.

I could go on, but I’ve already spoken too long. But don’t stop listening. Seek out those voices that are demanding peace and justice and hear what they are saying. And I will continue trying to do the same.