MySQLユーザーのためのMySQLプロトコル入門#2

2014/10/31

chobi_e

前回の記事ではInitial PacketまでParseしました。今回はAuth Response Packetを作って認証までやってみましょう

Handshake Response Packet

認証の一連の流れはhttp://dev.mysql.com/doc/internals/en/connection-phase.htmlに書いてあるので図をさらっと眺めつつ行きます。

ServerからInitial Packetを受け取った後に、ClientがHandshake Response Packetを作ってServerに送信すれば認証結果が返されます。

毎度のごとくdev.mysql.comからデータの定義の説明を参照するんですが、if文が入っていて分かりづらいので認証を通すために必要な部分だけを抜粋してみました:

4              capability flags, CLIENT_PROTOCOL_41 always set
4              max-packet size
1              character set
string[23]     reserved (all [0])
string[NUL]    username
1              length of auth-response
string[n]      auth-response
string[NUL]    auth plugin name

4 capability flags, CLIENT_PROTOCOL_41 always set

4 max-packet size

1 character set

string[23] reserved (all [0])

string[NUL] username

1 length of auth-response

string[n] auth-response

string[NUL] auth plugin name

認証さえとおせればいいのでこれだけ。Capability FlagsはCLIENT_PROTOCOL_41, CLIENT_SECURE_CONNECTION, CLIENT_LONG_PASSWORD, CLIENT_TRANSACTIONS, CLIENT_LONG_FLAGあたりをセットし、その他のFlagはとりあえず横においておきましょう。

Capability Flagsが決まればHandShake Packetのデータサイズが決まるので、bufferを確保してデータを積んで行きます。が、そもそもauth-responseってのがデータサイズよくわかりませんね。何でしょう、コレ？

1              length of auth-response
string[n]      auth-response

1 2	1 length of auth-response string[n] auth-response

auth-responseをどうすればよいかは実はServerから最初に送られてくるConnection Phase Packet中のcapability flagsとPlugin名の指定があるのでそれを参照します。
通常の場合mysql_native_passwordとCLIENT_SECURE_CONNECTIONを使え、と指示がされているので
CLIENT_SECURE_CONNECTIONを調べてみたところ、Secure Password Authenticationを使え、ということなのでとりあえずやってみましょう。

Secure Connection

Secure Password Authenticationの場合random dataはInitial Packet中のauth-plugin-data-part-1とauth-plugin-data-part-2の12byteを連結した値となるので前回parseした部分からデータを持ってきます。（auth_plugin_data_part2の最後のデータはゴミデータなので12byte分だけ連結させます）

  random_data := append(auth_plugin_data_part1, auth_plugin_data_part2[0:12]...)

1	random_data := append(auth_plugin_data_part1, auth_plugin_data_part2[0:12]...)

生成するhash値は下記の通りで生成できます。passwordのsha1 hashデータの各値に対してsha1(random_data + sha1(sha1(password)))して生成したデータをXORしていけば出来上がりです。

sha1(password) XOR sha1(random_data + sha1(sha1(password)))

1	sha1(password) XOR sha1(random_data + sha1(sha1(password)))

これでauth-response-dataにつっこむhash値もできました。あとはbufferの確保をしてたんたんとデータを積んでいくだけです。

MySQL Packetの書き込みから通信まで

MySQL PacketのHeaderは4byte(uint24 length, uint8 sequence_id)というのは前回説明しました。書き込み時は読み込み時と同じようにpayloadの先頭にヘッダを置けばOKです。

断片的なコードで説明しても分かりづらいので、Goで一連の流れを書いてあるのでこれで認証が成功するかやってみましょう。

package main

import (
  "fmt"
  "encoding/hex"
  "encoding/binary"
  "bytes"
  "net"
  "bufio"
  "io"
  "crypto/sha1"
)

var username = "chobie"
var password = "chobie"

type CapabilityFlag int

var (
  CLIENT_LONG_PASSWORD CapabilityFlag = 0x0001
  CLIENT_FOUND_ROWS CapabilityFlag = 0x0002
  CLIENT_LONG_FLAG CapabilityFlag = 0x0004
  CLIENT_CONNECT_WITH_DB CapabilityFlag = 0x0008
  CLIENT_NO_SCHEMA CapabilityFlag = 0x0010
  CLIENT_COMPRESS CapabilityFlag = 0x0020
  CLIENT_ODBC CapabilityFlag = 0x0040
  CLIENT_LOCAL_FILES CapabilityFlag = 0x0080
  CLIENT_IGNORE_SPACE CapabilityFlag = 0x0100
  CLIENT_PROTOCOL_41 CapabilityFlag = 0x0200
  CLIENT_INTERACTIVE CapabilityFlag = 0x0400
  CLIENT_SSL CapabilityFlag = 0x0800
  CLIENT_IGNORE_SIGPIPE CapabilityFlag = 0x1000
  CLIENT_TRANSACTIONS CapabilityFlag = 0x2000
  CLIENT_RESERVED CapabilityFlag = 0x4000
  CLIENT_SECURE_CONNECTION CapabilityFlag = 0x8000
  CLIENT_PLUGIN_AUTH CapabilityFlag   = 0x00080000
  CLIENT_SESSION_TRACK CapabilityFlag = 0x00800000
)
<br />

func read_packet(reader io.Reader) (byte, byte) {
  header := make([]byte, 4)
  reader.Read(header)

  payload_size := uint32(header[0])
  payload_size += uint32(header[1] >> 8)
  payload_size += uint32(header[2] >> 16)
  payload := make([]byte, payload_size)

  reader.Read(payload)
  return header, payload
}

func sha1_sum(data byte) byte{
  h := sha1.New()
  io.Copy(h, bytes.NewReader(data))

  return h.Sum(nil)
}

func main() {
  //READ INITIAL PACKET
  conn, err := net.Dial("tcp", "localhost:3306")
  reader := bufio.NewReader(conn)
  if err != nil {
    panic(err)
  }
  header, payload := read_packet(reader)
  fmt.Printf("[header]:\n%s\n[payload]:\n%s\n", hex.Dump(header), hex.Dump(payload))

  // INITIAL Packetのparse。前回と内容同じなのでよまなくて大丈夫
  offset := 0
  protocol_version := int(payload[offset])
  fmt.Printf("  [protocol_version]: %d\n", protocol_version)
  offset++

  fmt.Printf("  [Initial Handshake Packet]:\n")
  idx := bytes.IndexByte(payload[offset:], 0x00)
  version := payload[offset:offset+idx]
  fmt.Printf("    [version]: %s\n", version)
  offset += idx+1

  connectionId := binary.LittleEndian.Uint32(payload[offset:offset+4])
  fmt.Printf("    [connectionId]: %d\n", connectionId)
  offset += 4

  auth_plugin_data_part1 := payload[offset:offset+8]
  fmt.Printf("    [auth_plugin_data_part1]: \n")
  fmt.Printf("    %s", hex.Dump(auth_plugin_data_part1))
  offset += 8

  filter1 := payload[offset:offset+1]
  fmt.Printf("    [filter1]: %s\n", filter1)
  offset += 1

  capability_lower := payload[offset:offset+2]
  offset += 2

  charset := uint8(payload[offset])
  fmt.Printf("    [charset]: %d\n", charset)
  offset++

  status := binary.LittleEndian.Uint16(payload[offset:offset+2])
  fmt.Printf("    [status]: %d\n", status)
  offset += 2

  capability_upper := payload[offset:offset+2]
  offset += 2
  capabilities := binary.LittleEndian.Uint32(append(capability_lower, capability_upper...))

  var auth_plugin_data_part2 []byte
  var auth_plugin_name []byte
  if capabilities & 0x00080000 > 0{ //CLIENT_PLUGIN_AUTH
    length_of_plugin_auth_data := int(payload[offset])
    offset++

    // skips reserved 10bytes
    offset += 10
    if capabilities & 0x8000 > 0 { //CLIENT_SECURE_CONNECTION
      auth_plugin_data_part2 = payload[offset:offset+(length_of_plugin_auth_data - 8)]
      offset += length_of_plugin_auth_data - 8
      fmt.Printf("    [auth_plugin_data_part2]: \n")
      fmt.Printf("    %s", hex.Dump(auth_plugin_data_part2))
    } 

    idx = bytes.IndexByte(payload[offset:], 0x00)
    auth_plugin_name = payload[offset:offset+idx]
    fmt.Printf("    [auth_plugin_name]: %s\n", auth_plugin_name)
  } else {
    panic("not supported")
  }
  
  // 今回のテーマのINITIAL RESPONSE PACKET
  //
  // とりあえず先にscrambled passwordを作っておく
  // SHA1( password ) XOR SHA1( challenge + SHA1( SHA1( password ) ) )
  auth_response := sha1_sum([]byte(password))

  // XOR用のデータを作る
  key2 := sha1_sum(auth_response)
  challenge := append(auth_plugin_data_part1, auth_plugin_data_part2[0:12]...)

  // SHA1(challenge + SHA1(SHA1(password)))の部分
  challenge  = append(challenge, key2...)
  challenge_key := sha1_sum(challenge)
  for i := 0; i < 20; i++ {
    // XORしてく
    auth_response[i] ^= challenge_key[i]
  }

  // initial response packetのbuffer sizeを確保する
  buffer_size := 4 + 4 + 4 + 1 + 23
  buffer_size += len(username) + 1
  buffer_size += 1
  buffer_size += len(auth_response)
  buffer_size += len(auth_plugin_name) + 1
  buffer := make([]byte, buffer_size)

  offset = 0
  // headerに記載するのはpayloadのサイズなので-4しとく
  size := buffer_size - 4 
  buffer[0] = byte(size)
  buffer[1] = byte(size >> 8)
  buffer[2] = byte(size >> 16)
  // initial response packetはinitial packetの返答なのでsequence idを1にしとく
  buffer[3] = 0x01
  offset = 4

  // 必要なデータをどんどん積んでいく
  capability_flags := uint32(CLIENT_PROTOCOL_41 |
      CLIENT_SECURE_CONNECTION | CLIENT_LONG_PASSWORD |
      CLIENT_TRANSACTIONS | CLIENT_LONG_FLAG)
  charset = 0x21
  buffer[offset] = byte(capability_flags)
  buffer[offset+1] = byte(capability_flags >> 8)
  buffer[offset+2] = byte(capability_flags >> 16)
  buffer[offset+3] = byte(capability_flags >> 24)
  offset += 4
  // max packet sizeは0でいいのでskipする
  offset += 4
  buffer[offset] = byte(charset)
  offset += 1
  //reserved 23 bytes文をskipする
  offset += 23

  // username (null terminated string)
  copy(buffer[offset:], username)
  offset += len(username) +1
  buffer[offset] = byte(len(auth_response))
  offset += 1
  // さっき作ったauth responseのデータをコピー
  copy(buffer[offset:], auth_response)
  offset += len(auth_response)
  // 最後にauth plugin nameをコピーしておしまい
  copy(buffer[offset:], auth_plugin_name)
<br />

  fmt.Printf("\n[write_packet:%d bytes]:\n%s", len(buffer), hex.Dump(buffer))
  conn.Write(buffer)

  // payloadの先頭が0x00なら認証成功になる。
  header, payload = read_packet(conn)
  fmt.Printf("[header]:\n%s\n[payload]:\n%s\n", hex.Dump(header), hex.Dump(payload))
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

package main

import (

"fmt"

"encoding/hex"

"encoding/binary"

"bytes"

"net"

"bufio"

"io"

"crypto/sha1"

)

var username = "chobie"

var password = "chobie"

type CapabilityFlag int

var (

CLIENT_LONG_PASSWORD CapabilityFlag = 0x0001

CLIENT_FOUND_ROWS CapabilityFlag = 0x0002

CLIENT_LONG_FLAG CapabilityFlag = 0x0004

CLIENT_CONNECT_WITH_DB CapabilityFlag = 0x0008

CLIENT_NO_SCHEMA CapabilityFlag = 0x0010

CLIENT_COMPRESS CapabilityFlag = 0x0020

CLIENT_ODBC CapabilityFlag = 0x0040

CLIENT_LOCAL_FILES CapabilityFlag = 0x0080

CLIENT_IGNORE_SPACE CapabilityFlag = 0x0100

CLIENT_PROTOCOL_41 CapabilityFlag = 0x0200

CLIENT_INTERACTIVE CapabilityFlag = 0x0400

CLIENT_SSL CapabilityFlag = 0x0800

CLIENT_IGNORE_SIGPIPE CapabilityFlag = 0x1000

CLIENT_TRANSACTIONS CapabilityFlag = 0x2000

CLIENT_RESERVED CapabilityFlag = 0x4000

CLIENT_SECURE_CONNECTION CapabilityFlag = 0x8000

CLIENT_PLUGIN_AUTH CapabilityFlag = 0x00080000

CLIENT_SESSION_TRACK CapabilityFlag = 0x00800000

)

<br />

func read_packet(reader io.Reader) (byte, byte) {

header := make([]byte, 4)

reader.Read(header)

payload_size := uint32(header[0])

payload_size += uint32(header[1] >> 8)

payload_size += uint32(header[2] >> 16)

payload := make([]byte, payload_size)

reader.Read(payload)

return header, payload

}

func sha1_sum(data byte) byte{

h := sha1.New()

io.Copy(h, bytes.NewReader(data))

return h.Sum(nil)

}

func main() {

//READ INITIAL PACKET

conn, err := net.Dial("tcp", "localhost:3306")

reader := bufio.NewReader(conn)

if err != nil {

panic(err)

}

header, payload := read_packet(reader)

fmt.Printf("[header]:\n%s\n[payload]:\n%s\n", hex.Dump(header), hex.Dump(payload))

// INITIAL Packetのparse。前回と内容同じなのでよまなくて大丈夫

offset := 0

protocol_version := int(payload[offset])

fmt.Printf(" [protocol_version]: %d\n", protocol_version)

offset++

fmt.Printf(" [Initial Handshake Packet]:\n")

idx := bytes.IndexByte(payload[offset:], 0x00)

version := payload[offset:offset+idx]

fmt.Printf(" [version]: %s\n", version)

offset += idx+1

connectionId := binary.LittleEndian.Uint32(payload[offset:offset+4])

fmt.Printf(" [connectionId]: %d\n", connectionId)

offset += 4

auth_plugin_data_part1 := payload[offset:offset+8]

fmt.Printf(" [auth_plugin_data_part1]: \n")

fmt.Printf(" %s", hex.Dump(auth_plugin_data_part1))

offset += 8

filter1 := payload[offset:offset+1]

fmt.Printf(" [filter1]: %s\n", filter1)

offset += 1

capability_lower := payload[offset:offset+2]

offset += 2

charset := uint8(payload[offset])

fmt.Printf(" [charset]: %d\n", charset)

offset++

status := binary.LittleEndian.Uint16(payload[offset:offset+2])

fmt.Printf(" [status]: %d\n", status)

offset += 2

capability_upper := payload[offset:offset+2]

offset += 2

capabilities := binary.LittleEndian.Uint32(append(capability_lower, capability_upper...))

var auth_plugin_data_part2 []byte

var auth_plugin_name []byte

if capabilities & 0x00080000 > 0{ //CLIENT_PLUGIN_AUTH

length_of_plugin_auth_data := int(payload[offset])

offset++

// skips reserved 10bytes

offset += 10

if capabilities & 0x8000 > 0 { //CLIENT_SECURE_CONNECTION

auth_plugin_data_part2 = payload[offset:offset+(length_of_plugin_auth_data - 8)]

offset += length_of_plugin_auth_data - 8

fmt.Printf(" [auth_plugin_data_part2]: \n")

fmt.Printf(" %s", hex.Dump(auth_plugin_data_part2))

}

idx = bytes.IndexByte(payload[offset:], 0x00)

auth_plugin_name = payload[offset:offset+idx]

fmt.Printf(" [auth_plugin_name]: %s\n", auth_plugin_name)

} else {

panic("not supported")

}

// 今回のテーマのINITIAL RESPONSE PACKET

// とりあえず先にscrambled passwordを作っておく

// SHA1( password ) XOR SHA1( challenge + SHA1( SHA1( password ) ) )

auth_response := sha1_sum([]byte(password))

// XOR用のデータを作る

key2 := sha1_sum(auth_response)

challenge := append(auth_plugin_data_part1, auth_plugin_data_part2[0:12]...)

// SHA1(challenge + SHA1(SHA1(password)))の部分

challenge = append(challenge, key2...)

challenge_key := sha1_sum(challenge)

for i := 0; i < 20; i++ {

// XORしてく

auth_response[i] ^= challenge_key[i]

}

// initial response packetのbuffer sizeを確保する

buffer_size := 4 + 4 + 4 + 1 + 23

buffer_size += len(username) + 1

buffer_size += 1

buffer_size += len(auth_response)

buffer_size += len(auth_plugin_name) + 1

buffer := make([]byte, buffer_size)

offset = 0

// headerに記載するのはpayloadのサイズなので-4しとく

size := buffer_size - 4

buffer[0] = byte(size)

buffer[1] = byte(size >> 8)

buffer[2] = byte(size >> 16)

// initial response packetはinitial packetの返答なのでsequence idを1にしとく

buffer[3] = 0x01

offset = 4

// 必要なデータをどんどん積んでいく

capability_flags := uint32(CLIENT_PROTOCOL_41 |

CLIENT_SECURE_CONNECTION | CLIENT_LONG_PASSWORD |

CLIENT_TRANSACTIONS | CLIENT_LONG_FLAG)

charset = 0x21

buffer[offset] = byte(capability_flags)

buffer[offset+1] = byte(capability_flags >> 8)

buffer[offset+2] = byte(capability_flags >> 16)

buffer[offset+3] = byte(capability_flags >> 24)

offset += 4

// max packet sizeは0でいいのでskipする

offset += 4

buffer[offset] = byte(charset)

offset += 1

//reserved 23 bytes文をskipする

offset += 23

// username (null terminated string)

copy(buffer[offset:], username)

offset += len(username) +1

buffer[offset] = byte(len(auth_response))

offset += 1

// さっき作ったauth responseのデータをコピー

copy(buffer[offset:], auth_response)

offset += len(auth_response)

// 最後にauth plugin nameをコピーしておしまい

copy(buffer[offset:], auth_plugin_name)

<br />

fmt.Printf("\n[write_packet:%d bytes]:\n%s", len(buffer), hex.Dump(buffer))

conn.Write(buffer)

// payloadの先頭が0x00なら認証成功になる。

header, payload = read_packet(conn)

fmt.Printf("[header]:\n%s\n[payload]:\n%s\n", hex.Dump(header), hex.Dump(payload))

}

実際のコマンドの実行結果はこんな感じになるはずです。最後のpayloadが00で始まってれば認証成功です。

chobie% go run main.go
00000000  4a 00 00 00                                       |J...|

[header]:
00000000  4a 00 00 00                                       |J...|

[payload]:
00000000  0a 35 2e 36 2e 32 30 00  62 00 00 00 5c 45 24 25  |.5.6.20.b...\E$%|
00000010  2a 43 52 54 00 ff f7 21  02 00 7f 80 15 00 00 00  |*CRT...!........|
00000020  00 00 00 00 00 00 00 2d  24 69 69 53 45 3d 41 6e  |.......-$iiSE=An|
00000030  27 75 3a 00 6d 79 73 71  6c 5f 6e 61 74 69 76 65  |'u:.mysql_native|
00000040  5f 70 61 73 73 77 6f 72  64 00                    |_password.|

  [protocol_version]: 10
  [Initial Handshake Packet]:
    [version]: 5.6.20
    [connectionId]: 98
    [auth_plugin_data_part1]: 
    00000000  5c 45 24 25 2a 43 52 54                           |\E$%*CRT|
    [filter1]: 
    [charset]: 33
    [status]: 2
    [auth_plugin_data_part2]: 
    00000000  2d 24 69 69 53 45 3d 41  6e 27 75 3a 00           |-$iiSE=An'u:.|
    [auth_plugin_name]: mysql_native_password

[write_packet:86 bytes]:
00000000  52 00 00 01 05 a2 00 00  00 00 00 00 21 00 00 00  |R...........!...|
00000010  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000020  00 00 00 00 63 68 6f 62  69 65 00 14 76 8f 90 c4  |....chobie..v...|
00000030  38 08 a0 2d 6d 22 f0 ae  c9 7a c4 19 10 eb c2 3e  |8..-m"...z.....>|
00000040  6d 79 73 71 6c 5f 6e 61  74 69 76 65 5f 70 61 73  |mysql_native_pas|
00000050  73 77 6f 72 64 00                                 |sword.|
00000000  07 00 00 02                                       |....|

[header]:
00000000  07 00 00 02                                       |....|

[payload]:
00000000  00 00 00 02 00 00 00                              |.......|

chobie% go run main.go

00000000 4a 00 00 00 |J...|

[header]:

00000000 4a 00 00 00 |J...|

[payload]:

00000000 0a 35 2e 36 2e 32 30 00 62 00 00 00 5c 45 24 25 |.5.6.20.b...\E$%|

00000010 2a 43 52 54 00 ff f7 21 02 00 7f 80 15 00 00 00 |*CRT...!........|

00000020 00 00 00 00 00 00 00 2d 24 69 69 53 45 3d 41 6e |.......-$iiSE=An|

00000030 27 75 3a 00 6d 79 73 71 6c 5f 6e 61 74 69 76 65 |'u:.mysql_native|

00000040 5f 70 61 73 73 77 6f 72 64 00 |_password.|

[protocol_version]: 10

[Initial Handshake Packet]:

[version]: 5.6.20

[connectionId]: 98

[auth_plugin_data_part1]:

00000000 5c 45 24 25 2a 43 52 54 |\E$%*CRT|

[filter1]:

[charset]: 33

[status]: 2

[auth_plugin_data_part2]:

00000000 2d 24 69 69 53 45 3d 41 6e 27 75 3a 00 |-$iiSE=An'u:.|

[auth_plugin_name]: mysql_native_password

[write_packet:86 bytes]:

00000000 52 00 00 01 05 a2 00 00 00 00 00 00 21 00 00 00 |R...........!...|

00000010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

00000020 00 00 00 00 63 68 6f 62 69 65 00 14 76 8f 90 c4 |....chobie..v...|

00000030 38 08 a0 2d 6d 22 f0 ae c9 7a c4 19 10 eb c2 3e |8..-m"...z.....>|

00000040 6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 |mysql_native_pas|

00000050 73 77 6f 72 64 00 |sword.|

00000000 07 00 00 02 |....|

[header]:

00000000 07 00 00 02 |....|

[payload]:

00000000 00 00 00 02 00 00 00 |.......|

OK Packetが来たので認証が成功しましたね！

とはいえ、これだとまだ何もできないので次回はCOM_QUERYを通してクエリの実行から結果の取得までやってみましょう。

それでは、よいコーディングを

参考

http://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse