Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Decryptbykey Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 5:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 1:09 PM
Points: 9, Visits: 19
Hi

I am using an encrypted db, I run a select query but certain columns remain encypted.
To resolve this I used the

CONVERT(nvarchar(200),DECRYPTBYKEY(REG))

but when I filter on REG.

Where name like '%DY%'

It returns some values that don't meet the filter criteria.

What is needed


Post #1409492
Posted Monday, January 21, 2013 5:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
robert.nesta123 (1/21/2013)
...
but when I filter on REG.
....

You can't filter on REG in this way, because it is encrypted, you can filter only on a decrypted value so:
CONVERT(nvarchar(200),DECRYPTBYKEY(REG)) like  '%DY%'

Post #1409518
Posted Monday, January 21, 2013 6:09 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
Just as a word of caution here, if you put your DECRYPTBYKEY and CONVERT functions to the left of the operator, they'll both have to run on every row in the table to be able to perform the LIKE comparison and return the result set. I've found the performance impact of doing this to be significant.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1409522
Posted Monday, January 21, 2013 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
Ed Wagner (1/21/2013)
Just as a word of caution here, if you put your DECRYPTBYKEY and CONVERT functions to the left of the operator, they'll both have to run on every row in the table to be able to perform the LIKE comparison and return the result set. I've found the performance impact of doing this to be significant.


But with an encrypted column, you can't do this in another way. With = you can encrypt the searched value and search for equality.
Post #1409526
Posted Monday, January 21, 2013 6:22 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
I know what you mean. I don't know what your requirements are, but if you know the users can search by the first two (and only two) characters of a last name or by some other constant substring, you may be able to store that somewhere else in the table. Or you could store the ASCII values of the first two characters of the string as an integer, index it and then include that field in your WHERE clause. If, however, your specs state that the name has to be completely encrypted, then you're out of luck.

I've seen performance problems when doing something like this when the table gets large and just wanted you to start thinking about it up front so you're not caught off guard later.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1409528
Posted Monday, January 21, 2013 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 1:09 PM
Points: 9, Visits: 19
Thanks that worked using the encrypted version in the filter. I thought I had already tried that. I must of got the syntax wrong.

In terms of performance, I'm running on a copy of LIVE in test so not an issue but I will keep it in mind. Thanks again.
Post #1409529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse