Posted by Steve Kass on 05/29/05 05:34

Josh,

Hashing is not encryption, and hashing something like a last name
is useless, except for obfuscation. Only the 1,000,000 most common
last names in the world (if not 10,000) account for virtually everyone,
so if someone has a hash (say SHA1) of a last name, they basically
have the last name and can look up the hash in a small dictionary of
hashed last names. When hashing is appropriate, such as for creating
a message digest, it is not reversible. The sum of this is that
something based on the idea of "decrypting a hash" is flawed.


That said, the more you want to do efficiently with the encrypted
value, the less useful the encryption. If you can use LIKE or
other comparisons efficiently in predicates with the encrypted value,
you're letting your users play "Twenty Questions" with your data:

1. Does Secret start with the letter 'L' (LIKE 'L%')?
2. It does? Good. Does it satisfy WHERE Secret >= 'LN'?
3. No? Ok, does it satisfy WHERE Secret > 'LG'?
....

If you're just obfuscating the data with a reversible obfuscator,
you might just as well do this when someone needs a LIKE result:

select ...
from users_view
where dbo.deobfuscate(LNAME) like ' BON%'

If that's too slow, maybe you can manage to add dbo.deobfuscate(LNAME)
to the underlying table and index that computed column, hoping the
index will be used by the query. I'm not sure whether you can make
this work, but as Erland said, encryption and performance don't
go well together. Security and availability are Heisenbergian: you
can't have both, and the more of one you have, the less you have of
the other. This is as unavoidable as any law of physics.

Steve Kass
Drew University


joshsackett wrote:

> I am starting an encryption project for my database and I'm performing
> some tests on decryption speed. A lot of my application queries use a
> LIKE parameter in the WHERE clause. To keep from changing my
> application I am performing all the work on the back-end; creating
> views, triggers and UDFs to encrypt/decrypt the data. A problem has
> arisen around the LIKE parameter, though.
>
> Currently:
> SELECT SSN, FNAME, LNAME FROM USERS WHERE LNAME LIKE 'BON%'
>
> will become:
> SET @NEWVALUE = dbo.decrypt_hash('BON%')
> SELECT SSN, FNAME, LNAME FROM USERS_VIEW WHERE LNAME_HASH LIKE
> @NEWVALUE
>
> This will not work. A hash can only compare a string value to a string
> value. Has anyone else worked with this type of encryption and how did
> you get around using LIKE?
>
> Thanks,
> Josh
>

[Back to original message]