Good attacks make good detections make good attacks make..

(The making of a MySQL Canarytoken)

tl;dr

Consider this scenario: An industrious attacker lands on one of your servers and finds a 5MB MySQL dump file (say, called prod_primary.dump). What do they do next?

As soon as they do, you get an email/SMS/alert letting you know:

Eds note: You can create and deploy these by visiting canarytokens.org

There are obvious benefits to these sorts of booby-traps, but some rise above the rest:

• • They can be deployed in seconds;
  • They aren’t prone to high false-positives;
  • An attacker who suspects you are using these is no better off for knowing this (if nothing else, they now have to second-guess everything they touch);
  • It’s such a pure illustration of attack-minded defense.

It Begins…

• Can we get the system to surf to a URL we control;
• Can we get the system to lookup a DNS entry that we control;

Forcing a DBMS to reach out to other servers is a classic SQL Injection challenge and past work had laid out techniques [datathief][squeeza] to solve this on other DBMSes (Oracle and MSSQL) by leaning heavily on their robust shell programming abilities. MySQL was different though with less functionality in this regard. On my Linux host (or Docker container), I couldn’t use cute tricks like calling a LOAD DATA INFILE from a file path with a Canary-DNS entry

(e.g., LOAD DATA INFILE ‘\\DNS-entry.thinkst.com’;).

A quick search of attack-minded-blogs seemed to corroborate my thinking, that MySQL was restricted enough to prevent these types of tricks in its default state.

Sizing up the problem

After much fiddling and searching I found a promising lead through database replication.

This was a mixed bag. The community/open-source version of MySQL does support replication but a server would need to be configured (through its config files) to support it. We can’t convince an attacker who stands up a temp MySQL server to first reconfigure their DBMS and I almost gave up. But then in-band-signalling once more saved the day (or led to horrible insecurity). It turns out that these configuration files can be overridden at runtime by SQL commands!

Keep in mind that all we want at this stage is a set of MySQL queries (run on a standard MySQL instance) that will reach out and touch a foreign server somehow.

I set up a netcat listener on a remote server and on the MySQL server side I configured the replication to point to it:

CHANGE MASTER TO MASTER_HOST=’my-server.thinkst.com’, MASTER_PORT=3306, MASTER_USER=’root’, MASTER_PASSWORD=’root’, MASTER_RETRY_COUNT=1;

Then, I typed START REPLICA; and waited.

And waited.. and… nothing. The netcat listener showed no evidence of a connection, and I had nothing on my MySQL session.

Running SHOW REPLICA STATUS; on the MySQL server reveals that the server did try to connect (already now an option for a DNS token), but the connection failed. I replaced netcat with tcpdump and retried the process. This time I did see a connection to my server, but no data was exchanged, and the MySQL status still reported a failure.

A deeper dive into the MySQL Handshake

Turning to the MySQL documentation on how a connection between a client and server is established, we learn that although the client (or replica-seeking server) initiates the connection, once open the client then waits for the server to return a Handshake packet. This packet describes the server’s capabilities and supported authentication plugins to the client, so they can mutually agree on the fullest feature set for the connection.

We had enough capability at this point to create a DNS-based Canarytoken. Ie.

1. Create a unique DNS host-name on a domain we control; (this is done by simply visiting Canarytokens.org and creating a DNS-Canarytoken: we get back something like: 0iep6h5na3p4coxx4hax132b8.canarytokens.com)
2. On the MySQL server, issue the commands MASTER_HOST=’0iep6h5na3p4coxx4hax132b8.canarytokens.com; START REPLICA

Even though the actual replication never takes place, the MySQL server resolves the DNS name of the foreign server (effectively tripping the DNS token) and letting us know that it’s happened.

But… While a DNS based canarytoken (like this one) is great at letting us know that something happened (someone ran this mysql import command) it isn’t great at giving us more details. Our DNS server is able to tell that the record 0iep6h5na3p4coxx4hax132b8 was requested, but can only tell us the IP address of the DNS server that made the request.

We can do better.

If we can get the remote server to complete the MySQL handshake, we’d get a connecting IP address and we can possibly stuff more information from the MySQL server into the username/password fields that we submit.

There were a few useful blog posts that helped describe the packet format, but it was a relatively complex encoding that included some fixed-width fields, some NULL-terminated fields, and some packing fields. Since I didn’t need the connection to complete, I simply captured (above figure) the Handshake packet from a real MySQL server (configured to explicitly not support SSL to simplify the next steps), and wrote a small Python server to send those captured bytes to every connection.

This allows the handshake to go further (and lets us submit a username/password combination from MySQL to the foreign server).

This gives us all the pieces we need to create a point-and-click Canarytoken to cover this use case (and we did). Here’s how you can use it.

Using the Canarytoken

If you visit https://canarytokens.org and click the token selection dropdown, you’ll notice a shiny new MySQL token.

In typical Canarytoken style, you then supply just two pieces of information.

1. An email address to receive the alert;
2. A reminder note to jog your memory when this alert fires;

That’s it. When you hit “Create my Canarytoken”, we give you two quick ways to make use of the token, choose the one that’s best suited for your scenario (likely option 2):

1)

SET @bb = CONCAT(“CHANGE MASTER TO MASTER_PASSWORD=’my-secret-pw’, MASTER_RETRY_COUNT=1, MASTER_PORT=3306, MASTER_HOST=’k5sk4zeo5csej6pps4vkgzmtp.canarytokens.com’, MASTER_USER=’k5sk4zeo5csej6pps4vkgzmtp”, @@lc_time_names, @@hostname, “‘;”);

PREPARE stmt FROM @bb;

EXECUTE stmt;

START REPLICA;

1. 1. 1. Her server will lookup k5sk4zeo5csej6pps4vkgzmtp.canarytokens.com triggering the DNS token and letting us know that the MySQL dump file we left safely on NYC-DC1 was just loaded into MySQL somewhere;
      2. Her server will connect to our fake MySQL server (which now knows her MySQL Server’s IP address, which is a strong thread to pull on);
      3. Her server will attempt to login to our fake MySQL server with the username: ‘k5sk4zeo5csej6pps4vkgzmtp”, @@lc_time_names, @@hostname which now gives us more information on the attacker that we can report on when letting you know the token has tripped.

2)

For users who don’t have a mysql dump file lying around (or don’t want to risk creating one with real data), we also offer option-2, where we take a sample MySQL file, run it through a quick mixer to generate some random linked tables with data, and insert the tokened snippet into it. No mess, no fuss. Simply drop the file on your server (or in your dropbox, or in your email) and if you ever get the notification to let you know that it’s been loaded, you know you have a problem.

In many ways, tokens like this are a joy. If you leave this Staff_Salaries-2021.mysql-dump.sql file in your email and forget about it for 10 years, it costs you nothing.

If you get a notification letting you know that the mysql dump you left in your mailbox just got loaded into a MySQL server in $FOREIGN_COUNTRY? That’s priceless.

There’s no chance of it being an accidental alert, and there’s almost no chance that an attacker would find a large enough mysql dump file without loading it into a DB to check it out.

Absolute win.

Bizzaro Bonus: