Rails 将普通的旧字符串保存为 SQlite 作为 BLOB?我快要疯了！ [英] Rails saving plain old string to SQlite as BLOB? I'm about to lose my mind!

问题描述

我不知道为什么会发生这种情况，但 Rails 正在将一个字符串作为 BLOB 保存到 SQLite.在我的应用程序中创建新用户之前，我在保存到数据库之前使用他们的纯字符串密码和 MD5:

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

但是，密码字段每次都会作为一个该死的 BLOB 进入 SQLite！我什至可以告诉的唯一方法是将表导出到 SQL 时，我可以看到该字段的真实性质:

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, '[e-mail]', X'3639366432396530393430613439353737343866653366633965666432326133');

什么鬼??所以现在当我尝试通过查找用户的电子邮件和 MD5 散列密码来验证用户时，它每次都会失败.REAL 字符串显然与 BLOB 不匹配:

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('[e-mail]', Digest::MD5.hexdigest('password') => nil

我一生中从未使用过 BLOB，更不用说用作用户表的密码字段了.我的迁移明确地将 :string 定义为数据类型.做一个 User.columns 清楚地表明:

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

我已经在这个应用程序上工作了一个月的大部分时间，直到昨晚我为 User 模型编写一些测试时才发现这个问题.尝试对用户进行身份验证的测试每次都会失败，因此我开始在控制台中手动构建用户并发现密码永远不会匹配，因此所有用户查找都会失败.

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.

用于创建用户的 Rails 调试信息如下所示:

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", "[e-mail]"], ["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"]]

哪个，当我转换为常规 SQL 时工作得很好，所以一定有其他地方发生了其他事情.这是怎么回事?！?！

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

它变得越来越陌生...如果我在我的 encrypt_password 方法中硬编码一些随机字符串，那么它会正确地进入数据库:

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

我什至可以将它硬编码为字符串 'password' 的实际 MD5 哈希值并且它可以工作:

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

def encrypt_password
  self.password = '5f4dcc3b5aa765d61d8327deb882cf99'
end

但是如果我告诉它 Digest::MD5.hexdigest('password') 那么它就会作为一个 BLOB 进入.

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

我觉得我应该在 Rails 的某个地方打开一张票，但这太奇怪了，我不想因为暗示可能会发生这样的事情而永远被社区嘲笑！

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!

推荐答案

原来这是一个编码问题.在此处查看票证:https://github.com/rails/rails/issues/1965

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

hexdigest 返回一个 ASCII 字符串，但是当您返回查询同一字段时，查询将作为 UTF-8 字符串运行.我假设一旦我手动向字符串添加了一些东西，它就会在幕后转换为 UTF-8，然后作为 UTF-8 正确保存到数据库中.修复方法如下:

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

这篇关于Rails 将普通的旧字符串保存为 SQlite 作为 BLOB?我快要疯了！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！