Hi John,
OK, no conspiracy here. Here is your problem:
25 $qry = sprintf("SELECT id, line FROM `encryptietest`
WHERE AES_DECRYPT(`field`, '%') LIKE '%%%s%%'", $enckey, $word);
You are missing the "s" in "%s" for your first string argument, which
causes the query to be syntactically incorrect and fail. So your AES
test is only testing how quickly you can query with a syntax error. :)
After adding the "s", the results I get are:
>>>>>
(jcole@hekla) [~/datisstom/bench]$ php -q bench.php
Control test (plain/text LIKE %..%): 1.383749s
Decrypt test (AES_DECRYPT() LIKE %..%): 1.441944s
done
<<<<<
Nonetheless, I'd still argue that this entire concept is doomed to
terrible performance anyway.
Regards,
Jeremy
John Kraal wrote:
> I put it here:
>
> http://pro.datisstom.nl/tests/bench.tar.bz2
>
> The encryption isn't really a *real* security measure, except for when
> somebody is stupid enough to install phpMyAdmin or anything equivalent
> and try to get personal data. The problem is the password needs to be
> anywhere on the application-server and if you're in, you're in. But it's
> a request and I'm happy to oblige. Even if it only stops them for 1
> minute (which could be enough).
>
> Regards,
>
> John
>
> --
> / Humanique
> / Webstrategie en ontwikkeling
> / http://www.humanique.com/
>
> -
> Humanique zoekt een ervaren Web-ontwikkelaar (PHP).
> Bekijk de vacature op http://www.humanique.com/
> -
>
>
> Jeremy Cole wrote:
>> 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
