总体交互过程

交互示意

登录流程

  • TCP连接建立
1
2
3
4
5

# Connection.connection 
sock = socket.create_connection((host, port),connection_timeout)
sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_NODELAY, 1)                       
sock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
_rfile = _makefile(sock, 'rb')
  • Server向Client发送Handshake packet：包含server版本信息， 随机挑战数等
  • Client与Server发送Auth packet
  • Server向Client发送OK packet或者ERR packet

认证细节

  • CHAP协议

    1. the server sends the random scramble to the client.
    2. client sends the encrypted password back to the server.
    3. the server checks the password.

  • 客户端加密

    1. stage1 = sha1(password)
    2. crypt = (sha1(scramble + sha1(stage1))) ^ stage1
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

def scramble_native_password(password, message):
    # password为明文密码
    # message即random scramble
    stage1 = sha1_new(password).digest()
    stage2 = sha1_new(stage1).digest()
    s = sha1_new()
    s.update(message[:SCRAMBLE_LENGTH])
    s.update(stage2)
    result = s.digest()
    return _my_crypt(result, stage1)

def _my_crypt(message1, message2):
    result = bytearray(message1)
    for i in range(len(result)):
        result[i] ^= message2[i]
    return bytes(result)

  • 服务端验证 (基于异或的自反性： A ^ B ^ A = B)

    • stage1’ = crypt ^ (sha1(scramble + mysql.user.password))
    • sha1(stage1’) == mysql.user.password

    注：即使mysql.user.password泄露也无法认证，因为不知道stage1

服务端数据包

  • 组成
    • head(4 byte)
      • payload_length (3 byte)
      • sequense_id (1 byte)
    • payload
  • 最大长度（2 ** 24 - 1）,payload等于最大长度时，会拆成多个包，即最后一个包长度总小于最大长度
  • head 解析
1
2
3
4
5

# Connection._read_packet
packet_header = self._read_bytes(4)        
btrl, btrh, packet_number = struct.unpack('<HBB', packet_header)
bytes_to_read = btrl + (btrh << 16)

      注：strucut没有3 byte的类型，故需要分别读H(2 byte)和B(1 byte),然后进行拼接, 小端编码低位在前，所以实际值为 H + B « 16

  • 包的种类：
    • OK: header = 0 and length of packet > 7
    • EOF(deprecated since 5.7.5): header = 0xfe and length of packet < 9
    • ERROR: header = 0xff
    • Result Set: header in [1, 250]

客户端数据包

  • 组成
    • head(4 byte)
      • payload_length (3 byte)
      • sequense_id (1 byte)
    • payload
      • command(1 byte)
      • arguments
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

  # Connection._execute_command
  packet_size = min(MAX_PACKET_LEN, len(sql) + 1)  # +1 is for command

  prelude = struct.pack('<iB', packet_size, command)
  packet = prelude + sql[:packet_size-1]
  self._write_bytes(packet)
  self._next_seq_id = 1
  if packet_size < MAX_PACKET_LEN:
      return
  sql = sql[packet_size-1:]
  while True:
      packet_size = min(MAX_PACKET_LEN, len(sql))
      self.write_packet(sql[:packet_size])
      sql = sql[packet_size:]
      if not sql and packet_size < MAX_PACKET_LEN:
          break

cursor

  • Cursor： 默认curosr
  • SSCursor: 不预先将结果一次性全部读入缓存中, 适用于获取大数据时

参考