sqlite3 varchar matching with “like” but not “=”

问题:

Using Rails 3.1 and sqlite3 for development, test environments.

Added a new table in a migration:

create_table :api_keys do |t|
  t.string :api_key
  t.integer :user_id
  t.timestamps
end

This produces a table with the following schema:

create_table "api_keys", :force => true do |t|
  t.string   "api_key"
  t.integer  "user_id"
  t.datetime "created_at"
  t.datetime "updated_at"
end

In ActiveRecord model:

before_create :fill_api_key

private

def fill_api_key
  self.api_key = SecureRandom.hex(30)
end

ActiveRecord's dynamic finder method find_by_api_key(api_key) does not work (returns nil). Same with:

ApiKey.where({:api_key => 'something'}).first

In sqlite3, I do the following:

insert into api_keys (id, api_key) values (-1, '12345');

If I now run a select:

select api_keys.* from api_keys where api_keys.api_key = '12345';

the record will be found.

Pre-existing data created from my app is displayed if I run an unfiltered select:

select api_keys.* from api_keys;

If I try to find a pre-existing record by pasting into my query a long hex string from one of those pre-existing records:

select api_keys.* from api_keys where api_keys.api_key = 'long hex string';

then it returns no results. If I try this instead:

select api_keys.* from api_keys where api_keys.api_key like 'long hex string';

Then I get a match.

I have created an index on api_keys.api_key but that had no effect.

This problem affects one other model in my application that produces a similar string of random hex digits using Digest::SHA1::hexdigest instead.

James

回答1:

OK, I think I've figured it out. The problem isn't that this is Rails 3.1, it's that you've likely moved from Ruby 1.8.7 to Ruby 1.9.2.

In Ruby 1.9, all strings are now encoded. By default, all strings should be UTF-8, however, SecureRandom.hex(30) returns an encoding of ASCII-8BIT.

You can confirm this in sqlite3 by using this command: .dump api_keys and you'll probably see that the api_key field looks something like this:

INSERT INTO "api_keys" VALUES(1,X'376433356530[...]',1);    
INSERT INTO "api_keys" VALUES(1,'1234567890[...]',1);

The first one is the api_key generated by SecureRandom. The second is the one created by typing into the console. The X indicates the field is encoded as a blob, not as a string.

To get around this, change your fill_api_key to this:

self.api_key = SecureRandom.hex(30).force_encoding('UTF-8')

I just got bit big time by this, so hopefully it helps you out.

There are some good details about the changes to String in 1.9 here: http://blog.grayproductions.net/articles/ruby_19s_string

回答2:

If you are using strings of hex digits, it is important that you have a case match if you want to use an where x = y select. Unlike some databases, SQLite is case sensitive.