How to Establish Secure MySQL Connections with SSL

Previously, we discussed MySQL connections, but excluded the SSL scenario. This time, let’s look at how to connect with SSL.

Connection Process

The connection process is relatively simple: before sending the password, inform the server that we intend to use SSL.

client ->> server : connectserver ->> client : saltclient -->> server : SSL (36 bytes)client ->> server : passwordserver ->> client: OK/ERR

SSL Package

It’s the normal CLIENT_PROTOCOL_41 package’s initial part (the first 32 bytes, along with the header, making it 36 bytes in total).

Name	Size (Bytes)	Description
client_flag	4	https://cloud.tencent.com/developer/article/2243951
max_packet_size	4	Maximum packet size
character_set	1	Character set
filler	23	Padding field

Testing

This time, we’re not only connecting to the MySQL server, as it’s not interesting; we’ll simulate a MySQL server, forwarding data from the client while cloning another copy to other servers. However, for this experiment, there are no other servers, so let’s just print and look at it.

Principle

That’s roughly what it looks like.

It’s similar to before, just with added SSL support.

Usage

self.server # REAL SERVER, the actual MySQL serverself.host listening addressself.port listening portself.cert certificate used; MySQL's own or generated via OpenSSLself.key

Official certificate generation tutorial: https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html

Executing the script

python mysql_monitor.py

Confirmed SSL usage.

Note: TLS is inherited from SSL

Let’s see the traffic we printed out.

S->C : This indicates that the data packet is sent from the real server to the client (actually it’s forwarded by us)C->S : This indicates that the data packet is sent from the client to the real server (actually it’s forwarded by us)The following numbers indicate seq.seq is reset to 0 with each query, max is 255, and exceeding 255 will start again from 0 (seq %= 255)

Those interested can see the effect when seq exceeds 255.

Non-SSL connections are also possible, but we will not demonstrate them here.

The plain text visible here is because, once we read the packet, it is parsed into plain text for easier handling. If you use packet-sniffing software (tcpdump/wireshark), you will see encrypted data.

For easier observation, the MySQL connection uses 127.0.0.1, and the forwarded data is 192.168.101.21

Summary

1. Encapsulating SSL

if len(bdata) < 38: # encapsulate as SSL (32+4)    # Encapsulate client's SSL (because, in relation to the client, this is the server role)    context = ssl.create_default_context(ssl.Purpose.CLIENT_AUTH)    context.load_cert_chain(certfile=self.cert, keyfile=self.key)    conn = context.wrap_socket(conn, server_side=True)    client_rf = conn.makefile('rb')    # Encapsulate to the server's SSL    sock = ssl.wrap_socket(sock)    server_rf = sock.makefile('rb')

2. If using makefile, note that you need to remake the file after encapsulating SSL, or else it will report a broken pipe error.

Attached Script

You can customize the output, such as packet size, packet type, etc.

import structfrom threading import Threadfrom multiprocessing import Processimport socketimport timeimport sysimport ssldef btoint(bdata,t='little'):    return int.from_bytes(bdata,t)def read_pack(rf):    pack_header = rf.read(4)    if len(pack_header) < 4:        print(pack_header,' bye!')        sys.exit(2)    btrl, btrh, packet_seq = struct.unpack("C: ',btoint(bdata[3:4]),bdata)        client_rf = conn.makefile('rb')        bdata = read_pack(client_rf)        print('C->S: ',btoint(bdata[3:4]),bdata)        sock.sendall(bdata)        if len(bdata) < 38: # encapsulate as SSL (32+4)            #print('SSL')            # Encapsulate client's SSL (because, in relation to the client, this is the server role)            context = ssl.create_default_context(ssl.Purpose.CLIENT_AUTH)            context.load_cert_chain(certfile=self.cert, keyfile=self.key)            conn = context.wrap_socket(conn, server_side=True)            client_rf = conn.makefile('rb')            # Encapsulate to the server's SSL            sock = ssl.wrap_socket(sock)            server_rf = sock.makefile('rb')                t1 = Process(target=self.handler_msg,args=(client_rf,sock,'C->S: ')) # Monitor client data, then send to server side        t2 = Process(target=self.handler_msg,args=(server_rf,conn,'S->C: '))        t1.start()        t2.start()        t1.join()        t2.join()    def init(self):        socket_server = socket.socket(socket.AF_INET, socket.SOCK_STREAM)        socket_server.bind((self.host, self.port))        socket_server.listen(12345) # Set connection number        self.socket_server = socket_server        accept_client_thread = Thread(target=self.accept_client,)        accept_client_thread.start()        accept_client_thread.join()            def accept_client(self,):        while True:            conn, addr = self.socket_server.accept()            p = Process(target=self.handler,args=(conn,addr),)            p.start()aa = mmonitor()aa.init()",>