Changing the user_id of WordPress comments

July 18th, 2009 by Dave · 3 Comments

Lately, on my other blogs, I’ve fallen into the habit of not explicitly logging in when responding to comments — especially if I’m somewhere where I don’t trust the security of the network I’m on. However, this means WordPress 2.7 and later won’t automatically highlight my comments with a different background color since an unlogged-in user always a user_id of 0. I’ve discovered it’s not too hard to go into MySQL and clean this up though! (Of course, if your WordPress theme wasn’t applying such highlighting to comments made when logged in, the following won’t help you at all.)

ASIDE: Why can’t WordPress force logins to be through https instead of http so I don’t have to worry about the password being sent in plaintext?

The instructions below assume you’re using MySQL as your database backend for WordPress. They also assume you’re using WordPress 2.7.x (they may work on other versions but I haven’t tested that the DB tables have the same structure so I can’t claim it.) You’ll need to be familiar with a bit of SQL to understand this:

  1. First, you’ll need to refresh yourself on how your WordPress instance logs into MySQL.  You can simply look at the content of the ‘wp-config.php’ file for this.   You want the DB_NAME, DB_USER, and DB_PASSWORD.
  2. You’ll now need to run a MySQL client that can connect to the host machine running your database.  If your like me, a terminal / shell on the webserver will suffice with a default host of ‘localhost’.  Simply launch the client like:

    mysql -u <DB_USER> -p -D DB_NAME

  3. You can now search for all records of comments you might have made whether logged in or not by running a SQL query like below. This won’t list every comment, just the different combinations of your user_id, name, and ip address.

    SELECT DISTINCT user_id, comment_author, comment_author_IP FROM wp_comments WHERE comment_author = “<USERNAME>”;

    where <USERNAME> is the name you use to post comments when you’re logged on or not.  If you’ve used different user names, simply modify that query to do an OR expression to match multiple names.

    You’ll note that the results of that query include an IP address. I’m doing that because anyone can post a comment and use my username. Seeing the IP is a check to be sure that no one has done so — assuming you can remember the IP addresses from which you might have posted. If you can’t do that, you could instead show the “comment_author_email” or “comment_author_url” column values in your SQL query to ensure that you’re only seeing your own comments. However, again, anyone can type these values into your comment field so you need to be careful. Most people won’t know the full combination of values though. I just think the IP address is a bit harder for people to fake so I use that.

    If I find that the rows shown are not just my comments, I execute a SQL update command to change the comment_author. You may or may not want to do that. I’ll leave constructing that as an exercise for the reader since I’d have to know how you figured out they weren’t your comments in order to write SQL that worked for your case.

  4. Now note the different user_id’s shown. Find the one that matches when you comment from being logged in. It should be a non-zero value. From now on, we’ll denote this value by <MYUSERID>
  5. Now update all your comments to have that user_id you just noted. This is done by running a SQL update command like:

    UPDATE wp_comments SET user_id = <MYUSERID> WHERE comment_author = “<USERNAME>”;

    Of course, this only does the right thing if you can identify your own comments just by <USERNAME>

  6. Verify things worked okay by re-running the SQL select statement from above. You should now see all rows showing the value of <MYUSERID> you discovered above.
  7. Finally, revisit an actual post page on your blog, one with comments obviously, and verify your theme is applying the background highlight.

→ 3 CommentsTags: Blogging

Allowing ORANGE access to smoothwall’s NTP server

June 13th, 2009 by Dave · No Comments

For security reasons, a default install of Smoothwall 3.x isn’t designed to allow machines other than those on the GREEN network to use the NTP server running on the Smoothwall machine. But I find this is a bit annoying because the Smoothwall has a nice default ntpd config that synchronizes with a large group of random, publicly accessible time servers, and I’d rather not have to duplicate this on other machines, nor setup a timeserver in the GREEN network just to serve NTP requests to the ORANGE network. So here’s what I did to enable ORANGE connections to the NTP server on a Smoothwall install:

  1. First, let’s create a new modification directory in the standard Smoothwall location. I’m calling my extension ‘ntp_orange’:
    cd /var/smoothwall/mods
    mkdir ntp_orange
  2. Now, let’s follow the Smoothwall pattern for startup scripts by creating our script to enable NTP access within subdirectories like this:
    cd ntp_orange
    mkdir -p etc/rc.d
  3. Finally, let’s create our actual script to modify the system.
    vim etc/rc.d/rc.netaddress.up

    And then paste the following into the editor:

    # Source the standard smoothwall settings variables.
    . /var/smoothwall/ethernet/settings
    # Ensure the ntp.conf file contains instructions to listen on the orange
    # interface.  This is necessary because anytime the user edits the time
    # server settings page through the web interface, this setting goes away.
    NEW_LINE="listen on $ORANGE_ADDRESS"
    grep "$NEW_LINE" "$FILEPATH" > /dev/null
    if [ "$?" -eq "1" ]; then
      echo $NEW_LINE
      cat $FILEPATH
      ) > ${FILEPATH}_TMP
      rm $FILEPATH
    # Restart the ntpserver.
    /usr/bin/smoothcom ntpdrestart
    # Insert a firewall rule to allow NTP packets on the ORANGE interface to
    # be accepted.
    # NOTE: Rule index 13 is designed to be after the rules for ipblocks,
    # spoofing, etc.  including the rules that accept all traffic on the loopback
    # and GREEN interfaces.
    iptables -I INPUT 13 -p UDP -i $ORANGE_DEV --dport 123 -j ACCEPT

    You can see that I’m sourcing the standard Smoothwall settings script to get the current definition of what interface and addresses are assigned to the ORANGE interface.

    I then have a block of code that checks to see if we’ve already configured NTP to listen on it’s ORANGE interface. If it is already configured, no modifications are made. But if it wasn’t, we simply insert the appropriate line at the beginning of the ntpd.conf file.

    Next, the script restart’s the Smoothwall’s NTP server.

    And finally, the script inserts a firewall rule to ensure the Smoothwall will additionally allow only UDP NTP connections from the ORANGE interface. Because this rule is limited to NTP’s port 123, and the UDP protocol, I figure this isn’t too much of a security hole.

  4. Then, to ensure our script gets run after reboots, we modify the file

    and append these lines at the very end:

    echo "Allowing NTP requests from ORANGE interface"
    . /var/smoothwall/mods/ntp_orange/etc/rc.d/rc.netaddress.up
  5. And lastly, either reboot your Smoothwall, or directly execute your new extension script. I would recommend that you actually do both: first, directly execute the script to ensure you had no cut and paste errors, etc. You can then verify the ntpd.conf file contains what you expected:
    cat /var/smoothwall/time/ntpd.conf

    and verify iptables contains the new INPUT rule:

    iptables -L INPUT -v -vv

    Once you know those are correct, do a reboot and then test them again to ensure you’ve gotten things working on reboot.

Good luck!

→ No CommentsTags: Hardware · IT/Network

What Brown Can Do For Me

June 28th, 2008 by Dave · 1 Comment

What Brown Can Do For Me:  Do your freaking job and get me my package!  I’ve not been happy with UPS for a long time but this kinda takes the cake.   I didn’t get my TSU9400 yesterday like I expected because UPS decided to have it stop in the middle of the flood-ravaged midwest states on its way from the store’s warehouse in Pennsylvania to Texas where, of course, they promptly delayed further shipping due to inclement weather.   Why would you send newly shipped packages into disaster areas??? Route around that.   I thought these people were supposed to be experts in logistics?

Since I’m already complaining about UPS, let me tell you about the thing that most ignores me.  They never ring the doorbell when the deliver to my house.  Absolutely.  Never.   Even when I have taped a sign to the door saying “I’m home.  Please ring bell over there” and I draw a nice little arrow pointing right at the thing.  They’ve still not rung the bell!  I’ve had UPS leave multiple packages that required signatures sitting on the stoop when I or someone else has been home.   I don’t like this for two reasons.  First I don’t like them leaving packages worth thousands of dollars sitting out where any Joe Schmoe wandering by can pick them up and walk off with them.   Second, I don’t like when they leave perishable items like food sitting in the Texas heat.   Is it too much to ask the driver to frickin’ push the button?  It’s not like he has to sit around and wait for an answer (but he should wait when a signature is required!)  Hell, the FedEx guy is already driving away when I get to opening the door, but at least he pushed the button.  Even when I don’t have a sign taped to the door!   Perhaps UPS drivers just can’t read?

Dammit, get me my package.  If all online retailers offered a choice, I’d never, ever, ever use “Brown” again.

→ 1 CommentTags: Uncategorized

Pronto TSU9400

June 24th, 2008 by Dave · No Comments

Man, it’s been a long-time since I posted.  I’m gonna break that streak by first admitting that I’m a clumsy idiot.  You see, Kimberly and I have recently gotten ourselves a Wii and while playing Wii sports, I hit our older black-and-white screened Pronto remote off of the back of a chair.  Normally, it falls onto the carpeted floor rather harmlessly, but this time it’s fragile LCD was hit squarely into the sharp corner of the new Wii sitting on the floor behind the chair.  The sound of shattering glass (or is it plastic) was rather disconncerting.  While the remote’s hard buttons still work, the touchscreen was now toast since the spider-web of cracks borked the ability to sense the location of a finger.

Now comes the fun part of this post.  We’ve just rectified our problem of having to have 5 remotes on hand by ordering a brand new Pronto TSU9400.   While it is a bit of a pricey item, it turns out that getting a new TSU3500 (which is what I broke)  wasn’t that much less in cost. (That’s me rationalizing btw.)  Okay, I admit it, the TSU9400 costs twice as much.  Anyway, the TSU9400 is supposed to arrive on Friday and then I’ll spend the weekend porting our programming to the new device.

I’m psyched about the new remote as it has a few more hard buttons, targeted at those heavy DVR users it seems — which we definitely are — plus it has a high res color screen.  That color screen should seriously aid our ability to create a quality user interface that makes it easy to distinguish actionable buttons vs descriptive text, common buttons from infrequently used ones, etc.   Finally, this Pronto claims to be activity-based rather than equipment-based — which I’m hoping means it has some memory or state storage so it can tell if devices are already on or off, etc.  We’ll see soon!

→ No CommentsTags: Home Theater