Tag Archives: Apache

Forensics Postmortem Analysis and how abusive bots pushed me to migrate part of my services from Google Cloud to another Cloud provider

I tested Google Cloud when they were in beta, years ago, and I helped their Team to improve the service. And I’ve been using Google Cloud for years.

Here I explain, how an incident, this August, almost made me leave Google Cloud for good. In fact, I migrated many services to other providers. I walk through the steps I took.

The best advice to start with, is, enable Cloud Billing alerts.

Everything started with the alert of Cloud Billing alerting me that half of the budget for the month had been exhausted around the 7th of the month. I checked the panel, and at the beginning I didn’t pay much attention to it, as Billing was indicating that the extra cost was in Compute in a specific region where I had provisioned new instances and more storage and it was just few euros.

Compute Engine Costs are mixed with the bandwidth consumption in the Dashboards which makes no sense:

So, I had to check different dashboards to realise it was a data transfer issue, and honestly, at the beginning I didn’t know where the problem was, but as I wrongly inferred, that the instance generating egress traffic was the one named “ubuntu26-04” I was investigating mainly that.

The first thing to learn that Google Cloud Billing shows the pure Compute and bandwidth usage together. I dislike it, for obvious reasons.

Next day budget for the month was almost gone.

Running the reports I saw “SKU Network Internet Data Transfer Out from Americas to Americas” which was confusing, and initially made me think if it could be the new backups system, sending the backups to my centralised server using the AI compatible agent with the public IP for the API and encryption, but I discarded this very quickly due to the volume of data and I modified the system to use internal IPs instead of the public ones.

The only clear thing at this point was that Internet data transfer (egress) was being sent, and Google makes you pay for that.

I had released several projects recently, like https://read.carlesmateo.com/en Text-to-speech to generate MP3 from text files, or https://sendmeafile.carlesmateo.com/en to send and receive huge files, with resume, checksum verification, AI compatible… So my first thought was that one of the projects or its dependencies could have been compromised, or fanatic AI/crawlers were downloading the sample MP3 files again and again.

I made a mistake when checking Cloud Billing. It indicated that the expenses came from us-central1, so checked the list of instances’ regions and I thought the problem came from a new instance named “ubuntu26-04”.

But I made a mistake, because there are several zones in every region, like us-central1-a, us-central1-b, us-central1-c… My new instance for the new projects was in the zone us-central1-f but I had more than that one instance in the region, only that in different zones. But when I checked the zones for the instances I did not realise that there was more than one instance in the zone us-central1 and I assumed the new instance was the only one in the zone and should be the one generating the egress traffic.

I only had ports 80 and 443 opened in Google’s firewall, so there could be requests to the web server, or that the server got hacked. I analysed the nginx logs for the server and found no huge traffic or big files being requested.

Nowadays bots and crawlers and AI’s generate a crazy amount of traffic, so I had to discard this.

Also, recently Amazon had a problem were it presented huge bills (erroneous) of more than 1 Million dollars to some customers. I didn’t know if Google was calculating the bandwidth costs well.

The Billing Dashboard showed that the increment in data sent was costing me around 11€ per day, so it allowed me to start moving services from that server, with relative calm. The forecast for all the August month displayed that the bill would cost me 300€, which was not nice.

In the meantime I hired a VPS with unlimited data and started to migrate the Docker services there as fast as I could with the idea of shutting down the compromise instance to cut the costs.

One of the things that really made me angry, while I was migrating to avoid those over costs, is that I checked again, the next day, the Cloud Billing Reports, the web loaded and informed me that it had changed with news and showed some floating popups pointing how it work and some improvements pushed by the Google Dev Team, and when I continued, the Cloud Billing Report was displaying an unexpected amount of money to pay in August 2026, more than 10 times what I expected, instead of the forecast I saw previously. So it looked like I had to pay that amount for August, and I started to look at what caused that increment of costs quickly, which fas frustrating. And after a while, when I was thinking about stopping the services, I found that Google had changed the page of the reports and the filters were reset to yearly, and the page was showing the already paid costs from 1st August 2025 to 31th August 2026.

Later I reported how frustrating was the experience to Google as “you should not do that” and “users dislike frequent changes”.

Companies are not aware that when they change things, they often annoy the customers a lot.

Years ago I migrated from my long term account with Amazon AWS to Google Cloud Compute Engine cause I was feed up of the problems in Amazon, how many programming errors they had, the changes they did, causing problems, like to the identifiers of the objects (I had older created objects, with shorter hash, and some API calls did not work any more), and how difficult they made simple things. User Interface was bad, and they also lost two of my instances and communicated really poorly (I also reported that to a friend in Amazon and I had some conversations with Amazon’s AWS PMs), and they were over expensive for developers, so I ended migrating to Google Cloud. And now Google is repeating some of the same mistakes.

They are not only expensive. They make things that could be easy, difficult, for the developers.

These big companies force you to stay continuously learning they changes. They offer great solutions for the Enterprise, but for developers and Startups the costs could be overkill.

Probably they are over-kill and over-expensive for developers and Startups.

With Google Cloud I have experienced some technical issues too, as my older instance froze several times and became irresponsive and I had to do a stop from the web console and I had to wait until the command timed out and Google realised there was a problem so it would consider the instance finally shutdown and I could start it again in other non faulty hardware.

If you are a company with a Load Balancer, with health checks, and several front servers, and 20 developers, you can afford to have Web servers KO, as the Load Balancer will handle it nicely, but if you have a single server with WordPress sites, if the instance stops it means that nobody can access those sites.

As an example of costs, for a Google e2-medium (2 vCPUs, 4 GB of Memory and 20 GB of disk) in us-central1 I pay around 30 € per month. And it happens that the forecast for this month in bandwidth was 300 €.

Also storage is around 1€/month per 10GB for standard. So 200GB of regular space is 20 €/month.

For SSD 1.7€/month per 10GB, even tough provides more IOPS, but not more speed. 200GB of space more is 34 € more per month.

I migrated to a provider were I pay 30 € per month for a 8 vCPU and 24 GB of memory and includes 200 GB of NVMe, and I have unlimited bandwidth. Extra storage is also much more cheap. And the static IP is included (for Google you pay around 1.1€ per month).

The key in here was the unlimited bandwidth.

I also could have rented a dedicated server with 64GB of RAM for around 64 €/month, but these comes with some down sides, like if your server experiences a hardware error, you’ll have to wait for the System Administrators from the Cloud Provider to physically replace the components. With a VPS or instance, I can just launch it in a healthy hardware immediately.

As I was migrating services and investigating the origin of the problems, I saw that 500 GB of data were transferred from us-central1 since the 1st of August.

The Billing Report didn’t allow me to filter for IP. It only helped to know the region, us-central1, which helped me to identify the possible services that could be having the problems.

To be honest, I should have located the origin of the problem quickly, but I did so well blocking bots and uneducated crawlers in the past years, that I had no problems for years, and GCE has mad many changes over time, so I did not remember that I could monitor real time network usage of my servers. Most of the servers I troubleshoot I have only SSH access, so I use network tools.

But as I told previously, I mistakenly thought that just one instance was running in us-central1, and that was not the case. They were in different zones of us-central1.

The live traffic inspection in the server with tools like ss, netstat, nethogs, iftop, iotop and ps revealed normal traffic and load.

A second revision from the nginx logs didn’t reveal any problem over time. And the only opened ports were 80 and 443. With the services I considered potentially more vulnerable (as they used python dependencies) migrated to the new VPS and stopped in the “ubuntu26-04” Google server, the usage shown and the forecast for bandwidth was not reduced and it was the same.

One thing I learned, and I suspected it from my tests with https://sendmeafile.carlesmateo.com/en is that the maximum speed that my instances were able to transfer from Google to Internet (egress) was around 50 Mbps per connection.

I didn’t see this in Google Cloud’s documentation, but in my tests transferring data from Google servers using the public interface and from different locations to this google server I consistently achieved always that top speed of around 50 Mbps per connection (around 6.25 MB/s), but when I communicated Google servers using their private interfaces, the speed for a single connection was much more higher. So I assumed that the bandwidth was capped. After seeing the data transferred in the Billing Cloud Report, it looks clear to me that an important part of the server’s bandwidth max was being used. Later, Apache logs, confirmed me many requests at the same exact second.

Google anomalies tool detected nothing despite the huge increment in data transfer and cost variation (8,960% respect July!).

Before taking the decision to migrating to another server one has to think, not only in costs, but also on how trustworthy are the storage systems, the resiliency of the systems, how many times they are down per year… there are surprises that you discover as you go. With the experience.

But honestly, I felt it was not worth the price to continue with Google Cloud and it was conditioning the kind of projects I could start. My architecture is resilient, based in containers and I can deploy it fast. I also have backups.

Google storage is not that super trustable in my opinion (neither Amazon’s). Some times my VMs froze and Google monitor dashboard stops drawing the lines of metrics (like CPU usage), but Google doesn’t catch that the VM is frozen. My guess is that the problem is the storage or compute servers dying.

Having to pay per GB transferred in a time where bots and AI index your sites continuously, and abuse the servers by launching many requests concurrently, generating load, and where bots consume much more data than humans, force you to take architecture decisions and to implement strategies.

Possible solutions like using CDN’s like Cloudflare to be protected against DoS, to cache statics and to cut bandwidth and CPU abuse (requests to origin), using other cache systems or migrating to other Cloud providers that are more developer and Start up friendly, and that have unlimited data transfer, etcetera.

When I was presented with the new Google Billing Reports page and the filter was reset to one year, and I saw the price that should be showing multiplied by 10, I contacted Google Cloud by X. They kindly replied and invited me to contact them privately. I did and we opened a ticket. Around three hours later I got an email from a Google staff, telling me that they will investigate the problem, and giving me an expected response deadline of 5 days.

It was nice, but by the time I received the email I already had found and sorted the problem.

I troubleshoot the instance “ubuntu26-04” but the CPU usage was low, and no permanent connections (a sign that could have indicated that was hacked):

That Looked normal

sudo ss -tunp state established

Then I did something that I should have done before, I checked the metrics from all the different instances in Google Cloud Compute, starting from the one I initially guessed was the one suffering the problems. That machine was clear. No high CPU usage. No high bandwidth usage. Nothing.

And then I saw it.

Through VM Instances I narrowed to the one in us-central1 that was consuming the Internet bandwidth, “instance-1”:

I was checking the wrong server. The consuming excess of egress was from instance-1 that hosted several WordPress sites and it was using Apache2, not nginx.

Some commands I ran:

# Check for the data sent by Apache. This found a lot of traffic. Note: the access.log of all the sites were being analysed
sudo awk '{s+=$10} END {print s/1024/1024/1024 " GB"}' /var/log/apache2/*access.log
# returned 36.4578 GB that's for a day, so something was clearly wrong

# Analyze requests: See what is more heavy. So basically bots trying to hack into the server and some heavy images. Note root home for at least a server is huge (50MB)
sudo awk '{a[$7]+=$10} END {for(u in a) print a[u]/1024/1024" MB\t"u}' /var/log/apache2/*access.log | sort -rn | head -20
54.3218 MB      /
21.6173 MB      /wp-login.php
17.1676 MB      /wp-content/uploads/2022/10/20221001_200930-cut.png
16.5822 MB      /wp-content/uploads/2022/10/20221001_201033.png
10.7245 MB      /wp-content/uploads/2022/02/python_loop.gif
6.61174 MB      /xmlrpc.php
6.60595 MB      /page/2/?shopdetail%2FI37266592159
6.60595 MB      /page/2/?shopdetail%2FI37256267952
6.60595 MB      /page/2/?shopdetail%2FI37256137878
6.60595 MB      /page/2/?shopdetail%2FI37256033568
6.60595 MB      /page/2/?shopdetail%2FI37224959793
6.60595 MB      /page/2/?shopdetail%2FI37214305672
6.60595 MB      /page/2/?shopdetail%2FI37203620584
6.60595 MB      /page/2/?shopdetail%2FE37266925367
6.60595 MB      /page/2/?shopdetail%2FE37242498260
6.60595 MB      /page/2/?shopdetail%2FE37207321261
6.60595 MB      /page/2/?shopdetail%2FA37259909717
6.60595 MB      /page/2/?shopdetail%2FA37259600135
6.60595 MB      /page/2/?shopdetail%2FA37256306882
6.60595 MB      /page/2/?shopdetail%2FA37256197883

# Analyze requests: See what IP's are requesting more traffic in total. Gotcha
sudo awk '{a[$1]+=$10} END {for(i in a) print a[i]/1024/1024" MB\t"i}' /var/log/apache2/*access.log | sort -rn | head -20
34549.2 MB      74.7.241.60
1491.36 MB      116.202.235.23
287.651 MB      3.41.188.33
232.362 MB      74.7.227.160
199.987 MB      18.97.9.169
183.748 MB      66.249.74.42
84.1681 MB      114.119.140.64
78.3209 MB      114.119.149.169
70.5166 MB      114.119.156.46
67.8224 MB      114.119.150.29
66.7544 MB      114.119.145.237
58.7278 MB      114.119.147.113
57.1822 MB      114.119.162.62
56.0061 MB      66.249.74.43
55.3612 MB      114.119.141.79
21.9245 MB      216.73.217.35
21.2516 MB      104.243.44.93
17.7313 MB      79.116.217.181
17.1676 MB      189.81.71.184
16.5822 MB      205.188.63.181

So we see that this IP 74.7.241.60 is requesting 34.5 GB in a day.

So, according to this IP Info service, the IP is from Microsoft. So apparently, crawlers/IA from Microsoft or running in Microsoft infrastructure, were costing me money because they are abusing, scanning my sites, with many connections concurrently, like horrible soulless machines:

I double checked the IP with another provider:

A command to see a sample of what kind of requests I have from this IP, brings more light:

grep "74.7.241.60" /var/log/apache2/*access.log | head -20
/var/log/apache2/www_mysqlproxycache_com-access.log:74.7.241.60 - - [13/Aug/2026:00:00:22 +0000] "GET /page/2/?shopdetail%2FI37207088742 HTTP/1.1" 200 3463409 "http://www.mysqlproxycache.com/page/2/?shopdetail/I37207088742" "Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.4; +https://openai.com/gptbot)"
/var/log/apache2/www_mysqlproxycache_com-access.log:74.7.241.60 - - [13/Aug/2026:00:00:34 +0000] "GET /page/3/?shopdetail/I37207088742 HTTP/1.1" 301 264 "http://www.mysqlproxycache.com/page/2/?shopdetail/I37207088742" "Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.4; +https://openai.com/gptbot)"
/var/log/apache2/www_mysqlproxycache_com-access.log:74.7.241.60 - - [13/Aug/2026:00:00:35 +0000] "GET /page/3/?shopdetail%2FI37207088742 HTTP/1.1" 200 1900262 "http://www.mysqlproxycache.com/page/3/?shopdetail/I37207088742" "Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.4; +https://openai.com/gptbot)"
/var/log/apache2/www_mysqlproxycache_com-access.log:74.7.241.60 - - [13/Aug/2026:00:00:37 +0000] "GET /page/7/?shopdetail/E37249347969 HTTP/1.1" 301 264 "http://www.mysqlproxycache.com/page/6/?shopdetail/E37249347969" "Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.4; +https://openai.com/gptbot)"

According to the User-Agent is GPTBot. So apparently a bot running in Microsoft self-identifying as OpenAI were killing causing high usage to my server and to my budget by requesting many pages at the same time, for an old shopdetail page that does not exist in that server (maybe there was a shop years before I got that IP), and spends a lot of bandwidth responding with a heavy page of 6 MB.

In the logs I saw many request at the same precise second.

Asking to Claude it tells me that the range of IPs of the Microsoft attacker IP are not in the publicly published IPs of OpenAI for GPTBot, so apparently somebody using Microsoft IPs is pretending to be a GPT Bot, and it isn’t:

The domain is an old WordPress that has been hacked. Yes, this happens more often that we would like, with WordPress.

I checked the https and it returns a SSL potential warning in the SSL.

I tried with http http://www.mysqlproxycache.com/page/7/?shopdetail%2FI37224744693 (one of the requests in the logs) and I saw that the WordPress site was hacked.

So first things first: I stopped Apache.

sudo service apache2 stop

So, to summarise, there was a bot from a Microsoft IP, that was requesting many times per second pages to a WordPress site that was compromised and it was returning a large article with many photos, so heavy in terms of data usage.

With Apache2 server stopped, I moved the config symbolic link from /etc/apache2/sites-enabled/ to another folder /etc/apache2/sites-disabled/ that I created.

So basically, when I started Apache, the compromised site will not be served. It will be served by the default site.

I restarted the Apache server and checked that the URLs for that domain were catch by default site and no processing resources of bandwidth was used.

That was to avoid a main site marked as default in the Apache config to attend those requests and waste data.

And then I added to the firewall, to Google Cloud’s Firewall and to the servers in all my instances, to block all the IPs from Microsoft from that range.

I saw that the BGP Prefix is 74.7.0.0/16 so I block all the 65,536 IP Addresses:

ufw insert 1 deny from 74.7.0.0/16 to any

As you can see I inserted this rule on the first position. Don’t just add it with ufw deny from 74.7.0.0/16 to any or otherwise it will go to the last position, after the allow rules. And then it would not be enforced.

Check your rules with:

ufw status numbered

You can block the range in your provider’s Firewall (VPC Firewall in Google Cloud).

If you didn’t have the Ubuntu Firewall active, and want to activate it now, make sure that you have the rules for the SSH connection:

echo "Enabling SSH"
sudo ufw allow 22/tcp

echo "Enabling Web 443 and 80"
#sudo ufw allow "Apache Full"
sudo ufw allow 80/tcp
sudo ufw allow 443/tcp

echo "Enabling Firewall"
sudo ufw enable

echo "Showing rules"
sudo ufw status numbered

I recommend you to add the rules to a script in git (Infrastructure as Code), so you can replay it another day, and to any server you have.

By default, ufw sets deny by default for rules not specified. You can check it with:

sudo ufw status verbose | head

Bots, IA, crawlers abuse our servers nowadays.

Also the automated bots attempting to hack into the servers.

If you just check your logs, you’ll see many attempts to exploit vulnerabilities in your servers, every day.

I checked for the second IP consuming more bandwidth in the list:

116.202.235.23

It’s from hetzner, a Cloud provider. https://bgp.he.net/net/74.7.0.0/16

Another 65,536 addresses that get blocked:

# Hetzner
ufw insert 1 deny from 116.202.0.0/16  to any

The third IP will be blocked too, even if I didn’t find any information about who owns the block of IPs.

ufw insert 1 deny from 3.41.188.33 to any

The fourth was part of the same block I blocked first, so it’s already blocked.

The fifth IP, I ask to Google Gemini:

The block is 18.97.9.0/24 but I’ll block all the BGP Prefix, all the 16K addresses.

ufw insert 1 deny from 18.97.0.0/18 to any

See what is the traffic from the servers since I blocked the Microsoft range in the Ubuntu Firewall:

As you can see, after I blocked the first abusive IP allegedly from Microsoft with seemingly OpenAI User-Agent falsified, all the egress traffic went down.

I like how Google reacted when I contacted them. And I should have checked the Network traffic bandwidth first. So I decided to keep the WordPress instance in Google Cloud.

But the new instance “ubuntu26-04” I’m migrating it to the new server. What they offer for the price is much better, and the price of the storage really it’s important for some of my projects. The unlimited bandwidth usage in the new provider it’s also key.

The Billing report from Google Cloud doesn’t show the data up to the moment. But the 16th of August I was able to see that from the 12th and absolutely on 13th and 14th the egress Internet cost have almost disappeared.

Not all is perfect. I had to learn the web interface from that provider (I used it in the past, but companies change the user interface very often nowadays, causing headaches to users), the place were my VPS was listed was hidden and difficult to find, and by default the VPS had no firewall enabled. Something that I believe that is basic to have enabled by default.

But I think it will be great for my projects and for my budget, to have all those new resources available (8 vCPU and 24 GB of RAM and 200 GB of NVMe disk for the same cost provided in Google Cloud of 2 vCPU and 4 GB of RAM and 20 GB of standard disk).

In fact, the speed I have detected transferring from/to sendmeafile with the new provider shows a great improvement.

Take one thing in mind: They AI is hungry for contents, and the mega-rich AI providers are making you pay the bill, by scanning your sites non-stop.

The new provider says it provides 3 Gbps of bandwidth for the instance, with no data usage limits (not having to pay for GB transferred). If I had not reacted fast, the forecast for this month with Google for just a single instance was 300€. It is a risk for developers and small companies, to have the possibility to face a huge bill because more frequent every day bots/crawlers/AIs and processes attempting to breach in your servers, cause your server to use a lot of egress data. So it is worth considering using a Cloud Provider with unlimited data plan. Or at least, activate the Billing Alerts to be notified if your costs exceed a certain threshold.

If you have problems with abusive bots degrading your Drupal or WordPress performance, I created a commercial WordPress plugin and a commercial Drupal module that automatically and easily record and block offending IP Addresses.

Video: How to create a Docker Container for LAMPP step by step

How to create a Docker Container for Linux Apache MySQL PHP and Python for beginners.

Note: Containers are not persistent. Use this for tests only. If you want to keep persistent information use Volumes.

Sources: https://gitlab.com/carles.mateo/blog.carlesmateo.com-source-code/-/tree/master/twitch/live_20220708_dockerfile_lamp

File: Dockerfile

FROM ubuntu:20.04

MAINTAINER Carles Mateo

ARG DEBIAN_FRONTEND=noninteractive

RUN apt update && \
    apt install -y vim python3-pip &&  \
    apt install -y net-tools mc vim htop less strace zip gzip lynx && \
    apt install -y apache2 mysql-server ntpdate libapache2-mod-php7.4 mysql-server php7.4-mysql php-dev libmcrypt-dev php-pear && \
    apt install -y git && apt autoremove && apt clean && \
    pip3 install pytest

RUN a2enmod rewrite

RUN echo "Europe/Ireland" | tee /etc/timezone

ENV APACHE_RUN_USER  www-data
ENV APACHE_RUN_GROUP www-data
ENV APACHE_LOG_DIR   /var/log/apache2
ENV APACHE_PID_FILE  /var/run/apache2/apache2.pid
ENV APACHE_RUN_DIR   /var/run/apache2
ENV APACHE_LOCK_DIR  /var/lock/apache2
ENV APACHE_LOG_DIR   /var/log/apache2

COPY phpinfo.php /var/www/html/

RUN service apache2 restart

EXPOSE 80

CMD ["/usr/sbin/apache2", "-D", "FOREGROUND"]

File: phpinfo.php

<html>
<?php

// Show all information, defaults to INFO_ALL
phpinfo();

// Show just the module information.
// phpinfo(8) yields identical results.
phpinfo(INFO_MODULES);
?>
</html>

File: build_docker.sh

#!/bin/bash

s_DOCKER_IMAGE_NAME="lampp"

echo "We will build the Docker Image and name it: ${s_DOCKER_IMAGE_NAME}"
echo "After, we will be able to run a Docker Container based on it."

printf "Removing old image %s\n" "${s_DOCKER_IMAGE_NAME}"
sudo docker rm "${s_DOCKER_IMAGE_NAME}"

printf "Creating Docker Image %s\n" "${s_DOCKER_IMAGE_NAME}"
# sudo docker build -t ${s_DOCKER_IMAGE_NAME} . --no-cache
sudo docker build -t ${s_DOCKER_IMAGE_NAME} .

i_EXIT_CODE=$?
if [ $i_EXIT_CODE -ne 0 ]; then
    printf "Error. Exit code %s\n" ${i_EXIT_CODE}
    exit
fi

echo "Ready to run ${s_DOCKER_IMAGE_NAME} Docker Container"
echo "To run in type: sudo docker run -p 80:80 --name ${s_DOCKER_IMAGE_NAME} ${s_DOCKER_IMAGE_NAME}"
echo "or just use run_in_docker.sh"

echo
echo "If you want to debug do:"
echo "docker exec -i -t ${s_DOCKER_IMAGE_NAME} /bin/bash"

Provisioning AWS EC2 Instances with Ansible and Automating Apache deployment with or without using Ansible Dynamic Inventory from Ubuntu 20.04 LTS

This article is being included in my book Provisioning to Amazon AWS using boto3 SDK for Python 3.

Pre-requisites

I’ll use Ubuntu 20.04 LTS.

Python 2 is required for Ansible.

Python 3 is required for our programs.

sudo apt install python2 python3 python3-pip
# Install boto for Python 2 for Ansible (alternative way if pip install boto doesn't work for you)
python2 -m pip install boto
# Install Ansible
sudo apt install ansible

If you want to use Dynamic Inventory

So you can use the Python 2 ec2.py and ec2.ini files, adding them as to the /etc/ansible with mask +x, to use the Dynamic Inventory.

You will need to have your credentials set.

I use Environment variables:

#!/bin/bash

export ANSIBLE_HOST_KEY_CHECKING=false
export AWS_ACCESS_KEY=AKIXXXXXXXXXXXXXOS
export AWS_SECRET_KEY=e4dXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXY6F

Then use the calls inside the shell script, or assuming that the previous file was named credentiasl.sh use source credentials.sh

ec2.py is written in Python 2, so probably will fail for you as it is invoked by python and your default interpreter will be Python 3.

So edit the first line of /etc/ansible/ec2.py and add:

#!/bin/env python2

Once credentials.sh is sourced, then you can just invoke ec2.py to get the list of your Instances in a JSON format dumped by ec2.py

/etc/ansible/ec2.py --list

You can get that JSON file and load it and get the information you need, filtering by group.

You can call:

/etc/ansible/ec2.py --list > instances.json

Or you can run a Python program that escapes to shell and executes ec2.py –list and loads the Output as a JSON file.

I use my carleslibs here to escape to shell using my class SubProcessUtils. You can install them, they are Open Source, or you can code manually if you prefer importing subprocess Python library and catching the stdout, stderr.

import json
from carleslibs import SubProcessUtils

if __name__ == "__main__":
    s_command = "/etc/ansible/ec2.py"

    o_subprocess = SubProcessUtils()
    i_error_code, s_output, s_error = o_subprocess.execute_command_for_output(s_command, b_shell=True, b_convert_to_ascii=True, b_convert_to_utf8=False)
    if i_error_code != 0:
        print("Error escaping to shell!", i_error_code)
        print(s_error)
        exit(1)

    json = json.loads(s_output)

    d_hosts = json["_meta"]["hostvars"]

    for s_host in d_hosts:
        # You'll get a ip/hostnamename in s_host which is the key
        # You have to check for groups and the value for the key Name, in order to get the Name of the group
        # As an exercise, print(d_hosts[s_host]) and look for:
        # @TODO: Capture the s_group_name
        # @TODO: Capture the s_addres
        if s_group_name == "yourgroup":
             # This filters only the instances with your group name, as you want to create an inventory file just for them
             # That's because you don't want to launch the playbook for all the instances, but for those in your group name in the inventory file.
             a_hostnames.append(s_address)

    # After this you can parse you list a_hostnames and generate an inventory file yourinventoryfile 
    # The [ec2hosts] in your inventory file must match the hosts section in your yaml files
    # You'll execute your playbook with:
    # ansible-playbook -i yourinventoryfile youryamlfile.yaml

So an example of a yaml to install Apache2 in Ubuntu 20.04 LTS spawned instances , let’s call it install_apache2.yaml would be:

---
- name: Update web servers
  hosts: ec2hosts
  remote_user: ubuntu

  tasks:
  - name: Ensure Apache is at the latest version
    apt:
      name: apache2
      state: latest
      update_cache: yes
    become: yes

As you can see the section hosts: in the YAML playbook matches the [ec2hosts] in your inventory file.

You can choose to have your private key certificate .pem file in /etc/ansible/ansible.cfg or if you want to have different certificates per host, add them after the ip/address in your inventory file, like in this example:

[localhost]
127.0.0.1
[ec2hosts]
63.35.186.109	ansible_ssh_private_key_file=ansible.pem

The ansible.pem certificate must have restricted permissions, for example chmod 600 ansible.pem

Then you end by running:

ansible-playbook -i yourinventoryfile install_ubuntu.yaml

If you don’t want to use Dynamic Directory

The first method is to use add_host to print in the screen the properties form the ec2 Instances provisioned.

The trick is to escape to shell, executing ansible-playbook and capturing the output, then parsing the text looking for the ‘public_ip:’

This is the Python 3 code I created:

class AwesomeAnsible:

    def extract_public_ips_from_text(self, s_text=""):
        """
        Extracts the addresses returned by Ansible
        :param s_text:
        :return: Boolean for success, Array with the Ip's
        """

        b_found = False
        a_ips = []

        i_count = 0
        while True:
            i_count += 1
            if i_count > 20:
                print("Breaking look")
                break
            s_substr = "'public_ip': '"
            i_first_pos = s_text.find(s_substr)
            if i_first_pos > -1:
                s_text_sub = s_text[i_first_pos + len(s_substr):]
                # Find the ending delimiter
                i_second_pos = s_text_sub.find("'")
                if i_second_pos > -1:
                    b_found = True
                    s_ip = s_text_sub[0:i_second_pos]
                    a_ips.append(s_ip)
                    s_text_sub = s_text_sub[i_second_pos:]
                    s_text = s_text_sub
                    continue

            # No more Ip's
            break

        return b_found, a_ips

Then you’ll use with something like:

        # Catching the Ip's from the output
        b_success, a_ips = self.extract_public_ips_from_text(s_output)
        if b_success is True:
            print("Public Ips:")
            s_ips = ""
            for s_ip in a_ips:
                print(s_ip)
                s_ips = s_ips + self.get_ip_text_line_for_inventory(s_ip)
            print("Adding Ips to group1_inventory file")
            self.o_fileutils.append_to_file("group1_inventory", s_ips)
            print()

The get_ip_text_line_for_inventory_method() returns a line for the inventory file, with the ip and the key to use separated by a tab (\t):

    def get_ip_text_line_for_inventory(self, s_ip, s_key_path="ansible.pem"):
        """
        Returns the line to add to the inventory, with the Ip and the keypath
        """
        return s_ip + "\tansible_ssh_private_key_file=" + s_key_path + "\n"

Once you have the inventory file, like this below, you can execute the playbook for your group of hosts:

[localhost]
127.0.0.1
[ec2hosts]
63.35.186.109	ansible_ssh_private_key_file=ansible.pem
ansible-playbook -i yourinventoryfile install_ubuntu.yaml

Alternative way parsing with awk and grep

You can run this Bash Shell Script to get only the public ips when you provision to Amazon AWS EC2 the Instances from your group named group1 in this case:

./launch_aws_instances-group1.sh | grep "public_ip" | awk '{ print $13; }' | tr -d "',"
52.213.232.199

In this example 52.213.232.199 is the Ip from the Instance I provisioned.

So to put it together, from a Python file I generate this Bash file and I escape to shell to execute it:

#!/bin/bash

export ANSIBLE_HOST_KEY_CHECKING=false
export AWS_ACCESS_KEY=AKIXXXXXXXXXXXXXOS
export AWS_SECRET_KEY=e4dXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXY6F

# Generate a new Inventory File
echo "[localhost]" > group1_inventory
echo "127.0.0.1" >> group1_inventory
echo "[ec2hosts]" >> group1_inventory

ansible-playbook -i group1_inventory launch_aws_instances-group1.yaml

I set again the credentials because as this Bash Shell Script is invoked from Python, there are not sourced.

The trick in here is that the launch_aws_instances-group1.yaml file has a task to add the hosts to Ansible’s in memory inventory, and to print the information.

That output is what I scrap from Python and then I use extract_public_ips_from_text() showed before.

So my launch_aws_instances-group1.yaml (which I generate from Python customizing the parameter) looks like this:

# launch_aws_instances.yaml

- hosts: localhost
  connection: local
  gather_facts: False
  vars:
      s_keypair_name: "ansible"
      s_instance_type: "t1.micro"
      s_image: "ami-08edbb0e85d6a0a07"
      s_group: "ansible"
      s_region: "eu-west-1"
      i_exact_count: 1
      s_tag_name: "ansible_group1"
  tasks:

    - name: Provision a set of instances
      ec2:
         key_name: "{{ s_keypair_name }}"
         region: "{{ s_region }}"
         group: "{{ s_group }}"
         instance_type: "{{ s_instance_type }}"
         image: "{{ s_image }}"
         wait: true
         exact_count: "{{ i_exact_count }}"
         count_tag:
            Name: "{{ s_tag_name }}"
         instance_tags:
            Name: "{{ s_tag_name }}"
      register: ec2_ips
      
    - name: Add all instance public IPs to host group
      add_host: hostname={{ item.public_ip }} groups=ec2hosts
      loop: "{{ ec2_ips.instances }}"

In this case I use t1.micro cause I provision to EC2-Classic and not to the default VPC, otherwise I would use t2.micro.

So I have a Security Group named ansible created in Amazon AWS EC2 console as EC2-Classic, and not as VPC.

In this Security group I opened the Inbound HTTP Port and the SSH port for the Ip from I’m provisioning, so Ansible can SSH using the Key ansible.pem

The Public Key has been created and named ansible as well (section key_name under ec2).

The Image used is Ubuntu 20.04 LTS (free tier) for the region eu-west-1 which is my wonderful Ireland.

For the variables (vars) I use the MT Notation, so the prefixes show exactly what we are expecting s_ for Strings i_ for Integers and I never have collisions with reserved names.

It is very important to use the count_tag and instance_tags with the name of the group, as the actions will be using that group name. Remember the idempotency.

The task with the add_host is the one that makes the information for the instances to be displayed, like in this screenshot.

News of the blog 2021-08-16

  • I completed my ZFS on Ubuntu 20.04 LTS book.
    I had an error in an actual hard drive so I added a Troubleshooting section explaining how I fixed it.
  • I paused for a while the advance of my book Python: basic exercises for beginners, as my colleague Michela is translating it to Italian. She is a great Engineer and I cannot be more happy of having her help.
  • I added a new article about how to create a simple web Star Wars game using Flask.
    As always, I use Docker and a Dockerfile to automate the deployment, so you can test it without messing with your local system.
    The code is very simple and easy to understand.
mysql> UPDATE wp_options set option_value='blog.carlesmateo.local' WHERE option_name='siteurl';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

This way I set an entry in /etc/hosts and I can do all the tests I want.

  • I added a new section to the blog, is a link where you can see all the articles published, ordered by number of views.
    /posts_and_views.php

Is in the main page, just after the recommended articles.
Here you can see the source code.

  • I removed the Categories:
    • Storage
      • ZFS
  • In favor of:
    • Hardware
      • Storage
        • ZFS
  • So the articles with Categories in the group deleted were reassigned the Categories in the second group.
  • Visually:
    • I removed some annoying lines from the Quick Selection access.
      They came from inherited CSS properties from my WordPress, long time customized, and I created new styles for this section.
    • I adjusted the line-height to avoid separation between lines being too much.
  • I added a link in the section of Other Engineering Blogs that I like, to the great https://github.com/lesterchan site, author of many super cool WordPress plugins.

Migrating some Services from Amazon to Digital Ocean

Analyzing the needs

I start with a VM, to learn about the providers and the migration project as I go.

My VM has been running in Amazon AWS for years.

It has 3.5GB of RAM and 1 Core. However is uses only 580MB of RAM. I’m paying around $85/month for this with Amazon.

I need to migrate:

  • DNS Server
  • Email
  • Web
  • Database

For the DNS Server I don’t need it anymore, each Domain provider has included DNS Service for free, so I do not longer to have my two DNS.

For the email I find myself in the same scenario, most providers offer 3 email accounts for your domain, and some alias, for free.

I’ll start the Service as Docker in the new CSP, so I will make it work in my computer first, locally, and so I can move easily in the future.

Note: exporting big images is not the idea I have to make backups.

I locate a Digital Ocean droplet with 1GB of RAM and 1 core and SSD disks for $5, for $6 I can have a NVMe version. That I choose.

Disk Space for the Statics

The first thing I do is to analyze the disk space needs of the service.

In this old AWS CentOS based image I have:

[root@ip-10-xxx-yyy-zzz ec2-user]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       79G   11G   69G  14% /
devtmpfs        1.8G   12K  1.8G   1% /dev
tmpfs           1.8G     0  1.8G   0% /dev/shm

Ok, so if I keep the same I have I need 11GB.

I have plenty of space on this server so I do a zip of all the contents of the blog:

cd /var/www/wordpress
zip -r /home/ec2-user/wp_sizeZ.zip wp_siteZ

Database dump

I need a dump of the databases I want to migrate.

I check what databases are in this Server.

mysql -u root -p

mysql> show databases;

I do a dump of the databases that I want:

sudo mysqldump --password='XXXXXXXX' --databases wp_mysiteZ > wp_mysiteZ.sql

I get an error, meaning MySQL needs repair:

mysqldump: Got error: 145: Table './wp_mysiteZ/wp_visitor_maps_wo' is marked as crashed and should be repaired when using LOCK TABLES

So I launch a repair:

sudo mysqlcheck --password='XXXXXXXX' --repair --all-databases

And after the dump works.

My dump takes 88MB, not much, but I compress it with gzip.

gzip wp_mysiteZ.sql

It takes only 15MB compressed.

Do not forget the parameter –databases even if only one database is exported, otherwise the CREATE DATABASE and USE `wp_mysiteZ`; will not be added to your dump.

I will need to take some data form the mysql database, referring to the user used for accessing the blog’s database.

I always keep the CREATE USER and the GRANT permissions, if you don’t check the wp-config.php file. Note that the SQL format to create users and grant permissions may be different from a SQL version to another.

I create a file named mysql.sql with this part and I compress with gzip.

Checking PHP version

php -v
PHP 7.3.23 (cli) (built: Oct 21 2020 20:24:49) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.23, Copyright (c) 1998-2018 Zend Technologies

WordPress is updated, and PHP is not that old.

The new Ubuntu 20.04 LTS comes with PHP 7.4. It will work:

php -v
PHP 7.4.3 (cli) (built: Jul  5 2021 15:13:35) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.3, Copyright (c), by Zend Technologies

The Dockerfile

FROM ubuntu:20.04

MAINTAINER Carles Mateo

ARG DEBIAN_FRONTEND=noninteractive

# RUN echo "nameserver 8.8.8.8" > /etc/resolv.conf

RUN echo "Europe/Ireland" | tee /etc/timezone

# Note: You should install everything in a single line concatenated with
#       && and finalizing with 
# apt autoremove && apt clean

#       In order to use the less space possible, as every command 
#       is a layer

RUN apt update && apt install -y apache2 ntpdate libapache2-mod-php7.4 mysql-server php7.4-mysql php-dev libmcrypt-dev php-pear git mysql-server less zip vim mc && apt autoremove && apt clean

RUN a2enmod rewrite

RUN mkdir -p /www

# If you want to activate Debug
# RUN sed -i "s/display_errors = Off/display_errors = On/" /etc/php/7.2/apache2/php.ini 
# RUN sed -i "s/error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT/error_reporting = E_ALL/" /etc/php/7.2/apache2/php.ini 
# RUN sed -i "s/display_startup_errors = Off/display_startup_errors = On/" /etc/php/7.2/apache2/php.ini 
# To Debug remember to change:
# config/{production.php|preproduction.php|devel.php|docker.php} 
# in order to avoid Error Reporting being set to 0.

ENV PATH_WP_MYSITEZ /var/www/wordpress/wp_mysitez/
ENV PATH_WORDPRESS_SITES /var/www/wordpress/

ENV APACHE_RUN_USER  www-data
ENV APACHE_RUN_GROUP www-data
ENV APACHE_LOG_DIR   /var/log/apache2
ENV APACHE_PID_FILE  /var/run/apache2/apache2.pid
ENV APACHE_RUN_DIR   /var/run/apache2
ENV APACHE_LOCK_DIR  /var/lock/apache2
ENV APACHE_LOG_DIR   /var/log/apache2

RUN mkdir -p $APACHE_RUN_DIR
RUN mkdir -p $APACHE_LOCK_DIR
RUN mkdir -p $APACHE_LOG_DIR
RUN mkdir -p $PATH_WP_MYSITEZ

# Remove the default Server
RUN sed -i '/<Directory \/var\/www\/>/,/<\/Directory>/{/<\/Directory>/ s/.*/# var-www commented/; t; d}' /etc/apache2/apache2.conf 

RUN rm /etc/apache2/sites-enabled/000-default.conf

COPY wp_mysitez.conf /etc/apache2/sites-available/

RUN chown --recursive $APACHE_RUN_USER.$APACHE_RUN_GROUP $PATH_WP_MYSITEZ

RUN ln -s /etc/apache2/sites-available/wp_mysitez.conf /etc/apache2/sites-enabled/

# Please note: It would be better to git clone from another location and
# gunzip and delete temporary files in the same line, 
# to save space in the layer.
COPY *.sql.gz /tmp/

RUN gunzip /tmp/*.sql.gz; echo "Starting MySQL"; service mysql start && mysql -u root < /tmp/wp_mysitez.sql && mysql -u root < /tmp/mysql.sql; rm -f /tmp/*.sql; rm -f /tmp/*.gz
# After this root will have password assigned

COPY *.zip /tmp/

COPY services_up.sh $PATH_WORDPRESS_SITES

RUN echo "Unzipping..."; cd /var/www/wordpress/; unzip /tmp/*.zip; rm /tmp/*.zip

RUN chown --recursive $APACHE_RUN_USER.$APACHE_RUN_GROUP $PATH_WP_MYSITEZ

EXPOSE 80

CMD ["/var/www/wordpress/services_up.sh"]

Services up

For starting MySQL and Apache I relay in services_up.sh script.

#!/bin/bash
echo "Starting MySql"
service mysql start

echo "Starting Apache"
service apache2 start
# /usr/sbin/apache2 -D FOREGROUND

while [ true ];
do
    ps ax | grep mysql | grep -v "grep "
    if [ $? -gt 0 ];
    then
        service mysql start
    fi
    sleep 10
done

You see that instead of launching apache2 as FOREGROUND, what keeps the loop, not exiting from my Container is a while [ true ]; that will keep looping and checking if MySQL is up, and restarting otherwise.

MySQL shutting down

Some of my sites receive DoS attacks. More than trying to shutdown my sites, are spammers trying to publish comment announcing fake glasses, or medicines for impotence, etc… also some try to hack into the Server to gain control of it with dictionary attacks or trying to explode vulnerabilities.

The downside of those attacks is that some times the Database is under pressure, and uses more and more memory until it crashes.

More memory alleviate the problem and buys time, but I decided not to invest more than $6 USD per month on this old site. I’m just keeping the contents alive and even this site still receives many visits. A restart of the MySQL if it dies is enough for me.

As you have seen in my Dockerfile I only have one Docker Container that runs both Apache and MySQL. One of the advantages of doing like that is that if MySQL dies, the container does not exit. However I could have had two containers with both scripts with the while [ true ];

When planning I decided to have just one single Container, all-in-one, as when I export the image for a Backup, I’ll be dealing only with a single image, not two.

Building and Running the Container

I created a Bash script named build_docker.sh that does the build for me, stopping and cleaning previous Containers:

#!/bin/bash

# Execute with sudo

s_DOCKER_IMAGE_NAME="wp_sitez"

printf "Stopping old image %s\n" "${s_DOCKER_IMAGE_NAME}"
sudo docker stop "${s_DOCKER_IMAGE_NAME}"

printf "Removing old image %s\n" "${s_DOCKER_IMAGE_NAME}"
sudo docker rm "${s_DOCKER_IMAGE_NAME}"

printf "Creating Docker Image %s\n" "${s_DOCKER_IMAGE_NAME}"
# sudo docker build -t ${s_DOCKER_IMAGE_NAME} . --no-cache
sudo docker build -t ${s_DOCKER_IMAGE_NAME} .

i_EXIT_CODE=$?
if [ $i_EXIT_CODE -ne 0 ]; then
    printf "Error. Exit code %s\n" ${i_EXIT_CODE}
    exit
fi

echo "Ready to run ${s_DOCKER_IMAGE_NAME} Docker Container"
echo "To run type: sudo docker run -d -p 80:80 --name ${s_DOCKER_IMAGE_NAME} ${s_DOCKER_IMAGE_NAME}"
echo "or just use run_in_docker.sh"
echo
echo "Debug running Docker:"
echo "docker exec -it ${s_DOCKER_IMAGE_NAME} /bin/bash"
echo

I assign to the image and the Running Container the same name.

Running in Production

Once it works in local, I set the Firewall rules and I deploy the Droplet (VM) with Digital Ocean, I upload the files via SFTP, and then I just run my script build_docker.sh

And assuming everything went well, I run it:

sudo docker run -d -p 80:80 --name wp_mysitez wp_mysitez

I check that the page works, and here we go.

Some improvements

This could also have been put in a private Git repository. You only have to care about not storing the passwords in it. (Like the MySQL grants)

It may be interesting for you to disable directory browsing.

The build from the Git repository can be validated with a Jenkins. Here you have an article about setup a Jenkins for yourself.

Post-Mortem: The mystery of the duplicated Transactions into an e-Commerce

Me, with 4 more Senior BackEnd Engineers wrote the new e-Commerce for a multinational.

The old legacy Software evolved into a different code for every country, making it impossible to be maintained.

The new Software we created used inheritance to use the same base code for each country and overloaded only the specific different behavior of every country, like for the payment methods, for example Brazil supporting “parcelados” or Germany with specific payment players.

We rewrote the old procedural PHP BackEnd into modern PHP, with OOP and our own Framework but we had to keep the transactional code in existing MySQL Procedures, so the logic was split. There was a Front End Team consuming our JSONs. Basically all the Front End code was cached in Akamai and pages were rendered accordingly to the JSONs served from out BackEnd.

It was a huge success.

This e-Commerce site had Campaigns that started at a certain time, so the amount of traffic that would come at the same time would be challenging.

The project was working very well, and after some time the original Team was split into different projects in the company and a Team for maintenance and evolutives was hired.

At certain point they started to encounter duplicate transactions, and nobody was able to solve the mystery.

I’m specialized into fixing impossible problems. They used to send me to Impossible Missions, and I am famous for solving impossible problems easily.

So I started the task with a SRE approach.

The System had many components and layers. The problem could be in many places.

I had in my arsenal of tools, Software like mysqldebugger with which I found an unnoticed bug in decimals calculation in the past surprising everybody.

Previous Engineers involved believed the problem was in the Database side. They were having difficulties to identify the issue by the random nature of the repetitions.

Some times the order lines were duplicated, and other times were the payments, which means charging twice to the customer.

Redis Cluster could also play a part on this, as storing the session information and the basket.

But I had to follow the logic sequence of steps.

If transactions from customer were duplicated that mean that in first term those requests have arrived to the System. So that was a good point of start.

With a list of duplicated operations, I checked the Webservers logs.

That was a bit tricky as the Webserver was recording the Ip of the Load Balancer, not the ip of the customer. But we were tracking the sessionid so with that I could track and user request history. A good thing was also that we were using cookies to stick the user to the same Webserver node. That has pros and cons, but in this case I didn’t have to worry about the logs combined of all the Webservers, I could just identify a transaction in one node, and stick into that node’s log.

I was working with SSH and Bash, no log aggregators existing today were available at that time.

So when I started to catch web logs and grep a bit an smile was drawn into my face. :)

There were no transactions repeated by a bad behavior on MySQL Masters, or by BackEnd problems. Actually the HTTP requests were performed twice.

And the explanation to that was much more simple.

Many Windows and Mac User are used to double click in the Desktop to open programs, so when they started to use Internet, they did the same. They double clicked on the Submit button on the forms. Causing two JavaScript requests in parallel.

When I explained it they were really surprised, but then they started to worry about how they could fix that.

Well, there are many ways, like using an UUID in each request and do not accepting two concurrents, but I came with something that we could deploy super fast.

I explained how to change the JavaScript code so the buttons will have no default submit action, and they will trigger a JavaScript method instead, that will set a boolean to True, and also would disable the button so it can not be clicked anymore. Only if the variable was False the submit would be performed. It was almost impossible to get a double click as the JavaScript was so fast disabling the button, that the second click will not trigger anything. But even if that could be possible, only one request would be made, as the variable was set to True on the first click event.

That case was very funny for me, because it was not necessary to go crazy inspecting the different layers of the system. The problem was detected simply with HTTP logs. :)

People often forget to follow the logic steps while many problems are much more simple.

As a curious note, I still see people double clicking on links and buttons on the Web, and some Software not handling it. :)

How to block scanners that look for vulnerabilities to your Ubuntu Apache site

There are many robots scanning sites for vulnerabilities, to gain control or exploit the servers. Most of them come from China and Russia ip’s.

Here I explain an easy way to block them using the Ubuntu Firewall ufw.

If you use a CMS like WordPress and you know there are extensions that have had security exploits, for example, wp-file-manager then you can search directly for this request in Apache Access Logs.

For example:

cat /var/log/apache2/blog_carlesmateo_com-access.log | grep "wp-file-manager" | awk '{ print $1; }' | sort -u >> 2020-10-03-offending-ips.txt

cat /var/log/apache2/blog_carlesmateo_com-access.log.1 | grep "wp-file-manager" | awk '{ print $1; }' | sort -u >> 2020-10-03-offending-ips.txt

zcat /var/log/apache2/blog_carlesmateo_com-access.log.2.gz | grep "wp-file-manager" | awk '{ print $1; }' | sort -u >> 2020-10-03-offending-ips.txt

In the example we look for the access.log file, for the rotated access.log.1 and for the rotated and compressed access.log.2.gz. We use the tool zcat which does a cat over a compressed file.

If we don’t expect to have anybody posting to our xmlrpc Service, we can check for the offending Ip’s by doing:

cat /var/log/apache2/blog_carlesmateo_com-access.log | grep "POST /xmlrpc.php" | wc --lines
2490

In my case I have 2490 request just in the last log.

cat /var/log/apache2/blog_carlesmateo_com-access.log | grep "POST /xmlrpc.php" |awk '{ print $1; }' | sort -u | wc --lines

Interested in how many Ip’s are launching those requests, you can see how many different Ip’s are those:

cat /var/log/apache2/blog_carlesmateo_com-access.log | grep "POST /xmlrpc.php" |awk '{ print $1; }' | sort -u | wc --lines
145

And to add those Ip’s to the offending Ip’s list:

cat /var/log/apache2/blog_carlesmateo_com-access.log | grep "POST /xmlrpc.php" | awk '{ print $1; }' | sort -u >> 2020-10-03-offending-ips.txt

I can also check for repeated requests in the logs:

cat /var/log/apache2/blog_carlesmateo_com-access.log | awk '{ print $7; }' | sort | uniq -c | sort -r | less

That shows me some requests legit and others that are not:

   2532 /xmlrpc.php
    209 /wp-login.php
    205 /wp-admin/admin-ajax.php
     84 /
     83 *
     48 /robots.txt
     21 /favicon.ico
     16 /wp-login.php?redirect_to=https%3A%2F%2Fblog.carlesmateo.com%2Fwp-admin%2F&reauth=1
     15 /wp-includes/js/jquery/jquery.js?ver=1.12.4-wp
     14 /wp-includes/css/dist/block-library/theme.min.css?ver=5.5.1
     14 /wp-includes/css/dist/block-library/style.min.css?ver=5.5.1
     14 /wp-content/themes/2012-carles/style.css?ver=5.5.1
     14 /wp-content/plugins/contact-form-7/includes/js/scripts.js?ver=5.2.2
     14 /wp-content/plugins/captcha/css/front_end_style.css?ver=4.4.5
     13 /wp-includes/css/dashicons.min.css?ver=5.5.1
     13 /wp-content/themes/2012-carles/css/blocks.css?ver=20181230
     13 /wp-content/plugins/contact-form-7/includes/css/styles.css?ver=5.2.2
     12 /wp-includes/js/wp-embed.min.js?ver=5.5.1
     12 /wp-includes/images/w-logo-blue-white-bg.png
     12 /wp-content/themes/2012-carles/js/navigation.js?ver=20140711
     11 /wp-includes/js/wp-emoji-release.min.js?ver=5.5.1
     11 /wp-content/plugins/captcha/css/desktop_style.css?ver=4.4.5
     11 /feed/
     11 /contact/
     10 /wp-comments-post.php
     10 /?author=1
      9 /2016/06/30/creating-a-content-filter-for-postfix-in-php/
      9 /2014/10/13/performance-of-several-languages/
      8 /wp-includes/js/comment-reply.min.js?ver=5.5.1
      8 /wp-content/plugins/captcha/js/front_end_script.js?ver=5.5.1
      8 /e/admin/index.php
      8 /e/admin/
      7 /wp-login.php?action=register
      7 /current-projects/
      7 //xmlrpc.php
      6 /.env
      5 /2019/08/12/a-sample-forensic-post-mortem-for-a-iscsi-initiator-client-that-had-connectivity-problems-to-the-server/
      5 /2017/03/26/csort-multithread-versus-quicksort-java/
      4 /wp-json/wp/v2/types/wp_block?_locale=user
      4 /wp-json/wp/v2/blocks?per_page=100&_locale=user
      4 /wp-admin/
      4 /diguo/index.php
      4 /diguo/
      4 /category/web-development/
      4 /category/news-for-the-blog/
      3 /vendor/phpunit/phpunit/src/Util/PHP/eval-stdin.php
      3 /mt-notation-for-python/
      3 /ebk/index.php
      3 /ebk/
      3 /comments/feed/
      3 /bf/index.php
      3 /bf/
      3 /beifen/index.php
      3 /beifen/
      3 /Ebak/index.php
      3 /Ebak/
      3 /Bak/index.php
      3 /Bak/
      3 /2020/09/21/how-to-recover-access-to-your-amazon-aws-ec2-instance-if-you-loss-your-private-key-for-ssh/
      3 /2020/08/23/adding-a-ramdisk-as-slog-zil-to-zfs/
      3 /2019/07/03/adding-my-server-as-docker-with-php-catalonia-framework-explained/
      3 /2019/06/25/some-handy-tricks-for-working-with-zfs/
      3 /2015/02/01/stopping-definitively-the-massive-distributed-dos-attack/
      2 /ycadmin/login.php?gotopage=%2Fycadmin%2Findex.php
      2 /ueditor/net/controller.ashx
      2 /sql_beifen/index.php
      2 /sql_beifen/
      2 /sql/index.php
      2 /sql/
      2 /dgbf/index.php
      2 /dgbf/
      2 //xmlrpc.php?rsd
      2 //.env
      1 /wp-login.php?registration=disabled
      1 /wp-login.php?action=lostpassword
      1 /wp-json/wp/v2/users/me?_locale=user
      1 /wp-json/wp/v2/users/?who=authors&per_page=100&_locale=user
      1 /wp-json/wp/v2/taxonomies/post_tag?context=edit&_locale=user
      1 /wp-json/wp/v2/taxonomies/category?context=edit&_locale=user
      1 /wp-json/wp/v2/tags?per_page=100&orderby=count&order=desc&_fields=id%2Cname&search=ufw&_locale=user

You can identify manually what are attacks, and what are legit requests.

After you have your definitive list of offending Ip’s (and make sure you didn’t introduce yours accidentally), then you can execute the second part of the script:

echo '#!/bin/bash' > add_ufw_rules.sh

i_COUNTER_RULE=0; for s_OFFENDING_IP in $(cat 2020-10-03-offending-ips.txt); do i_COUNTER_RULE=$((i_COUNTER_RULE+1)); echo "ufw insert $i_COUNTER_RULE deny from $s_OFFENDING_IP to any" >> add_ufw_rules.sh; done

echo "ufw status numbered" >> add_ufw_rules.sh
echo "sudo ufw allow OpenSSH" >> add_ufw_rules.sh
echo "sudo ufw allow 22/tcp" >> add_ufw_rules.sh
echo 'sudo ufw allow "Apache Full"' >> add_ufw_rules.sh
echo "sudo ufw enable" >> add_ufw_rules.sh

Then you less your file add_ufw_rules.sh to see everything is Ok:

#!/bin/bash
ufw insert 1 deny from 40.79.250.88 to any
ufw insert 2 deny from 52.173.148.212 to any
ufw insert 3 deny from 94.103.85.175 to any
ufw insert 4 deny from 40.79.250.88 to any
ufw insert 5 deny from 78.85.208.240 to any
ufw insert 6 deny from 80.82.68.173 to any
ufw insert 7 deny from 188.165.230.118 to any
ufw insert 8 deny from 195.201.117.103 to any
ufw insert 9 deny from 40.79.250.88 to any
ufw insert 10 deny from 5.135.138.188 to any
ufw insert 11 deny from 51.116.189.135 to any
...
ufw insert 223 deny from 95.173.161.167 to any
ufw insert 224 deny from 95.84.228.227 to any
ufw status numbered
sudo ufw allow OpenSSH
sudo ufw allow 22/tcp
sudo ufw allow "Apache Full"
sudo ufw enable

Then you simply give permissions with chmod +x add_ufw_rules.sh and run the script to apply.

It’s up to you to turn on the Firewall logging:

sudo ufw logging on

Adding my Server as Docker, with PHP Catalonia Framework, explained

Update: 2021-07-23 Ubuntu 19.04 is no longer available, so I updated the article in order to work with Ubuntu 20.04. and with PHP 7.4 and all their dependencies.

The previous day I explained how I migrated my old Server (Amazon Instance) to a more powerful model, with more recent OS, WebServer, etc…

This was interesting under the point of view of dealing with elastic Ip’s, Amazon AWS Volumes, etc… but was a process basically manual. I could have generated an immutable image to start from next time, but this is another discussion, specially because that Server Instance has different base Software, including a MySql Database.

This time I want to explain, step by step, how to containerize my Server, so I can port to different platforms, and I can be independent on what the Server Operating System is. It will work always, as we defined the Operating System for the Docker Container.

So we start to use IaC (Infrastructure as Code).

So first you need to install docker.

So basically if your laptop is an Ubuntu 18.04 LTS or 20.04 LTS you have to:

sudo apt install docker.io

Start and Automate Docker

The Docker service needs to be setup to run at startup. To do so, type in each command followed by enter:

sudo systemctl start docker
sudo systemctl enable docker

Create the Dockerfile

For doing this you can use any text editor, but as we are working with IaC why not use a Code Editor?.

You can use the versatile PyCharm, that has modules for understanding Docker and so you can use Control Version like git too.

This is the updated Dockerfile to work with Ubuntu 20.04 LTS

FROM ubuntu:20.04

MAINTAINER Carles <carles@carlesmateo.com>

ARG DEBIAN_FRONTEND=noninteractive

#RUN echo "nameserver 8.8.8.8" > /etc/resolv.conf

RUN echo "Europe/Ireland" | tee /etc/timezone

# Note: You should install everything in a single line concatenated with
#       && and finalizing with 
# apt autoremove && apt clean

#       In order to use the less space possible, as every command is a layer
RUN apt update && apt install -y apache2 ntpdate libapache2-mod-php7.4 mysql-server php7.4-mysql php-dev libmcrypt-dev php-pear git && apt autoremove && apt clean

RUN a2enmod rewrite

RUN mkdir -p /www

# In order to activate Debug
# RUN sed -i "s/display_errors = Off/display_errors = On/" /etc/php/7.2/apache2/php.ini 
# RUN sed -i "s/error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT/error_reporting = E_ALL/" /etc/php/7.2/apache2/php.ini 
# RUN sed -i "s/display_startup_errors = Off/display_startup_errors = On/" /etc/php/7.2/apache2/php.ini 
# To Debug remember to change:
# config/{production.php|preproduction.php|devel.php|docker.php} 
# in order to avoid Error Reporting being set to 0.

ENV PATH_CATALONIA /www/www.cataloniaframework.com/
ENV PATH_CATALONIA_WWW /www/www.cataloniaframework.com/www/
ENV PATH_CATALONIA_CACHE /www/www.cataloniaframework.com/cache/

ENV APACHE_RUN_USER  www-data
ENV APACHE_RUN_GROUP www-data
ENV APACHE_LOG_DIR   /var/log/apache2
ENV APACHE_PID_FILE  /var/run/apache2/apache2.pid
ENV APACHE_RUN_DIR   /var/run/apache2
ENV APACHE_LOCK_DIR  /var/lock/apache2
ENV APACHE_LOG_DIR   /var/log/apache2

RUN mkdir -p $APACHE_RUN_DIR
RUN mkdir -p $APACHE_LOCK_DIR
RUN mkdir -p $APACHE_LOG_DIR
RUN mkdir -p $PATH_CATALONIA
RUN mkdir -p $PATH_CATALONIA_WWW
RUN mkdir -p $PATH_CATALONIA_CACHE

# Remove the default Server
RUN sed -i '/<Directory \/var\/www\/>/,/<\/Directory>/{/<\/Directory>/ s/.*/# var-www commented/; t; d}' /etc/apache2/apache2.conf 

RUN rm /etc/apache2/sites-enabled/000-default.conf

COPY www.cataloniaframework.com.conf /etc/apache2/sites-available/

RUN chmod 777 $PATH_CATALONIA_CACHE
RUN chmod 777 $PATH_CATALONIA_CACHE.
RUN chown --recursive $APACHE_RUN_USER.$APACHE_RUN_GROUP $PATH_CATALONIA_CACHE

RUN ln -s /etc/apache2/sites-available/www.cataloniaframework.com.conf /etc/apache2/sites-enabled/

# Note: You should clone locally and COPY to the Docker Image
#       Also you should add the .git directory to your .dockerignore file
#       I made this way to show you and for simplicity, having everything
#       in a single file
##RUN git clone https://github.com/cataloniaframework/cataloniaframework_v1_sample_website /www/www.cataloniaframework.com
##RUN git checkout tags/v.1.16-web-1.0
# In order to change profile to Production
# RUN sed -i "s/define('ENVIRONMENT', DOCKER)/define('ENVIRONMENT', PRODUCTION)/" /var/www/www.cataloniaframework.com/config/general.php 
COPY *.php /www/www.cataloniaframework.com/www

# for debugging
#RUN apt-get install -y vim

RUN service apache2 restart

EXPOSE 80

CMD ["/usr/sbin/apache2", "-D", "FOREGROUND"]

The www.cataloniaframework.com.conf file

As you saw in the Dockerfile you have the line:

COPY www.cataloniaframework.com.conf /etc/apache2/sites-available/

This will copy the file www.cataloniaframework.com.conf that must be in the same directory that the Dockerfile file, to the /etc/apache2/sites-available/ folder in the container.

<VirtualHost *:80>
    ServerAdmin webmaster@cataloniaframework.com
    # Uncomment to use a DNS name in a multiple VirtualHost Environment
    #ServerName www.cataloniaframework.com
    #ServerAlias cataloniaframework.com
    DocumentRoot /www/www.cataloniaframework.com/www
    <Directory /www/www.cataloniaframework.com/www/>
            Options -Indexes +FollowSymLinks +MultiViews
            AllowOverride All
            Order allow,deny
            allow from all
            Require all granted
    </Directory>
    ErrorLog ${APACHE_LOG_DIR}/www-cataloniaframework-com-error.log
    # Possible values include: debug, info, notice, warn, error, crit,
    # alert, emerg.
    LogLevel warn
    CustomLog ${APACHE_LOG_DIR}/www-cataloniaframework-com-access.log combined
</VirtualHost>

Stopping, starting the docker Service and creating the Catalonia image

service docker stop && service docker start

To build the Docker Image we will do:

docker build -t catalonia . --no-cache

I use the –no-cache so git is pulled and everything is reworked, not kept from cache.

Now we can run the Catalonia Docker, mapping the 80 port.

docker run -d -p 80:80 catalonia

If you want to check what’s going on inside the Docker, you’ll do:

docker ps

And so in this case, we will do:

docker exec -i -t distracted_wing /bin/bash

Finally I would like to check that the web page works, and I’ll use my preferred browser. In this case I will use lynx, the text browser, cause I don’t want Firefox to save things in the cache.

Upgrading the Blog after 5 years, AWS Amazon Web Services, under DoS and Spam attacks

Few days ago I was under a heavy DoS attack.

Nothing new, zombie computers, hackers, pirates, networks of computers… trying to abuse the system and to hack into it. Why? There could be many reasons, from storing pirate movies, trying to use your Server for sending Spam, try to phishing or to host Ransomware pages…

Most of those guys doesn’t know that is almost impossible to Spam from Amazon. Few emails per hour can come out from the Server unless you explicitly requests that update and configure everything.

But I thought it was a great opportunity to force myself to update the Operating System, core tools, versions of PHP and MySql.

Forensics / Postmortem of the incident

The task was divided in two parts:

  • Understanding the origin of the attack
  • Blocking the offending Ip addresses or disabling XMLRPC
  • Making the VM boot again (problems with Amazon AWS)
    • I didn’t know why it was not booting so.
  • Upgrading the OS

I disabled the access to the site while I was working using Amazon Web Services Firewall. Basically I turned access to my ip only. Example: 8.8.8.8/32

I changed 0.0.0.0/0 so the world wide mask to my_Ip/3

That way the logs were reflecting only what I was doing from my Ip.

Dealing with Snapshots and Volumes in AWS

Well the first thing was doing an Snapshot.

After, I tried to boot the original Blog Server (so I don’t stop offering service) but no way, the Server appeared to be dead.

So then I attached the Volume to a new Server with the same base OS, in order to extract (dump) the database. Later I would attach the same Volume to a new Server with the most recent OS and base Software.

Something that is a bit annoying is that the new Instances, the new generation instances, run only in VPC, not in Amazon EC2 Classic. But my static Ip addresses are created for Amazon EC2 Classic, so I could not use them in new generation instances.

I choose the option to see all the All the generations.

Upgrading the system base Software had its own challenges too.

Upgrading the OS / Base Software

My approach was to install an Ubuntu 18.04 LTS, and install the base Software clean, and add any modification I may need.

I wanted to have all the supported packages and a recent version of PHP 7 and the latest Software pieces link Apache or MySQL.

sudo apt update

sudo apt install apache2

sudo apt install mysql-server

sudo apt install php libapache2-mod-php php-mysql

Apache2

Config files that before were working stopped working as the new Apache version requires the files or symlinks under /etc/apache2/sites-enabled/ to end with .conf extension.

Also some directives changed, so some websites will not able to work properly.

Those projects using my Catalonia Framework were affected, although I have this very well documented to make it easy to work with both versions of Apache Http Server, so it was a very straightforward change.

From the previous version I had to change my www.cataloniaframework.com.conf file and enable:

    <Directory /www/www.cataloniaframework.com>
             Options Indexes FollowSymLinks MultiViews
             AllowOverride All
             Order allow,deny
             allow from all
     </Directory>

Then Open the ports for the Web Server (443 and 80).

sudo ufw allow in "Apache Full"

Then service apache restart

Catalonia Framework Web Site, which is also created with Catalonia Framework itself once restored

MySQL

The problem was to use the most updated version of the Database. I could use one of the backups I keep, from last week, but I wanted more fresh data.

I had the .db files and it should had been very straightforward to copy to /var/lib/mysql/ … if they were the same version. But they weren’t. So I launched an instance with the same base Software as the old previous machine had, installed mysql-server, stopped it, copied the .db files, started it, and then I made a dump with mysqldump –all-databases > 2019-04-29-all-databases.sql

Note, I copied the .db files using the mythical mc, which is a clone from Norton Commander.

Then I stopped that instance and I detached that volume and attached it to the new Blog Instance.

I did a Backup of my original /var/lib/mysql/ files for the purpose of faster restoring if something went wrong.

I mounted it under /mnt/blog_old and did mysql -u root -p < /mnt/blog_old/home/ubuntu/2019-04-29-all-databases.sql

That worked well I had restored the blog. But as I was watching the /var/log/mysql/error.log I noticed some columns were not where they should be. That’s because inadvertently I overwritten the MySql table as well, which in MySQL 5.7 has different structure than in MySQL 5.5. So I screwed. As I previewed this possibility I restored from the backup in seconds.

So basically then I edited my .sql files and removed all that was for the mysql database.

I started MySql, and run the mysql import procedure again. It worked, but I had to recreate the users for all the Databases and Grant them permissions.

GRANT ALL PRIVILEGES ON db_mysqlproxycache.* TO 'wp_dbuser_mysqlproxy'@'localhost' IDENTIFIED BY 'XWy$&{yS@qlC|<¡!?;:-ç';

PHP7

Some modules in my blogs where returning errors in /var/log/apache2/mysite-error.log so I checked that it was due to lack of support of latest PHP versions, and so I patched manually the code or I just disabled the offending plugin.

WordPress

As seen checking the /var/log/apache2/blog.carlesmateo.com-error.log some URLs where not located by WordPress.

For example:

The requested URL /wordpress/wp-json/ was not found on this server

I had to activate modrewrite and then restart Apache.

a2enmod rewrite; service apache2 restart

Making the site more secure

Checking at the logs of Apache, /var/log/apache2/blog.carlesmateo.com-access.log I checked for Ip’s accessing Admin areas, I looked for 404 Errors pointing to intents to exploit any unsafe WP Plugin, I checked for POST protocol as well.

I added to the Ubuntu Uncomplicated Firewall (UFW) the offending Ip’s and patched the xmlrpc.php file to exit always.

Stopping and investigating a WordPress xmlrpc.php attack

One of my Servers got heavily attacked for several days. I describe here the steps I took to stop this.

The attack consisted in several connections per second to the Server, to path /xmlrpc.php.

This is a WordPress file to control the pingback, when someone links to you.

My Server it is a small Amazon instance, a m1.small with only one core and 1,6 GB RAM, magnetic disks and that scores a discrete 203 CMIPS (my slow laptop scores 460 CMIPS).

Those massive connections caused the server to use more and more RAM, and while the xmlrpc requests were taking many seconds to reply, so more and more processes of Apache were spawned. That lead to more memory consumption, and to use all the available RAM and start using swap, with a heavy performance impact until all the memory was exhausted and the mysql processes stopped.

I saw that I was suffering an attack after the shutdown of MySql. I checked the CloudWatch Statistics from Amazon AWS and it was clear that I was receiving many -out of normal- requests. The I/O was really high too.

This statistics are from today to three days ago, look at the spikes when the attack was hitting hard and how relaxed the Server is now (plain line).

blog-carlesmateo-com-statistics-use-last-3-days

First I decided to simply rename the xmlrpc.php file as a quick solution to stop the attack but the number of http connections kept growing and then I saw very suspicious queries to the database.

blog-carlesmateo-suspicious-queries-2014-08-30-00-11-59Those queries, in addition to what I’ve seen in the Apache’s error log suggested me that may be the Server was hacked by a WordPress/plugin bug and that now they were trying to hide from the database’s logs. (Specially the DELETE FROM wp_useronline WHERE user_ip = the Ip of the attacker)

[Tue Aug 26 11:47:08 2014] [error] [client 94.102.49.179] Error in WordPress Database Lost connection to MySQL server during query a la consulta SELECT option_value FROM wp_options WHERE option_name = 'uninstall_plugins' LIMIT 1 feta per include('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include_once('/plugins/captcha/captcha.php'), register_uninstall_hook, get_option
[Tue Aug 26 11:47:09 2014] [error] [client 94.102.49.179] Error in WordPress Database Lost connection to MySQL server during query a la consulta SELECT option_value FROM wp_options WHERE option_name = 'uninstall_plugins' LIMIT 1 feta per include('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include_once('/plugins/captcha/captcha.php'), register_uninstall_hook, get_option
[Tue Aug 26 11:47:10 2014] [error] [client 94.102.49.179] Error in WordPress Database Lost connection to MySQL server during query a la consulta SELECT option_value FROM wp_options WHERE option_name = 'widget_wppp' LIMIT 1 feta per include('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('plugins_loaded'), call_user_func_array, wppp_check_upgrade, get_option

The error log was very ugly.

The access log was not reassuring, as it shown many attacks like that:

94.102.49.179 - - [26/Aug/2014:10:34:58 +0000] "POST /xmlrpc.php HTTP/1.0" 200 598 "-" "Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)"
94.102.49.179 - - [26/Aug/2014:10:34:59 +0000] "POST /xmlrpc.php HTTP/1.0" 200 598 "-" "Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)"
127.0.0.1 - - [26/Aug/2014:10:35:09 +0000] "OPTIONS * HTTP/1.0" 200 126 "-" "Apache/2.2.22 (Ubuntu) (internal dummy connection)"
94.102.49.179 - - [26/Aug/2014:10:34:59 +0000] "POST /xmlrpc.php HTTP/1.0" 200 598 "-" "Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)"
94.102.49.179 - - [26/Aug/2014:10:34:59 +0000] "POST /xmlrpc.php HTTP/1.0" 200 598 "-" "Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)"
94.102.49.179 - - [26/Aug/2014:10:35:00 +0000] "POST /xmlrpc.php HTTP/1.0" 200 598 "-" "Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)"
94.102.49.179 - - [26/Aug/2014:10:34:59 +0000] "POST /xmlrpc.php HTTP/1.0" 200 598 "-" "Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)"

Was difficult to determine if the Server was receiving SQL injections so I wanted to be sure.

Note: The connection from 127.0.0.1 with OPTIONS is created by Apache when spawns another Apache.

As I had super fresh backups in another Server I was not afraid of the attack dropping the database.

I was a bit suspicious also because the /readme.html file mentioned that the version of WordPress is 3.6. In other installations it tells correctly that the version is the 3.9.2 and this file is updated with the auto-update. I was thinking about a possible very sophisticated trojan attack able to modify wp-includes/version.php and set fake $wp_version = ‘3.9.2’;
Later I realized that this blog had WordPress in Catalan, my native language, and discovered that the guys that do the translations forgot to update this file (in new installations it comes not updated, and so showing 3.6). I have alerted them.

In fact later I did a diff of all the files of my WordPress installation against the official WordPress 3.9.2-ca and later a did a diff between the WordPress 3.9.2-ca and the WordPress 3.9.2 (English – default), and found no differences. My Server was Ok. But at this point, at the beginning of the investigation I didn’t know that yet.

With the info I had (queries, times, attack, readme telling v. 3.6…) I balanced the possibility to be in front of something and I decided that I had an unique opportunity to discover how they do to inject those Sql, or discover if my Server was compromised and how. The bad point is that it was the same Amazon’s Server where this blog resides, and I wanted the attack to continue so I could get more information, so during two days I was recording logs and doing some investigations, so sorry if you visited my blog and database was down, or the Server was going extremely slow. I needed that info. It was worth it.

First I changed the Apache config so the massive connections impacted a bit less the Server and so I could work on it while the attack was going on.

I informed my group of Senior friends on what’s going on and two SysAdmins gave me some good suggestions on other logs to watch and on how to stop the attack, and later a Developer joined me to look at the logs and pointed possible solutions to stop the attack. But basically all of them suggested on how to block the incoming connections with iptables and to do things like reinstalling WordPress, disabling xmlrpc.php in .htaccess, changing passwords or moving wp-admin/ to another place, but the point is that I wanted to understand exactly what was going on and how.

I checked the logs, certificates, etc… and no one other than me was accessing the Server. I also double-checked the Amazon’s Firewall to be sure that no unnecessary ports were left open. Everything was Ok.

I took a look at the Apache logs for the site and all the attacks were coming from the same Ip:

94.102.49.179

It is an Ip from a dedicated Servers company called ecatel.net. I reported them the abuse to the abuse address indicated in the ripe.net database for the range.

I found that many people have complains about this provider and reports of them ignoring the requests to stop the spam use from their servers, so I decided that after my tests I will block their entire network from being able to access my sites.

All the requests shown in the access.log pointed to requests to /xmlrpc.php. It was the only path requested by the attacker so that Ip did nothing more apparently.

I added some logging to WordPress xmlrpc.php file:

if ($_SERVER['REMOTE_ADDR'] == '94.102.49.179') {
    error_log('XML POST: '.serialize($_POST));
    error_log('XML GET: '.serialize($_GET));
    error_log('XML REQUEST: '.serialize($_REQUEST));
    error_log('XML SERVER: '.serialize($_SERVER));
    error_log('XML FILES: '.serialize($_FILES));
    error_log('XML ENV: '.serialize($_ENV));
    error_log('XML RAW: '.$HTTP_RAW_POST_DATA);
    error_log('XML ALL_HEADERS: '.serialize(getallheaders()));
}

This was the result, it is always the same:

[Fri Aug 29 19:02:54 2014] [error] [client 94.102.49.179] XML POST: a:0:{}
[Fri Aug 29 19:02:54 2014] [error] [client 94.102.49.179] XML GET: a:0:{}
[Fri Aug 29 19:02:54 2014] [error] [client 94.102.49.179] XML REQUEST: a:0:{}
[Fri Aug 29 19:02:54 2014] [error] [client 94.102.49.179] XML SERVER: a:24:{s:9:"HTTP_HOST";s:24:"barcelona.afterstart.com";s:12:"CONTENT_TYPE";s:8:"text/xml";s:14:"CONTENT_LENGTH";s:3:"287";s:15:"HTTP_USER_AGENT";s:50:"Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)";s:15:"HTTP_CONNECTION";s:5:"close";s:4:"PATH";s:28:"/usr/local/bin:/usr/bin:/bin";s:16:"SERVER_SIGNATURE";s:85:"<address>Apache/2.2.22 (Ubuntu) Server at barcelona.afterstart.com Port 80</address>\n";s:15:"SERVER_SOFTWARE";s:22:"Apache/2.2.22 (Ubuntu)";s:11:"SERVER_NAME";s:24:"barcelona.afterstart.com";s:11:"SERVER_ADDR";s:14:"[this-is-removed]";s:11:"SERVER_PORT";s:2:"80";s:11:"REMOTE_ADDR";s:13:"94.102.49.179";s:13:"DOCUMENT_ROOT";s:29:"/var/www/barcelona.afterstart.com";s:12:"SERVER_ADMIN";s:19:"webmaster@localhost";s:15:"SCRIPT_FILENAME";s:40:"/var/www/barcelona.afterstart.com/xmlrpc.php";s:11:"REMOTE_PORT";s:5:"40225";s:17:"GATEWAY_INTERFACE";s:7:"CGI/1.1";s:15:"SERVER_PROTOCOL";s:8:"HTTP/1.0";s:14:"REQUEST_METHOD";s:4:"POST";s:12:"QUERY_STRING";s:0:"";s:11:"REQUEST_URI";s:11:"/xmlrpc.php";s:11:"SCRIPT_NAME";s:11:"/xmlrpc.php";s:8:"PHP_SELF";s:11:"/xmlrpc.php";s:12:"REQUEST_TIME";i:1409338974;}
[Fri Aug 29 19:02:54 2014] [error] [client 94.102.49.179] XML FILES: a:0:{}
[Fri Aug 29 19:02:54 2014] [error] [client 94.102.49.179] XML ENV: a:0:{}
[Fri Aug 29 19:02:54 2014] [error] [client 94.102.49.179] XML RAW: <?xmlversion="1.0"?><methodCall><methodName>pingback.ping</methodName><params><param><value><string>http://seretil.me/</string></value></param><param><value><string>http://barcelona.afterstart.com/2013/09/27/afterstart-barcelona-2013-09-26/</string></value></param></params></methodCall>
[Fri Aug 29 19:02:54 2014] [error] [client 94.102.49.179] XML ALL_HEADERS: a:5:{s:4:"Host";s:24:"barcelona.afterstart.com";s:12:"Content-type";s:8:"text/xml";s:14:"Content-length";s:3:"287";s:10:"User-agent";s:50:"Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)";s:10:"Connection";s:5:"close";}

So nothing in $_POST, nothing in $_GET, nothing in $_REQUEST, nothing in $_SERVER, no files submitted, but a text/xml Posted (that was logged by storing: $HTTP_RAW_POST_DATA):

<?xmlversion="1.0"?><methodCall><methodName>pingback.ping</methodName><params><param><value><string>http://seretil.me/</string></value></param><param><value><string>http://barcelona.afterstart.com/2013/09/27/afterstart-barcelona-2013-09-26/</string></value></param></params></methodCall>

I show you in a nicer formatted aspect:blog-carlesmateo-com-xml-xmlrpc-requestSo basically they were trying to register a link to seretil dot me.

I tried and this page, hosted in CloudFare, is not working.

accessing-seretil-withoud-id

The problem is that responding to this spam xmlrpc request took around 16 seconds to the Server. And I was receiving several each second.

I granted access to my Ip only on the port 80 in the Firewall, restarted Apache, restarted MySql and submitted the same malicious request to the Server, and it even took 16 seconds in all my tests:

cat http_post.txt | nc barcelona.afterstart.com 80

blog-carlesmateo-com-response-from-the-server-to-xmlrpc-attackI checked and confirmed that the logs from the attacker were showing the same Content-Length and http code.

Other guys tried xml request as well but did one time or two and leaved.

The problem was that this robot was, and still sending many requests per second for days.

May be the idea was to knock down my Server, but I doubted it as the address selected is the blog of one Social Event for Senior Internet Talents that I organize: afterstart.com. It has not special interest, I do not see a political, hateful or other motivation to attack the blog from this project.

Ok, at this point it was clear that the Ip address was a robot, probably running from an infected or hacked Server, and was trying to publish a Spam link to a site (that was down). I had to clarify those strange queries in the logs.

I reviewed the WPUsersOnline plugin and I saw that the strange queries (and inefficient) that I saw belonged to WPUsersOnline plugin.

blog-carlesmateo-com-grep-r-delete-from-wp-useronline-2014-08-30-21-11-21-cut

The thing was that when I renamed the xmlrpc.php the spamrobot was still posting to that file. According to WordPress .htaccess file any file that is not found on the filesystem is redirected to index.php.

So what was happening is that all the massive requests sent to xmlrpc.php were being attended by index.php, then showing an error message that page not found, but the WPUsersOnline plugin was deleting those connections. And was doing it many times, overloading also the Database.

Also I was able to reproduce the behaviour by myself, isolating by firewalling the WebServer from other Ips other than mine and doing the same post by myself many times per second.

I checked against a friend’s blog but in his Server xmlrpc.php responds in 1,5 seconds. My friend’s Server is a Digital Ocean Virtual Server with 2 cores and SSD Disks. My magnetic disks on Amazon only bring around 40 MB/second. I’ve to check in detail why my friend’s Server responds so much faster.

Checked the integrity of my databases, just in case, and were perfect. Nothing estrange with collations and the only errors in the /var/log/mysql/error.log was due to MySql crashing when the Server ran out of memory.

Rechecked in my Server, now it takes 12 seconds.

I disabled 80% of the plugins but the times were the same. The Statistics show how the things changed -see the spikes before I definitively patched the Server to block request from that Spam-robot ip, to the left-.

I checked against another WordPress that I have in the same Server and it only takes 1,5 seconds to reply. So I decided to continue investigating why this WordPress took so long to reply.

blog-carlesmateo-com-statistics-use-last-24-hours

As I said before I checked that the files from my WordPress installation were the same as the original distribution, and they were. Having discarded different files the thing had to be in the database.

Even when I checked the MySql it told me that all the tables were OK, having seen that the WPUserOnline deletes all the registers older than 5 minutes, I guessed that this could lead to fragmentation, so I decided to do OPTIMIZE TABLE on all the tables of the database for the WordPress failing, with InnoDb it is basically recreating the Tables and the Indexes.

I tried then the call via RPC and my Server replied in three seconds. Much better.

Looking with htop, when I call the xmlrpc.php the CPU uses between 50% and 100%.

I checked the logs and the robot was gone. He leaved or the provider finally blocked the Server. I don’t know.

Everything became clear, it was nothing more than a sort of coincidences together. Deactivating the plugin the DELETE queries disappeared, even under heavy load of the Server.

It only was remain to clarify why when I send a call to xmlrpc to this blog, it replies in 1,5 seconds, and when I request to the Barcelona.afterstart.com it takes 3 seconds.

I activated the log of queries in mysql. To do that edit /etc/mysql/my.cnf and uncomment:

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

Then I checked the queries, and in the case of my blog it performs many less queries, as I was requesting to pingback to an url that was not existing, and WordPress does this query:

SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND ( ( YEAR( post_date ) = 2013 AND MONTH( post_date ) = 9 AND DAYOFMONTH( post_date ) = 27 ) ) AND wp_posts.post_name = 'afterstart-barcelona-2013-09-26-meet' AND wp_posts.post_type = 'post'  ORDER BY wp_posts.post_date DESC

As the url afterstart-barcelona-2013-09-26-meet with the dates indicated does not exist in my other blog, the execution ends there and does not perform the rest of the queries, that in the case of Afterstart blog were:

40 Query     SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (81) ORDER BY meta_id ASC
40 Query     SELECT ID, post_name, post_parent, post_type
FROM wp_posts
WHERE post_name IN ('http%3a','','seretil-me')
AND post_type IN ('page','attachment')
40 Query     SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND (wp_posts.ID = '0') AND wp_posts.post_type = 'page'  ORDER BY wp_posts.post_date DESC
40 Query     SELECT * FROM wp_comments WHERE comment_post_ID = 81 AND comment_author_url = 'http://seretil.me/'

To confirm my theory I tried the request to my blog, with a valid url, and it lasted for 3-4 seconds, the same than Afterstart’s blog. Finally I double-checked with the blog of my friend and was slower than before. I got between 1,5 and 6 seconds, with a lot of 2 seconds response. (he has PHP 5.5 and OpCache that improves a bit, but the problem is in the queries to the database)

Honestly, the guys creating WordPress should cache this queries instead of performing 20 live queries, that are always the same, before returning the error message. Using Cache Lite or Stash, or creating an InMemory table for using as Cache, or of course allowing the use of Memcached would eradicate the DoS component of this kind of attacks. As the xmlrpc pingback feature hits the database with a lot of queries to end not allowing the publishing.

While I was finishing those tests (remember that the attacker ip has gone) another attacker from the same network tried, but I had patched the Server to ignore it:

94.102.52.157 - - [31/Aug/2014:02:06:16 +0000] "POST /xmlrpc.php HTTP/1.0" 200 189 "-" "Mozilla/4.0 (compatible: MSIE 7.0; Windows NT 6.0)"

This was trying to get a link published to a domain called socksland dot net that is a domain registered in Russia and which page is not working.

As I had all the information I wanted I finally blocked the network from the provider to access my Server ever again.

Unfortunatelly Amazon’s Firewall does not allow to block a certain Ip or range.
So you can block at Iptables level or in .htaccess file or in the code.
I do not recommend blocking at code level because sadly WordPress has many files accessible from outside so you would have to add your code at the beginning of all the files and because when there is a WordPress version update you’ll loss all your customizations.
But I recommend proceeding to patch your code to avoid certain Ip’s if you use a CDN. As the POST will be sent directly to your Server, and the Ip’s are Ip’s from the CDN -and you can’t block them-. You have to look at the Header: X-Forwarded-For that indicates the Ip’s the proxies have passed by, and also the Client’s Ip.

I designed a program that is able to patch any PHP project to check for blacklisted Ip’s (even though a proxy) with minimal performance impact. It works with WordPress, drupal, joomla, ezpublish and Framework like Zend, Symfony, Catalonia… and I patched my code to block those unwanted robot’s requests.

A solution that will work for you probably is to disable the pingback functionality, there are several plugins that do that. Disabling completely xmlrpc is not recommended as WordPress uses it for several things (JetPack, mobile, validation…)

The same effect as adding the plugin that disables the xmlrpc pingback can be achieved by editing the functions.php from your Theme and adding:

add_filter( 'xmlrpc_methods', 'remove_xmlrpc_pingback_ping' );
function remove_xmlrpc_pingback_ping( $methods ) {
    unset( $methods['pingback.ping'] );
    
    return $methods;
}

Update: 2016-02-24 14:40 CEST
I got also a heavy dictionary attack against wp-login.php .Despite having a Captcha plugin, that makes it hard to hack, it was generating some load on the system.
What I did was to rename the wp-login.php to another name, like wp-login-carles.php and in wp-login.php having a simply exit();

<?php
exit();

Take in count that this will work only until WordPress is updated to the next version. Then you have to reapply the renaming trick.