In today’s fast-paced digital environment, operational efficiency and security are important. This blog post explores a streamlined approach to retrieving user emails from specific IPs in WordPress using Slack Slash commands powered by AWS Systems Manager (SSM) and AWS Lambda. Automating this process significantly reduces the operational overhead associated with running SQL commands manually. Additionally, this method enhances security by allowing users who lack access to production environments to execute necessary scripts through Slack commands, ensuring they can perform their tasks without compromising system integrity.

Retrieving User Emails in WordPress via MySQL

To retrieve user emails from WordPress based on specific IP addresses, we can utilize a MySQL query. This query extracts the user email by identifying the user ID associated with a given IP address stored in the session tokens. Here is the MySQL query used for this purpose:

SELECT user_email 
FROM db_name.wp_users 
WHERE id = (
    SELECT DISTINCT user_id 
    FROM db_name.wp_usermeta 
    WHERE meta_key = 'session_tokens' 
    AND meta_value LIKE '%IP_HERE%'
);

This query works by first identifying the user ID from the wp_usermeta table where the meta_key is session_tokens and the meta_value contains the specific IP address. It then uses this user ID to fetch the corresponding user email from the wp_users table.

To automate this process, we can use a shell script that executes the above MySQL query. Here is an example of such a script:

result=$(mysql -h $DB_HOST -P 3306 -u $DB_USER -p$DB_PASSWORD -e "SELECT user_email FROM db_name.wp_users WHERE id = (SELECT DISTINCT user_id FROM db_name.wp_usermeta WHERE meta_key = 'session_tokens' AND meta_value LIKE '%$1%' LIMIT 1);")

You can run this script by executing the following command:

sudo sh get-email-from-IP.sh IP_HERE

Leveraging AWS SSM and Lambda

To further enhance this process, we can integrate AWS Systems Manager (SSM) and AWS Lambda. AWS SSM allows us to securely manage and execute the shell script, while AWS Lambda provides the serverless compute power to run the script in response to Slack commands. This integration not only automates the retrieval process but also ensures that it is secure and scalable.

By leveraging these AWS services, we can create a seamless and efficient workflow that minimizes manual intervention and enhances security. Users can simply issue a Slack Slash command to retrieve user emails based on specific IP addresses without needing direct access to the production environment or running SQL commands manually.

This approach not only streamlines operations but also ensures that security protocols are maintained, making it an ideal solution for modern web applications.

Prerequisites

Before proceeding to the implementation steps, ensure you have the following in place:

1. Amazon EC2 Instance: A running Amazon EC2 instance where your WordPress site is hosted.
2. Slack Workspace: Access to a Slack workspace where you can create and manage Slack Slash commands.
3. MySQL Database: Access to the MySQL database used by your WordPress site.
4. Shell Script: The automation shell script for retrieving user emails based on IP addresses.

Implementation Steps:

Note: Ensure that AWS SSM and AWS Lambda are configured in the same region as your Amazon EC2 instance.

Step 1: Go to AWS Systems Manager

Step 2: Create Document → Command or Session

Step 3: Provide the necessary information for the document.

• Name: fetch-email-from-IP

• Target type: /AWS::EC2::Instance

• Document type: Leave the default value (Command)

• Content: JSON

Step 4: For the content, replace the template using the code below:

{
  "schemaVersion": "2.2",
  "description": "Run get-email-from-IP.sh script with dynamic IP and send Slack notification",
  "parameters": {
    "ipAddress": {
      "type": "String",
      "description": "(Required) IP address to search."
    },
    "slackHook": {
      "type": "String",
      "description": "(Required) Slack Webhook URL for notifications."
    }
  },
  "mainSteps": [
    {
      "action": "aws:runShellScript",
      "name": "runShellScript",
      "inputs": {
        "runCommand": [
          "#!/bin/bash",
          "cd /var/tools",
          "ip_address='{{ ipAddress }}'",
          "hook='{{ slackHook }}'",
          "echo \"Running script with IP address: $ip_address\"",
          "result=$(sudo sh get-email-from-IP.sh $ip_address)",
          "echo \"Script result: $result\"",
          "curl -H \"Content-type: application/json\" -d \"{\\\"text\\\": \\\"Email address for IP $ip_address: $result\\\"}\" -X POST \"$hook\""
        ]
      }
    }
  ]
}

Step 5: Click Create a document to save changes.

Step 6: Run the command to ensure that the script is working.

• SSM Document → Open the fetch-email-from-IP → Click the Run command button.

Command Parameters:

• Slack URL: Enter your Slack URL here.

• IP: Put your IP here. Make sure you have recently logged into the WordPress environment to record your IP.

Target Selection:

• Choose instance manually.

• Select your instance.

Expected Output:

Creating a Lambda Function

Step 1: Go to Lambda → Functions

Step 2: Create a Function.

• Name: fetch-email-from-IP

• Runtime: python 3.12

• Click the Create function button.

Step 3: Once created, copy and paste the code below and replace the template for lambda_function.py

import json
import os
import boto3
import hmac
import base64
import time
import hashlib
import urllib.parse
import ipaddress
import re

# Set up constants
SLACK_CHANNEL = os.environ["SLACK_CHANNEL"]
SLACK_HOOK = os.environ["SLACK_HOOK"]
SLACK_SIGNING_KEY = os.environ["SIGNING_KEY"]
EC2_INSTANCE_ID = os.environ.get("EC2_INSTANCE_ID")
SSM_DOC_FETCH_EMAIL_FROM_IP = os.environ.get("SSM_DOC_FETCH_EMAIL_FROM_IP")

def lambda_handler(event, context):
    try:
        # Parse event data from Slack
        slack_signature = event['headers']["x-slack-signature"]
        slack_timestamp = event['headers']['x-slack-request-timestamp']
        slack_payload = event['body']
        payload = parse_slack_payload(slack_payload)
        from_channel = payload["channel_name"]

        # Check if request originates from a valid source
        from_valid_source = verify_source(
            signing_key=SLACK_SIGNING_KEY.encode('utf-8'),
            timestamp=slack_timestamp,
            body=base64.b64decode(slack_payload).decode('utf-8'),
            slack_signature=slack_signature,
            from_channel=from_channel
        )

        # Parse the command and the IP from the payload
        command = urllib.parse.unquote(payload["command"])
        ip_address = extract_ip(urllib.parse.unquote(payload["text"]))

        # Check if request IP is valid
        from_valid_ip = is_valid_ip(ip_str=(ip_address))

        # Main Code
        if from_valid_source:
            if from_valid_ip:
                if command.startswith("/get-email-from-ip"):
                    return process_command(ip_address, SSM_DOC_FETCH_EMAIL_FROM_IP, SLACK_HOOK, EC2_INSTANCE_ID, "Fetching email from specific IP")                
                else:
                    return "Invalid command"
            else:
                return f"Your input, \"{ip_address}\", is not a valid IPv4 address. Please provide a valid IPv4 address."
        else:
            return "You're not authorized to perform this operation"
    except Exception as e:
        print(e)
        return str(e)


def parse_slack_payload(data):
    decoded_data_raw = base64.b64decode(data).decode('utf-8').split('&')
    decoded_data_formatted = {}
    for item in decoded_data_raw:
        data_object = item.split('=')
        decoded_data_formatted[data_object[0]] = data_object[1]
    return decoded_data_formatted


def verify_source(signing_key, timestamp, body, slack_signature, from_channel):
    basestring = f'v0:{timestamp}:{body}'
    hmac_digest = hmac.new(
        key=signing_key,
        msg=basestring.encode('utf-8'),
        digestmod=hashlib.sha256
    ).hexdigest()
    return hmac.compare_digest(slack_signature, f'v0={hmac_digest}') and SLACK_CHANNEL == from_channel

def extract_ip(text):
    # Regular expression pattern to match IPv4 address
    pattern = r'\b(?:\d{1,3}\.){3}\d{1,3}\b'
    match = re.search(pattern, text)
    if match:
        return match.group()
    else:
        return text
        
def is_valid_ip(ip_str):
    try:
        ip = ipaddress.IPv4Address(ip_str)
        return True
    except ipaddress.AddressValueError:
        return False


def execute_ssm_document(instance_id, ssm_document, parameters):
    try:
        ssm = boto3.client('ssm')
        response = ssm.send_command(
            InstanceIds=[instance_id],
            DocumentName=ssm_document,
            Parameters=parameters,
            TimeoutSeconds=30
        )
        # Convert datetime objects to strings
        if 'RequestedDateTime' in response:
            response['RequestedDateTime'] = response['RequestedDateTime'].isoformat()
        return response
    except Exception as e:
        print(e)
        return str(e)


def process_command(ip_address, ssm_document, slack_hook, instance_id, message):
    parameters = {'ipAddress': [ip_address], 'slackHook': [slack_hook]}
    response = execute_ssm_document(instance_id, ssm_document, parameters)
    print('Command Parameters:', response.get('Command', {}).get('Parameters'))
    return {
        'statusCode': 200,
        'body': f'{message} request received and processing...'
    }

This Lambda function processes Slack commands to fetch email addresses based on IP addresses. It verifies the request’s authenticity, checks if the IP address is valid, and executes an SSM document to fetch the email. If the command or IP is invalid, it returns an error message.

Step 4: Click Deploy to save the changes.

Step 5: Go to the Configuration tab → Environmental variables.

Populate the following variables:

• EC2_INSTANCE_ID – Your EC2 instance ID.
• SIGNING_KEY – Enter the secret signing key from Slack. You can get it here: https://api.slack.com/apps/
• SLACK_CHANNEL – Enter the name of your slack channel.
• SLACK_HOOK – Enter your Slack webhook URL.
• SSM_DOC_FETCH_EMAIL_FROM_IP – fetch-email-from-IP

Step 6: Click the Save button.

Step 7: Go to the Configuration tab → General configuration

Step 8: Edit the General configuration → Set the timeout to 1 minute → Save

Step 9: Go to the Configuration tab → Permissions → Open the Execution role

Step 10: Add permissions → Create an inline policy

Policy name: SSM-SendCommand

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "ssm:SendCommand",
            "Resource": "*"
        }
    ]
}

The ssm:SendCommand action is part of AWS Systems Manager (SSM) and enables you to run commands on one or more managed instances.

Step 11: Go to the Configuration tab → Function URL

Step 12: Create a Function URL, then configure it.

• Auth type: Select NONE, then click Save.

Configuring the Slack command

Step 1: Go to Slack Slash commands

Step 2: Create a new command.

• Command:/get-email-from-ip 

• Request URL: Retrieve this URL from the Lambda Function URL.

• Short Description: Fetch the user_email from a specific IP.

• Usage Hint: IP

Step 3: Click Save.

Verifications

1. Test the Slack command.

   /get-email-from-ip IP_here

2. Make sure you have recently logged into the WordPress environment to record your IP address.

3. Expected output:

   

4. Suppose the IPv4 you entered is invalid. The expected output should be:

   

That’s it! I sincerely hope this guide helps you streamline your workflow and enhance security in your WordPress environment.