UNDERSTANDING MySQL CLIENT / SERVER PROTOCOL USING PYTHON AND WIRESHARK – PART 2

In the previous article we researched MySQL Client / Server Protocol using WireShark. Now lets start to write our codes in python to simulate MySQL native client. Ready codes are here: Github repo

First of all we have to create MYSQL_PACKAGE class. MYSQL_PACKAGE class is the parent of all other package classes (HANDSHAKE_PACKAGE, LOGIN_PACKAGE, OK_PACKAGE and etc.)

class MYSQL_PACKAGE:
“””Data between client and server is exchanged in packages of max 16MByte size.”””
   def __init__(self, resp= b”):
      self.resp = resp
      self.start =0
      self.end =0
It accepts resp parameter on initialization. Resp is the binary response received from the server in bytesarray type. One of the important and interesting method of this class is next method.
def next(self, length = None, type=int, byteorder=’little’, signed=False, freeze=False):
   if not freeze:
      if length:
         self.end += length
         portion = self.resp[self.start:self.end]
         self.start =self.end
      else:
         portion = self.resp[self.start:]
         self.start =self.end =0
   else:
      if length:
         portion = self.resp[self.start:self.start + length]
      else:
         portion = self.resp[self.start:]
   if type is int:
      return int.from_bytes(portion, byteorder=byteorder, signed=signed)
   elif type is str:
      return portion.decode(‘utf-8’)
   elif type is hex:
      return portion.hex()
   else:
      return portion
Method next reads portion of bytes from the binary response. When we call this method it reads some portion of bytes and put pointer to the last position where reading was ended (changes a value of self.start and self.end properties). When we call this method again, it starts to read bytes where stayed last time.
Method next accepts five parameters: length, type, byteorder, signed and freeze. If freeze is True it reads some portion of bytes from binary response but does not change pointer position. Otherwise it reads portion of bytes with given length and change the position of pointer. If length is None then method reads bytes until the end of response bytesarray. Parameter type can be int, str and hex datatypes. Method next converts portion of bytes to appropriate datatype according to the value of type parameter.
Parameter byteorder determines conversion of bytes to integer type. It ups to your architecture of computer. If your machine is big-endian then it stores bytes in memory from big address to the little. Or if your machine is little-endian then it stores bytes in memory from little address to the big. Thats why we have to know exact type of our architecture to be able to convert bytes to integer correctly. In my case it is little-endian, thats why i’ve set the default value of byteorder parameter to “little”.
Parameter signed is also used in conversion of bytes to integer. We tell the function to consider each intereg as unsigned or signed.
Second interesting method of this class is encrypt_password. This method encrypts password with given algorithm.
from hashlib import sha1
def encrypt_password(self, salt, password):
   bytes1 = sha1(password.encode(“utf-8”)).digest()
   concat1 = salt.encode(‘utf-8’)
   concat2 = sha1(sha1(password.encode(“utf-8”)).digest()).digest()
   bytes2 = bytearray()
   bytes2.extend(concat1)
   bytes2.extend(concat2)
   bytes2 = sha1(bytes2).digest()
   hash=bytearray(x ^ y for x, y in zip(bytes1, bytes2))
   return hash
This method accepts two parameters: salt and password. Parameter salt is the concatenation of two salt1 and salt2 strings from Greeting Packet received from server. And parameter password is the password string of mysql user.
In the official documentation password encryption algorithm is:
password_encrypt_algorithm
Here “20-bytes random data from server” is concatenation of salt1 and salt2 from Greeting Packet received from server. To remember what is greeting packet look at the previous article
Now I want to explain encrypt_password method line by line.
bytes1 = sha1(password.encode(“utf-8”)).digest()
We are converting password string to bytes, then encrypting it with sha1 function and assigning to bytes1 variable. It is equal to this part of algorithm:
password_encrypt_algorithm1
Then we are converting salt string into bytes and assigning to the concat1 variable.
concat1 = salt.encode(‘utf-8’)
password_encrypt_algorithm5
Third line of the method is:
concat2 = sha1(sha1(password.encode(“utf-8”)).digest()).digest()
password_encrypt_algorithm2
Here we are double-encrypting password string with sha1 function and assign it to the concat2 string.
Now we have two concat1 and concat2 variables. We have to concatenate them into one byte array:
bytes2 = bytearray()
bytes2.extend(concat1)
bytes2.extend(concat2)
password_encrypt_algorithm6
Then we have to encrypt concatenated bytes with sha1 function and assign to the bytes2 variable.
bytes2 = sha1(bytes2).digest()
password_encrypt_algorithm3
So we have two variables with encrypted bytes: bytes1 and bytes2. Now we have to do bitwise XOR operation between this variables and return the obtained hash.
hash=bytearray(x ^ y for x, y in zip(bytes1, bytes2))
return hash
password_encrypt_algorithm4

CLASSES FOR DATATYPES

In the previous article we learned about Int and String data types of MySQL Client / Server protocol. Now we need some classes to be able to read fields from received packets.

INT CLASS

class Int:
def __init__(self, package, length=-1, type=’fix’):
   self.package = package
   self.length = length
   self.type =type
def next(self):
   # int<n>
   if self.type == ‘fix’ and self.length > 0:
      return self.package.next(self.length)
   # int<lenenc>
   if self.type == ‘lenenc’:
      byte = self.package.next(1)
   if byte < 0xfb:
      return self.package.next(1)
   elif byte == 0xfc:
      return self.package.next(2)
   elif byte == 0xfd:
      return self.package.next(3)
   elif byte == 0xfe:
      return self.package.next(8)
Int class implements INT data type of MySQL Client / Server protocol. It accepts package parameter on initialization. Parameter package should be the instance of any package class inherited from MYSQL_PACKAGE class. Method next detects the type of integer (int<fix> or int<lenenc>. See previous article) and calls the next method of package object to read the byte portion of received response.

STR CLASS

class Str:
   def __init__(self, package, length=-1, type=”fix”):
      self.package = package
      self.length = length
      self.type =type
   def next(self):
      # string<fix>
      if self.type == ‘fix’ and self.length > 0:
         return self.package.next(self.length, str)
      # string<lenenc>
      elif self.type == ‘lenenc’:
         length = self.package.next(1)
         if length == 0x00:
            return “”
         elif length == 0xfb:
            return “NULL”
         elif length == 0xff:
            return “undefined”
         return self.package.next(length, str)
         # string<var>
      elif self.type == ‘var’:
         length = Int(self.package, type=’lenenc’).next()
         return self.package.next(length, str)
      # string<eof>
      elif self.type == ‘eof’:
         return self.package.next(type=str)
      # string<null> – null terminated strings
      elif self.type == ‘null’:
         strbytes = bytearray()
      byte = self.package.next(1)
      while True:
         if byte == 0x00:
            break
         else:
            strbytes.append(byte)
            byte = self.package.next(1)
      return strbytes.decode(‘utf-8’)
Str class implements STRING data type of MySQL Client / Server protocol. It accepts package parameter on initialization. Parameter package should be the instance of any package class inherited from MYSQL_PACKAGE class. Method next detects the type of String (String<fix>, String<Var>, String<NULL>, String<EOF> or String<lenenc>. See previous article) and calls the next method of package object to read the byte portion of received response.

HANDSHAKE_PACKAGE CLASS

HANDSHAKE_PACKAGE class is used for parse Greeting Packet received from server. It is inherited from MYSQL_PACKAGE class and accepts resp parameter on initialization. Parameter resp is the Greeting Packet response in bytes type recieved from the server.

class HANDSHAKE_PACKAGE(MYSQL_PACKAGE):
   def __init__(self, resp):
      super().__init__(resp)
def parse(self):
   return {
      “package_name”: “HANDSHAKE_PACKAGE”,
      “package_length”: Int(self, 3).next(), #self.next(3),
      “package_number”: Int(self, 1).next(), #self.next(1),
      “protocol”: Int(self, 1).next(), #self.next(1),
      “server_version”: Str(self, type=’null’).next(),
      “connection_id”: Int(self, 4).next(), #self.next(4),
      “salt1”: Str(self, type=’null’).next(),
      “server_capabilities”: self.get_server_capabilities(Int(self, 2).next()),
      “server_language”: self.get_character_set(Int(self, 1).next()),
      “server_status”: self.get_server_status(Int(self, 2).next()),
      “server_extended_capabilities”: self.get_server_extended_capabilities(Int(self, 2).next()),
      “authentication_plugin_length”: Int(self, 1).next(),
      “unused”: Int(self, 10).next(), #self.next(10, hex),
      “salt2”: Str(self, type=’null’).next(),
      “authentication_plugin”: Str(self, type=’eof’).next()
}
Method parse reading fields from the response using Int and Str classes and puts them into a dictionary and returns.

LOGIN_PACKAGE CLASS

This class is used for create Login Request packet.

class LOGIN_PACKAGE(MYSQL_PACKAGE):
   def __init__(self, handshake):
      super().__init__()
      self.handshake_info = handshake.parse()
   def create_package(self, user, password, package_number):
      package = bytearray()
      # client capabilities
      package.extend(self.capabilities_2_bytes(self.client_capabilities))
      # extended client capabilities
      package.extend(self.capabilities_2_bytes(self.extended_client_capabilities))
      # max package -> 16777216
      max_package = (16777216).to_bytes(4, byteorder=’little’)
      package.extend(max_package)
      # charset -> 33 (utf8_general_ci)
      package.append(33)
      # 23 bytes are reserved
      reserved = (0).to_bytes(23, byteorder=’little’)
      package.extend(reserved)
      # username (null byte end)
      package.extend(user.encode(‘utf-8’))
      package.append(0)
      # password
      salt = self.handshake_info[‘salt1’] + self.handshake_info[‘salt2’]
      encrypted_password = self.encrypt_password(salt.strip(), password)
      length = len(encrypted_password)
      package.append(length)
      package.extend(encrypted_password)
      # authentication plugin
      plugin = self.handshake_info[‘authentication_plugin’].encode(‘utf-8’)
      package.extend(plugin)
      finpack = bytearray()
      package_length = len(package)
      finpack.append(package_length)
      finpack.extend((0).to_bytes(2, byteorder=’little’))
      finpack.append(package_number)
      finpack.extend(package)
      return finpack
This class accepts handshake parameter on initialization. Parameter handshake should be the instance of HANDSHAKE_PACKAGE class. In the __init__ method we call the parse method of handshake object and get all fields of Greeting Packet received from the server.
Method create_package prepares the login request package to be able to send to the server for authentication. Accepts user, password and packet_number parameters.

OK_PACKAGE & ERR_PACKAGE CLASSES

OK package and ERR package are the response package of server after authentication or after sending query to server on command phase.

class OK_PACKAGE(MYSQL_PACKAGE):
   def __init__(self, resp):
      super().__init__(resp)
   def parse(self):
      return {
         “package_name”: “OK_PACKAGE”,
         “package_length”: Int(self, 3).next(), #self.next(3),
         “package_number”: Int(self, 1).next(), #self.next(1),
         “header”: hex(Int(self, 1).next()),
         “affected_rows”: Int(self, 1).next(), #self.next(1),
         “last_insert_id”: Int(self, 1).next(), #self.next(1),
         “server_status”: self.get_server_status(Int(self, 2).next()),
         “warnings”: Int(self, 2).next()
      }
class ERR_PACKAGE(MYSQL_PACKAGE):
   def__init__(self, resp):
      super().__init__(resp)
   def parse(self):
      return {
         “package_name”: “ERR_PACKAGE”,
         “package_length”: Int(self, 3).next(), #self.next(3),
         “package_number”: Int(self, 1).next(), #self.next(1),
         “header”: hex(Int(self, 1).next()), #self.next(1, hex),
         “error_code”: Int(self, 2).next(), #self.next(2),
         “sql_state”: Str(self, 6).next(),
         “error_message”: Str(self, type=’eof’).next()
      }

MYSQL CLASS

MYSQL class is the wrapper class which creates TCP connection with server, sends and receives packages from server using above classes.

from socket import AF_INET, SOCK_STREAM, socket, gethostbyname
from modules.packages import *
class MySQL:
   def __init__(self, host=””, port=””, user=””, password=””):
      self.host = host
      self.port = port
      self.user = user
      self.password = password
   def connect(self):
      resp = self.client.recv(65536)
      return HANDSHAKE_PACKAGE(resp)
   def login(self, handshake_package, package_number):
   “””Sending Authentication package”””
      login_package = LOGIN_PACKAGE(handshake_package)
      package = login_package.create_package(user=self.user, password=self.password, package_number=package_number)
      self.client.sendall(package)
      resp = self.client.recv(65536)
      package = self.detect_package(resp)
      return package.parse()[‘package_number’]
   def __enter__(self):
      self.client = socket(AF_INET, SOCK_STREAM)
      ip = gethostbyname(self.host)
      address=(ip,int(self.port))
      self.client.connect(address)
      return self
   def __exit__(self, exc_type, exc_value, traceback):
      print(“Good Bye!”)
      self.close()
   def close(self):
      self.client.close()
I think nothing is not unclear in this class. I’ve defined __enter__ and __exit__ to be able to use this class with “with” statement to automatically close TCP connection with server. In __enter__ method i’m creating TCP connection over socket. And in __exit__ method i’m closing created connection. This class accepts host, port, user and password parameters on initialization.
In the connect method we receive greeting packet from server:
resp = self.client.recv(65536)
return HANDSHAKE_PACKAGE(resp)
In the login method we create Login request package using LOGIN_PACKAGE and HANDSHAKE_PACKAGE classes and sends to the server and gets OK or ERR packages.
That’s all. We’ve implemented connection phase. To avoid be this article too long I will not explain command phase. Because command phase is more easer that connection phase and you can research it yourself with accumulated knowledge from this and previous articles.
Also you can get already implemented command phase codes from this Github repo and research: https://github.com/elshadaghazade/techacademy_mysql_native_client_in_python
Demo Video: 
Thank you!

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569