≡ Menu

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))
	INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
	SELECT post_parent, 'itsec_enable_ssl', 1
	WHERE newpost_parent NOT IN (
		FROM `wp_postmeta`
		WHERE meta_key = 'itsec_enable_ssl' and meta_value = 1 and post_id = post_parent)
	AND post_parent <> 0;

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`
	CALL sp_enable_ssl(new.post_parent);
CREATE TRIGGER `wp_posts_trigger_update` AFTER UPDATE ON `wp_posts`
	CALL sp_enable_ssl(new.post_parent);

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`
	INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
	SELECT new.id, 'itsec_enable_ssl', 1
	WHERE new.id NOT IN (
		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';
CREATE TRIGGER `wp_posts_trigger_update` AFTER UPDATE ON `wp_posts`
	INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
	SELECT new.id, 'itsec_enable_ssl', 1
	WHERE new.id NOT IN (
		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';

Adding Configuration Files to Oracle Database 12c Instant Client

Oracle Database 12c

Happy New Year!

Though I generally prefer to use the full Oracle client, I have many customers that use the Instant Client to save space. However, this setup frequently is missing the configuration files that may be necessary for some applications to be able to find database servers.

If you are missing any of the files sqlnet.ora, ldap.ora, or tnsnames.ora, then this post is for you. In the full client, these files are stored in {ORACLE_HOME}/network/admin; in the Instant Client, this folder may not even exist – but there’s no reason to panic!

First, check to see if you have an environment variable called TNS_ADMIN. If so, this is where your files should be placed. Check to make sure they’re not already there.

If there is no environment variable called TNS_ADMIN, create one and put the full path to the Oracle base folder. Place the files in this folder. Generally, no reboot is necessary.

Oracle Instant Client FAQ
Configuring sqlnet.ora and tnsnames.ora on the client side

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 (
    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.

Peter Griffin is calm while the world burns around him

The action of moving an Oracle Primavera P6 database from one server to another should be fairly simple. Changing the authentication type on that database from a virtual LDAP server to native Active Directory should also be simple. Doing both at the same time? Not so much. And even though I generally try to keep each major change in an application isolated from others so as not to cause unnecessary problems, the tight timelines I’ve had to deal with lately have made keeping these sorts of things separate well nigh impossible. The fact that most of our third-party applications are not under vendor support contracts also adds to the fun!

The good news is that I’ve gotten used to thinking about every possible thing that could go wrong with all these moving parts, and have an excellent record in averting disaster. Of course, there’s a first time for everything.

For a couple of months I had been aware that our VLDAP servers were going to be shut down, and all applications using them would have to authenticate their users directly against AD or some other method.

I had inherited responsibility for maintaining our P6 environment earlier this year, but had not had a chance to take any formal training or to dig too far into the documentation from Oracle, as I have many other applications to deal with besides this one. It’s been a power-through-it-with-my-hair-on-fire kind of year.

I was somewhat familiar with the P6 Web Administrator, and I learned that is where the VLDAP authentication settings were. Before the VLDAP server was shut down, I managed to change the settings to point at AD for (what I thought was) all of the P6 databases.

Users were able to get into the application both through a thick client via Citrix and also through the Web client – everything was working. All of the VLDAP servers were turned off but not yet decommissioned. Life was good!

A few days later, the P6 databases – which are Oracle Databases – were to be moved to a different database server which had a different IP address and the original databases were to be placed in a standby mode.

The same morning that the databases were to be moved, a user calls in a trouble ticket. He can’t log on to one of the P6 databases – not the primary one that’s used 95%+ of the time, but one that only had occasional use. The error he received specifically mentions the name of the old VLDAP server that I thought had been completely disconnected from the system.

About the time I get the call that this is happening, the databases get moved and no users are able to get into the system. Fortunately, this move was planned and everyone was expecting an outage.

I ask to have the named VLDAP server turned back on long enough to get this user back into the system and for me to reconfigure the authentication on his database (and any other databases I might have somehow missed).

This is where things get really interesting.

I had found the location of the file that configures the database config information for the thick client, which most people were using. For anyone who is interested, it is found in these two locations on a Windows server:

%PROGRAMDATA%\Oracle\Primavera P6\P6 Professional\PrmBootStrap.xml


C:\Users\All Users\Oracle\Primavera P6\P6 Professional\PrmBootStrap.xml.

The database connection info can be changed there.

<?xml version="1.0" encoding="UTF-8"?>
<BootStrap MajorVersion="8" MinorVersion="2" PatchVersion="0" DeplyomentVersion="4">
	<DataBaseAliases DefaultPMAlias="primavp">
			<AppType>Project Management</AppType>
				<Password>(encrypted password)</Password>
[... an Alias block for each P6 database ...]

I wouldn’t recommend changing the encrypted password in the Alias blocks, nor anything after the DatabaseAliases section.

Be sure to also increase the number in the DeploymentVersion field at the top of the file, otherwise, it won’t be sent to the working directory when P6 is started again.

The working directory for this file for each user is:

%LOCALAPPDATA%\Oracle\Primavera P6\P6 Professional\PrmBootStrap.xml.

This is how the file looks if Oracle Database is used for P6. Should you use MS SQL Server or some other system, YMMV.

Incidentally, I only had to make changes here because the name of the database actually changed – in the old location, it had a suffix that was removed. I found that as long as I could tnsping the database from the computer hosting the thick client, the users could connect. For an added layer of insurance, I added a tnsnames.ora file and added entries using the old database names, but pointed at the new database service names and servers, and finally changed the order to “(TNSNAMES, LDAP)” in the sqlnet.ora file to ensure that tnsnames was queried before the Oracle LDAP servers – just in case there was something I was missing here in changing the P6 XML file.

At this point, all users could get into all databases using the thick client, and the outage did not extend past the expected time.

I then proceeded to go to the P6 Web Administrator to look for the places I missed when changing the authentication settings. This is when a bit of panic set in.

P6 Web Administrator error message

A message like the one above shows up – and not only can I not get into the Web Administrator, I can’t get into the Web client either.

I assume (wrongly, by the way – don’t we know what assuming does by now?!) that I can change some XML or INI files and everything will be back to normal.

At this point, be aware that I had a full backup of the server and all databases, and I always backed up any files I changed so as not to corrupt the environment beyond repair. This is not business as usual – but as we had no working test environment, and no vendor support, I had little choice but (and perhaps most importantly – explicit permission!) to work in production.

Unlike the thick client, which is running on three Windows 2008 R2 servers, the Web client runs on Solaris 10 with Oracle WebLogic. Though I can log into the OS via SSH, I don’t have admin rights on the box.

I decide to run several searches, checking for all files with various strings – primarily the old database server name and the names of the databases.

The search I run uses this command:

find /u02/app/bea64 -type f -exec grep '(DB server name, etc.)' /dev/null {} +

I chose the “bea64” folder as the starting point of the search, as that’s where WebLogic is installed and all P6 files and folders seemed to be under that folder.

For further info about this use of the find command in UNIX: StackExchange.

I found a few instances here and there where the server names could be changed. I even found a single binary file that I tried editing with a hex editor. It didn’t matter what I did – I would stop and restart the Web services and the old server names would still show up in the web logs. I noticed that the logs always had a JDBC connection string – which told me that Oracle LDAP and tnsnames.ora were not being used by WebLogic to find the databases. This was made especially clear when I found that the Oracle client wasn’t fully set up correctly – meaning that I couldn’t tnsping the databases from the bash prompt without a few environment variables being set up by me.

I then found what would prove to be the tool that would save the day – the thick client (not web-based!) P6 Web Administrator! It was located at {p6_eppm_home}/p6/dbconfigpv.sh, where {p6_eppm_home} is the home folder for P6 selected at installation.

I tried running the app: ./dbconfig.sh…

First message:

Running Database Configuration Setup...

…getting excited now! And then…

Exception in thread "main" java.awt.HeadlessException:
No X11 DISPLAY variable was set, but this program performed an operation which requires it.
        at java.awt.GraphicsEnvironment.checkHeadless(GraphicsEnvironment.java:159)
        at java.awt.Window.<init>(Window.java:432)
        at java.awt.Frame.<init>(Frame.java:403)
        at java.awt.Frame.<init>(Frame.java:368)
        at javax.swing.SwingUtilities$SharedOwnerFrame.<init>(SwingUtilities.java:1733)
        at javax.swing.SwingUtilities.getSharedOwnerFrame(SwingUtilities.java:1810)
        at javax.swing.JWindow.<init>(JWindow.java:168)
        at javax.swing.JWindow.<init>(JWindow.java:120)
        at com.primavera.admintool.Initializing.<init>(Unknown Source)
        at com.primavera.admintool.AdminApp.main(Unknown Source)

So close!

I hadn’t worked much with UNIX in the last few years, but I remembered the X Windows GUI and decided to find out if I could run this app remotely using X Windows.

I was, up until this point, using PuTTY as my SSH client, and WinSCP to transfer files via SSH. But how to run X Windows?

After doing some searching, I found Cygwin, which I had used several years ago in attempting to set up Ruby on Rails in Windows.

Using this wonderful documentation, I was quickly able to get up and running.

I was able to open the P6 Web administrator remotely using X Windows!

At the local prompt in Cygwin:

$ export DISPLAY=:0.0


$ ssh -Y username@servername_or_IP_address

Enter your password at the prompt. You should then be logged onto the remote system via SSH. Now, at the remote prompt…

$ xterm &

Nota bene – from the documentation mentioned above: “By default, the OpenSSH server does not allow forwarded X connections. This must be configured on the remote host by adding X11Forwarding yes to the sshd_config configuration file. The OpenSSH server must be restarted or SIGHUP’ed to re-read the configuration file after it is changed.” Also: “The OpenSSH server requires the xauth command to be available to forward X connections. Consequently, it must be installed on the remote host.”

At this point, a new xterm window should open that has another command prompt from the remote system. Run the executable command – in this case, “./dbconfigpv.sh” from its folder, and the GUI-based app should open!

The thick-client P6 Web Administrator opened, I was able to change the JDBC connection strings for each database and also to reset the VLDAP authentication back to AD.

I saved my settings, restarted all services, crossed my fingers and stood on one leg.

BINGO! Everything worked now – even with the VLDAP server turned off!