Is there are reason I select by a string columns which contains SHA1 hexdigests?

Question

I have an invitations table which looks like this

sqlite> .schema invitations
CREATE TABLE "invitations" 
    ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
    , "sender_id" integer
    , "recipient_email" varchar(255)
    , "token" varchar(255)
    , "sent_at" datetime
    , "team_id" integer
    , "created_at" datetime
    , "updated_at" datetime
    );

CREATE UNIQUE INDEX "index_invitations_on_recipient_email_and_team_id"
    ON "invitations" ("recipient_email", "team_id");

CREATE INDEX "index_invitations_on_sender_id"
    ON "invitations" ("sender_id");

CREATE INDEX "index_invitations_on_team_id"
    ON "invitations" ("team_id");

The token column stores hexdigests which are generated on record create like so (Ruby):

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

When I insert an invitation into the database, I can retrieve it with

SELECT * FROM "invitations" where "invitations"."recipient_email" = "an email"

but

SELECT * FROM "invitations" where "invitations"."token" = "an token"

returns noting even though I'm copy/pasting the exact token from the insert statement?

Edit
It turns out that

SELECT * FROM "invitations" where "invitations"."token" LIKE "an token"

will retrive the record correctly.

Why would "LIKE" work, but "=" not? I've tried stripping the hex before insert and doing a case insensitive select. Neither worked.

Edit 2 It seems I'm able to replicate this issue using only the rubygem "sqlite3" and the command line. That is without Rails etc.

Here's the process:

stuff $ gem install sqlite3
Fetching: sqlite3-1.3.3.gem (100%)
Building native extensions.  This could take a while...
Successfully installed sqlite3-1.3.3
1 gem installed
Installing ri documentation for sqlite3-1.3.3...
Installing RDoc documentation for sqlite3-1.3.3...
stuff $ irb
ruby-1.9.2-head :001 > require "sqlite3"
ruby-1.9.2-head :017 > rows = db.execute <<-SQL
ruby-1.9.2-head :018"> create table invitations (
ruby-1.9.2-head :019"> token varchar(40)
ruby-1.9.2-head :020"> );
ruby-1.9.2-head :021"> SQL
# with normal strings for comparison
ruby-1.9.2-head :022 > ['4535435', 'jfeu833'].each {|hash| db.execute "insert into 'invitations' ('token') values (?)", hash }
 => ["4535435", "jfeu833"] 
ruby-1.9.2-head :023 > db.execute("select * from invitations where invitations.token = '4535435'") {|row| p row }
# it finds the row successfully
["4535435"]
 => #<SQLite3::Statement:0x000001011741c8> 
ruby-1.9.2-head :028 > require "digest/sha1"
 => true 
# now to try it with a hash
ruby-1.9.2-head :029 > [Digest::SHA1.hexdigest("banana")].each {|hash| db.execute "insert into 'invitations' ('token') values (?)", hash }
 => ["250e77f12a5ab6972a0895d290c4792f0a326ea8"]
ruby-1.9.2-head :031 > db.execute("select * from invitations where invitations.token = '250e77f12a5ab6972a0895d290c4792f0a326ea8'") {|row| p row }
# notice that no record is printed
 => #<SQLite3::Statement:0x0000010107c630>

I edited the post with the schema. Let me know if you'd like to see anything else. — David Tuite, Jul 13 '11 at 06:43
Yep. `select "token" from "invitations"` gets me a token correctly -> `e8c6ab9d5d1df98c906952c58e1be4d640d3c5ae`. Copy pasting that into a select like so: `select * from "invitations" where "invitations"."token" = "e8c6ab9d5d1df98c906952c58e1be4d640d3c5ae";` returns nothing. — David Tuite, Jul 13 '11 at 06:55
Ok now I got something. When I do `insert into "invitations" ("recipient_email", "token") values ("fred","e8c6ab9d5d1df98c906952c58e1be3d640d3c5a7");` and then retrieve like `select * from "invitations" where "invitations"."token" = "e8c6ab9d5d1df98c906952c58e1be3d640d3c5a7";` I get the record out. So there must be something going wrong with my inserts (which were done within the Ruby on Rails environment). But what? — David Tuite, Jul 13 '11 at 07:30
@muistooshort let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/1397/discussion-between-duckyfuzz-and-mu-is-too-short) — David Tuite, Jul 13 '11 at 07:38
@duckyfuzz: Can you try with `WHERE invitations.token = '250e...' COLLATE RTRIM` ? — ypercubeᵀᴹ, Jul 13 '11 at 12:08
@ypercube: Here's the query I ran -> `SELECT * FROM invitations WHERE invitations.token = '03ae5376b6a590840ebe9b138999133debbdc5c1' COLLATE RTRIM;`. It returns nothing. To show the record really is there: `SELECT * FROM invitations WHERE invitations.recipient_email = 'bob@email.com' COLLATE RTRIM;` returns `1|1|bob@email.com|03ae5376b6a590840ebe9b138999133debbdc5c1||1|2011-07-13 12:23:42.111740|2011-07-13 12:23:42.111740`. Those tokens should match fine. — David Tuite, Jul 13 '11 at 12:29

score 5 · Accepted Answer · edited Jul 14 '11 at 05:25

I discussed this with the duckyfuzz (the OP) in a chat, and we found that the hash is stored as a BLOB in sqlite:

sqlite> select typeof(token) from invitations; 
blob 
blob

So for some reason, even though ruby says that what is being inserted is a string:

irb(main):002:0> (Digest::SHA1.hexdigest("banana")).class() 
=> String

it ends up in sqlite as a BLOB.

Interpolating the value, or inserting as literal, instead of inserting using parameters makes the problem go away (as tested by the OP):

oh ok got it
ruby-1.9.2-head :010 > db.execute("insert into invitations (token) VALUES ('#{the_hash}')") 
=> []
ok now the dump..

INSERT INTO "invitations" VALUES('bda04628ea94f26cac0793eac103258eb515c505');
much better!

The issue is caused by the fact that binary strings will be stored as blobs by the sqlite3 rubygem. The way to prevent this is to encode the hash as UTF-8 before insert.

hash = Digest::SHA1.hexdigest("banana").encode("UTF-8")
db.execute("insert into invitations (token) values (?)", hash)

Once you do that, the hash will be stored as text.

Hehe I kinda have to assume that something of the sort is happening here, but I don't know ruby so my ability to verify this is limited. — , Jul 13 '11 at 17:03

ypercubeᵀᴹ · Answer 2 · 2011-07-13T11:07:15.440

0

Changing token from varchar(255) to char(40) may help.

One other thing that bugs me is that you use double quotes: "

I've always thought that

select *
from "invitations" 
where "invitations"."token" = "e8c6ab9d5d1df98c906952c58e1be4d640d3c5ae"

is different than:

select *
from 'invitations' 
where 'invitations'.'token' = 'e8c6ab9d5d1df98c906952c58e1be4d640d3c5ae'

and different than:

select *
from `invitations` 
where `invitations`.token` = `e8c6ab9d5d1df98c906952c58e1be4d640d3c5ae`

Not at all sure how Rails and SQLite behaves on this matter. I'm always using (in MySQL and SQL-Server):

select *
from invitations 
where invitations.token = 'e8c6ab9d5d1df98c906952c58e1be4d640d3c5ae'

edited Jul 13 '11 at 11:07

answered Jul 13 '11 at 10:04

ypercubeᵀᴹ

113,259
19
174
235

Nope. Doesn't seem to have worked. Thanks for the suggestion anyway. – David Tuite Jul 13 '11 at 10:53
@duckyfuzz: I installed ruby, rubygem-sqlite3, and yet I can't seem to load sqlite3 via "require 'sqlite3'"; any idea what else I need to do to use sqlite3? (on Fedora 15) – Jul 13 '11 at 10:56
@sixfeetsix: I just guessed it may be some kind of strange behaviour (or conversion) to varchar(255) before the equality check. He needs only 40 chars anyway, doesn't he? – ypercubeᵀᴹ Jul 13 '11 at 10:57
@ypercube: Not sure about any overhead of having >40 in the column definition caused by ruby's sqlite3 module, but as far as sqlite only is concerned, it changes nothing; to make this stand-out I only use TEXT in my table definitions. – Jul 13 '11 at 11:00
@sixfeetsix Once you install the 'sqlite3' gem via `gem install sqlite3` you should be good to go. Try opening a ruby interactive console session by typing `irb` in the folder you installed the sqlite3 gem (i dunno if you installed it globally or not). In `irb`, `require "sqlite3"` should return `=> true` and then you can run sqlite stuff. https://github.com/luislavena/sqlite3-ruby for more info – David Tuite Jul 13 '11 at 11:10
@duckyfuzz: any chance you can link a db with that strange token? – Jul 13 '11 at 11:27
@sixfeetsix I added complete steps to replicate the problem with the `sqlite3` rubygem. I'll see if I can put a sample DB up somewhere and link it. – David Tuite Jul 13 '11 at 11:43
@duckyfuzz: I must really suck with this ruby/gem/install/require stuff, please see the chat for what I did and see if you can tell me where things go awry... – Jul 13 '11 at 11:53

Is there are reason I select by a string columns which contains SHA1 hexdigests?

2 Answers2

Linked