Enabling SSL on WordPress Posts Automatically

SSL https

At the end of last year, I wrote about setting the Enable SSL checkbox on all past pages and posts. What I did not address was how to automatically set the Enable SSL checkbox automatically – you currently have to manually click the box. Hard, I know.

so difficult
…clicking that checkbox…

Nevertheless, I always like to find ways to automate mundane and repetitive tasks. In this case, I decided a trigger was in order!

First, I created a MySQL stored procedure to set the Enable SSL flag:

CREATE DEFINER=`username`@`localhost` PROCEDURE `sp_enable_ssl`(post_parent BIGINT(20))
BEGIN
	INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
	SELECT post_parent, 'itsec_enable_ssl', 1
	FROM DUAL
	WHERE newpost_parent NOT IN (
		SELECT DISTINCT post_id
		FROM `wp_postmeta`
		WHERE meta_key = 'itsec_enable_ssl' and meta_value = 1 and post_id = post_parent)
	AND post_parent <> 0;
END

Then, I created an AFTER INSERT trigger and an AFTER UPDATE trigger, both of which use the stored procedure, in keeping with the DRY principle:

CREATE TRIGGER `wp_posts_trigger_insert` AFTER INSERT ON `wp_posts`
 FOR EACH ROW BEGIN
	CALL sp_enable_ssl(new.post_parent);
END
---------------------
CREATE TRIGGER `wp_posts_trigger_update` AFTER UPDATE ON `wp_posts`
 FOR EACH ROW BEGIN
	CALL sp_enable_ssl(new.post_parent);
END

I manually tested the triggers by updating and inserting into the wp_posts table, and the row that would enable SSL on the post got created.

However, when I actually tried saving a post in WordPress, nothing happened! I forgot to grant execute procedure permissions to the user with which WordPress connects to the database!

As it turns out, I am not granted SUPER privileges on my host, so I can’t grant any privileges. I would have two options, create the stored proc using the WordPress account, or to use the code in the stored proc in the triggers. In this case, I chose the latter option as it was much more practical than setting up phpMyAdmin as the WordPress user to do this. Should I ever need to do that, I’ll revisit this issue then.

The code for the triggers is below. Notice that there are differences from the code in the stored procedure, such as the use of “new.” to get values from the updated or inserted row in the wp_posts table.

CREATE TRIGGER `wp_posts_trigger_insert` AFTER INSERT ON `wp_posts`
 FOR EACH ROW BEGIN
	INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
	SELECT new.id, 'itsec_enable_ssl', 1
	FROM DUAL
	WHERE new.id NOT IN (
		SELECT DISTINCT post_id
		FROM `wp_postmeta`
		WHERE meta_key = 'itsec_enable_ssl' and meta_value = 1 and post_id = new.id)
	AND new.post_parent = 0 AND new.post_type='post';
END
-------------------
CREATE TRIGGER `wp_posts_trigger_update` AFTER UPDATE ON `wp_posts`
 FOR EACH ROW BEGIN
	INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
	SELECT new.id, 'itsec_enable_ssl', 1
	FROM DUAL
	WHERE new.id NOT IN (
		SELECT DISTINCT post_id
		FROM `wp_postmeta`
		WHERE meta_key = 'itsec_enable_ssl' and meta_value = 1 and post_id = new.id)
	AND new.post_parent = 0 AND new.post_type='post';
END

Bulk Check “Enable SSL” Checkbox in WordPress

SSL https

I think this is really going to be my last post of the year! I promise!!!

I recently discovered that, for some reason, there does not appear to be a built-in way to set the Enable SSL checkbox on multiple posts in WordPress.

While there may not be an app for that – there is now a script for that!

Run this against your MySQL WordPress database:

INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
SELECT DISTINCT post_parent, 'itsec_enable_ssl', 1
FROM `wp_posts`
WHERE post_parent NOT IN (
    SELECT DISTINCT post_id
    FROM `wp_postmeta`
    WHERE meta_key = 'itsec_enable_ssl' and meta_value = 1)
AND post_parent <> 0;

You may need to change the “wp_” prefix on the table names if you have changed those for security purposes. This works with the current version of WordPress, version 4.9.1.

You can run the script multiple times to no ill effect. Just make sure you have SSL set up properly before using this.

Oracle MySQL Workbench / macOS High Sierra Bug Officially Fixed!

MySQL Workbench logo

This is an update for a previous post.

Oracle has released version 6.3.10 of MySQL Workbench, fixing the results grid bug that only plagued users running macOS High Sierra.

MySQL Workbench with missing Results Grid

I was very glad to see this release, as I was contemplating switching to Toad for MySQL, which I quickly found out is not free anymore.