# Rails 1.0: MySQL password hashing **URL:** \ **Category:** Rails\ **Created:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/rails-1-0-mysql-password-hashing/47560 "2005-11-12T12:49:26Z")\ **Posts on this page:** 6\ **Page:** 1

**Author:** ![jeremy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/82dd89/32.png) [@jeremy](https://www.ruby-forum.com/u/jeremy)\ **Post date:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/rails-1-0-mysql-password-hashing/47560/1 "2005-11-12T12:49:26Z")

-----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] [Ruby/MySQL](http://tmtm.org/en/ruby/mysql/) [2] [MySQL/Ruby](http://tmtm.org/en/mysql/ruby/) [3] [http://dev.mysql.com/doc/refman/5.0/en/old-client.html](http://dev.mysql.com/doc/refman/5.0/en/old-client.html) [4] [http://tmtm.org/ruby/mysql/ruby\_mysql4.patch](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----- ---

On 11/8/05, Jeremy K. [removed\_email\_address@domain.invalid](mailto: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\ 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 ---

-----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----- ---

-----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----- ---

On 11/9/05, Jeremy K. [removed\_email\_address@domain.invalid](mailto: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. 🙂 > 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 ---

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 1. +++ 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 “digest/sha1” - 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 - ``` @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