Forum: Ruby on Rails Rails 1.0: MySQL password hashing

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
jeremy (Guest)
on 2005-11-12 12:49
(Received via mailing list)
-----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 Mower 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 �� 亮 <akuroda at gmail.com> [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-----
doug.fales (Guest)
on 2005-11-12 12:49
(Received via mailing list)
On 11/8/05, Jeremy Kemper <jeremy@bitsweat.net> wrote:
----snip-----
> These are poor answers.  Solutions:
>    - Improve MySQL 4.1+ support in ruby-mysql [1].  Merge the nice,
> clean patch by $B9uED(B $BN<(B <akuroda at gmail.com> [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
jeremy (Guest)
on 2005-11-12 12:49
(Received via mailing list)
-----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-----
doug.fales (Guest)
on 2005-11-12 12:49
(Received via mailing list)
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
2944)
+++ 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 (C) 2003 TOMITA Masahiro
+# Copyright (C) 2003-2005 TOMITA Masahiro
 # tommy@tmtm.org
 #

 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 = 256*256*256-1
   MAX_ALLOWED_PACKET = 1024*1024*1024
@@ -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
jeremy (Guest)
on 2005-11-12 12:49
(Received via mailing list)
-----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,
tommy@tmtm.org 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-----
doug.fales (Guest)
on 2005-11-12 12:49
(Received via mailing list)
On 11/9/05, Jeremy Kemper <jeremy@bitsweat.net> 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. :)

> 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,
> tommy@tmtm.org with this bug in the patch?

Sure thing.  I'll CC you on it.

-Doug
This topic is locked and can not be replied to.