Querying Encrypted Data

[philhege]

Just polling the populace, because I think I might already know the answer (the answer being: "I'm hosed").

I store PCI data in an encrypted column. The end users query the data by entering (you guessed it) a PCI value - in plain text. The system we're designing must be high-performing, with sub-second responses to queries against billion-record tables.

Any recommendations on how to make this system perform to (or even close to) spec given the search parameter constraint? NB: TDE is not an option in this environment. The data must be encrypted at the field level.

-----------
With business clients like mine, you'd be better off herding cats.

 

[gmmastros]

I have no idea what PCI data is, but there is an interesting way you can handle this.

The details can be found here:

http://msdn.microsoft.com/en-us/library/ms189788.aspx

Basically, you create a computed column that represents the checksum of your actual data. In this case, it would be the unencrypted version. The checksum value cannot be converted back to it's original value. It's also possible for multiple values to produce the same checksum value.

The trick here is... you add an index on the computed checksum column and then you compare the checksum of the input data (user entered value) to the computed (indexed) column. This will likely narrow your results from billions down to a couple hundred (or less).

Checksum returns an int, so it only takes 4 bytes per row to store.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Hey George,

PCI data is the storage of credit card data.

Simi

 

[philhege]

Hey George,

I found a blog that uses HashBytes() (mentioned in the CHECKSUM article because it's "stronger"). It's an interesting read and (so far) works well in my initial performance tests.

Oh, BTW here's the PCI reference.

-----------
With business clients like mine, you'd be better off herding cats.

 

[gmmastros]

All I can say is... I'm glad I'm not storing credit card data! I'm glad I was able to point you towards a possible solution.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

We use the hash system to search. Our table has an encrypted data column, a token column and a search key column which is a hash. We use a seed for the hash as it makes it more difficult to match.
Something like:

OPEN SYMMETRIC KEY Key_01
   DECRYPTION BY CERTIFICATE A01;

DECLARE @Salt VARCHAR(50);

SET @Salt = (SELECT TOP 1 CONVERT(VARCHAR(30), DECRYPTBYKEY(SaltPhrase))FROM SALTTable ORDER BY CreateDate DESC);
--print @Salt

SET @SearchKey = HASHBYTES('SHA1', @Salt + LTRIM(RTRIM(@ClearAcct)));

We use the token where a reference to the account number is needed.



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!