List:General Discussion« Previous MessageNext Message »
From:Jeremy Cole Date:October 23 2007 11:32pm
Subject:Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%
View as plain text  
Hi John,

Your attachment for the php code got stripped somewhere.  Can you post 
it somewhere (http preferable)?  In either case it's going to result in 
a full table scan, so they are actually both a bad strategy long term, 
but they should in theory perform as you would expect, with with 
encryption being slightly slower.

Have you tried with longer strings?

What is your customer's fear with having the data in plain text? 
Presumably in order to use this in your application, you will have the 
AES password stored in your application, and it will end up in logs 
(such as the slow query log) quite frequently.  I would think your data 
can be safer and your security more effective by setting some policies 
which are less intrusive into the actual workings of the data, such as 
encrypting backups and setting system-level policies.

Regards,

Jeremy

John Kraal wrote:
> Dear you,
> 
> I've been working on encrypting some data for a customer. They want
> their personal/sensitive information encrypted in the database, but they
> want to be able to search it too, through the application. So we've been
> thinking a bit, and just started trying and benchmarking some solutions
> we thought up.
> 
> The next one really got my attention, I created a table with 4 fields:
> 
> 1. id (primary/auto_increment, not really interesting)
> 2. "field", with encrypted data
> 3. md5sum (it has no special use, we benched it though.)
> 4. "line", always containing three words (the same three as encrypted)
> 
> When we started querying the table for random words (from lipsum.com),
> it seems that searching in the encrypted fields was _lots_ faster.
> Results below:
> 
> 1.000 queries per field:
> 
> ~$ php -q searchtest.php
> Control test (plain/text LIKE %..%):    1.409699s
> Decrypt test (AES_DECRYPT() LIKE %..%): 1.226069s
> done
> 
> 1.000.000 queries per field:
> 
> ~$ php -q searchtest.php
> Control test (plain/text LIKE %..%):    155.059671s
> Decrypt test (AES_DECRYPT() LIKE %..%): 137.003216s
> done
> 
> Actually, the only thing I could think of to say was: "Well, at least 
> it's consistent".
> 
> I've attached all the files I used for this test. Edit db.inc.php (add 
> some more lipsum if you want), execute fill.php, and then have fun with 
> bench.php.
> 
> Does any of you know why this is, how come, etc? I'm just very curious.
> 
> Regards,
> 
> John Kraal
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 

-- 
high performance mysql consulting
www.provenscaling.com
Thread
Query performance plain/text versus AES_DECRYPT(): LIKE %..%John Kraal23 Oct
  • RE: Query performance plain/text versus AES_DECRYPT(): LIKE %..%Jerry Schwartz23 Oct
    • Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%John Kraal23 Oct
  • Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%Jeremy Cole24 Oct
    • Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%John Kraal24 Oct
      • Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%Jeremy Cole24 Oct
        • Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%John Kraal25 Oct