Searching Encrypted Fields using A5_Encrypt_String()

[bobbeckett]

Has anyone been able to create a search which includes an encrypted field via a5_encrypt_string?

I have a requirement to store data in an encrypted state in SQL Server but still be able to provide a search against the encrypted data.

I'm sure I managed to get this working earlier today but somehow it has ceased to work. Either it was working by pure fluke or I changed something somewhere that caused my simple function to fail or I was asleep and dreaming.


Anyway, in a test version, I have a new record entry in a grid that has this in the CanInsertRecord server-side event:

DataSubmitted.encrypted = a5_encrypt_string(DataSubmitted.unencrypted, "abc")

It takes the user entry in the unencrypted field, encrypts it and saves it in the encrypted field. This works successfully. This is not the real world situation, just my test bed to try and prove the process.

I need to be able to search the encrypted data and decided to use the CanSearch function to change the plain text input into the encrypted version. I have a search field for the encrypted data and I added this code to change the user entry to be encrypted and therefore match the data in the table:

dim testsearch as c
testsearch = a5_encrypt_string(e.SearchDataSubmitted.encrypted, "abc" )
e.SearchDataSubmitted.encrypted = testsearch

When I run the function and check the SQL generated, the search field is blank ie the data I entered is properly overrided but not with the encrypted version, with no data.


If I change it to the following, it is also blank:

e.SearchDataSubmitted.encrypted = a5_encrypt_string("test_encrypt", "abc" )

If I drop the encryption it works ie the SQL command now includes the search filter specified and overwrites the user input:

e.SearchDataSubmitted.encrypted = "test_search"

Therefore, it seems that the a5_encrypt_string function does not work for me in CanSearch - or I've missed something obvious!


Now, if I go back to the old version, what I am doing does work:

dim testsearch as c
testsearch = encrypt_string(e.SearchDataSubmitted.encrypted)
e.SearchDataSubmitted.encrypted = testsearch

The search filter is shown in the command set and also retrieves the correct record ie it takes the plain text entry in the search field, encrypts it and then includes it as part of the SQL query thus bring back the correct data.


Now, I want to use the latest version of encryption and also have a requirement for a secure key which is not provided for in the earlier A5 incarnation so I am keen to work out why the new version does not seem to work or what I need to do to make it work.

Any assistance would be gratefully received, even if it is to point out what an idiot I am for missing something staring me in the face!

Thanks

Regards

Bob

[Jim Coltz]

Re: Searching Encrypted Fields using A5_Encrypt_String()

It's failing due to the characters used in the encryption. Try this.

DataSubmitted.encrypted = [COLOR="#FF0000"]urlencode([/COLOR]a5_encrypt_string(DataSubmitted.unencrypted, "abc")[COLOR="#FF0000"])[/COLOR] 

dim testsearch as c
testsearch = a5_encrypt_string(e.SearchDataSubmitted.encrypted, "abc" )
e.SearchDataSubmitted.encrypted = [COLOR="#FF0000"]urlencode([/COLOR]testsearch[COLOR="#FF0000"])[/COLOR]

[bobbeckett]

Re: Searching Encrypted Fields using A5_Encrypt_String()

Jim,

You are a life saver, it works a treat! I have no idea how I would have come up with the solution otherwise.

Thanks again,

Regards

Bob

It's failing due to the characters used in the encryption. Try this.

DataSubmitted.encrypted = [COLOR="#FF0000"]urlencode([/COLOR]a5_encrypt_string(DataSubmitted.unencrypted, "abc")[COLOR="#FF0000"])[/COLOR] 

dim testsearch as c
testsearch = a5_encrypt_string(e.SearchDataSubmitted.encrypted, "abc" )
e.SearchDataSubmitted.encrypted = [COLOR="#FF0000"]urlencode([/COLOR]testsearch[COLOR="#FF0000"])[/COLOR]