Class: MysqlPR::Protocol

Inherits:
Object
  • Object
show all
Defined in:
lib/mysql-pr/protocol.rb

Overview

MySQL network protocol

Defined Under Namespace

Classes: AuthenticationPacket, ExecutePacket, FieldPacket, InitialPacket, PrepareResultPacket, ResultPacket, SSLRequestPacket

Constant Summary collapse

VERSION = 
10
MAX_PACKET_LENGTH = 
2**24 - 1

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(host, port, socket, conn_timeout, read_timeout, write_timeout, ssl_options = nil) ⇒ Protocol

make socket connection to server.

Argument

host
String

if “localhost” or “” nil then use UNIXSocket. Otherwise use TCPSocket

port
Integer

port number using by TCPSocket

socket
String

socket file name using by UNIXSocket

conn_timeout
Integer

connect timeout (sec).

read_timeout
Integer

read timeout (sec).

write_timeout
Integer

write timeout (sec).

ssl_options
Hash / nil

SSL options. nil means no SSL.

Exception

ClientError

connection timeout



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
 
# File 'lib/mysql-pr/protocol.rb', line 168

def initialize(host, port, socket, conn_timeout, read_timeout, write_timeout, ssl_options = nil)
  @insert_id = 0
  @warning_count = 0
  @gc_stmt_queue = [] # stmt id list which GC destroy.
  set_state :INIT
  @read_timeout = read_timeout
  @write_timeout = write_timeout
  @ssl_options = ssl_options
  @ssl_enabled = false
  begin
    Timeout.timeout conn_timeout do
      if host.nil? || host.empty? || (host == "localhost")
        socket ||= ENV["MYSQL_UNIX_PORT"] || MYSQL_UNIX_PORT
        @sock = UNIXSocket.new socket
      else
        port ||= ENV["MYSQL_TCP_PORT"] || begin
          Socket.getservbyname("mysql", "tcp")
        rescue StandardError
          MYSQL_TCP_PORT
        end
        @sock = TCPSocket.new host, port
      end
    end
  rescue Timeout::Error
    raise ClientError, "connection timeout"
  end
end

Instance Attribute Details

#affected_rows ⇒ Object (readonly)

Returns the value of attribute affected_rows.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def affected_rows
  @affected_rows
end

#charset ⇒ Object

Returns the value of attribute charset.



149
150
151
 
# File 'lib/mysql-pr/protocol.rb', line 149

def charset
  @charset
end

#insert_id ⇒ Object (readonly)

Returns the value of attribute insert_id.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def insert_id
  @insert_id
end

#message ⇒ Object (readonly)

Returns the value of attribute message.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def message
  @message
end

#server_info ⇒ Object (readonly)

Returns the value of attribute server_info.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def server_info
  @server_info
end

#server_status ⇒ Object (readonly)

Returns the value of attribute server_status.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def server_status
  @server_status
end

#server_version ⇒ Object (readonly)

Returns the value of attribute server_version.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def server_version
  @server_version
end

#sqlstate ⇒ Object (readonly)

Returns the value of attribute sqlstate.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def sqlstate
  @sqlstate
end

#thread_id ⇒ Object (readonly)

Returns the value of attribute thread_id.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def thread_id
  @thread_id
end

#warning_count ⇒ Object (readonly)

Returns the value of attribute warning_count.



147
148
149
 
# File 'lib/mysql-pr/protocol.rb', line 147

def warning_count
  @warning_count
end

Class Method Details

.net2value(pkt, type, unsigned) ⇒ Object

Convert netdata to Ruby value

Argument

data
Packet

packet data

type
Integer

field type

unsigned
true or false

true if value is unsigned

Return

Object

converted value.



26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/mysql-pr/protocol.rb', line 26

def self.net2value(pkt, type, unsigned)
  case type
  when Field::TYPE_STRING, Field::TYPE_VAR_STRING, Field::TYPE_NEWDECIMAL, Field::TYPE_BLOB
    pkt.lcs
  when Field::TYPE_TINY
    v = pkt.utiny
    if unsigned
      v
    else
      v < 128 ? v : v - 256
    end
  when Field::TYPE_SHORT
    v = pkt.ushort
    if unsigned
      v
    else
      v < 32_768 ? v : v - 65_536
    end
  when Field::TYPE_INT24, Field::TYPE_LONG
    v = pkt.ulong
    if unsigned
      v
    else
      v < 2**32 / 2 ? v : v - 2**32
    end
  when Field::TYPE_LONGLONG
    n1 = pkt.ulong
    n2 = pkt.ulong
    v = (n2 << 32) | n1
    if unsigned
      v
    else
      v < 2**64 / 2 ? v : v - 2**64
    end
  when Field::TYPE_FLOAT
    pkt.read(4).unpack1("e")
  when Field::TYPE_DOUBLE
    pkt.read(8).unpack1("E")
  when Field::TYPE_DATE
    len = pkt.utiny
    y, m, d = pkt.read(len).unpack("vCC")
    MysqlPR::Time.new(y, m, d, nil, nil, nil)

  when Field::TYPE_DATETIME, Field::TYPE_TIMESTAMP
    len = pkt.utiny
    y, m, d, h, mi, s, sp = pkt.read(len).unpack("vCCCCCV")
    MysqlPR::Time.new(y, m, d, h, mi, s, false, sp)
  when Field::TYPE_TIME
    len = pkt.utiny
    sign, d, h, mi, s, sp = pkt.read(len).unpack("CVCCCV")
    h = d.to_i * 24 + h.to_i
    MysqlPR::Time.new(0, 0, 0, h, mi, s, sign != 0, sp)
  when Field::TYPE_YEAR
    pkt.ushort
  when Field::TYPE_BIT
    pkt.lcs
  else
    raise "not implemented: type=#{type}"
  end
end

.value2net(v) ⇒ Object

convert Ruby value to netdata

Argument

v
Object

Ruby value.

Return

Integer

type of column. Field::TYPE_*

String

netdata

Exception

ProtocolError

value too large / value is not supported



95
96
97
98
99
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
 
# File 'lib/mysql-pr/protocol.rb', line 95

def self.value2net(v)
  case v
  when nil
    type = Field::TYPE_NULL
    val = ""
  when Integer
    if v >= 0
      if v < 256
        type = Field::TYPE_TINY | 0x8000
        val = [v].pack("C")
      elsif v < 256**2
        type = Field::TYPE_SHORT | 0x8000
        val = [v].pack("v")
      elsif v < 256**4
        type = Field::TYPE_LONG | 0x8000
        val = [v].pack("V")
      elsif v < 256**8
        type = Field::TYPE_LONGLONG | 0x8000
        val = [v & 0xffffffff, v >> 32].pack("VV")
      else
        raise ProtocolError, "value too large: #{v}"
      end
    elsif -v <= 256 / 2
      type = Field::TYPE_TINY
      val = [v].pack("C")
    elsif -v <= 256**2 / 2
      type = Field::TYPE_SHORT
      val = [v].pack("v")
    elsif -v <= 256**4 / 2
      type = Field::TYPE_LONG
      val = [v].pack("V")
    elsif -v <= 256**8 / 2
      type = Field::TYPE_LONGLONG
      val = [v & 0xffffffff, v >> 32].pack("VV")
    else
      raise ProtocolError, "value too large: #{v}"
    end
  when Float
    type = Field::TYPE_DOUBLE
    val = [v].pack("E")
  when String
    type = Field::TYPE_STRING
    val = Packet.lcs(v)
  when MysqlPR::Time, ::Time
    type = Field::TYPE_DATETIME
    val = [7, v.year, v.month, v.day, v.hour, v.min, v.sec].pack("CvCCCCC")
  else
    raise ProtocolError, "class #{v.class} is not supported"
  end
  [type, val]
end

Instance Method Details

#authenticate(user, passwd, db, flag, charset) ⇒ Object

initial negotiate and authenticate.

Argument

user
String / nil

username

passwd
String / nil

password

db
String / nil

default database name. nil: no default.

flag
Integer

client flag

charset
MysqlPR::Charset / nil

charset for connection. nil: use server’s charset

Exception

ProtocolError

The old style password is not supported



221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
 
# File 'lib/mysql-pr/protocol.rb', line 221

def authenticate(user, passwd, db, flag, charset)
  check_state :INIT
  @authinfo = [user, passwd, db, flag, charset]
  reset
  init_packet = InitialPacket.parse read
  @server_info = init_packet.server_version
  @server_version = init_packet.server_version.split(/\D/)[0, 3].inject { |a, b| a.to_i * 100 + b.to_i }
  @thread_id = init_packet.thread_id
  client_flags = CLIENT_LONG_PASSWORD | CLIENT_LONG_FLAG | CLIENT_TRANSACTIONS |
                 CLIENT_PROTOCOL_41 | CLIENT_SECURE_CONNECTION
  client_flags |= CLIENT_PLUGIN_AUTH
  client_flags |= CLIENT_CONNECT_WITH_DB if db
  client_flags |= flag
  @charset = charset
  unless @charset
    @charset = Charset.by_number(init_packet.server_charset)
    @charset.encoding # raise error if unsupported charset
  end

  # SSL handshake if requested and server supports it
  if @ssl_options && (init_packet.server_capabilities & CLIENT_SSL) != 0
    client_flags |= CLIENT_SSL
    # Send SSL request packet (partial auth packet with SSL flag)
    write SSLRequestPacket.serialize(client_flags, 1024**3, @charset.number)
    # Upgrade connection to SSL
    upgrade_to_ssl
  elsif @ssl_options && @ssl_options[:required]
    raise ClientError, "SSL required but server does not support SSL"
  end

  auth_plugin = init_packet.auth_plugin_name || "mysql_native_password"
  scramble = init_packet.scramble_buff

  # Choose password encryption based on auth plugin
  netpw = if auth_plugin == "caching_sha2_password"
            encrypt_password_sha256(passwd, scramble)
          else
            encrypt_password(passwd, scramble)
          end

  write AuthenticationPacket.serialize(client_flags, 1024**3, @charset.number, user, netpw, db, auth_plugin)

  # Read response
  response = read
  response_data = response.to_s

  # Handle different response types
  case response_data.getbyte(0)
  when 0x00
    # OK packet - authentication successful
    set_state :READY
  when 0xfe
    # Auth switch request
    handle_auth_switch(response_data, passwd)
  when 0x01
    # More data - caching_sha2_password specific
    handle_caching_sha2_more_data(response_data, passwd, scramble)
  else
    raise ProtocolError, "Unexpected auth response: #{response_data.getbyte(0)}"
  end
end

#close ⇒ Object 



208
209
210
 
# File 'lib/mysql-pr/protocol.rb', line 208

def close
  @sock.close
end

#field_list_command(table, field) ⇒ Object

Field list command

Argument

table
String

table name.

field
String / nil

field name that may contain wild card.

Return

Array of Field

field list



489
490
491
492
493
494
495
496
497
498
499
 
# File 'lib/mysql-pr/protocol.rb', line 489

def field_list_command(table, field)
  synchronize do
    reset
    write [COM_FIELD_LIST, table, 0, field].pack("Ca*Ca*")
    fields = []
    until (data = read).eof?
      fields.push Field.new(FieldPacket.parse(data))
    end
    return fields
  end
end

#gc_stmt(stmt_id) ⇒ Object 



624
625
626
 
# File 'lib/mysql-pr/protocol.rb', line 624

def gc_stmt(stmt_id)
  @gc_stmt_queue.push stmt_id
end

#get_result ⇒ Object

get result of query.

Return

integer / nil

number of fields of results. nil if no results.



420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'lib/mysql-pr/protocol.rb', line 420

def get_result
  res_packet = ResultPacket.parse read
  if res_packet.field_count.to_i.positive? # result data exists
    set_state :FIELD
    return res_packet.field_count
  end
  if res_packet.field_count.nil? # LOAD DATA LOCAL INFILE
    filename = res_packet.message
    File.open(filename) { |f| write f }
    write nil # EOF mark
    read
  end
  @affected_rows = res_packet.affected_rows
  @insert_id = res_packet.insert_id
  @server_status = res_packet.server_status
  @warning_count = res_packet.warning_count
  @message = res_packet.message
  set_state :READY
  nil
rescue StandardError
  set_state :READY
  raise
end

#handle_auth_switch(response_data, passwd) ⇒ Object

Handle auth switch request



284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
 
# File 'lib/mysql-pr/protocol.rb', line 284

def handle_auth_switch(response_data, passwd)
  # Parse auth switch request: 0xfe + plugin_name + scramble
  pkt = Packet.new(response_data[1..])
  plugin_name = pkt.string
  scramble = pkt.to_s

  if plugin_name == "mysql_native_password"
    netpw = encrypt_password(passwd, scramble)
    write netpw
    read # OK or error
    set_state :READY
  elsif plugin_name == "caching_sha2_password"
    netpw = encrypt_password_sha256(passwd, scramble)
    write netpw
    response = read
    if response.to_s.getbyte(0) == 0x01
      handle_caching_sha2_more_data(response.to_s, passwd, scramble)
    else
      set_state :READY
    end
  else
    raise ProtocolError, "Unsupported auth plugin: #{plugin_name}"
  end
end

#handle_caching_sha2_more_data(response_data, passwd, scramble) ⇒ Object

Handle caching_sha2_password “more data” response



310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
 
# File 'lib/mysql-pr/protocol.rb', line 310

def handle_caching_sha2_more_data(response_data, passwd, scramble)
  # 0x01 + status byte
  status = response_data.getbyte(1)

  case status
  when 0x03
    # Fast auth success - server already has cached password hash
    read # Read the final OK packet
    set_state :READY
  when 0x04
    # Full authentication required
    if @ssl_enabled
      # Send plaintext password over SSL
      write "#{passwd}\x00"
    else
      # Need RSA encryption - request public key
      write "\x02" # Request public key
      pubkey_response = read
      pubkey_data = pubkey_response.to_s

      raise ProtocolError, "Failed to get server public key" unless pubkey_data.getbyte(0) == 0x01

      # Got public key
      public_key = pubkey_data[1..]
      encrypted_password = rsa_encrypt_password(passwd, scramble, public_key)
      write encrypted_password

    end
    read
    set_state :READY
  else
    raise ProtocolError, "Unknown caching_sha2_password status: #{status}"
  end
end

#kill_command(pid) ⇒ Object

Kill command



526
527
528
 
# File 'lib/mysql-pr/protocol.rb', line 526

def kill_command(pid)
  simple_command [COM_PROCESS_KILL, pid].pack("CV")
end

#ping_command ⇒ Object

Ping command



521
522
523
 
# File 'lib/mysql-pr/protocol.rb', line 521

def ping_command
  simple_command [COM_PING].pack("C")
end

#process_info_command ⇒ Object

Process info command

Return

Array of Field

field list



504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
 
# File 'lib/mysql-pr/protocol.rb', line 504

def process_info_command
  check_state :READY
  begin
    reset
    write [COM_PROCESS_INFO].pack("C")
    field_count = read.lcb
    fields = field_count.times.map { Field.new FieldPacket.parse(read) }
    read_eof_packet
    set_state :RESULT
    fields
  rescue StandardError
    set_state :READY
    raise
  end
end

#query_command(query) ⇒ Object

Query command

Argument

query
String

query string

Return

Integer / nil

number of fields of results. nil if no results.



405
406
407
408
409
410
411
412
413
414
415
 
# File 'lib/mysql-pr/protocol.rb', line 405

def query_command(query)
  check_state :READY
  begin
    reset
    write [COM_QUERY, @charset.convert(query)].pack("Ca*")
    get_result
  rescue StandardError
    set_state :READY
    raise
  end
end

#quit_command ⇒ Object

Quit command



392
393
394
395
396
397
398
 
# File 'lib/mysql-pr/protocol.rb', line 392

def quit_command
  synchronize do
    reset
    write [COM_QUIT].pack("C")
    close
  end
end

#refresh_command(op) ⇒ Object

Refresh command



531
532
533
 
# File 'lib/mysql-pr/protocol.rb', line 531

def refresh_command(op)
  simple_command [COM_REFRESH, op].pack("CC")
end

#retr_all_records(nfields) ⇒ Object

Retrieve all records for simple query

Argument

nfields
Integer

number of fields

Return

Array of Array of String

all records



467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
 
# File 'lib/mysql-pr/protocol.rb', line 467

def retr_all_records(nfields)
  check_state :RESULT
  enc = charset.encoding
  begin
    all_recs = []
    until (pkt = read).eof?
      all_recs.push RawRecord.new(pkt, nfields, enc)
    end
    pkt.read(3)
    @server_status = pkt.utiny
    all_recs
  ensure
    set_state :READY
  end
end

#retr_fields(n) ⇒ Object

Retrieve n fields

Argument

n
Integer

number of fields

Return

Array of MysqlPR::Field

field list



449
450
451
452
453
454
455
456
457
458
459
460
 
# File 'lib/mysql-pr/protocol.rb', line 449

def retr_fields(n)
  check_state :FIELD
  begin
    fields = n.times.map { Field.new FieldPacket.parse(read) }
    read_eof_packet
    set_state :RESULT
    fields
  rescue StandardError
    set_state :READY
    raise
  end
end

#rsa_encrypt_password(passwd, scramble, public_key_pem) ⇒ Object

RSA encrypt password for caching_sha2_password



346
347
348
349
350
351
352
353
354
355
 
# File 'lib/mysql-pr/protocol.rb', line 346

def rsa_encrypt_password(passwd, scramble, public_key_pem)
  # XOR password with scramble
  passwd_bytes = "#{passwd}\x00".bytes
  scramble_bytes = scramble.bytes
  xored = passwd_bytes.each_with_index.map { |b, i| b ^ scramble_bytes[i % scramble_bytes.length] }

  # Encrypt with RSA public key
  rsa = OpenSSL::PKey::RSA.new(public_key_pem)
  rsa.public_encrypt(xored.pack("C*"), OpenSSL::PKey::RSA::PKCS1_OAEP_PADDING)
end

#set_option_command(opt) ⇒ Object

Set option command



536
537
538
 
# File 'lib/mysql-pr/protocol.rb', line 536

def set_option_command(opt)
  simple_command [COM_SET_OPTION, opt].pack("Cv")
end

#shutdown_command(level) ⇒ Object

Shutdown command



541
542
543
 
# File 'lib/mysql-pr/protocol.rb', line 541

def shutdown_command(level)
  simple_command [COM_SHUTDOWN, level].pack("CC")
end

#ssl_cipher ⇒ Object

Returns SSL cipher info if SSL is enabled



202
203
204
205
206
 
# File 'lib/mysql-pr/protocol.rb', line 202

def ssl_cipher
  return nil unless @ssl_enabled && @sock.respond_to?(:cipher)

  @sock.cipher
end

#ssl_enabled? ⇒ Boolean

Returns true if SSL is enabled for this connection

Returns:

  • (Boolean) 


197
198
199
 
# File 'lib/mysql-pr/protocol.rb', line 197

def ssl_enabled?
  @ssl_enabled
end

#statistics_command ⇒ Object

Statistics command



546
547
548
 
# File 'lib/mysql-pr/protocol.rb', line 546

def statistics_command
  simple_command [COM_STATISTICS].pack("C")
end

#stmt_close_command(stmt_id) ⇒ Object

Stmt close command

Argument

stmt_id
Integer

statement id



617
618
619
620
621
622
 
# File 'lib/mysql-pr/protocol.rb', line 617

def stmt_close_command(stmt_id)
  synchronize do
    reset
    write [COM_STMT_CLOSE, stmt_id].pack("CV")
  end
end

#stmt_execute_command(stmt_id, values) ⇒ Object

Stmt execute command

Argument

stmt_id
Integer

statement id

values
Array

parameters

Return

Integer

number of fields



582
583
584
585
586
587
588
589
590
591
592
 
# File 'lib/mysql-pr/protocol.rb', line 582

def stmt_execute_command(stmt_id, values)
  check_state :READY
  begin
    reset
    write ExecutePacket.serialize(stmt_id, MysqlPR::Stmt::CURSOR_TYPE_NO_CURSOR, values)
    get_result
  rescue StandardError
    set_state :READY
    raise
  end
end

#stmt_prepare_command(stmt) ⇒ Object

Stmt prepare command

Argument

stmt
String

prepared statement

Return

Integer

statement id

Integer

number of parameters

Array of Field

field list



557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
 
# File 'lib/mysql-pr/protocol.rb', line 557

def stmt_prepare_command(stmt)
  synchronize do
    reset
    write [COM_STMT_PREPARE, charset.convert(stmt)].pack("Ca*")
    res_packet = PrepareResultPacket.parse read
    if res_packet.param_count.positive?
      res_packet.param_count.times { read } # skip parameter packet
      read_eof_packet
    end
    if res_packet.field_count.positive?
      fields = res_packet.field_count.times.map { Field.new FieldPacket.parse(read) }
      read_eof_packet
    else
      fields = []
    end
    return res_packet.statement_id, res_packet.param_count, fields
  end
end

#stmt_retr_all_records(fields, charset) ⇒ Object

Retrieve all records for prepared statement

Argument

fields
Array of MysqlPR::Fields

field list

charset
MysqlPR::Charset

Return

Array of Array of Object

all records



600
601
602
603
604
605
606
607
608
609
610
611
612
 
# File 'lib/mysql-pr/protocol.rb', line 600

def stmt_retr_all_records(fields, charset)
  check_state :RESULT
  enc = charset.encoding
  begin
    all_recs = []
    until (pkt = read).eof?
      all_recs.push StmtRawRecord.new(pkt, fields, enc)
    end
    all_recs
  ensure
    set_state :READY
  end
end