Naoko Reeves 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?
>
very ugly for all the reasons given by the others.
if all your LIKE searches are specifically for the first 3 chars of the
phn_phone field, I dunno, but maybe you could have a seperate field (and
index) which consists of just the first three characters encrypted.
this would, however, allow people with access to the database but
without the cipher key to group your data by this prefixm, and
potentially the knowlege of that grouping could be used as an aid in
cracking the keys.