Data-based

DROP DATABASE 'heart_stopper';

This one was really fun... (Really really really fun, I cannot express just how fun it was)

Higher-deaducation

My workplace as you might have guessed is in the higher education space. What that means from an IT perspective is that we are a centralised one-stop shop for all of your information technology needs. What that means from an individual school or college's perspective is that we are the evil overlords that want total control and lock down of systems.
Given this a few schools have a habit of running their own systems, usually science or engineering schools. We don't have any control or real visibility into how these work besides the odd security scan telling us that "internet facing software xyz is 20 years old WTAF please remediate."

One such science department reached out to our corporate world for some help with their own systems. We don't technically have to help them, especially because they aren't connected to our systems in any way besides being in an isolated subnet we control, however I'm a nice person (Jokes, I'm a sysadmin) so I offered to at least take a look.

The ticket described a random Ubuntu system that the department had for running various bespoke pieces of software which had failed for one reason or another. Digging into the issue a bit more revealed that the cyber security department had performed one of their aforementioned scans and discovered an ancient version of Apache running on the box. They in turn requested that the software be updated which necessitated an operating system upgrade. This isn't usually a problem and can be achieved either through the software centre on a desktop Ubuntu system or through a single command on a server system without a GUI.

sudo do-release-upgrade

For a quick breakdown of what those commands do:

1. sudo allows a command to be run as another user, by default this is the super user otherwise known as "root". This user has unfettered administrative power over the system.
2. do-release-upgrade is a Python helper script that Canonical (The creators of Ubuntu Linux) have written to handle Ubuntu major version upgrades easily.

So putting that all together we essentially have an invocation that will start the upgrade helper script as the root user.

I didn't have the full history of this server or the upgrade path that the department took however the version it was on when I got to it was 20.04. This means it had been upgraded at least once from 18.04 but had possibly jumped through other versions too.
When a Linux operating system is upgraded between major versions, certain packages MUST be upgraded too as the new OS may have newer libraries that the old software is no longer compatible with. Quite often these forced upgrades are harmless and software maintainers try to protect against world ending issues or at the very least they alert the user that "BREAKING CHANGE WILL OCCUR IN THIS NEW VERSION RED ALERT SHIELDS UP!!"

However...

Some package maintainers are a bit more subtle in communicating these changes cough Oracle cough. Super subtle.

Your computer will restart to perform updates in 1 Pico second. Save your work!

So the update had been completed and went off without hitch, mostly. The department had tested their server and everything seemed fine. It was only when they got to testing the web apps that things started to look a bit more bleak, they all seemed to be live and running but no one could login. To make matters worse, the errors that were spat out into the log didn't seem relevant to the current situation, they consisted of warnings about future deprecations and changes but nothing show stopping.
This is when they decided to ask for help, so I had a call with the department and got the lay of the land as well as access to the server in question.

Poking around the server showed a very standard Ubuntu Linux setup with Apache and PHP installed albeit with some weird directory layout choices (Apache's webroot in /home/apache). Just like the staff had done, I checked all the logging for Apache and MySQL since failing to login seemed like a database issue though I did consider possible deprecations in PHP libraries as well.

One thing of note was the lack of web framework used to build the PHP application. Normally when working on software of any serious scale you use a framework to keep things consistent and provide niceties like logging drivers and SQL injection protection. Since no framework was in use, the developer had written their own logging handler for errors which sent an email with the function that had been called when the error occurred and the error itself.
Once I had figured out what the application was doing with the logs, I modified the email variable to my own so I would receive the errors.

Starting to get somewhere, finally.

Plain hashbrowns

Firing the login mechanism a few times revealed that the password entered into the login form was sent in the error message as plain text. Not after the hashing function had converted it into an string of gibberish, not with a placeholder variable or empty string, literally my own password. Horrified I quickly changed my password, this would need to be brought up with whichever son of a bitch wrote this god forsaken thing. Though this wouldn't actually be possible as it turned out the developer had long since left the organisation. I suppose that's why we are in this mess in the first place and didn't just get them to fix it.

Horrifying developments aside, this plain text emailing of passwords (If you're reading this and are thinking "That doesn't seem so bad", just go, leave now.) did give me a clue as to why the login was falling over. If hashing hadn't taken place on the backend in the PHP code before the logging handler had kicked in, it must be occurring as data is written to the database. Digging a little further revealed this:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

The rest of the error detailed the part of the SQL query that was blowing up. It turned out to be the PASSWORD() function. Essentially what the developer was doing was something like this:

SELECT username, password FROM users WHERE username=? AND password=PASSWORD(?);

What this does is lookup the user in the database by matching their username and password combination and passes their password through the MySQL PASSWORD() function. This function converts a string into a hash, in this case the user's password. At least the passwords weren't stored in plain text in the database.

Since the program was blowing up with an SQL error and everything pointed to the PASSWORD() function. I decided to Google for similar errors, I got LOTS of hits, hmm this seems like a common problem. I landed on this stackoverflow page, god bless stackoverflow for helping me keep my job.
For everyone who doesn't want to bother reading the page, essentially the PASSWORD() function had been deprecated because it was considered insecure.

Great I'm going to be rewriting this application aren't I...

A very nice soul on that stackoverflow question had answered with an easy fix thankfully, simply wrap the password variable in a bunch of other functions. The result looks like this:

SELECT username, password FROM users WHERE username=? AND password=CONCAT('*', UPPER(SHA1(UNHEX(SHA1(?))));

Absolutely DISGUSTING. It does however, work. So that's nice.

I set about fixing all the other references to this deprecated function by searching through the files using grep. I could have written a sed regex to replace them all for me but I was being lazy, incidentally it probably would have looked like this:

sed -i.bak "s/PASSWORD()/CONCAT('*', UPPER(SHA1(UNHEX(SHA1('mypass')))))./g" /home/apache/*

That's free, you can have that, write it down. Also the code block editor in Ghost is shocking.

Clicking login now logged you in, who would have thought?!
The department was happy, I was happy, everyone (probably not the developer that wrote the software) was happy.

I really love coffee

Content that I had done these guys a wonderful favour way beyond what my job role normally entails. I sat back and poured myself a nice cup of Java.

Then the department messaged me again. Paraphrased:

Hi, we are now having issues with another application, this one runs on the user's workstations and is written in JAVA...

I had to stop reading, it was too horrible.

I don't know JAVA at all, and even if I did I wouldn't admit to it. It's known for being the most memory hungry, slow to start nightmare of a language. Plus Oracle own it so any time Safra Catz needs a new super yacht they go after organisations and make them pay stupid money for running it. They once hit our work place and asked for me to scan every server for any trace of JAVA. Not necessarily in use, not even installed per-say, just a reference to the word. We complied but with the caveat that all the Linux machines use OpenJDK so even if you see the word JAVA you can't have any money. What a merry waste of time that was.

I made it pretty clear that I wouldn't be able to debug any JAVA applications but that it is likely the same issue as with the PHP web app so if they could get a JAVA dev to take a look they should be ok. They did not know any JAVA devs...

That left us with only two options. Rollback the MySQL version from 8 to 5 or install MariaDB. The first option seemed absurd and probably wouldn't install on a modern Ubuntu version anyway. The second option seemed like the best especially because MariaDB was written by the original author of MySQL (His two kids are called My and Maria) and is considered a drop-in replacement.
Great, let's get cracking.

/tmp/ stands for temporary

Firs things first, BACKUP EVERYTHING. MySQL features a handy tool to do this called mysqldump, this will dump the databases to a file that can be imported again. We also need all the users and their permissions which are stored separately.

mysqldump -u root -p databasename > /tmp/databasename.sql

Do that for each database and you have an easy to restore file based backup.

The users and "grants" are a little trickier but still doable.

mysql -u root -p$PASSWORD --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u root -p$PASSWORD --skip-column-names -A | sed 's/$/;/g' > /tmp/user_grants.sql

mysql -u root -p$PASSWORD --skip-column-names -A mysql -e "SELECT CONCAT('CREATE USER \'', user, '\'@\'', host, '\' IDENTIFIED WITH \'mysql_native_password\' AS \'', password,'\';') FROM mysql.user WHERE user NOT IN ('mysql.session','mysql.sys','debian-sys-maint','root');" > /tmp/create_user.sql

Pretty gross but it does work.

Cool, so now we have the databases backed up and users with their permissions. Next we need to uninstall MySQL (MariaDB and MySQL can't coexist because of course they can't, that would be too easy) and blow away it's configs and directories.

sudo apt remove --purge mysql*
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql

So far so good.
Now let's install MariaDB:

sudo apt install mariadb-server -y

MariaDB installs but won't start, this is likely due to some MySQL crud still existing on the system. apt wasn't happy about removing the files either since MySQL and MariaDB share similar directories. Since the system was in a weird state and there were likely quite a few temporary files in /tmp/ that MySQL had dumped there, I thought it might be a good idea to reboot the machine...

A good idea if I hadn't stored the database backups in /tmp/ and then blown away the original copies. See /tmp/ on a Linux system is what we call a ramdisk, that is a part of the system RAM that has been configured to act as storage for files. This makes perfect sense since the contents of RAM is lost on system reboot or power off and usually the contents of /tmp/ is supposed to be temporary. Usually.

As I saw the ominous Connection to server closed message indicating the machine had disconnected my session so it could reboot, my heart sank. I realised that everything was gone, no backups in /tmp/ and no original copies.
My skin began to prickle and I started sweating, I felt tension under my eyes and in my temples. The world started to close in and my breathing got heavy.
I was having a full blown panic attack.

Once the server had come back up and I logged in to survey the damage I remembered something one of the scientists at the department had said.

We use the Unix dump command to backup files to a NAS.

I have never typed a message into Teams so fast. I didn't expect a reply, it was closing in on 7 PM after all.

Where are your backups stored, I need to recover some data.

I got a reply back pretty quickly pointing me at the mount point for the NAS, investigating the directories I found some full disk backups and some smaller incremental backups. I popped open one of the backups and searched for the /var/lib/mysql directory which was present. Phew, I'm saved.

The backup was from just under a month ago right around the time they upgraded the server and everything stopped working. It couldn't have been a more perfect time to restore from since the system wouldn't have been able to write anything between then and now. Well a better time would have been 30 minutes ago but you take what you can get.

Restoring the files took a very long time, too long for my stress levels. It did eventually complete and after the files were in place I reinstalled MySQL so we could try again.
Except no databases showed up.

Just when my stress levels had returned to normal too.

Panicking and searching the web for answers led to nothing productive. It then occurred to me that this backup may have been taken on a different MySQL version since the upgrade was the whole reason everything broke. I found the mysql_upgrade_info file and printed it out:

cat /etc/mysql/mysql_upgrade_info
5.7

That confirmed the version was from a pre-MySQL 8 era.
I guess we really do need to go with option 1, roll back the MySQL version.

Luckily the version of MySQL 5.7 for Ubuntu 18.04 seems to install fine on 20.04 when using Oracle's software repositories.

sudo systemctl enable --now mysql
sudo systemctl status mysql

The lovely green "active (running)" has never looked nicer. All the software was tested and able to talk to the restored databases and everyone was happy.

I still needed a stiff drink afterwards though.