The content of this article is collated and validated according to the content of mysql official documents (all pictures are taken from the official website)
The main contents of mysql communication co-connection stage include:
Exchange information between client and server
If the client has set up SSL, set up the SSL communication channel
-
The server authenticates the user according to the data returned by the client
When the client starts to connect to the server, the server can send ERR_Packet to complete the handshake or send the data packet needed by the client for the initial handshake. At this stage, the client can also request an SSL connection and establish an SSL communication channel before authentication.
At the first handshake, the server sends a method for validation. The client assembles and returns the data package according to the received information. The whole connection process lasts until the server returns ERR_Packet or OK_Packet. The whole process is shown in the following figure (from the official website):
First handshake:
The initial handshake starts with the server sending Protocol::Handshake packet. After that, the client can request to use Protocol::SSLRequest package to establish an SSL connection, or the client can send Protocol::Handshake Response package directly. The following two connection modes are described separately.
ssl connection:
-
The server sends Protocol::Handshake packet
Client Response Protocol::SSLRequest Package
Server Setting up ssl Channel
Client sends Protocol::HandshakeResponse packet
Ordinary connection:
-
The server sends Protocol::Handshake packet
-
Client sends Protocol::HandshakeResponse packet
Of course, handshake is not always smooth, and there will be various situations on the way, such as authentication failure, password verification method does not meet, customer service did not return to the verification method as required, etc. Here only describes the error of password verification method, want to see the official documents in detail:
-
Client Connects to Server
The server sends Protocol::Handshake
Client returns Protocol::HandshakeResponse
The server sends Protocol::AuthSwitchRequest to tell the client that it needs to switch to a new authentication method.
Clients and servers may exchange more data packets according to the method of client authentication.
Server sends OK_Packet or ERR_Packet packets
It can be seen that this situation is two more steps than the one-step connection between Protocol::AuthSwitchRequest and the client to reply to the packet. Of course, this exception or other anomalies may continue to occur in this case. We need to continue exchanging data packets. Let's look at Protocol::Handshake and Procool:: HandshakeResponse two data packets. All packages in mysql communication protocol will have 4 bytes head, the first 3 bytes record the size of the packages, the order id of the 1bytes record the packages at the fourth position, and the protocol interaction between the client and the server until the end. The id of all the packages on the way increases sequentially, so the structure described below does not include these 4 bytes. Bytes need to understand:
Protocol::Handshake:
In the figure, we can clearly see that the package contains protocol version, mysql version, current connection id, default character set and some information that needs to be exchanged. The content of password verification method is divided into two parts, which may be for security reasons....
Protocol::HandshakeResponse:
Response packages include Protocol:: HandshakeResponse 320 and Protocol:: HandshakeResponse 41, because Protocol:: HandshakeResponse 320 is a protocol pack of version 4.1 below, so only focus on the content of Protocol:: HandshakeResponse 41 package, after all, the mainstream of Mysql is now 5.7, more detailed content is to see the official documents. After all, the space is limited. Here we mainly talk about the encryption method of password. The default password authentication method of 8.0 is caching_sha2_password. We haven't had time to study it carefully. Here we talk about the commonly used encryption method: mysql_navicat_password:
As you can see above, Protocol::Handshake can send auth-plugin-data in two parts, which can encrypt user passwords in the following ways:
SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )
If the server does not send auth-plugin-data data data, only SHA1(password) will be used. If the server sends this part of data, the password will be encrypted with this part of data, so that the sniffer tool can not directly see the password and play a certain role in security.
The establishment of connection in mysql protocol is basically all about this. It still needs to be verified by oneself. Here is a small script. Look at the code directly. It only takes routine steps. Various exceptions in the process are not handled. For reference only:
#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@author: xiao cai niao
'''
import struct,sys
from socket import *
from contextlib import closing
import hashlib,os
from functools import partial
sha1_new = partial(hashlib.new, 'sha1')
SHA1_HASH_SIZE = 20
MULTI_RESULTS = 1 << 17
SECURE_CONNECTION = 1 << 15
CLIENT_PLUGIN_AUTH = 1 << 19
CLIENT_CONNECT_ATTRS = 1<< 20
CLIENT_PROTOCOL_41 = 1 << 9
CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA = 1<<21
LONG_PASSWORD = 1
LONG_FLAG = 1 << 2
PROTOCOL_41 = 1 << 9
TRANSACTIONS = 1 << 13
CAPABILITIES = (
LONG_PASSWORD | LONG_FLAG | PROTOCOL_41 | TRANSACTIONS
| SECURE_CONNECTION | MULTI_RESULTS
| CLIENT_PLUGIN_AUTH | CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA | CLIENT_CONNECT_ATTRS)
CLIENT_CONNECT_WITH_DB = 9
max_packet_size = 2 ** 24 - 1
charset_id = 45
class TcpClient:
def __init__(self,host_content,user_name,password,databases):
_host_content = host_content.split(':')
self.user = user_name
self.password = password
self.database = databases
HOST = _host_content[0]
PORT = int(_host_content[1])
self.BUFSIZ = 1024
self.ADDR = (HOST, PORT)
self.client=socket(AF_INET, SOCK_STREAM)
self.client.connect(self.ADDR)
self.client.settimeout(1)
self.server_packet_info = {}
self.packet = None
def header(self):
self.offset = 0
self.payload_length = self.packet[2] << 16 | self.packet[1] << 8 | self.packet[0]
self.sequence_id = self.packet[3]
self.offset += 4
def check_packet(self):
self.header()
packet_header = self.packet[self.offset]
self.offset += 1
if packet_header == 0x00:
print('connection ok')
elif packet_header in (0xfe,0xff):
print(self.packet[self.offset:])
def Send(self):
self.packet=self.client.recv(self.BUFSIZ)
self.header()
self.__read_server_info()
self.__handshakeresponsepacket()
response_payload = len(self.response_packet)
self.client.send(struct.pack('<I',response_payload)[:3] + struct.pack('!B',1) + self.response_packet)
self.packet = self.client.recv(self.BUFSIZ)
self.header()
packet_header = self.packet[self.offset]
self.offset += 1
if packet_header == 0xff:
error_code = struct.unpack('<H', self.packet[self.offset:self.offset + 2])
self.offset+= 2
print(error_code,self.packet[self.offset:])
elif packet_header == 0xfe:
"""AuthSwitchRequest"""
_data = self.__authswitchrequest()
self.client.send(struct.pack('<I', len(_data))[:3] + struct.pack('!B', 3) + _data)
self.packet = self.client.recv(self.BUFSIZ)
self.check_packet()
elif packet_header in (0x00,0xfe):
if self.payload_length > 7:
print('ok packet')
elif self.payload_length < 9:
print('error packet')
print(self.server_packet_info)
"""Stay here for a while, stay in mysql Check if the connection is working"""
import time
time.sleep(1000)
def __authswitchrequest(self):
end_pos = self.packet.find(b'\0', self.offset)
auth_name = self.packet[self.offset:end_pos].decode()
self.offset = end_pos + 1
auth_plugin_data = self.packet[self.offset:]
if self.server_packet_info['capability_flags'] & CLIENT_PLUGIN_AUTH and auth_name:
data = self.__sha1_password(auth_plugin_data)
return data
def __read_server_info(self):
PLUGIN_AUTH = 1 << 19
#Packet content
self.server_packet_info['packet_header'] = self.packet[self.offset]
self.offset += 1
_s_end = self.packet.find(b'\0', self.offset)
self.server_packet_info['server_version'] = self.packet[self.offset:_s_end]
self.offset = _s_end + 1
self.server_packet_info['thread_id'] = struct.unpack('<I',self.packet[self.offset:self.offset+4])
self.offset += 4
self.server_packet_info['auth_plugin_data'] = self.packet[self.offset:self.offset+8]
self.offset += 8 + 1
self.server_packet_info['capability_flags'] = struct.unpack('<H',self.packet[self.offset:self.offset+2])[0]
self.offset += 2
self.server_packet_info['character_set_id'],\
self.server_packet_info['status_flags'],\
capability_flags_2,auth_plugin_data_len = struct.unpack('<BHHB',self.packet[self.offset:self.offset+6])
self.server_packet_info['capability_flags'] |= capability_flags_2 << 16
self.offset += 6
self.offset += 10
auth_plugin_data_len = max(13,auth_plugin_data_len-8)
if len(self.packet) - 4 >= self.offset + auth_plugin_data_len:
# salt_len includes auth_plugin_data_part_1 and filler
self.server_packet_info['auth_plugin_data'] += self.packet[self.offset:self.offset + auth_plugin_data_len]
self.offset += auth_plugin_data_len
if self.server_packet_info['capability_flags'] & PLUGIN_AUTH and len(self.packet) - 4 >= self.offset:
_s_end = self.packet.find(b'\0',self.offset)
self.server_packet_info['auth_plugin_name'] = self.packet[self.offset:_s_end]
def __handshakeresponsepacket(self):
client_flag = 0
client_flag |= CAPABILITIES
if self.database:
client_flag |= CLIENT_CONNECT_WITH_DB
server_version = (self.server_packet_info['server_version']).decode()
if int(server_version.split('.', 1)[0]) >= 5:
client_flag |= MULTI_RESULTS
self.response_packet = struct.pack('<iIB23s',client_flag,max_packet_size,charset_id,b'')
self.response_packet += self.user.encode() + b'\0'
sha1_password = self.__sha1_password()
if self.server_packet_info['capability_flags'] & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA:
self.response_packet += struct.pack('!B',len(sha1_password)) + sha1_password
elif self.server_packet_info['capability_flags'] & SECURE_CONNECTION:
self.response_packet += struct.pack('B',len(sha1_password)) + sha1_password
else:
self.response_packet += sha1_password + b'\0'
if self.server_packet_info['capability_flags'] & CLIENT_CONNECT_WITH_DB:
self.response_packet += self.database.encode() + b'\0'
if self.server_packet_info['capability_flags'] & CLIENT_PLUGIN_AUTH:
self.response_packet += b'' + b'\0'
if self.server_packet_info['capability_flags'] & CLIENT_CONNECT_ATTRS:
_connect_attrs = {
'_client_name': 'pymysql',
'_pid': str(os.getpid()),
'_client_version': '3.6.5',
'program_name' : sys.argv[0]
}
connect_attrs = b''
for k, v in _connect_attrs.items():
k = k.encode('utf8')
connect_attrs += struct.pack('B', len(k)) + k
v = v.encode('utf8')
connect_attrs += struct.pack('B', len(v)) + v
self.response_packet += struct.pack('B', len(connect_attrs)) + connect_attrs
def __sha1_password(self,auth_plugin_data=None):
_pass1 = sha1_new(self.password.encode()).digest()
_pass2 = sha1_new(_pass1).digest()
s = sha1_new()
if auth_plugin_data is None:
s.update(self.server_packet_info['auth_plugin_data'][:SHA1_HASH_SIZE])
else:
s.update(auth_plugin_data[:SHA1_HASH_SIZE])
s.update(_pass2)
t = bytearray(s.digest())
for i in range(len(t)):
t[i] ^= _pass1[i]
return t
def close(self):
self.client.close()
with closing(TcpClient('192.168.10.12:3306','root','root','sys')) as tcpclient:
tcpclient.Send()Technical Exchange Q Group (479472450) and Personal Public Number will share their learning results from time to time:
