From: John Kraal (johnhumanique.com)
Date: Thu Oct 25 2007 - 02:09:52 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Auch, thanks for pointing that out, what a terrible mistake.

I am aware of the performance issue, and so is the customer. But with a
table that's only going to hold maximally 60.000 records in 10 years,
I'm not afraid it'll cause significant problems. If it gets out of hand
we'll have to think of a better solution.

Once again, thanks!

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,
>
> 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:
>
> >>>>>
> (jcolehekla) [~/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
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------
>>>>
>>>>
>>>>
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]