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.

Hidden Results Grid on MySQL Workbench and macOS High Sierra

MySQL Workbench with missing Results Grid

UPDATE: Oracle has released version 6.3.10!

I recently updated my MacBook Pro to macOS High Sierra. Like every other OS update, this one was fairly uneventful – with the exception of having to do a hard reboot to get the Mac to come back up after the upgrade. That was definitely a nail-biter!

Once the Mac rebooted, everything appeared to be as it should be. All was fine and dandy – or so I thought.

Though I have not had any other major issues, it would appear that High Sierra has broken the current version of MySQL Workbench (6.3.9). When trying to run a query, the Results Grid never appears. A status declaring that the query completed is shown, and the Action Output window will even tell how many rows were returned. However, the rows themselves cannot be seen.

MySQL Workbench with missing Results Grid

It took quite a bit of searching to find this – apparently not too many folks have encountered this problem as yet – but I did find confirmation on Stack Overflow that this is now a known issue, and that the next release should have it fixed.

It is interesting that Oracle has closed the official bug report, despite the fact that no patch or new version is available. A user has devised a workaround that involves installing his build of MySQL Workbench, but that is not ideal in many production environments, for obvious security concerns. The only other solution given was to downgrade to an older version of MySQL Workbench – 6.1.7.

An excerpt of the comments show several users’ reactions to the bug report having been closed with no solution offered:


[20 Oct 10:32] Bob Davenport
Changing back to open

[20 Oct 10:36] Bob Davenport
I’ve changed back to open as its not fixed till the release is available.
I never clsed it perhaps the pperson who closed it could come forward and explain how a bug rendering a product 99% unusable can be marked as closed?

[20 Oct 12:12] Mike Lischke
This bug entry was closed as part of our internal processes. Please don’t touch the status of a bug anymore, once it closed.

[20 Oct 15:25] Bob Davenport
Mike Lischke

Why is a bug closed when their is no fix ?
Or can you advides the date the fix was released?

Very disappointing that the attitude of yours is so poor.

Maybe next time i wont bother to post a bug report or how to fix.
Also mustn’t forget Mike Lischke who also wasted time with the “Works on My PC” attitude in the firs place.

A discredit to the community.

[20 Oct 15:34] Michael Brogley
I agree with Bob. Based on my experience running just such an effort, a critical status bug should only be changed to “Status: Closed” if a fix has been tested and released. Otherwise any Oracle manager that’s tracking open bugs will be misled on the product’s status. Playing bug status games like this basically cuts the nerves between the user community and management, leading to misallocation of resource problems downstream, and managers and executives getting blindsided by angry users.

Is there no “Pending” status in this system?

[20 Oct 15:37] Darren Coleman
Why would a bug be closed if the problem is not fixed?

I’ve never heard of a bug tracking system where people close bugs without actually resolving the problem??

Is it just inconvenient for Oracle to see this (very simple) bug on their TODO list? It’s ridiculous enough as it is that a community member has to go out of their way to patch a bug that existed and was reported more than once while macOS High Sierra was in BETA.. but to close the bug without resolution is just egregious.

[20 Oct 15:57] Mike Lischke
The typical workflow for a bug report in this bug database is like this:

  • The bug gets reported.
  • A support person checks the validity and clarifies things when necessary.
  • The bug is transfered to our internal bug system where the developers are notified.
  • The developers fix the bug.
  • QA tests the fix.
  • The documentation team takes a note for the change log and sets the bug to closed (both internally and externally).
  • Once all bugs for a release are closed the new version is released.

Hopefully this makes the process more clear.

[21 Oct 15:12] Bob Davenport
Mike Lischke/ Oracle Can you please advise:

How many bugs are currently open and closed and a release date please?

The bug renders the application unable to display any query results on high sierra. This isn’t an insignificant or cosmetic issue, it’s a show stopper.

If some other bugs are still unfixed could you advise a cut-off date when a release will be provided?

Is this also affecting Oracle Enterprise customers?
I think it is.

For what it’s worth…

I was performing some beta testing for apple and found the fault so decided to report to Oracle.

I provided Oracle with a suggested fix which is relatively easy to implement and is verified as working.

The fix would be effortless & without risk to deploy as a ‘patch’ if in an Agile development environment was utilised.

I would have thought Oracle would be up to date on this technology by now.

[22 Oct 22:19] Aaron Langley
It’s becoming apparent that Oracle’s senior management intends to starve MySQL of oxygen in the hope it’s user base with move on to other alternatives.

Obviously not an ideal situation. Hopefully, Oracle will release a patch soon!