Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Blastrix
Posting Yak Master
208 Posts |
 Posted - 2002-11-13 : 20:05:27
|
| I don't know if anyone will have an answer for this, but does anyone have any idea on how to query encrypted data? My encryption logic resides in a COM component, and as such, is inaccessible on SQL Server. However, I'm presented with situations where I need to search for a partial credit card number, or part of a card holder's name. I thought about just encrypting the data being searched for, but I quickly realized that that simply wouldn't work due to the encryption being totally different between the two values. The only solution I have come up with is that I search by any other criteria, and then I filter the results in my code, after decryption. Can anyone think of a better way to do this?ThanksSteve |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-13 : 22:03:24
|
| Sounds like two problems. 1 The credit card numbers are stored as a complete CC number, encrypted.2 Your encryption com component is not currently referenced from SQL.The second problem is probably solvable. You can write SQL to reference a com component within a procedure. There are some examples on sqlteam about doing this. Try the search on the homepage or check the email example at[url]http://www.sqlteam.com/item.asp?ItemID=5003[/url]But even if you solve this problem, you only have part of the CC number to compare against a full CC number that's encrypted. If that's right I don't think there's a good solution. Decrypting every value in the column then comparing partials isn't thinkable if there's a large number of rows.If you added a partials column, (with encrypted partial values), you could encrypt the item you're looking for, then select from the partial column to find a match. But this requires adding another column for partials. Does this make sense?Sam |
 |
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2002-11-13 : 23:32:04
|
| Great idea Sam. It won't be perfect, but if I store maybe the first four, and last four as partials, or maybe even all four, four number blocks, then I can work out something with the querying to determine which part to search against. Regarding the names though, I see problems arising. Using a LIKE comparison will be out of the question, but also the encrypted name 'bob' and 'Bob', will be completely different. I guess the solution to that is to convert everything to upper or lowercase prior to saving it to the DB, and that if you're searching for a name you have to be spot on. Oh well, a small enough price to pay for having the security of encryption.Thanks for your suggestions.Steve |
|
|
|
|
|
|
|
|
Querying Encrypted Data