I’m working on a similar issue myself. I’m planning on encrypting
part of the data and I also need to do some “SQL” on the data, to aid
with reporting. I’m thinking of using a pair of UDF functions to
encrypt / decrypt data on the fly. Using such UDF’s allows stuff to
work on the SQL side, and you can use the UDF in “WHERE” clauses
(such as “where containing”) but, as it has been said before, that
would lead to a NATURAL search – at least on the text portion of the
search, it’s entirely possible that your code includes other search
clauses as well, and they may be used to limit the search enough to
make the “NATURAL” bit acceptable. Any ways, a “where containing”
search would be NATURAL with or without encryption.

Using this system you could deliver databases pre-encrypted with the
customer’s password, so customer X would be unable to use customer
Y’s database. It would also limit the damage posed by a “lost”
password.

In its basic form this system has one major weakness you’ll need to
address: It’s susceptible to “reply attacks”. One can easily create a
“clone” UDF DLL (the interface is well-known) and call your DLL from
the clone DLL. They’ll gain access to the passed password this way so
they’ll be able to decrypt the data using simple SQL and the stolen
password.

You might be able to protect against this by using some kind of anti-
reply algorithm, where you call a function on your DLL to give you
some “salt”, then you use that random (but known by the DLL) salt to
do some encryption on the password you’re sending out to the DLL.
Make the “salt” valid for only a few seconds and you might be on to
something...

You might also do something else, but this would clearly limit your
ability to use standard SQL. Don’t define encrypt/decrypt UDF’s,
define UDF’s for specific tests. Like:
TEXT_INCLUDES(ENCRIPTED_TEXT, TEXT, PASSWORD)
So you’d be writing SQL like this:
SELECT * FROM ARTICLES WHERE TEXT_INCLUDES_TEXT(ARTICLES.TEXT,
‘SomeText’, ‘HashedPassword’)=1
And not
SELECT * FROM ARTICLES WHERE DECRYPT_TEXT(ARTICLES.TEXT,
‘HashedPassword’) CONTAINING ‘SomeText’
Depending on your code, you can define enough functions to make this
work. This, of course, would not be immune to reverse engeneering
your UDF’s DLL or your main EXE, but that’s risk you can’t be without
since any data protection schema you might come up with would be
susceptible to this kind of attack.

You may combine all this tecniques with a "full text search" algoritm
(using "where containing" is a bad strategy any way) so you may
actually search encrypted text without decrypting it (you split the
search string into words, you encrypt the separated words and you
search against the full text search index)

--
Cosmin Prund
--- In firebird-support@yahoogroups.com, jesus martinez
<jesus_martinez1967@...> wrote:

>
>
> Anderson, thanks for your reply.
> i cant do this, because i have to make searches
> inside the records
>
> select .... where containing ....
>
> about the other suggestion about using TrueCrypt,
> written disadvanges are so clear that it is not
> an option.
>
> thanks in advance to both,
> j.-
>
>
> > Hi,
> >
> > 1) Encrypt the data on the client side then store
> > into FB.
> > 2) Read the encrypted data from FB, decrypt on the
> > client and display.
> >
> > Does it make sense? (that's what I do when need some
> > info to be encripted)
> >
> >
> > Regards,
> > Anderson
> >
> > [Non-text portions of this message have been
> > removed]
> >
> >
>
>
>
> __________________________________________________
> Preguntá. Respondé. Descubrí.
> Todo lo que querías saber, y lo que ni imaginabas,
> está en Yahoo! Respuestas (Beta).
> ¡Probalo ya!
> http://www.yahoo.com.ar/respuestas
>