PHP step by step implementation of MySQL protocol (2) — handshake initialization

Time:2020-12-18

Interaction process

The interaction between MySQL client and server can be divided into two phases: handshake authentication phase and command execution phase.

Handshake authentication phase

In the handshake authentication stage, the client and the server establish a connection, and the interaction process is as follows:

  • Server > client: handshake initialization message
  • Client > server: login authentication message
  • Server > client: authentication result message

Command execution phase

After the client authentication is successful, it will enter the command execution phase. The interaction process is as follows:

  • Client > server: Execute command message
  • Server > client: command execution results

 

The above is the interaction process of MySQL client and server, and then combined with the actual packet capture tool to see this process first. Here, we use the PDO extension of PHP to connect to the database and execute a query statement. The packet capture situation is as follows

Next, analyze the content of each packet one by one. Before that, look at the structure of the message. The message is divided into two parts: the message header and the message body. The message header occupies a fixed 4 bytes. The length of the message body is determined by the length field in the message header. The message structure is as follows:

First, look at the message of handshake initialization, as shown in the figure:

The first three bytes4a 00 00Indicates the length of the message body, but it should be noted that the integer in the message isSmall end storage(that is, the low address is placed in the low address, and the high position is placed in the high address) mode. Therefore, if it is converted to the form we usually read, it should be00 00 4aThe decimal conversion should be 74, that is to say, the length of the message body in the message should be 74 bytes (that is, all the blue background parts except the first 4A 00 00 byte message header). Let’s take a look at what is included in the initialization information

The first byte in the message body represents the protocol version number0a The conversion to decimal is 10, so the protocol version number is 10. The implementation of converting the hexadecimal integer to decimal is as follows

The next seven bytes indicate the version number of the server, which is converted by PHP

Two parameters are specially recorded here8-bit challenge random numberand12 bit challenge random numberThese two parameters are used to authenticate the user when password encryption is used. Assemble the handshake initialization message into an object

HandleShake.php
hex_string = $hex_string;
        $this->setProtocolVersion();
        $this->setServerVersion();
        $this->setThreadId();
        $this->setSalt1();
        $this->setSalt2();
    }

    public function setProtocolVersion(){
        $this->protocol_version = UtiliHelper::HexToInt(UtiliHelper::HexSub($this->hex_string,0,1));
    }

    public function setServerVersion(){
        $this->server_version = UtiliHelper::HexToStr(UtiliHelper::HexSub($this->hex_string,1,7));
    }

    public function setThreadId(){
        $this->thread_id = UtiliHelper::HexToInt(UtiliHelper::HexSub($this->hex_string,8,4));
    }

    public function setSalt1(){
        $this->salt1 = UtiliHelper::HexSub($this->hex_string,12,8);
    }

    public function setSalt2(){
        $this->salt2 = UtiliHelper::HexSub($this->hex_string,39,12);
    }

    public function getSalt(){
        return $this->salt1.$this->salt2;
    }

}

UtilHelper.php

 

$value) {
             $send_msg .= chr(hexdec($value));
        }
        return $send_msg;
    }

    /**
     *Hexadecimal to integer
     * @author gphper 20200908
     * @param $hex_string
     * @return float|int
     */
    public static function HexToInt($hex_string){
        $z = implode("", array_reverse(str_split($hex_string,2)));
        return hexdec($z);
    }

    /**
     *Hexadecimal truncation
     * @author gphper 20200908
     * @param $hex_str
     * @param $start
     * @param $length
     * @return bool|string
     */
    public static function HexSub($hex_str,$start,$length=0){
        if($length){
            return substr($hex_str, $start*2,$length*2);
        }
        return substr($hex_str, $start*2);
    }

    /**
     *Segment hexadecimal message body
     * @author gphper 20200908
     * @param $hex_str
     * @param int $is_body
     * @param array $all
     * @return array
     */
    public static function HexSplit($hex_str,$is_body=0,$all=[]){
        //Get the top three first
        $length = self::HexToInt(self::HexSub($hex_str,0,3));
        $total_length = $length + 4;
        $start = $is_body*8;
        $pre_str = substr($hex_str,$start,$total_length*2-$start);
        $sub_str = substr($hex_str,$total_length*2);
        $all = array_merge($all,array($pre_str));
        if($sub_str){
            return self::HexSplit($sub_str,$is_body,$all);
        }
        return $all;
    }

    /**
     *Decimal to hexadecimal small end storage
     * @author gphper 20200908
     * @param $length
     * @return string
     */
    public static function IntToHex($length){
        $big = str_pad(dechex($length),6,0,STR_PAD_LEFT);
        return implode("",array_reverse(str_split($big,2)));
    }

    /**
     *Character to hexadecimal
     * @author gphper 20200908
     * @param $string
     * @return string
     */
    public static function StrToHex($string){
        $length = strlen($string);
        $hex = "";
        for ($i = 0; $i

 

index.php

Generated object

class PHPMysql\MysqlPacket\HandleShake#3 (6) {
  private $hex_string =>
  string(148) "0a352e372e31360024000000232e200b147b397f00fff7080200ff811500000000000000000000735c762246383411280b5b73006d7973716c5f6e61746976655f70617373776f726400"
  private $protocol_version =>
  int(10)
  private $server_version =>
  string(7) "5.7.16
class PHPMysql\MysqlPacket\HandleShake#3 (6) {
private $hex_string =>
string(148) "0a352e372e31360024000000232e200b147b397f00fff7080200ff811500000000000000000000735c762246383411280b5b73006d7973716c5f6e61746976655f70617373776f726400"
private $protocol_version =>
int(10)
private $server_version =>
string(7) "5.7.16\000"
private $thread_id =>
int(36)
private $salt1 =>
string(16) "232e200b147b397f"
private $salt2 =>
string(24) "735c762246383411280b5b73"
}
0" private $thread_id => int(36) private $salt1 => string(16) "232e200b147b397f" private $salt2 => string(24) "735c762246383411280b5b73" }

 

Code sharing address

   https://github.com/gphper/PHPMysql

Reference articles:

  https://www.cnblogs.com/davygeek/p/5647175.html