This isn’t really a feature we should brag about, because it’s a bug that took us a while to figure out, but we believe in sharing the bad as well as the good. There’s a lot to learn from TCP reassembly and protocol reverse engineering!
We received a request from a customer to help track down the user that was sending some queries to their database. Normally we can find this information easily: the user is one of the properties of query samples, and we can just click on a sample and see it. But for this particular customer, the user was always
unknown_user.
This means we weren’t able to figure out what database user was issuing the query. Normally there are two ways we can figure out what the user is:
- We see the connection handshake and capture the username from there.
- We see a
COM_CHANGE_USER packet and capture it from that (rare).
Our theory was that this customer’s database connections were all very long-lived, and we never got to see the connection setup sequence. But this didn’t hold up under deeper investigation. We
never captured the username for this customer. Argh!
Maybe this customer was using an authentication method we didn’t support? Possible. Some of the newer auth methods in the latest version of MySQL hadn’t been implemented in our sniffer yet. We implemented them. Still nothing!
Much debugging and tcpdumping later, we found out the problem was an undocumented protocol feature, combined with odd client behavior, that caused us to ignore the username during the connection handshake.
The good news is, for this customer and some others, we immediately saw a difference. In the screenshot below (lightly redacted for privacy) you can see how the number of
unknown_user queries goes way down. This happens after an agent upgrade. Meanwhile, the number of queries attributed to known users rises in a nice wedge shape as new connections are established and the sniffer keeps track of their queries.
This is not the first undocumented protocol feature we’ve found. (If you’re in the business of reverse engineering wire protocols, you’d better accept incomplete documentation as a given). We assume it won’t be the last.
