Rails: using binary value to query a binary column returns nothing

Question

I have a binary column which contains 256-bit checksums. I can store the checksums ok, but when I try to query via the checksum, nothing is returned.

d = Digest::SHA2.new
d.update "large str i'm creating the hash with"
begin
    codebase = Codebase.find_or_create_by_checksum(d.digest)
rescue ActiveRecord::StatementInvalid => e
    # handle duplicate record error
end

I've tried where and different versions of find. Nothing returns. When I use find_or_create_by_checksum, since it doesn't find anything it tries to create it and an exception is raised since I have a uniq index on the checksum column, but still I need to be able to get the record with the matching checksum.

    create_table :codebases do |t|
        t.binary :checksum, :null => false, :limit => 32
    end

    add_index :codebases, :checksum, :unique => true, :name => 'name_of_the_codebas_uniq_index'

Anybody know how to do this?

Could you please post the appropriate section of your db/schema.rb? — Pedro Nascimento
– Pedro Nascimento, Commented Dec 20, 2011 at 18:37

daniel · Accepted Answer · 2011-12-20 19:41:45Z

1

So if its binary on the database, I couldn't reproduce:

migration:

class Checksum < ActiveRecord::Migration
  def up
    create_table :checksums do |t|
        t.binary :checksum, :null => false, :limit => 32
    end
  end

  def down
  end
end

And then trying it on the rails console:

ruby-1.9.2-p290 :009 > Checksum.create(:checksum => digest.digest)
  SQL (0.4ms)  INSERT INTO "checksums" ("checksum") VALUES (?)  [["checksum", ",&\xB4kh\xFF\xC6\x8F\xF9\x9BE<\x1D0A4\x13B-pd\x83\xBF\xA0\xF9\x8A^\x88bf\xE7\xAE"]]
 => #<Checksum id: 1, checksum: ",&\xB4kh\xFF\xC6\x8F\xF9\x9BE<\x1D0A4\x13B-pd\x83\xBF\xA0\xF9\x8A^\x88bf\xE7\xAE"> 

ruby-1.9.2-p290 :010 > Checksum.first
  Checksum Load (0.2ms)  SELECT "checksums".* FROM "checksums" LIMIT 1
 => #<Checksum id: 1, checksum: ",&\xB4kh\xFF\xC6\x8F\xF9\x9BE<\x1D0A4\x13B-pd\x83\xBF\xA0\xF9\x8A^\x88bf\xE7\xAE"> 

ruby-1.9.2-p290 :011 > Checksum.find_by_checksum(digest.digest)
  Checksum Load (0.1ms)  SELECT "checksums".* FROM "checksums" WHERE "checksums"."checksum" = x'2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae' LIMIT 1
 => #<Checksum id: 1, checksum: ",&\xB4kh\xFF\xC6\x8F\xF9\x9BE<\x1D0A4\x13B-pd\x83\xBF\xA0\xF9\x8A^\x88bf\xE7\xAE">

So it works as expected.....

edited Dec 20, 2011 at 19:41

answered Dec 20, 2011 at 18:51

daniel

9,8458 gold badges45 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Ben Over a year ago

It is a binary in the database. I've updated the question to show the table schema. I thought about storing the hexdigest string and this is a viable workaround. Still curious though as to why this doesn't work with binary. Hmm..

Ben Over a year ago

Wierd, I did the same thing you posted and Codebase.find_by_checksum(d.digest) returns nil. Maybe something wrong with my configuration (ruby 1.9.3-p0, activerecord 3.1.1)?

daniel Over a year ago

Ok let's try to get rid of the encoding variable...when you save do force_encoding("UTF-8"), like: Checksum.create(:checksum => d.digest.force_encoding("UTF-8")

Ben Over a year ago

I've started with a fresh table like yours.

Ben Over a year ago

Funny thing I noticed, when digest has empty string loaded I can find the record, update the digest with non-empty string, all find returns nil. UTF-8 didn't do the trick either. Using force_encoding in create works normally. Using it in find (Checksum.find_by_checksum(d.digest.force_encoding("UTF-8"))) I get "ArgumentError: invalid byte sequence in UTF-8"

|

Collectives™ on Stack Overflow

Rails: using binary value to query a binary column returns nothing

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related