It (3DES Wrapper) will encrypt them, but any attempt to decrypt a string greater than 7 characters generates the error Invalid length for a Base-64 char array. I have reproduced on several machines running 1.6.6 using very simple code to decrypt and enrypt a string (no BO's, no custom anything).

For example "1111111" encrypts and decrypts fine, but "11111111" will encrypt but not decrypt.

Any insight appreciated as always.

The occurs in tripledeswrapper.vb at the function Decrypt, in the first line of the function:

Public Function Decrypt(ByVal text As String) As String

Dim input() As Byte = Convert.FromBase64String(text)

Dim output() As Byte = Transform(input, m_des.CreateDecryptor(m_key, m_iv))

Return m_utf8.GetString(output)

End Function

Keith Chisarik

Querying on a partial SSN will not work because the encryption string would not be the same with 3 chars versus 9, etc.  However, you can encrypt the data prior to sending the query to the server through the Searching event.  In the Searching event you can encrypt the text and sent the encrypted value back to the server instead of the clear text.  So in the Searching event you would just enumerate through the passed in event args which includes the raw Where clauses and of the SSN was supplied then encrypt the value.  The only thing that would be required in order for this to work is that they would have to enter the full SSN number.

If you don't like (or it isn't flexible enough) the idea of denormalizing and adding an additional column for the last four (which you might not even need to encrypt), the only thing I can think of is to load a BO with all the records, probably just the SSN and PK and then use this to filter the results returned. I guess what you could do in the searching event is get the partial SSN entered and how the user is comparing this to the SSN field (string search, so starts with, ends with, contains, equals), use that to retrieve all the PKs that match the indicated comparison from the BO with PKs and SSNs. Then add a where clause that would check if the PK is one of the PKs that match the SSN (using an IN()). Not very pretty....



I'd question why the data is encrypted within a field in the first place...I'm sure this is out of your control, but seems over kill since the db should have security in place and the application should have security in place to restrict access.