command-line-tutorials

Connecting to a Remote Database through an SSH Tunnel on WP Engine

Reading Time: 3 minutes

In this guide, we’ll explore what it’s like to connect to a remote database using MySQL, through an SSH tunnel. WP Engine recently stopped allowing direct remote SQL connections to their servers for security reasons, and switched to allowing customers remote database connections through an SSH tunnel.

Connecting through an SSH tunnel can be tricky, especially if you’re not used to it. I had my own issues as I fumbled my way through figuring out how to connect for the first time, but after I understood how SSH tunneling works, it allowed me to have a deeper understanding of how remote database connections work through SSH tunneling. I’m writing this guide in hopes that it will help demystify the set up process.

What is SSH tunnel?

Let’s start off by giving a brief understanding of SSH tunneling. If you’re already familiar with this, and just need to know how to connect on WP Engine, see the Connecting to the MySQL Database section below.

Start the SSH Connection

You can read WP Engine’s official guide on remote database connections by clicking this link. However, I think it’s important to know the pieces to connect.

ssh -L 3307:127.0.0.1:3306 env@env.ssh.wpengine.net -oStrictHostKeyChecking=no

The aforementioned command creates an SSH connection, forwarding port 3307 on the local machine to port 3306 on the remote server; meanwhile, -oStrictHostKeyChecking=no flag tells SSH to ignore the HostKeyCheck (the part you’re usually asked to accept or deny the connection; i.e removes unnecessary pop-ups, instructions or dialogue). Once successfully connected, you should see a message or prompt like what I have below:

___       _________       __________              _____
__ |     / /__  __ \      ___  ____/_____________ ___(_)___________
__ | /| / /__  /_/ /      __  __/  __  __ \_  __ `/_  /__  __ \  _ \
__ |/ |/ / _  ____/       _  /___  _  / / /  /_/ /_  / _  / / /  __/
____/|__/  /_/            /_____/  /_/ /_/_\__, / /_/  /_/ /_/\___/
                                          /____/
WP Engine Shell - PHP 8.0

* WP-CLI Commands:   https://developer.wordpress.org/cli/commands/

*** NOTE ***
This is a sandboxed environment that interacts with the production server for
your website. Most resource usage commands (i.e. top, vmstat, free, etc.) do not
reflect the usage of your production server environment.
wpe-user@env.ssh.wpengine.net:~$

Connecting to the MySQL Database

Once the SSH tunnel is established, the next step is to connect to the MySQL database using the local port. Your SSH connection will need to run in the background. This can be done using any MySQL client software or terminal program. This guide covers connecting via terminal. Below are what you’ll need for your connection settings. The information for Username, Password and Database can all be found in your wp-config.php file:

Hostname: 127.0.0.1
Port: 3307
Username: WordPress database user
Password: WordPress database user password
Database: WordPress database user

With the above settings, the MySQL client will connect to the database through the SSH tunnel, providing a secure remote connection. Once successful, you should get something like I have below.

mysql -h 127.0.0.1 -P 3307 -u username -p wp_database_name
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12856769
Server version: 5.7.40-43-log Percona Server (GPL), Release '43', Revision 'c1b94a6cfd7'

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Closing the SSH Tunnel

After the MySQL connection is no longer needed, the SSH tunnel can be closed. This can be done by pressing Ctrl + D in the terminal window where the SSH tunnel was created.

Conclusion

Creating a secure remote MySQL connection through an SSH tunnel is an effective way to protect the database from unauthorized access. By following the steps outlined in this blog post, remote access to a MySQL database can be established securely.

Parsing WP Engine Security Auditor Information from Your Error Logs

Reading Time: 2 minutes

WP Engine has a plugin called Security Auditor which logs certain activities to a site’s error logs. While it can muddy up your logs, it’s a great resource, but they only keep the logs for 5 days, so download them from within your WordPress site by looking for the WP Engine logo, and clicking “General Settings”.
The direct link is: wp-admin/admin.php?page=wpengine-common

Once on this page, look for “Web Server & PHP Error Log” (which is usually towards the bottom) and ensure you click the links that say “Live Site” to download your files. This is the last two days, but you can easily script if you know what you’re doing.
I’m not posting the link here since it has an API key, and I don’t recommend you publicly post the link either.
Alternatively, you can set up an Amazon S3 bucket to store your logs – read here for setting that up.

Okay, now that you have your logs in front of you, let’s get to the meat and potatoes. In the You also may not need zgrep if your logs are not compressed by the gzip program. If that’s the case, change zgrep to grep (ggrep if on Mac using Homebrew if you haven’t set your alias).

This script prints the user’s email, time and date as well as the plugin that was updated. In order for the user’s email to print, you must have wp-cli installed for this to work. If you don’t, the function will print out the user ID instead.
Usage: printupdates logfile
Replace “logfile” with the path to your log file
e.g. /var/log/apache2/site.error.log

function printupdates(){
    getlog=$(zgrep -vh 'scan' "${1}" | grep 'auditor.*upgrade.*plugin');
    if [[ -z "${getlog}" ]]; then 
        printf "%s\n" "No data found. Exiting."; 
        return 1;
    else
        if [[ $(which wp) ]]; then 
            printf "%s\n" "${getlog}" | awk 'BEGIN{a[$2];a[$3];a[$4]}{for (x in a)
            date=gensub(/.*([A-Z][a-z]{2}) ([0-9]{2}).([0-9]{2}):([0-9]{2}):([0-9]{2}).*$/, "\\1 \\2 \\3:\\4", "g", $x);
            plugin=gensub(/.*plugin\":\"(.*)\/.+\.php.*/, "\\1", "g", $12);
            cmd = "wp plugin get " plugin " --field=title 2>/dev/null"; 
            cmd | getline plugin
            getuser=gensub(/.*current_user_id\":([0-9]+).*/, "\\1", "g", $12);
            cmd = "wp user get " getuser " --field=user_email 2>/dev/null"
            cmd | getline getuser
                printf "\nDate/Time: %s\tUser: %s\nPlugin: %s\n\n",
                date, getuser, plugin
            close(cmd)
            }'
        else
            printf "wp-cli not found. Double check if wp-cli is installed. Printing user ID instead.\n" &&
            printf "%s\n" "${getlog}" | awk 'BEGIN{a[$2];a[$3];a[$4]}{for (x in a)
            date=gensub(/.*([A-Z][a-z]{2}) ([0-9]{2}).([0-9]{2}):([0-9]{2}):([0-9]{2}).*$/, "\\1 \\2 \\3:\\4", "g", $x);
            plugin=gensub(/.*plugin\":\"(.*)\/.+\.php.*/, "\\1", "g", $12);
            getuser=gensub(/.*current_user_id\":([0-9]+).*/, "\\1", "g", $12);
            printf "\nDate/Time: %s\tUser: %s\nPlugin: %s\n\n", date, getuser, plugin
            }'
        fi
   fi
}
User: myemail@gmail.com
Date/Time: Feb 05 20:32
Plugin: myplugin

User: myemail@gmail.com
Date/Time: Feb 05 20:32
Plugin: my-next-plugin

I went through a few iterations to get this working perfectly. It will also detect if you don’t have wp-cli installed.

Cut

Reading Time: < 1 minute

There’s no way to cut it when it comes to telling you how to use this great tool!

Whenever I’ve used it, I commonly use it in lieu of a user’s home directory:
$(pwd | cut -d\/ -f5). This helps me save time from copy and pasting the install name, however it doesn’t always work.
For example, if we’re not in /path/to/live/SITE or /path/to/log/SITE then cut can’t grab the appropriate path. Let me break it down by first running pwd.

spencer@local:/nas/content/live/dubtraxx/wp-content/themes/minimum-pro$ pwd
/nas/content/live/dubtraxx/wp-content/themes/minimum-pro

We see that it’s printing the working directory, all the way up to the directory we’re in. PWD is just printing the folder I’m currently in, including the server’s absolute path. Whenever you include the -d you’re telling pwd that you want to use / as your delimiter (separator).
However, since /is a special character in Linux’s bash shell, we need to escape it by adding a backslash \.

On this note, it’s also perfectly acceptable to use -d'/' instead of a \ to signal the delimiter of choice to cut.

Let’s take a look at what happens if we run the following:

spencer@local~ :/path/to/live/dubtraxx $ pwd | cut -d\/ -f2
path
spencer@local~ :/path/to/live/dubtraxx $ pwd | cut -d\/ -f3
to
spencer@local~ :/path/to/live/dubtraxx $ pwd | cut -d\/ -f4
live
spencer@local~ :/path/to/live/dubtraxx $ pwd | cut -d\/ -f5
dubtraxx

And for some testing…

spencer@local~ :/path/to/live/dubtraxx $ pwd | cut -d'/' -f4-
live/dubtraxx
spencer@local~ :/path/to/live/dubtraxx $ pwd | cut -d\/ -f2-
path/to/live/dubtraxx
spencer@local~ :/path/to/live/dubtraxx $ pwd | cut -d\/ -f1

spencer@local~ :/path/to/live/dubtraxx $ pwd | cut -d\/ -f1-
/path/to/live/dubtraxx

There are many more uses for cut, but we’ll go over those in another tutorial.
Ciao!

Awk

Reading Time: 2 minutes

Awk is a powerful tool that we’ll take a look at to parse our logs. It’s much more powerful than what we’re going to be using it for in these tutorials, but I encourage you to learn more about awk on your own if you like it.

Awk separates a file into columns (known as fields) and rows – whenever you think of a file and how awk parses it, think of columns and rows.

With this knowledge, we can now take a look at awk’s -F flag which allows us to specify how awk sees columns. By default, awk uses a space as its delimiter to read through text. In the following example, we can see that if we just print then awk is going to print the entire file:

awk '{ print }' /var/log/nginx/site.access.log| head -n3

100.43.90.123 supadupa.rocks - [27/Apr/2019:00:54:32 +0000] "GET /robots.txt HTTP/1.1" 200 106 "-" "Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)"
127.0.0.1 supadupa.rocks - [27/Apr/2019:00:59:52 +0000] "GET //?wp-cmd=ping HTTP/1.0" 200 5 "-" "curl/7.47.0"
127.0.0.1 supadupa.rocks - [27/Apr/2019:00:59:53 +0000] "GET //wp-admin/?wp-cmd=ensure HTTP/1.0" 200 305 "-" "curl/7.47.0"

Now if we go to print field $1 (the IP addresses) we simply need –
awk '{ print $1 }' /var/log/nginx/site.apachestyle.log

awk '{ print $1 }' /var/log/nginx/site.access.log |head -n3
100.43.90.123
127.0.0.1
127.0.0.1

If I wanted to search like grep, but using awk, there’s a solution for that. With awk, you have to put your regex nested between two / in order for it to be interpreted.
Additionally, if your regex has special characters inside of it (like ?!*& or other other special characters) you will need to escape them using a \. See awk’s man page for more information.

awk -F'"' '/104\.196\.177\.220/{ print $2 }' /var/log/nginx/site.apachestyle.log |head -n3
POST /wp-cron.php?doing_wp_cron=1556326791.3251020908355712890625 HTTP/1.0
POST /wp-cron.php?doing_wp_cron=1556334044.2906210422515869140625 HTTP/1.0
POST /wp-cron.php?doing_wp_cron=1556336718.0919361114501953125000 HTTP/1.0

That’s all for now. I hope you enjoyed reading this post!