Does anyone have a suggestion how I could speed up the process of finding AES Encrypted data in fields with a database with >10,000 records?

Seems that each record being decrypted makes the find to take much longer than I expected. (I figured it would add some overhead…)

So far the only idea I have come up with was to create another field thats encrypted using the Base64Encode function and search for records that are >= the Base64Encoded search criteria. Sometimes it returns results that are not even close to what you searched for.

— In depth section—

First, middle and last names are stored encrypted in the database. In order to search these, I was searching the (unstored) calculated result field. If it were a stored calculation (plain text), it would defeat the purpose of encryption. Worked fine with 10s and 100s of records, however after importing 10k records, it takes a couple minutes to search for a first name that contains "jo" (such as John or Jon). Not gonna work for the client. ScriptMaster has a few other options, of which Base64Encode seemed to be the only option. I may be stuck in a paradigm though and needed some ideas from others…

Base64Encode mostly works if you search more than ½ the total characters in the respective name field. So, I tried the PerformFind ( <stored Base64Encryption of respective field>">=" <search criteria stored in variable> ) function, and well, that some times returns names that are not even close to what you were searching for depending on how many characters you specify. In one case, I while searching for a last name that is 7 characters long, searching on the first 3 characters got results but the first 4, 5, and 6 resulted in nothing.

The variable is set to substitute nothing when it encounters an equal sign so perhaps that is part of the problem. Not everything Base64Encoded ends with one or two equal signs.

The point for the encryption is along with other safeguards, if someone were to get a copy of the database, they will not be able to read it even if they have a password reset program or cracking program for FileMaker.

Some other options I have pondered:

Decrypting names into temporary table or fields on open and deleting records on close (not very practical IMHO)

No encryption, but thats not an option so I give up...

I was searching the (unstored) calculated result field.

And what would prevent a use of a cracked file (with full access) from getting into those fields? If you're storing the encryption calculation (aka the key) anywhere in the same file, you're leaving a big hole. Or are you removing admin access from the deployed file? In which case what are you worried about?

One idea is that you could store partial names in one or more fields, kind of like the way credit cards are displayed as xxxx-xxxx-xxxx-1234. Decide how many letters are acceptable, for first last, and then users could search and view "Toxxxx Fixxxx" or something. The full name would never be decrypted unless the user explicitly requests it, and then you could track that activity as needed.

And what would prevent a use of a cracked file (with full access) from getting into those fields? If you're storing the encryption calculation (aka the key) anywhere in the same file, you're leaving a big hole. Or are you removing admin access from the deployed file? In which case what are you worried about?

One idea is that you could store partial names in one or more fields, kind of like the way credit cards are displayed as xxxx-xxxx-xxxx-1234. Decide how many letters are acceptable, for first last, and then users could search and view "Toxxxx Fixxxx" or something. The full name would never be decrypted unless the user explicitly requests it, and then you could track that activity as needed.

Nothing thats considered PII (Personally Identifiable Information) is stored in plain text, hence the problem when in find mode.

The data that I am currently searching is the result of the Base64Encode of the first and last names for now. Its not plain text, however its not impossible to decrypt either.

I do appreciate the suggestion about separate fields though and will evaluate my options with that included.

Currently experimenting creating array and copying to global field when searching to locate values then erasing the global field.

you create a duplicate data set of fields that is a one way HASH of the data plain text data -

then encrypt the data fields

when searching perform a quick HASH of the search term use this search the HASHed fields.

you create a duplicate data set of fields that is a one way HASH of the data plain text data -

then encrypt the data fields

when searching perform a quick HASH of the search term use this search the HASHed fields.

Only problem with that idea is that you cannot do partial searches on a person's name(s). Example: John Doe may be searched as "jo" in the first name and "d" in the last name. "Jo" does not hash out the same as "John".

UPDATE

I found that the majority of the problem was due to a problem with the imported data.

Many of the fields (un-stored text calculations) showed ERROR and that was due to invalid data in 2 of the 3 fields for name, First and Middle somehow didn't import correctly for many records of the 10k total. A 2nd import seemed to fix things. Strangely one OLD computer took over 10 minutes to return results, other identical machines from its generation were only about 22 seconds to decrypt and search just over 10k records.

So bottom line is searching the individual name fields (First, Middle, and Last) OR the concatenated full name field returns results in about 6-22 seconds depending on the age of the machine.

Looking into option using global field to store Record ID and F,M,L on one line with double || delimiters to see if that speeds up the find…

Thanks for the feed back though!