Hello, welcome to the talk about the MySQL network protocol.
So that agenda for today is first the introduction, then we go over some of the connection
set up phase of the whole protocol, including handshake, TLS compression, authentication,
and then we go to the command phase.
Once we have a connection and look at queries, results, sets, including prepared statements.
And then we will also quickly hit something about replication and a few other things.
So who am I?
I'm a dead end of an eating.
I'm working for Pink App on TITB, which is a database that implements the MySQL protocol.
There are more here, like there's also Feetest, that also does MySQL protocol, and of course,
MySQL itself, MariaDB, it's all the same protocol.
And this database is written in Go by the way.
I'm also a long time MySQL user, and I'm contributing to various projects, including
WireShark, that I will also be using in this presentation.
They also have a stand pretty much just outside of this room.
So they have stickers, they have nice people there, go say hi.
Also working on Go by SQL, TITB, of course, MySQL, and then the build driver for MySQL.
So what are the reasons why you should care about MySQL protocol?
First thing is that troubleshooting, things happen, bugs happen.
It can be either on the client side, server side.
It can be network devices that do smart things while the student.
So that's one of the cases where the protocol is important, but also performance.
So some languages like PHP, set up a connection, do solving and disconnect, and there are
like many wrong trips that you can avoid, and that can really increase performance.
There's just nenders cost, like you might be able to reduce the amount of network bandwidth,
which is especially in some specific cloud deployments, really can make a difference.
And then there's like contributing for a protocol support, to new languages, new connectors,
new tools, doing things with binary logs, for example.
And there like the protocol is the thing you need to work with, because sometimes there's
not a library for your language yet.
So definition and scope.
This is the protocol that's used between the client and the server.
It's known as the classic protocol.
There's also an exporticle that I will not be offering today.
It's newer.
Different, but the classic protocol is way more widely used still nowadays.
The protocol can be used over TCP and over a unique socket.
There's protocol information available from Oracle.
It's part of the same repository, which is generated with DocsieGen, quite nice.
Good documentation, but it's lacking a few parts, and it is documentation.
It's not a specification.
Like other things like HTTP might have a formal specification,
and that's just missing for the MySQL protocol.
So often when you're looking into documentation, you might also want to look at network traffic
from like mySQL, like Java connector, other things, and just compare things to see what's
actually happening.
So who implements the protocol, of course, the MySQL type of VFS, etc.
And then there are all the clients for like Java, a PHP, and then there's the CAPI,
and on top of the CAPI, there are a few other connectors that use the CAPI for the protocol implementation.
If you want to use Wireshark with mySQL, there are a few things you can do, and you can
capture traffic with TCP dump or other tools, or you can capture things with Wireshark directly,
but it's a GUI tool, and usually that's not really something you want to use on a server.
On the MySQL site, if you want to make it easy to capture traffic,
you want to make sure that you are sending the traffic over TCP, not over the Unix socket,
because if you're dumping on the Unix socket sending the traffic over a something else, then that's not working.
Disabling TLS makes things a lot easier.
It is possible to also work with TLS, but then you need to go through extra steps.
So, but default, you want to just disable it and make it easy for yourself.
If you're not running mySQL, on board 3, 3, or 6, because you might be using TIDB, other implementations,
or just having multiple sandboxes running, there's a decode S option, where you can say like,
well, board 3, 3, 10 is not also using mySQL, that might be needed to actually see the MySQL stuff.
Wireshark shows you a lot of TCP information, mySQL information, other traffic that might be in your
dump, just use mySQL as a display filter, and all the other things that are less interesting, go away.
With the MySQL protocol, it's really useful to start the capture at the beginning of the
conversation. So, when you connect to the server, don't capture only like a part of a conversation,
because in the beginning of the conversation, a lot of the capabilities are exchanged, and that makes the decoding much more reliable.
So, this is what Wireshark looks like. I'm already filtering on with mySQL. You see, if you pack
it on the top, in a bottom left, you see the decode at structures, and on the bottom right, you see
just the packet bytes that are sent over the wire. So, let's start with actual protocol,
who sends the first message? So, the client connects, so with ATP, just as a comparison,
the client sends the first message, which with mySQL, the server is the one that is actually sending the message.
So, the client connects to the server, and then the server replies with a server greeting.
The other thing about the protocol is versioning. The classic protocol has versions,
version 9 and version 10, but version 10 is used since MySQL 321, like 98. So, the protocol version is
something that you can basically ignore. So, instead of the protocol version, the capability
flex being used to extend the protocol for new features. So, ATP is quite simple protocol that many
people might know. You just send a get request, and you get a get a get a response.
With MySQL, the whole handshake is a bit more complex. So, you connect, and then the server
presents the first message. Then the client starts to send the response. There might be extra steps
for authentication, depending on what authentication plugin you're using. Eventually, you should be
getting an OK or an error if you don't have to write password for example. And then you switch
into the command mode where you're actually sending queries, getting responses back.
So, the server greeting at the server is sending. It contains the server version,
connection ID, capabilities, and the authentication scramble that's used by some of the
authentication methods. Okay, the abilities are quite a few. So, these can all be set on both
by the client and by the server, and if both set the flag then it tends to be used in the protocol.
So, this is what wire-shark decodes of the server greeting. So, you can see that there's the
version and a lot of other useful things, including like the authentication plugin and whatnot.
So, the capabilities are also in their wire-shark disabled to decode them, and like the
highlighted line here is the flag that it says that it speaks the 4.1 or newer protocol.
And in two parts, because they're the basic capabilities and extended capabilities.
So, then after the server has sent it's greeting, then the client will start to try and log in.
This will contain the username authentication data, a password or a password has depending on
what authentication method you're using. It might contain the database. It might set the
correlation for like the character set you're using. It might define a maximum packet size
and things like connection attributes, like in the previous presentation you've seen that
connection attributes can be very useful and this is where they are set. It also means that the
connection attributes are set once per session and not for like every query.
The connection attributes are key value pairs that are sent to the by the client, to the server.
The both set by the connectors, which you can also set them manually in your application.
So, it's just a whole set and they come from different sources.
So, this is what it looks like on the MySQL server. You get a list of connection attributes for
specific connection. The names usually start with an underscore if they're defined by the
connector and they don't have an underscore if it's the program that defines them.
So, here you can see the program name is MySQL which is the MySQL client.
So, the login packet looks like this in Wireshark. So, you can inspect all the different things
and it knows how to decode a lot of things, including like the correlation.
So, then there's TLS in the particle which is a bit for Wiresh implementation.
Many other particles are using a different port to do TLS. MySQL does TLS and non-TLS on the same port.
Every connection starts as non-TLS and then switches to TLS. If both the client and the server
set the TLS flag. Interesting thing, there is that openness as well also knows how to do this.
The whole handshake of MySQL can also be done by openness as well if you want to like just inspect
TLS things with the open SL command line clients.
Wireshark is able to decode TLS traffic. It depends on what server suite you're using and of course,
it will need access to your RSA private keys that are used for that connection.
Besides actually using like decode in TLS traffic, Wireshark is also very useful if
your client just refuses to connect and say like well I can't use TLS settings because for example,
some versions of the Java client might have a different set of server suite that a server might
support and Wireshark can also help to troubleshoot these kinds of things.
Next protocol feature is compression. So compression is basically a set-lit base since the
beginning. It's been there for a long time. I don't think it was used by many people but it's been
there since ages. Then in 8-0 they added support for set standard. A different compression algorithm
usually does a better job. So there are two flex for setting the compression or turning on the
compression. Compress is for the CLIP and the set standard compression algorithm is for set standard.
So what's interesting with the protocol is that here I hope the slide is somewhat readable.
Like I've advised my client and I'm setting the compression algorithm first to set standard.
Then I get set standard. Then I set the compression algorithm to CLIP. I get CLIP, no surprises there.
But if you supply both, then you get CLIP. I would expect that if both are set that you would get
the newer one but that's not what's happening. These are the kind of corner cases that are
usually missing from a documentation. Now this has been documented in the documentation but
those are the kind of things where you just have to experiment to see what's happening.
So compression is done on somewhat different layer. So regular my scope packet starts with a
length in a sequence and then the payload of the whole packet. But the compressed ones have
a compressed length, sequence and an uncompressed length and then the compressed payload.
However, the thing here is that actually doing the compression is still optional.
So if a client sends something to the server and thinks compression is useful,
that will just set the uncompressed length to zero and sends it as uncompressed data.
So this is what it's looked like in a wire shark. The highlight thing here is the uncompressed
length of zero. As you can see in the packet bytes, you can just see the whole query there.
That just looks like a normal query. Nothing compressed there.
Then the next packet you can here see that the uncompressed length is actually non-zero.
And then in wire shark you get two different tabs. One with a row packet bytes and the other one
with the bytes from wire shark where it actually decoded or decompressed and the data.
And there you can actually see the data from the query. So basically what many of the clients do
is it tries to compress something, if the compressed, the length of the compressed data is more
than the uncompressed one just sends uncompressed and that's the length to zero.
But of course this is also the question like do you want to compress everything? Do you want to
aggressively compress or not? There might be quite a lot of room for tuning.
Then without indication, the default authentication method is sent by the surfer in the greeting
but the client is free to completely ignore this and do something else entirely. It's just a suggestion.
Then the client tries to authenticate and then the surfer can say like well
actually I want you to use this specific authentication method and then the client has to comply
basically and eventually there's an okay packet or an error packet. The compression methods are
free to add as many wrong trips as they need. So when migrating to a newer authentication method,
the surfer announces the default one and the client might be wrong in choosing it.
So if you have a surfer and in like old accounts are using the native password one
but the casting shadow is the newer thing and the surfer might be announcing this
then you get a lot of mismatches.
Now I don't think there are any encounters for the number of authentication switch requests that are sent.
Yes. One of that on many clients nowadays you can actually set an explicit
authentication method. So even like with newer clients you can tell it to like no I really want to
only drive my school native password because I know that this is going to work in my environment
so there are some options there. So the first password thing that my skill did was to
call it old password. Basically the surfer sending a greeting or sending a greeting of eight
bytes and then the client would use this result together with the password to in a somewhat
secure way send the password to the surfer. This kind of protects the whole password during
the authentication phase if you're not choosing TLS but it will not protect any of the other
phases later on if you're sending a query to like maybe a data password or anything.
Luckily this is deprecated by now.
Then there's the native password slightly better, say my D, slightly longer sold and now it's
a shia one standardized hash function instead of like a custom on. So we're getting in the
right direction. Looks like this in the whole authentication stream.
And here you can also see the password part of the authentication data part that's being sent to the
surfer. So here this authentication switch where the surfer just says no I want you to use
my school native password for this specific account and here's the the salt that you have to
use then the client knows okay I will do this in a different way and send you the authentication data
again and then it's okay. So then next iteration on the whole password stuff was the
shia 256 passwords using a shia 2 function slightly better but this already started to rely
on TLS because basically it's just sending the plain text password over the connection without any
special handling which is fine with with TLS and the other option there was to use an RSA key pair
to encrypt the password before sending it but that's usually a management nightmare.
So TLS is kind of required here. I'll just skip over that part.
And then there's the caching shia 2 password that everyone is now hopefully using.
Slide slightly newer and this actually does a smart thing where it has a caching functionality
which allows better performance. So shia 256 password or caching shia 2 password can use
like a public key for the RSA and can also request it from the surfer and you can see that
where shark is able to actually get the public key and on the bottom you can see that it has
the raw bytes of the frame but it can also actually give you the actual public key.
So it can just decal to how public key from the conversation.
There's also a socket authentication plugin and that uses the more reliable way of getting the
username of the other end of the socket on a UNIX system.
Multi-factor authentication that's something quite new and here basically the surfer has
multiple authentication methods and just instead of sending an OK it sends
next of the education factor packet and then basically the next step happens.
There is option to have a clear password so if you're defining your own way for like
maybe ellop authentication or your custom thing you can get the client to actually send a clear
password without any anything else. So queries and results sets and there's basically to
almost different protocols here. There's the text protocol for comp query and there's the
binary protocol that's being used with prepared statements. So for comp query it's a really simple
network packet. It starts with the ID of comp query and then just a query text. That's the whole thing.
However, they change it a little bit nowadays. So it's become a bit more complicated.
So if you have the option to send query attributes and that's also one of the clients
flex in the protocol. If this is enabled you can actually send key value pairs along with your query
maybe that can be used by the router or not. And that's done with these options and it will
send the type, the flag and the name of all the key value pairs. And that's completely optional.
And from a user standpoint this is what it looks like. So here you're just adding like an
attribute, maybe the proxy user, if you're working on a proxy and you can just send some more
metadata to the server in a way that the server can also easily decode it. So this is what a
comp query looks like. In the packet trace you can see that there are actually query attributes
with a name and a value and a type. And then there's the actual statement of the query.
Then of course if you send the query you expect a result packet. Well actually you can have an
error packet or an OK packet or actually a text result. The text result consists of a field
counts and then for every field field definition and eventually a list of the actual row values.
So this is what it looks like and as you can see some of the things are collapsed here. So it
doesn't show you the details of the row packet. We look here at the column counts it says it just
one column and then the field packet describes this one field that we have. So we know that we
only expect one column. This one column has these properties and then there are actually two row
packets. So one column, two rows with these values. Prepared statements. It's a
room statement prepared with the query. Kind of similar to the comb query I want. But then you can
back some metadata about the parameters that are used. And eventually you can then execute the
prepared statement like one or more times. And when you're executing the statement or you of course
have to specify the ID of the statement you're executing. So this is how this might look like.
So you're connecting to a server. Preparing a query, you execute it. You might be
changing the parameters that you're using for the query. You execute it again and you're disconnect.
And then this is how it looks like in a wire shark. So you see the prepareer, you see the
executes, et cetera. And if you go back to the previous slide, you can see that on the
connection string, I have to say my score server prepare equals falls. So some of the languages
both Pearl and Java have the option to do the prepareer actually in the client and not send it to the
server. And then of course you will also not see it on a network protocol.
Yeah, I think the protocol was already allows you to send multiple my score packets in a single
TCP packet. Yeah, but then you know that that doesn't work actually. So the thing is you're sending
the prepareer, then you get back to metadata that you're actually needing to check if you have the
right number of parameters, et cetera. And you're then missing it.
Yeah. So here's the response to the prepareer. You get back some information about the number of fields,
the parameters, and some details about all the fields that are included. You can hear seated our
two parameters in three fields. And this is the executes. And then you have to specify the
parameters that you're sending along. I think I will skip the replication, cloning.
Yeah, sorry out of time.
Two questions.
Since it looks like there are flags about enabling TLS or not during the handshake, how is
the TLS down green attack prevented?
It's not.
Yeah.
Yeah, I had the client capabilities in the server capabilities in one of the first slides.
So you're asking about load local in file?
Yeah, it's in the capability. But sometimes this flag is not set directly.
I have not seen that, but it's also setting like a protocol flag. If it supports it in the
protocol, it might support it in the protocol. And then you send the request and it might deny it.
It might also be a permission thing for.