Subject FB Embedded: Table Field encryption questions
Author Chuck Belanger

I just finished reading Geoff Worboys' "Firebird File and Metadata
Security" which raised a number of interesting points about my project
and I really could use some help with these questions.


I'm using FB Embedded for a desktop application which has a distributed
database. We all know what that means as far as security. I'm under no
illusions about iron clad security in such a scenario. I just finished a
modest program for my wife for her learning center business which is a
testing and evaluation program with a database of reading material and
various tests. (D7 for Windows)

I successfully encrypted that and decrypt it using OnGetText event or
just directly decrypt the BLOB streams as necessary. Everything works
just fine. I have reasonably good key control. I can sleep at night just

Now I'm working on another project in which the user will be making a
number of searches of VarChar fields and even in BLOBS--the very same
fields that should be encrypted to protect my intellectual property.

I really had not got to the point of introducing encryption yet as I was
reading Geoff's article, but I can see there's a real problem here: How
does one search an encrypted field or BLOB? What about index's?

Geoff states:

"In the second instance (meaning concern over the integrity of the data)
it may be possible to implement client side encryption/decryption
features, but then you will lose the ability to make effective use of
database indexes and search facilities – and key management remains a
major problem."

I'm not particularly concerned about Metadata structure (Geoff's "first
instance") getting to any possible competitors. Nor am I particularly
concerned about absolute key control--I feel that I have a reasonable
handle on that issue for this particular product.

I thought about writing a UDF that does the decryption for me so that
the query results would be decrypted and then I could dataset.locate or
something similar for BLOBs.

i.e. SELECT FB_DECRYPT(hashed key, TEXT_FIELD), FB_DECRYPT(hashed key,

But a UDF declared in the database would be pretty much of a red flag to
any half way tech person looking at the metadata, plus the dll would be
right there. The only thing that keeps everything secure at that point
would be keeping the key safe.

Any thoughts on using an UDF? I would certainly attempt to do this. But
I noticed a suspicious lack of anyone else doing this when I searched
the Internet, plus I don't remember reading about anyone doing this

At this point a field search would mean looping through the dataset one
row at a time decrypting and seeing if the item matches. This seems
horrendously slow!

I was thinking about encryption/decryption schemes (albeit not
particularly secure) that have a character by character replacement.
Thus a search word or phrase could just be converted then looked up in
the "encrypted" dataset. As you can imagine, that seems like no security
at all (it would not take long to create a text conversion key and apply
it to the entire set of tables and fields.)

Geoff suggests the use of an encrypted volume on the user's disk, but
I'm not sure if I could control such a system during the install of my
program or open the volume for the program to use without having the
user become manually involved with the password.

He also suggests the use of:
"A simple XOR against some known string (the key) is sufficient - this
will obscure, and while the key is not known to the thief it will be
relatively difficult to break as long as some care is taken."

What do you think? It still sounds like I'd need a UDF to implement

Is there another/better way? At this point I'll accept "security by
obscurity," but I'm not even sure how to do that and still be able to
search the dataset.

Thank you for any thoughts or help,

Chuck Belanger