On Sat, Nov 14, 2009 at 4:27 PM, Naoko Reeves <naoko@lawlogix.com> wrote:
> I have a encrypted column use encrypt function.
>
> Querying against this column is almost not acceptable – returning 12 rows
> took 25,908 ms.
>
> The query was simply Select decrypt(phn_phone_enc) FROM phn WHERE
> decrypt(phn_phone_enc,’xxx’,’xxx’) LIKE ‘123%’
>
> So I built index like: CREATE INDEX idx_phn_phone_dec ON phn
> (decrypt(phn_phone_enc, ‘xxx’, ‘xxx’))
>
> This returns 12 rows in 68 ms.
>
> Would this be the solution for the fast encrypted field search or does this
> raise the security issue?
You are storing the unencrypted phone number in the index...can't do
that. As I see it, any solution that needs to support 'LIKE' (or
anything other than equality case) is going to be problematic because
it has to expose details of the encrypted data to work. It may be
possible to rig something...how high are your security requirements?
merlin