SQLite-Ruby storing a hexdigest hash as a blob rather than text?

376 views
Skip to first unread message

duckyfuzz

unread,
Jul 13, 2011, 10:48:13 AM7/13/11
to sqlite3-ruby
Hi, I'm having an odd problem whereby I'm trying to store a hash in an
sqlite VARCHAR(40) column like so:

ruby-1.9.2-head :001 > require "digest/sha1"
=> true
ruby-1.9.2-head :002 > require "sqlite3"
=> true
ruby-1.9.2-head :003 > db = SQLite3::Database.new "test.db"
=> #<SQLite3::Database:0x00000100942388>
ruby-1.9.2-head :004 > the_hash = Digest::SHA1.hexdigest("hello")
=> "aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d"
ruby-1.9.2-head :005 > db.execute("insert into invitations (token)
VALUES (?)", the_hash)
=> []

but I can't retrieve by searching the for the hash

ruby-1.9.2-head :006 > db.execute("select token from invitations where
token = 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d'")
=> []

unless I use lower(token)

ruby-1.9.2-head :006 > db.execute("select token from invitations where
lower(token) = 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d'")
=> ["aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d"]

The reason it appears is that sqlite3-ruby inserts the hash as type
blob but them I'm trying to read it as type text so I get nothing.

sqlite $ sqlite3 test.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select typeof(token) from invitations;
blob
sqlite>

Here is the dump:

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE invitations (
token varchar(40)
);
INSERT INTO "invitations"
VALUES(X'61616634633631646463633565386132646162656465306633623438326364396165613934333464');

So, is there a way that I can force sqlite3-ruby to insert as a string
so that I can read it back by finding by the same string?

More importantly (kinda), is there a way I can do it through
activerecord:

class Invitation < ActiveRecord::Base
before_create :generate_token

def generate_token
self.token = Digest::SHA1.hexdigest([Time.now, rand].join)
end
end

Here's the (messy) stackoverflow thread I made to try and figure this
out:
http://stackoverflow.com/questions/6674644/is-there-are-reason-i-select-by-a-string-columns-which-contains-sha1-hexdigests/6679717#6679717

And Here is the subsequent chat with one of the users where we figured
out what was happening:
http://chat.stackoverflow.com/transcript/1397

Aaron Patterson

unread,
Jul 13, 2011, 10:01:36 PM7/13/11
to sqlite...@googlegroups.com
Try encoding the hash as UTF8. Binary strings will be stored as blobs.

--
Aaron Patterson
http://tenderlovemaking.com/
Forgive me for top posting, I'm on my iPhone.

duckyfuzz

unread,
Jul 13, 2011, 10:15:14 PM7/13/11
to sqlite3-ruby
Excellent. That worked perfectly! Thanks.

On Jul 14, 3:01 am, Aaron Patterson <aaron.patter...@gmail.com> wrote:
> Try encoding the hash as UTF8. Binary strings will be stored as blobs.
>
> --
> Aaron Pattersonhttp://tenderlovemaking.com/
> > VALUES(X'616166346336316464636335653861326461626564653066336234383263643961 65613934333464');
>
> > So, is there a way that I can force sqlite3-ruby to insert as a string
> > so that I can read it back by finding by the same string?
>
> > More importantly (kinda), is there a way I can do it through
> > activerecord:
>
> > class Invitation < ActiveRecord::Base
> >  before_create :generate_token
>
> >  def generate_token
> >    self.token = Digest::SHA1.hexdigest([Time.now, rand].join)
> >  end
> > end
>
> > Here's the (messy) stackoverflow thread I made to try and figure this
> > out:
> >http://stackoverflow.com/questions/6674644/is-there-are-reason-i-sele...
Reply all
Reply to author
Forward
0 new messages