Posted by Greg D. Moore \(Strider\) on 05/28/05 04:21

"joshsackett" <joshsackett@gmail.com> wrote in message
news:1117212160.020006.275040@z14g2000cwz.googlegroups.com...
> 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.

I was just reading an article on this I think in this month's SQL Server
magazine.

I'll agree that encrypting last name is a bit "different".

One thing they suggested for things like credit card numbers is a) being
able to index on a column OTHER than the ccn so you can get the row(s) in
question and only decrypt that absolute minimum needed and if you DO need to
use the ccn, b) store the last 4 digits unencrypted to use that to help
narrow your search.


>
> 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]