Rails 1.0: MySQL password hashing


#1

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Password hashing has long been among our top support issues.
Once upon a time, Rails came bundled ruby-mysql 0.2.4 [1]

It doesn’t support the new MySQL 4.1 client protocol or new-style
password hashing, so in January 2005 Matt M. wrote the mysql411
shim, activated if the server version >= 4.1.1, which adds support
for these new features but breaks old-style password hashing.

This is included with Rails; otherwise, we have no out-of-the-box
support for the stable MySQL release. Unforunately, it means that
users who are migrating from 4.0 or earlier must deal with the
password hashing headache.

The answers are:

  1. gem install mysql. This installs mysql-ruby 2.7 C driver [2]
    which is faster and supports MySQL 4.1 and later. However, there is
    no Windows binary gem available.
  2. Upgrade the database to new-style password hashing [3]. Some
    users on shared hosts cannot do this (apparently) and it is an
    annoying step to perform.
  3. Use an empty password. Then hashing is not an issue.

These are poor answers. Solutions:

  • Improve MySQL 4.1+ support in ruby-mysql [1]. Merge the nice,
    clean patch by �� 亮 [4] with ruby-mysql
    0.2.6 and include it with Rails.
  • gem install mysql. We need to package the compiled mysql-ruby
    2.7 bindings [2] as a binary gem for Windows.

This is an open call for anyone to take a crack at the problem.
Either solution would make a lot of people very grateful.

Best,
jeremy

[1] http://tmtm.org/en/ruby/mysql/
[2] http://tmtm.org/en/mysql/ruby/
[3] http://dev.mysql.com/doc/refman/5.0/en/old-client.html
[4] http://tmtm.org/ruby/mysql/ruby_mysql4.patch

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDcWIVAQHALep9HFYRAkrmAKC2LCuukd+DXDSn4tCCiAA6OJDMKwCcDraC
OC1v3haaWW/mh/E12X+JBC0=
=55Zb
-----END PGP SIGNATURE-----


#2

On 11/8/05, Jeremy K. removed_email_address@domain.invalid wrote:
----snip-----

These are poor answers. Solutions:

  • Improve MySQL 4.1+ support in ruby-mysql [1]. Merge the nice,
    clean patch by e$B9uEDe(B e$BN<e(B [4] with ruby-mysql
    0.2.6 and include it with Rails.

Jeremy, quick question about this. Are these two separate tasks?
Improve the 4.1+ support in ruby-mysql AND merge akuroda’s patch?

I took a quick look, and it seems like it will take about zero effort
to merge this patch forward to version 02.6, since the only difference
between 0.2.6 and 0.2.5 was a one-character bugfix (maybe that’s what
you meant by ‘nice, clean patch.’)

What else needs to be done to ‘Improve MySQL 4.1+ support in ruby-mysql’
?

-Doug


#3

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 8, 2005, at 7:56 PM, Doug Fales wrote:

I took a quick look, and it seems like it will take about zero effort
to merge this patch forward to version 02.6, since the only difference
between 0.2.6 and 0.2.5 was a one-character bugfix (maybe that’s what
you meant by ‘nice, clean patch.’)

What else needs to be done to ‘Improve MySQL 4.1+ support in ruby-
mysql’ ?

Nope; that’s it! Sorry my language was unclear.

I applied the patch and replaced the current vendor/mysql.rb and
vendor/mysql411.rb, but couldn’t get the patched driver working. Did
you have any luck?

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDcX1bAQHALep9HFYRAoEKAKDBUrVq4NnreZq3Ph+GtVzh9ErXvgCfecox
LEAOFDjwJk/zN9jsDIHuc94=
=aMyx
-----END PGP SIGNATURE-----


#4

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 8, 2005, at 11:35 PM, Doug Fales wrote:

   if PROTO_AUTH41
     data << db + "\0"
   else
     data << "\0"+db
  end

Fantastic! This is exactly the bit that was giving me errors, and
your fix works. I am tossing rose petals in your direction.

I’ll check whether we have any other local modifications to mysql.rb
then swap in the new one. Would you email the ruby-mysql author,
removed_email_address@domain.invalid with this bug in the patch?

Thanks again!
jeremy

ps – anyone up for a Windows MySQL gem?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDcbmvAQHALep9HFYRAs5DAKCFRA1VfKsvbTZdUu3fsWUNozSzRACgzPfA
HlQR2JsB5BxxEtWQc4HvPDg=
=jwmq
-----END PGP SIGNATURE-----


#5

On 11/9/05, Jeremy K. removed_email_address@domain.invalid wrote:

Fantastic! This is exactly the bit that was giving me errors, and
your fix works. I am tossing rose petals in your direction.

Great, glad I could help. :slight_smile:

I’ll check whether we have any other local modifications to mysql.rb
then swap in the new one. Would you email the ruby-mysql author,
removed_email_address@domain.invalid with this bug in the patch?

Sure thing. I’ll CC you on it.

-Doug


#6

Jeremy,

It looks like there was a small bug with akuroda’s change which
prevented the database from being correctly selected. The bug is
around line 143 of the patched version of vendor/mysql.rb:
if db and @server_capabilities & CLIENT_CONNECT_WITH_DB != 0 then
data << “\0”+db
end

Apparently, for 4.1+, there should not be a null between the password
and the database. So the fix looks like this:
if db and @server_capabilities & CLIENT_CONNECT_WITH_DB != 0 then
if PROTO_AUTH41
data << db + “\0”
else
data << “\0”+db
end

Of course, there are a few other things you need to do to get this to
work. Namely, they are:

  1. Remove vendor/mysql411.rb.
  2. Comment out the line in connection_adapters/msyql_adapter.rb that
    requires mysql411.rb.

After doing that, I successfully ran all of the ‘test_mysql’ rake test
tasks in the active_record/test directory against my MySQL 5.x
database.

Just to avoid any confusion, I will paste the patch at the end of this
email.

-Doug

…output from svn diff below…

Index: activerecord/test/connections/native_mysql/connection.rb

— activerecord/test/connections/native_mysql/connection.rb (revision
2944)
+++ activerecord/test/connections/native_mysql/connection.rb (working
copy)
@@ -9,12 +9,14 @@

ActiveRecord::Base.establish_connection(
:adapter => “mysql”,

  • :username => “rails”,
  • :username => “ruby_mysql”,
  • :password => “ruby_mysql”,
    :database => db1
    )

Course.establish_connection(
:adapter => “mysql”,

  • :username => “rails”,
  • :username => “ruby_mysql”,
  • :password => “ruby_mysql”,
    :database => db2
    )
    Index:
    activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
    ===================================================================
    — activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
    (revision

+++ activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
(working
copy)
@@ -14,7 +14,6 @@
# Only use the supplied backup Ruby/MySQL driver if no
driver is already in place
begin
require ‘active_record/vendor/mysql’

  •        require 'active_record/vendor/mysql411'
           # The ruby version of mysql returns null fields in 
    

each_hash
ConnectionAdapters::MysqlAdapter.null_values_in_each_hash =
true
rescue LoadError
Index: activerecord/lib/active_record/vendor/mysql.rb

— activerecord/lib/active_record/vendor/mysql.rb (revision 2944)
+++ activerecord/lib/active_record/vendor/mysql.rb (working copy)
@@ -1,14 +1,15 @@
-# $Id: mysql.rb,v 1.1 2004/02/24 15:42:29 webster132 Exp $
+# $Id: mysql.rb,v 1.24 2005/02/12 11:37:15 tommy Exp $

-# Copyright © 2003 TOMITA Masahiro
+# Copyright © 2003-2005 TOMITA Masahiro

removed_email_address@domain.invalid

class Mysql

  • VERSION = “4.0-ruby-0.2.4”
  • VERSION = “4.0-ruby-0.2.6_akurodas_patch_and_dougs_db_fix”

    require “socket”

  • require “digest/sha1”

    MAX_PACKET_LENGTH = 256256256-1
    MAX_ALLOWED_PACKET = 102410241024
    @@ -51,11 +52,15 @@
    CLIENT_ODBC = 1 << 6
    CLIENT_LOCAL_FILES = 1 << 7
    CLIENT_IGNORE_SPACE = 1 << 8

  • CLIENT_PROTOCOL_41 = 1 << 9
    CLIENT_INTERACTIVE = 1 << 10
    CLIENT_SSL = 1 << 11
    CLIENT_IGNORE_SIGPIPE = 1 << 12
    CLIENT_TRANSACTIONS = 1 << 13

  • CLIENT_RESERVED = 1 << 14

  • CLIENT_SECURE_CONNECTION = 1 << 15
    CLIENT_CAPABILITIES =
    CLIENT_LONG_PASSWORD|CLIENT_LONG_FLAG|CLIENT_TRANSACTIONS

  • PROTO_AUTH41 = CLIENT_PROTOCOL_41 | CLIENT_SECURE_CONNECTION

    Connection Option

    OPT_CONNECT_TIMEOUT = 0
    @@ -115,19 +120,37 @@
    @server_capabilities, = a.slice!(0,2).unpack(“v”)
    end
    if a.size >= 16 then

  •  @server_language, @server_status = a.unpack("cv")
    
  •  @server_language, @server_status = a.slice!(0,3).unpack("cv")
    

    end

    flag = 0 if flag == nil
    flag |= @client_flag | CLIENT_CAPABILITIES
    flag |= CLIENT_CONNECT_WITH_DB if db

  • data =
    Net::int2str(flag)+Net::int3str(@max_allowed_packet)+(user||"")+"\0"+scramble(passwd,
    @scramble_buff, @protocol_version==9)
  • if !@server_capabilities & PROTO_AUTH41
  •  data = Net::int2str(flag)+Net::int3str(@max_allowed_packet)+
    
  •         (user||"")+"\0"+
    
  •               scramble(passwd, @scramble_buff, 
    

@protocol_version==9)

  • else
  •  dummy, @salt2 = a.unpack("a13a12")
    
  •  @scramble_buff += @salt2
    
  •  flag |= PROTO_AUTH41
    
  •  data = Net::int4str(flag) + Net::int4str(@max_allowed_packet) +
    
  •         ([8] + Array.new(23, 0)).pack("c24") + (user||"")+"\0"+
    
  •         scramble41(passwd, @scramble_buff)
    
  • end
  • if db and @server_capabilities & CLIENT_CONNECT_WITH_DB != 0 then
  •  data << "\0"+db
    
  •   if PROTO_AUTH41
    
  •     data << db + "\0"
    
  •   else
    
  •     data << "\0"+db
    
  •  end
     @db = db.dup
    
    end
    write data
    read
  • ObjectSpace.define_finalizer(self, Mysql.finalizer(@net))
    self
    end
    alias :connect :real_connect
    @@ -182,7 +205,11 @@
    end

def change_user(user="", passwd="", db="")

  • if !@server_capabilities & PROTO_AUTH41
    data = user+"\0"+scramble(passwd, @scramble_buff,
    @protocol_version==9)+"\0"+db
  • else
  •  data = user+"\0"+ scramble41(passwd, @scramble_buff)
    
  • end
    command COM_CHANGE_USER, data
    @user = user
    @passwd = passwd
    @@ -243,7 +270,11 @@

def list_fields(table, field=nil)
command COM_FIELD_LIST, “#{table}\0#{field}”, true

  • if !@server_capabilities & PROTO_AUTH41
    f = read_rows 6
  • else
  •  f = read_rows 7
    
  • end
    fields = unpack_fields(f, @server_capabilities & CLIENT_LONG_FLAG
    != 0)
    res = Result::new self, fields, f.length
    res.eof = true
    @@ -253,7 +284,11 @@
    def list_processes()
    data = command COM_PROCESS_INFO
    @field_count = get_length data
  • if !@server_capabilities & PROTO_AUTH41
    fields = read_rows 5
  • else
  •  fields = read_rows 7
    
  • end
    @fields = unpack_fields(fields, @server_capabilities &
    CLIENT_LONG_FLAG != 0)
    @status = :STATUS_GET_RESULT
    store_result
    @@ -311,7 +346,11 @@

def read_one_row(field_count)
data = read

  • return if data[0] == 254 and data.length == 1
  • if data[0] == 254 and data.length == 1 ## EOF
  •  return
    
  • elsif data[0] == 254 and data.length == 5
  •  return
    
  • end
    rec = []
    field_count.times do
    len = get_length data
    @@ -363,7 +402,11 @@
    end
    else
    @extra_info = get_length(data, true)
  •  if !@server_capabilities & PROTO_AUTH41
     fields = read_rows 5
    
  •  else
    
  •    fields = read_rows(7)
    
  •  end
     @fields = unpack_fields(fields, @server_capabilities &
    

CLIENT_LONG_FLAG != 0)
@status = :STATUS_GET_RESULT
end
@@ -373,6 +416,7 @@
def unpack_fields(data, long_flag_protocol)
ret = []
data.each do |f|

  •  if !@server_capabilities & PROTO_AUTH41
     table = org_table = f[0]
     name = f[1]
     length = f[2][0]+f[2][1]*256+f[2][2]*256*256
    

@@ -386,8 +430,22 @@
end
def_value = f[5]
max_length = 0

  •  else
    
  •    catalog = f[0]
    
  •    db = f[1]
    
  •    table = f[2]
    
  •    org_table = f[3]
    
  •    name = f[4]
    
  •    org_name = f[5]
    
  •    length = f[6][2]+f[6][3]*256+f[6][4]*256*256
    
  •    type = f[6][6]
    
  •    flags = f[6][7]+f[6][8]*256
    
  •    decimals = f[6][9]
    
  •    def_value = ""
    
  •    max_length = 0
     ret << Field::new(table, org_table, name, length, type, flags,
    

decimals, def_value, max_length)
end

  • end
    ret
    end

@@ -489,6 +547,19 @@
to.join
end

  • def scramble41(password, message)
  • if password.length != 0
  •  buf = [0x14]
    
  •  s1 = Digest::SHA1.new(password).digest
    
  •  s2 = Digest::SHA1.new(s1).digest
    
  •  x = Digest::SHA1.new(message + s2).digest
    
  •  (0..s1.length - 1).each {|i| buf.push(s1[i] ^ x[i])}
    
  •  buf.pack("C*")
    
  • else
  •  0x00.chr
    
  • end
  • end
  • def error(errno)
    @errno = errno
    @error = Error::err errno
    @@ -1022,9 +1093,6 @@
    end
    @sock.sync = true
    buf.join
  • rescue

  •  errno = Error::CR_SERVER_LOST
    
  •  raise Error::new(errno, Error::err(errno))
    

    end

    def write(data)
    @@ -1042,9 +1110,6 @@
    @pkt_nr = @pkt_nr + 1 & 0xff
    @sock.sync = true
    @sock.flush

  • rescue

  •  errno = Error::CR_SERVER_LOST
    
  •  raise Error::new(errno, Error::err(errno))
    

    end

    def close()
    @@ -1091,6 +1156,13 @@
    end
    alias :connect :real_connect

  • def finalizer(net)
  • proc {
  •  net.clear
    
  •  net.write Mysql::COM_QUIT.chr
    
  • }
  • end
  • def escape_string(str)
    str.gsub(/([\0\n\r\032’"\])/) do
    case $1