AWS SSL Certificate setup for WordPress site hosted on Nginx + CloudFront

Hello friends. Today I’m going to talk you through the process of setting up a free SSL certificate with Amazon ACM. This is specifically for those of you who have your sites served via Nginx running on AWS EC2 instances and have everything going through CloudFront. It’s quite a specific setup, but it’s a good setup. Here we go then!

Step 0: Make sure you can receive admin emails on your domain

When we request the SSL certificate Amazon will send an email to the following email address formats. You can’t specify alternatives so the first thing to do is make sure you can receive messages at at least one of the following:


Step 1: Order your certificate

To use an ACM Certificate with CloudFront, you must request the certificate in the US East (N. Virginia) region so choose N. Virginia from the location menu in the top-right of the AWS dashboard.

Now you need to go to your list of CloudFront distributions and edit the one for the site that needs SSL.

On the Distribution Settings page you should see a ‘Request an ACM certificate’ button. Clicking that button will whisk you away to the certificate request form. Here you must enter your domain name as follows:


(for a wildcard certificate)

(for a specific sub domain)

If you’re using the naked domain (i.e without www. or any other subdomain) make sure to enter that, the wildcard alone won’t work. You will likely have two entries (e.g. and *

Click the ‘Review and request’ button followed by ‘Confirm and request’ on the next screen.

Step 2: Approve the certificate request

You should soon receive an email from Amazon Certificates. Click on the approval link in the email and then the ‘I Approve’ button on the linked page. You should be shown a success message.

Step 3: Select your new certificate in CloudFront

Go back to the CloudFront Distribution Settings page where you earlier clicked the grey ‘Request an ACM certificate button’.

Selecting the ‘Custom SSL Certificate (’ radio button will enable the dropdown list of certificates for you to choose from. You may need to click the refresh button first. Select your new certificate and click the blue ‘Yes Edit’ button.

Step 4: Update your server settings

You’ll need to update your Nginx site config. Here’s an example:

server {
        listen          80;
        return 301$request_uri;

server {
        listen       80;

        if ($http_user_agent !~ “Amazon CloudFront”) {
                return 303$request_uri;

        root            /var/www/yourdomain/htdocs;
        index index.php;

        # For PHP:
        location ~ \.php$ {
                fastcgi_pass unix:/var/run/php5-fpm.sock;
                include fastcgi_params;
                fastcgi_param SERVER_NAME $host;
                fastcgi_param SCRIPT_FILENAME $document_root/$fastcgi_script_name;

        location / {
                try_files $uri $uri/ /index.php?$args;

        if (!-e $request_filename) {
                rewrite ^(.+)$ /index.php?q=$1 last;

    location ~*  \.(jpg|jpeg|png|gif|ico|css|js|woff)$ {
        expires 7d;

    location ~*  \.(pdf)$ {
        expires 7d;

You’ll need to reload Nginx on your server for the changes to take effect.

sudo service nginx reload

Step 5: Install the SSL Insecure Content Fixer WordPress plugin

I’ve found it necessary to install the SSL Insecure Content Fixer plugin for WordPress to get everything working as expected with this specific setup. I believe this is to do with the fact that WordPress cannot detect SSL on the server because that’s actually being handled by CloudFront. If you know a way round this please let me know!

After installing and activating the plugin change HTTPS detection to ‘unable to detect HTTPS’ on the Plugin’s settings page.

Step 6: Update WordPress URL

You’ll need to tell WordPress that your site URLs should now include https. You can do this from WordPress’ Settings > General page. I prefer however to add the following to the top of my wp-config.php file:

$scheme = “https://“;
$website_url = ‘’;
$_SERVER[“HTTP_HOST”] = $website_url;
define(“WP_HOME”, $scheme.$website_url);
define(“WP_SITEURL”, $scheme.$website_url);

define(‘FORCE_SSL_ADMIN’, true);
$_SERVER[“HTTPS”] = ‘on’;

Step 7: Check everything works

After making that last WordPress URL change you’ll get booted from the Admin area and need to log in again. If something has gone wrong you may get a redirect loop. If everything’s working fine though you’ll be able to log in and everything should appear normal, only now with bonus securitay! Check the front end of your site is loading over SSL too. You may need to force a CloudFront invalidation to speed things up.

The last thing to do is check the SSL itself which you can do at — This test can take quite a few minutes so be patient.


Remove non-alphanumeric characters in Google Sheets

I recently found I needed to remove non-alphanumeric characters in Google Sheets. I had a column of text containing business names which I needed to format as URL friendly equivalents. For example, I needed to turn these:

  • My Business Name
  • Derek’s World of Fish!
  • Jane & Janet’s Pieland

Into these:

  • my-business-name
  • dereks-world-of-fish
  • jane-janets-pieland

I achieved this by added the following formula into the top cell of a blank column. This assumes the names you need to format are in the fist column of the spreadsheet

=ArrayFormula( lower(REGEXREPLACE( A2:A , "([^A-Za-z0-9]+)|(---)" , "-" ) ))

Creating a robust event search facility for Derby Museums

It’s been a while since I’ve posted anything here because I’ve been busy working on the prototype for the new Derby Museums website. The prototype has come together very well but there are always going to be a few head-scratching moments. For me, one of these centred around how to search and filter events. The tricky part is that we have single-day events, multi-day exhibitions, three locations and lots of other filters.

I had initially thought of performing specific searches on the database based on the above conditions. So I’d grab the conditions of the users search e.g. ‘Show events between tomorrow and next Thursday’ or ‘Show events for children at Pickford’s House’ and search on that. But it became clear pretty quickly that there are far too many conditions that could come into play to make this clean so I changed tack. Instead I decided to pull in results for all events and exhibitions from a single search and then filter them out with if/else conditions inside a while loop.

Finding events that span multiple dates

Another aspect that increased the complexity a little is searching for events that span multiple dates. As you’d expect the event is stored with it’s start and end dates in the database. Matching an event on a specific day is easy. If I search for events that are running on June 30th all I need to do is match events with start dates on or before June 30th which also have end dates of on or after June 30th. This would turn up any single-day events on that date or any multi-date events running on that date. But what about multi-date event that start and end after our search range? Or events that start before our first search date and that end before our end search date? That condition could legitimately match events that are running for the start of our search range, but it would also find events from any point in the past.

What are all the event search conditions we need to match?

I found it easiest to draw this out visually. In the following example we want to match events 1 – 4 but not return results for events 5 and 6.

[  jan  ][  feb  ][  mar  ][  apr  ][  may  ]
         [ ———————— event1 ———————— ]
         [ ——— event2 ——— ]
                        [ ——— event3 ——— ]

I worked out the following set of search conditions to achieve this:

Condition 1: Finds event1

if event start date is before first search date AND event end date is after last search date

Condition 2: Finds event1 & event2

if event start date is before first search date AND event end date is after first search date

Condition 3: Finds event3

if event start date is before end search date AND event end date is after last search date

Condition 4: Finds event4

if event start date is after first search date AND event end date is before last search date

We can discard Condition 1 as Condition 2 returns those results too. Now that we have our logic we can translate this to code. I put the following inside a PHP while loop.

if ( (isset($filter_date_from)) && (isset($filter_date_to)) ) {

    if ( 
    ( ($eventdate <= $filter_from) && ($enddate >= $filter_from) )
    ( ($eventdate <= $filter_to) && ($enddate >= $filter_to) )
    ( ($eventdate >= $filter_from) && ($enddate <= $filter_to) )
    ) {
        // show results
    } else {
        // nothing found so skip to next iteration


This assumes you’ve set variables based on a user’s desired date range and pulled your events from the database and set variables for start and end dates.

All of the code for displaying the event listing comes after the above code. If an event doesn’t match one of the conditions the continue function will jump the loop to the next iteration.

What the above doesn’t take into account is that Museum locations are shut on Sundays and Mondays. Currently you could search for events on a Monday and receive results based on the above logic. The event is ‘technically’ on for the date you’ve searched but the Museum itself is closed. I’m not sure whether to return these results and point out that the Museum is closed or not to show them at all. That’s one for another day.


Move WordPress from localhost to server without using plugins

If you develop your WordPress websites on a local machine you might be viewing at a URL like localhost:8888/yourdevsite/ which is fine until you get to the point you need to launch the website or move it online for a client to take a look. I’m going to show you how to move WordPress from localhost to server without using plugins.

The problem you’ll face if you copy the local database over to your server all of your internal links will be broken as they’ll all point to localhost. One solution is to create a completely fresh WordPress install on the new server and copy over just the theme and plugin files before re-inputting any content. This approach is OK for very small sites but is a huge pain if you’ve spent any time at all on this already. There’s also the chance that you’ll miss something. The other option is to update the URLs wherever they appear in your WordPress database.

There are a few different ways you can update the database. As with most WordPress tasks I prefer to do things without the use of a third-party plugin, especially if it something pretty easy like this. Ignoring the plugin route, you can either run an SQL command on your database or export and modify the database. I prefer the export option as it leaves the original database intact.

First, have you removed any insecure passwords?

Before we start, let’s take a moment to think about security. One mistake I’ve seen people make when moving a dev site to a live server is to leave a highly insecure admin account in place. I recommend using strong, hard-to-guess usernames and strong passwords even while in your local development environment. If you have an a user account called ‘admin’ with a password of ‘password’ then please make sure to change it while still in you local environment and before you begin this process.

Updating your database URLS in a text editor

Export your local database to a file

If you’re using PHPMyAdmin or MySQLWorkbench locally you can quickly export the contents of your database to a file which can be opened in a text editor like Sublime Text, BBEdit, TextWrangler or anything else.

In PHPMyAdmin just click the Export tab, choose the Quick option under Export Method, make sure the Format is set to SQL and then click Go. You’ll be asked to choose a location to save the file.

In MySQLWorkbench choose the Data Export option from the Server menu, and choose the database to export in the Tables to Export window. Lower down the screen you can choose al location to save the file where it says Export to Self-Contained File. Now just click Start Export and save the file.

Use a text editor to find and replace the URLs

Open your exported file in your text editor of choice. I use BBEdit for this. Now you can simply do a find and replace changing http://localhost:8888/yourdevsite/ to

Once that’s done save the file.

Import the updated file on your live server

Now move over to your live server and create the database through your hosting control panel, MySQLWorkbench or Terminal. Make a note of the database name and password for later.

Now you need to import the database file you modified and saved in the previous step. In PHPMyAdmin you do this from the Import tab. In MySQLWorkbench it’s the Data Import option, which you’ll find in the Server menu again.

Upload your WordPress files

You will also need to move your site files of course as we’ve only ported across the database so far. I like to do this with SSH but if you need to use FTP that’ll work too. Just copy your complete local site including all WordPress files, plugins, theme files and uploads to wherever they’re going to live on the live server.

Update your WordPress configuration file

The last part of the puzzle is allowing WordPress to connect to the database. To do this edit your wp-config.php file to point to the new database name and password. After you’ve done that you should be able to view the site in its new home including all your previously entered content.


Hide your WordPress username for extra security

I love WordPress but there are a few defaults which aren’t great from a security point of view. One obvious example is the way that WordPress makes your username visible to the whole world in the slug of your author archive which generally appears at There is often a link to this URL included on every single post you publish making it extra easy to find. To avoid the chance of some malicious fool gaining access to your site you should hide your WordPress username to keep both your password and your username secret.

What WordPress is actually showing in the author URL is what’s called your ‘nicename’. Your nicename is stored separately in the site database and matches your username unless your username contains any special characters. WordPress sets this to match your username automatically when your user account is first created and uses it in the aforementioned author URL. Which is a bad idea as it gives potential attackers a head start on gaining access to your website.

Hide your WordPress username by removing it from the author URL

If you have access to your website’s database it’s very simple to change your ‘nice name’ and thereby replace the username that appears in your author archive with something else. If you use a GUI like PHPMyAdmin or MySQLWorkbench just go to the users table (by default called wp_users) and find the row for your account. You should see columns for user_login, user_pass, user_nicename and some others. You can change the entry from user_nicename to anything you want but as this is where you do want to be identified it makes sense for this to be you first name or full name.

You want to end up with something like this:

ID user_login user_pass user_nicename
1 notyourname somethingreallysecret yourname

If your user_login and user_nicename are both already set to your first name or full name I’d strongly recommend changing your user_login to something more secure. You can do this from this table too. The only thing your user_login should be used for is logging in so it doesn’t need to be short or look cool. Just don’t try to change your password here, you’ll need to do that through the WordPress settings page.

Changing your WordPress nicename with a plugin

I prefer to make these sort of changes to the database manually but if that’s not possible or you don’t feel comfortable making edits to the database directly then using a plugin is probably the better option. Install and activate the Edit Author Slug plugin and you’ll find you can change your username very easily. This plugin also allows you to change the ‘author’ part of the URL meaning you could, if you wanted, direct people to an archive your posts at

Redirecting your old author URL to your new one

If your old author URL has been listed in search engines you may want to create a 301 redirect from the old URL to the new one. You can do this at the web server level or with a plugin such as Simple 301 Redirects.