Good luck!

yup nothing pretty about it, I have no control over it either, they have to be encrypted at the database level. Thank you for the comments and ideas, I'll let you know how it works out

Keith Chisarik

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.

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.

Gregs idea for the full SSN would probably work, but I need partial SSN searches, if you have any thoughs on that I would appreciate it. There may just not be a way around this since the BD queries the table directly and not the BO property that is decrypted, but I would like to hear that from "the man" before looking for another less desireable solution.

Keith Chisarik

Let me know if you didn't get your question answered...I have been more busy than a one legged man in a butt whoopin' contest lately...there is some more Texas lingo for ya .  I have been trying to create a POS (Point of Sale) interface and getting some of these hardware companies to work with you do give you the information to write software for their devices has been a pain!  If anyone has any experience with writing an app towards a Verifone 1000SE device, then please feel free to share!!!  I think I just finally signed the last required NDA to get a development guide with all of the commands to send to the device...I have my fingers crossed!  I have been trying to write towards OPOS for all devices, but I have finally given up that pipe dream as I don't think I am going to be able to shoot par with OPOS as some of these devices won't support it...but I suppose that is life in the development world..OK, I will shut up now!

That could be tough. I just checked and if you encrypt the last four, it won't be anything like the equivalent last four in the full SSN.



I.e. here is an example (SSN -> encrypted text)



555-55-5555 -> zScg4jyKGSbryxUfHJktAQ==

5555 -> Z8cKO1v0OfI=



One option might be to denormalize a bit and store the last four in another column...



Hopefully somebody a lot smarter than me will respond soon!

This is the best forum ever!

Keith Chisarik

Hmm lll think on that Greg thanks for the reply. I do know I have to be able to search for the last 4 digits of the SSN, in fact the customer requires that the SSN is always shown as ***-**-9999 except for very high level users. 

Keith Chisarik

Keith,



I'm not the best person to answer the question (I don't use the browse dialog), but some times even a bad answer can spark a thought and help you move forward.



I'd try using the Searching event to change the query. Since it is an SSN, hopefully, you will always be providing the full SSN and using an equality comparison. If so, I'd look through the where collection for SSN field and then encrypt the value provided by the user and use that value instead of the one they provided. That way you'll be comparing encrypted value to encrypted value.



No idea if it will work, but that's were I'd start...good luck!