WEBVTT 00:00.000 --> 00:13.360 Hello, welcome to the talk about the MySQL network protocol. 00:13.360 --> 00:18.520 So that agenda for today is first the introduction, then we go over some of the connection 00:18.520 --> 00:27.760 set up phase of the whole protocol, including handshake, TLS compression, authentication, 00:27.800 --> 00:30.360 and then we go to the command phase. 00:30.360 --> 00:36.400 Once we have a connection and look at queries, results, sets, including prepared statements. 00:36.400 --> 00:44.280 And then we will also quickly hit something about replication and a few other things. 00:44.280 --> 00:45.120 So who am I? 00:45.120 --> 00:47.120 I'm a dead end of an eating. 00:47.120 --> 00:54.680 I'm working for Pink App on TITB, which is a database that implements the MySQL protocol. 00:54.680 --> 00:58.840 There are more here, like there's also Feetest, that also does MySQL protocol, and of course, 00:58.840 --> 01:03.360 MySQL itself, MariaDB, it's all the same protocol. 01:03.360 --> 01:06.760 And this database is written in Go by the way. 01:06.760 --> 01:14.960 I'm also a long time MySQL user, and I'm contributing to various projects, including 01:14.960 --> 01:19.360 WireShark, that I will also be using in this presentation. 01:19.360 --> 01:23.560 They also have a stand pretty much just outside of this room. 01:23.560 --> 01:30.680 So they have stickers, they have nice people there, go say hi. 01:30.680 --> 01:40.440 Also working on Go by SQL, TITB, of course, MySQL, and then the build driver for MySQL. 01:40.440 --> 01:44.880 So what are the reasons why you should care about MySQL protocol? 01:44.880 --> 01:49.680 First thing is that troubleshooting, things happen, bugs happen. 01:49.680 --> 01:52.680 It can be either on the client side, server side. 01:52.680 --> 01:58.080 It can be network devices that do smart things while the student. 01:58.080 --> 02:03.920 So that's one of the cases where the protocol is important, but also performance. 02:03.920 --> 02:09.920 So some languages like PHP, set up a connection, do solving and disconnect, and there are 02:09.920 --> 02:17.320 like many wrong trips that you can avoid, and that can really increase performance. 02:17.320 --> 02:24.040 There's just nenders cost, like you might be able to reduce the amount of network bandwidth, 02:24.040 --> 02:31.240 which is especially in some specific cloud deployments, really can make a difference. 02:31.240 --> 02:38.600 And then there's like contributing for a protocol support, to new languages, new connectors, 02:38.600 --> 02:43.640 new tools, doing things with binary logs, for example. 02:43.640 --> 02:47.400 And there like the protocol is the thing you need to work with, because sometimes there's 02:47.400 --> 02:53.640 not a library for your language yet. 02:53.640 --> 02:56.440 So definition and scope. 02:56.440 --> 03:01.680 This is the protocol that's used between the client and the server. 03:01.680 --> 03:04.240 It's known as the classic protocol. 03:04.240 --> 03:07.920 There's also an exporticle that I will not be offering today. 03:07.920 --> 03:08.920 It's newer. 03:09.000 --> 03:17.640 Different, but the classic protocol is way more widely used still nowadays. 03:17.640 --> 03:27.240 The protocol can be used over TCP and over a unique socket. 03:27.240 --> 03:31.800 There's protocol information available from Oracle. 03:31.800 --> 03:38.880 It's part of the same repository, which is generated with DocsieGen, quite nice. 03:39.440 --> 03:45.200 Good documentation, but it's lacking a few parts, and it is documentation. 03:45.200 --> 03:49.040 It's not a specification. 03:49.040 --> 03:53.040 Like other things like HTTP might have a formal specification, 03:53.040 --> 03:58.960 and that's just missing for the MySQL protocol. 03:58.960 --> 04:03.840 So often when you're looking into documentation, you might also want to look at network traffic 04:03.920 --> 04:11.360 from like mySQL, like Java connector, other things, and just compare things to see what's 04:11.360 --> 04:16.560 actually happening. 04:16.560 --> 04:21.760 So who implements the protocol, of course, the MySQL type of VFS, etc. 04:21.760 --> 04:28.280 And then there are all the clients for like Java, a PHP, and then there's the CAPI, 04:28.360 --> 04:40.760 and on top of the CAPI, there are a few other connectors that use the CAPI for the protocol implementation. 04:40.760 --> 04:46.520 If you want to use Wireshark with mySQL, there are a few things you can do, and you can 04:46.520 --> 04:56.280 capture traffic with TCP dump or other tools, or you can capture things with Wireshark directly, 04:56.280 --> 05:01.080 but it's a GUI tool, and usually that's not really something you want to use on a server. 05:03.320 --> 05:07.240 On the MySQL site, if you want to make it easy to capture traffic, 05:08.280 --> 05:14.760 you want to make sure that you are sending the traffic over TCP, not over the Unix socket, 05:14.760 --> 05:21.480 because if you're dumping on the Unix socket sending the traffic over a something else, then that's not working. 05:22.440 --> 05:25.880 Disabling TLS makes things a lot easier. 05:25.880 --> 05:31.880 It is possible to also work with TLS, but then you need to go through extra steps. 05:31.880 --> 05:35.480 So, but default, you want to just disable it and make it easy for yourself. 05:37.880 --> 05:44.760 If you're not running mySQL, on board 3, 3, or 6, because you might be using TIDB, other implementations, 05:44.760 --> 05:50.920 or just having multiple sandboxes running, there's a decode S option, where you can say like, 05:50.920 --> 05:57.640 well, board 3, 3, 10 is not also using mySQL, that might be needed to actually see the MySQL stuff. 05:59.640 --> 06:04.840 Wireshark shows you a lot of TCP information, mySQL information, other traffic that might be in your 06:04.840 --> 06:11.480 dump, just use mySQL as a display filter, and all the other things that are less interesting, go away. 06:11.720 --> 06:20.360 With the MySQL protocol, it's really useful to start the capture at the beginning of the 06:20.360 --> 06:27.080 conversation. So, when you connect to the server, don't capture only like a part of a conversation, 06:27.080 --> 06:35.800 because in the beginning of the conversation, a lot of the capabilities are exchanged, and that makes the decoding much more reliable. 06:35.880 --> 06:46.760 So, this is what Wireshark looks like. I'm already filtering on with mySQL. You see, if you pack 06:46.760 --> 06:56.360 it on the top, in a bottom left, you see the decode at structures, and on the bottom right, you see 06:56.360 --> 07:04.600 just the packet bytes that are sent over the wire. So, let's start with actual protocol, 07:04.680 --> 07:13.320 who sends the first message? So, the client connects, so with ATP, just as a comparison, 07:13.320 --> 07:20.920 the client sends the first message, which with mySQL, the server is the one that is actually sending the message. 07:20.920 --> 07:26.760 So, the client connects to the server, and then the server replies with a server greeting. 07:26.840 --> 07:35.640 The other thing about the protocol is versioning. The classic protocol has versions, 07:35.640 --> 07:44.040 version 9 and version 10, but version 10 is used since MySQL 321, like 98. So, the protocol version is 07:44.040 --> 07:49.880 something that you can basically ignore. So, instead of the protocol version, the capability 07:49.880 --> 08:00.440 flex being used to extend the protocol for new features. So, ATP is quite simple protocol that many 08:00.440 --> 08:06.120 people might know. You just send a get request, and you get a get a get a response. 08:07.960 --> 08:14.440 With MySQL, the whole handshake is a bit more complex. So, you connect, and then the server 08:14.520 --> 08:22.680 presents the first message. Then the client starts to send the response. There might be extra steps 08:22.680 --> 08:28.840 for authentication, depending on what authentication plugin you're using. Eventually, you should be 08:28.840 --> 08:36.360 getting an OK or an error if you don't have to write password for example. And then you switch 08:36.360 --> 08:40.120 into the command mode where you're actually sending queries, getting responses back. 08:44.840 --> 08:48.920 So, the server greeting at the server is sending. It contains the server version, 08:49.640 --> 08:55.160 connection ID, capabilities, and the authentication scramble that's used by some of the 08:55.160 --> 09:04.440 authentication methods. Okay, the abilities are quite a few. So, these can all be set on both 09:04.440 --> 09:11.240 by the client and by the server, and if both set the flag then it tends to be used in the protocol. 09:15.000 --> 09:22.440 So, this is what wire-shark decodes of the server greeting. So, you can see that there's the 09:22.440 --> 09:31.480 version and a lot of other useful things, including like the authentication plugin and whatnot. 09:33.880 --> 09:39.160 So, the capabilities are also in their wire-shark disabled to decode them, and like the 09:39.160 --> 09:44.920 highlighted line here is the flag that it says that it speaks the 4.1 or newer protocol. 09:49.080 --> 09:56.040 And in two parts, because they're the basic capabilities and extended capabilities. 10:00.600 --> 10:06.600 So, then after the server has sent it's greeting, then the client will start to try and log in. 10:07.560 --> 10:15.160 This will contain the username authentication data, a password or a password has depending on 10:15.800 --> 10:23.000 what authentication method you're using. It might contain the database. It might set the 10:23.000 --> 10:29.880 correlation for like the character set you're using. It might define a maximum packet size 10:30.040 --> 10:37.960 and things like connection attributes, like in the previous presentation you've seen that 10:37.960 --> 10:43.800 connection attributes can be very useful and this is where they are set. It also means that the 10:43.800 --> 10:49.000 connection attributes are set once per session and not for like every query. 10:52.840 --> 10:57.880 The connection attributes are key value pairs that are sent to the by the client, to the server. 11:00.280 --> 11:05.960 The both set by the connectors, which you can also set them manually in your application. 11:05.960 --> 11:09.640 So, it's just a whole set and they come from different sources. 11:12.200 --> 11:18.920 So, this is what it looks like on the MySQL server. You get a list of connection attributes for 11:18.920 --> 11:24.920 specific connection. The names usually start with an underscore if they're defined by the 11:25.880 --> 11:31.880 connector and they don't have an underscore if it's the program that defines them. 11:32.920 --> 11:36.680 So, here you can see the program name is MySQL which is the MySQL client. 11:42.840 --> 11:51.000 So, the login packet looks like this in Wireshark. So, you can inspect all the different things 11:51.160 --> 11:55.000 and it knows how to decode a lot of things, including like the correlation. 12:00.760 --> 12:06.680 So, then there's TLS in the particle which is a bit for Wiresh implementation. 12:08.280 --> 12:15.480 Many other particles are using a different port to do TLS. MySQL does TLS and non-TLS on the same port. 12:16.120 --> 12:22.840 Every connection starts as non-TLS and then switches to TLS. If both the client and the server 12:22.840 --> 12:33.320 set the TLS flag. Interesting thing, there is that openness as well also knows how to do this. 12:33.880 --> 12:39.240 The whole handshake of MySQL can also be done by openness as well if you want to like just inspect 12:40.200 --> 12:44.280 TLS things with the open SL command line clients. 12:50.040 --> 12:57.080 Wireshark is able to decode TLS traffic. It depends on what server suite you're using and of course, 12:57.080 --> 13:02.440 it will need access to your RSA private keys that are used for that connection. 13:02.680 --> 13:09.560 Besides actually using like decode in TLS traffic, Wireshark is also very useful if 13:10.200 --> 13:18.440 your client just refuses to connect and say like well I can't use TLS settings because for example, 13:18.440 --> 13:25.400 some versions of the Java client might have a different set of server suite that a server might 13:25.480 --> 13:30.680 support and Wireshark can also help to troubleshoot these kinds of things. 13:33.800 --> 13:42.360 Next protocol feature is compression. So compression is basically a set-lit base since the 13:42.360 --> 13:50.840 beginning. It's been there for a long time. I don't think it was used by many people but it's been 13:50.920 --> 14:00.760 there since ages. Then in 8-0 they added support for set standard. A different compression algorithm 14:00.760 --> 14:09.640 usually does a better job. So there are two flex for setting the compression or turning on the 14:09.640 --> 14:17.240 compression. Compress is for the CLIP and the set standard compression algorithm is for set standard. 14:21.800 --> 14:27.560 So what's interesting with the protocol is that here I hope the slide is somewhat readable. 14:29.400 --> 14:34.280 Like I've advised my client and I'm setting the compression algorithm first to set standard. 14:34.280 --> 14:41.400 Then I get set standard. Then I set the compression algorithm to CLIP. I get CLIP, no surprises there. 14:41.960 --> 14:50.520 But if you supply both, then you get CLIP. I would expect that if both are set that you would get 14:50.600 --> 14:56.760 the newer one but that's not what's happening. These are the kind of corner cases that are 14:56.760 --> 15:03.720 usually missing from a documentation. Now this has been documented in the documentation but 15:04.920 --> 15:08.920 those are the kind of things where you just have to experiment to see what's happening. 15:09.320 --> 15:22.680 So compression is done on somewhat different layer. So regular my scope packet starts with a 15:22.680 --> 15:28.440 length in a sequence and then the payload of the whole packet. But the compressed ones have 15:29.240 --> 15:37.560 a compressed length, sequence and an uncompressed length and then the compressed payload. 15:37.640 --> 15:43.720 However, the thing here is that actually doing the compression is still optional. 15:44.520 --> 15:50.520 So if a client sends something to the server and thinks compression is useful, 15:50.520 --> 15:57.320 that will just set the uncompressed length to zero and sends it as uncompressed data. 15:58.200 --> 16:09.320 So this is what it's looked like in a wire shark. The highlight thing here is the uncompressed 16:09.320 --> 16:15.320 length of zero. As you can see in the packet bytes, you can just see the whole query there. 16:16.040 --> 16:19.240 That just looks like a normal query. Nothing compressed there. 16:19.240 --> 16:29.800 Then the next packet you can here see that the uncompressed length is actually non-zero. 16:30.840 --> 16:37.800 And then in wire shark you get two different tabs. One with a row packet bytes and the other one 16:37.800 --> 16:45.640 with the bytes from wire shark where it actually decoded or decompressed and the data. 16:46.280 --> 16:53.560 And there you can actually see the data from the query. So basically what many of the clients do 16:53.560 --> 17:00.200 is it tries to compress something, if the compressed, the length of the compressed data is more 17:00.200 --> 17:04.760 than the uncompressed one just sends uncompressed and that's the length to zero. 17:07.320 --> 17:13.560 But of course this is also the question like do you want to compress everything? Do you want to 17:13.720 --> 17:19.080 aggressively compress or not? There might be quite a lot of room for tuning. 17:24.600 --> 17:32.920 Then without indication, the default authentication method is sent by the surfer in the greeting 17:34.040 --> 17:39.880 but the client is free to completely ignore this and do something else entirely. It's just a suggestion. 17:40.760 --> 17:47.640 Then the client tries to authenticate and then the surfer can say like well 17:48.760 --> 17:55.720 actually I want you to use this specific authentication method and then the client has to comply 17:55.720 --> 18:03.400 basically and eventually there's an okay packet or an error packet. The compression methods are 18:03.400 --> 18:18.440 free to add as many wrong trips as they need. So when migrating to a newer authentication method, 18:18.440 --> 18:26.200 the surfer announces the default one and the client might be wrong in choosing it. 18:26.280 --> 18:34.280 So if you have a surfer and in like old accounts are using the native password one 18:35.160 --> 18:39.400 but the casting shadow is the newer thing and the surfer might be announcing this 18:40.120 --> 18:42.120 then you get a lot of mismatches. 18:42.120 --> 18:58.520 Now I don't think there are any encounters for the number of authentication switch requests that are sent. 19:04.520 --> 19:10.600 Yes. One of that on many clients nowadays you can actually set an explicit 19:11.480 --> 19:17.240 authentication method. So even like with newer clients you can tell it to like no I really want to 19:17.240 --> 19:22.280 only drive my school native password because I know that this is going to work in my environment 19:23.320 --> 19:30.840 so there are some options there. So the first password thing that my skill did was to 19:31.560 --> 19:47.000 call it old password. Basically the surfer sending a greeting or sending a greeting of eight 19:47.000 --> 19:55.320 bytes and then the client would use this result together with the password to in a somewhat 19:55.320 --> 20:01.720 secure way send the password to the surfer. This kind of protects the whole password during 20:01.720 --> 20:07.080 the authentication phase if you're not choosing TLS but it will not protect any of the other 20:07.080 --> 20:11.400 phases later on if you're sending a query to like maybe a data password or anything. 20:12.600 --> 20:15.960 Luckily this is deprecated by now. 20:16.840 --> 20:27.160 Then there's the native password slightly better, say my D, slightly longer sold and now it's 20:27.160 --> 20:34.120 a shia one standardized hash function instead of like a custom on. So we're getting in the 20:34.120 --> 20:40.760 right direction. Looks like this in the whole authentication stream. 20:47.240 --> 20:52.760 And here you can also see the password part of the authentication data part that's being sent to the 20:52.760 --> 21:06.040 surfer. So here this authentication switch where the surfer just says no I want you to use 21:06.040 --> 21:11.640 my school native password for this specific account and here's the the salt that you have to 21:11.640 --> 21:18.760 use then the client knows okay I will do this in a different way and send you the authentication data 21:18.760 --> 21:26.520 again and then it's okay. So then next iteration on the whole password stuff was the 21:26.520 --> 21:35.480 shia 256 passwords using a shia 2 function slightly better but this already started to rely 21:35.480 --> 21:40.440 on TLS because basically it's just sending the plain text password over the connection without any 21:41.080 --> 21:51.160 special handling which is fine with with TLS and the other option there was to use an RSA key pair 21:51.160 --> 21:57.240 to encrypt the password before sending it but that's usually a management nightmare. 21:58.200 --> 22:05.560 So TLS is kind of required here. I'll just skip over that part. 22:05.560 --> 22:14.200 And then there's the caching shia 2 password that everyone is now hopefully using. 22:15.320 --> 22:24.840 Slide slightly newer and this actually does a smart thing where it has a caching functionality 22:25.400 --> 22:38.280 which allows better performance. So shia 256 password or caching shia 2 password can use 22:38.280 --> 22:45.640 like a public key for the RSA and can also request it from the surfer and you can see that 22:45.640 --> 22:51.560 where shark is able to actually get the public key and on the bottom you can see that it has 22:51.880 --> 22:56.280 the raw bytes of the frame but it can also actually give you the actual public key. 22:57.480 --> 23:00.600 So it can just decal to how public key from the conversation. 23:03.000 --> 23:11.400 There's also a socket authentication plugin and that uses the more reliable way of getting the 23:11.400 --> 23:15.000 username of the other end of the socket on a UNIX system. 23:15.320 --> 23:25.560 Multi-factor authentication that's something quite new and here basically the surfer has 23:25.560 --> 23:30.600 multiple authentication methods and just instead of sending an OK it sends 23:32.200 --> 23:38.440 next of the education factor packet and then basically the next step happens. 23:39.400 --> 23:46.840 There is option to have a clear password so if you're defining your own way for like 23:46.840 --> 23:52.040 maybe ellop authentication or your custom thing you can get the client to actually send a clear 23:52.040 --> 24:05.080 password without any anything else. So queries and results sets and there's basically to 24:05.080 --> 24:11.560 almost different protocols here. There's the text protocol for comp query and there's the 24:11.560 --> 24:20.920 binary protocol that's being used with prepared statements. So for comp query it's a really simple 24:20.920 --> 24:28.600 network packet. It starts with the ID of comp query and then just a query text. That's the whole thing. 24:29.000 --> 24:40.280 However, they change it a little bit nowadays. So it's become a bit more complicated. 24:41.320 --> 24:47.400 So if you have the option to send query attributes and that's also one of the clients 24:47.400 --> 24:54.280 flex in the protocol. If this is enabled you can actually send key value pairs along with your query 24:54.920 --> 25:04.200 maybe that can be used by the router or not. And that's done with these options and it will 25:04.200 --> 25:12.520 send the type, the flag and the name of all the key value pairs. And that's completely optional. 25:15.640 --> 25:23.480 And from a user standpoint this is what it looks like. So here you're just adding like an 25:23.480 --> 25:31.080 attribute, maybe the proxy user, if you're working on a proxy and you can just send some more 25:31.080 --> 25:41.320 metadata to the server in a way that the server can also easily decode it. So this is what a 25:41.320 --> 25:48.440 comp query looks like. In the packet trace you can see that there are actually query attributes 25:48.520 --> 25:55.320 with a name and a value and a type. And then there's the actual statement of the query. 25:57.400 --> 26:03.160 Then of course if you send the query you expect a result packet. Well actually you can have an 26:03.160 --> 26:12.280 error packet or an OK packet or actually a text result. The text result consists of a field 26:12.360 --> 26:23.720 counts and then for every field field definition and eventually a list of the actual row values. 26:27.560 --> 26:34.600 So this is what it looks like and as you can see some of the things are collapsed here. So it 26:34.600 --> 26:40.680 doesn't show you the details of the row packet. We look here at the column counts it says it just 26:40.760 --> 26:47.640 one column and then the field packet describes this one field that we have. So we know that we 26:47.640 --> 26:57.080 only expect one column. This one column has these properties and then there are actually two row 26:57.160 --> 27:12.440 packets. So one column, two rows with these values. Prepared statements. It's a 27:12.440 --> 27:19.720 room statement prepared with the query. Kind of similar to the comb query I want. But then you can 27:19.720 --> 27:27.240 back some metadata about the parameters that are used. And eventually you can then execute the 27:27.240 --> 27:36.120 prepared statement like one or more times. And when you're executing the statement or you of course 27:36.120 --> 27:45.720 have to specify the ID of the statement you're executing. So this is how this might look like. 27:46.440 --> 27:54.360 So you're connecting to a server. Preparing a query, you execute it. You might be 27:55.240 --> 28:00.840 changing the parameters that you're using for the query. You execute it again and you're disconnect. 28:02.600 --> 28:10.040 And then this is how it looks like in a wire shark. So you see the prepareer, you see the 28:10.120 --> 28:16.120 executes, et cetera. And if you go back to the previous slide, you can see that on the 28:17.560 --> 28:26.520 connection string, I have to say my score server prepare equals falls. So some of the languages 28:27.400 --> 28:33.960 both Pearl and Java have the option to do the prepareer actually in the client and not send it to the 28:33.960 --> 28:37.800 server. And then of course you will also not see it on a network protocol. 28:37.800 --> 29:06.760 Yeah, I think the protocol was already allows you to send multiple my score packets in a single 29:06.760 --> 29:15.240 TCP packet. Yeah, but then you know that that doesn't work actually. So the thing is you're sending 29:15.240 --> 29:21.240 the prepareer, then you get back to metadata that you're actually needing to check if you have the 29:21.240 --> 29:25.560 right number of parameters, et cetera. And you're then missing it. 29:37.560 --> 29:52.920 Yeah. So here's the response to the prepareer. You get back some information about the number of fields, 29:52.920 --> 29:59.800 the parameters, and some details about all the fields that are included. You can hear seated our 30:00.520 --> 30:13.160 two parameters in three fields. And this is the executes. And then you have to specify the 30:13.240 --> 30:23.000 parameters that you're sending along. I think I will skip the replication, cloning. 30:28.040 --> 30:29.400 Yeah, sorry out of time. 30:36.440 --> 30:37.240 Two questions. 30:38.120 --> 30:46.120 Since it looks like there are flags about enabling TLS or not during the handshake, how is 30:46.120 --> 30:48.120 the TLS down green attack prevented? 30:48.760 --> 30:49.320 It's not. 30:55.720 --> 30:56.040 Yeah. 30:56.040 --> 31:18.600 Yeah, I had the client capabilities in the server capabilities in one of the first slides. 31:26.440 --> 31:43.320 So you're asking about load local in file? 31:43.640 --> 31:50.600 Yeah, it's in the capability. But sometimes this flag is not set directly. 31:52.120 --> 31:57.480 I have not seen that, but it's also setting like a protocol flag. If it supports it in the 31:57.480 --> 32:04.600 protocol, it might support it in the protocol. And then you send the request and it might deny it. 32:05.960 --> 32:07.800 It might also be a permission thing for.