Rails saving plain old string to SQlite as BLOB? I'm about to lose my mind!

Question

I have no idea why this would be happening, but Rails is saving a string to SQLite as a BLOB. Before creating a new user in my app I take their plain string password and MD5 it before saving to the database:

class User < ActiveRecord::Base
  before_create :encrypt_password

  def encrypt_password
    self.password = Digest::MD5.hexdigest(self.password)
  end
end

But, the password field goes into SQLite as a freaking BLOB every time! The only way I could even tell is when exporting the table to SQL I can see the true nature of the field:

INSERT INTO "users" VALUES (24, '[email protected]', X'3639366432396530393430613439353737343866653366633965666432326133');

What the hell?? So now when I try to authenticate a user by looking up their email and MD5 hashed password it will fail every time. REAL strings don't match against BLOBs apparently:

User.find_by_email_and_password('[email protected]', Digest::MD5.hexdigest('password') => nil

I have never used a BLOB in my entire life, let alone as the password field for a user table. My migrations clearly define :string as the datatype. Doing a User.columns clearly shows:

#<ActiveRecord::ConnectionAdapters::SQLiteColumn:0x00000105256ce0 @name="password", @sql_type="varchar(255)"

I've been working on this app for the better part of a month and have never seen this issue until last night when I was writing some tests for the User model. Testing trying to authenticate a user would fail every time, so I started manually building users in the console and come to find out the password would never match so all user lookups would fail.

The Rails debug info for creating a user looks like:

INSERT INTO "users" ("created_at", "email", "first_name", "last_login_at", "last_name", "login_count", "password", "role_id", "twitter", "updated_at", "uuid") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["created_at", Mon, 04 Jul 2011 18:50:58 UTC +00:00], ["email", "[email protected]"], ["first_name", "Ebba"], ["last_login_at", nil], ["last_name", "Bayer"], ["login_count", nil], ["password", "5f4dcc3b5aa765d61d8327deb882cf99"], ["role_id", 2], ["twitter", nil], ["updated_at", Mon, 04 Jul 2011 18:50:58 UTC +00:00], ["uuid", "7ab57110-889c-012e-e207-482a140835c4"]]

Which, when I convert to regular SQL works just fine, so there's gotta be something else going on somewhere. What is going on?!?!

• sqlite3 3.6.12
• rails 3.1.0.rc4
• sqlite3-ruby 1.3.3

UPDATE

It gets stranger and stranger...if I hardcode some random string in my encrypt_password method then it goes into the database correctly:

def encrypt_password
  self.password = 'foo'
end

I can even hardcode it to the actual MD5 hash of the string 'password' and it works:

def encrypt_password
  self.password = '5f4dcc3b5aa765d61d8327deb882cf99'
end

But if I tell it to Digest::MD5.hexdigest('password') then it goes in as a BLOB.

Now, if I add anything onto the string created by the digest, then it works!

def encrypt_password
  self.password = Digest::MD5.hexdigest(self.password) + ' '
end

What the hell is that?? So for now my workout is to add a newline and then chomp it off:

def encrypt_password
  self.password = (Digest::MD5.hexdigest(self.password) + "\n").chomp
end

I feel like I should open a ticket in Rails somewhere, but this is so amazingly strange that I don't want to get laughed out of the community forever for even suggesting that something like this could be happening!

Answer 1

Turns out this was an encoding issue. See the ticket here: https://github.com/rails/rails/issues/1965

hexdigest returns an ASCII string, but when you go back to query against that same field the query runs as a UTF-8 string. I assume that as soon as I manually added something to the string it was converted to UTF-8 behind the scenes and then was properly saved to the DB as UTF-8. Here's the fix:

def encrypt_password
  self.password = Digest::MD5.hexdigest(self.password).encode('UTF-8')
end