SEO

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

--
/ Humanique
/ Webstrategie en ontwikkeling
/ http://www.humanique.com/

-
Humanique zoekt een ervaren Web-ontwikkelaar (PHP).
Bekijk de vacature op http://www.humanique.com/
-



-- MySQL dump 10.10
--
-- Host: localhost Database: hmnq_test
-- ------------------------------------------------------
-- Server version 5.0.30-Debian_3-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `encryptie`
--

DROP TABLE IF EXISTS `encryptie`;
CREATE TABLE `encryptie` (
`id` int(11) NOT NULL auto_increment,
`field` blob NOT NULL,
`md5sum` varchar(32) NOT NULL,
`line` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `md5sum` (`md5sum`),
KEY `line` (`line`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `encryptie`
--

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2007-10-23 12:17:47

Have you tried reversing the order of your tests, to see if there is some
influence from caching?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

> -----Original Message-----
> From: John Kraal [mailto:john@humanique.com]
> Sent: Tuesday, October 23, 2007 8:51 AM
> To: mysql@lists.mysql.com
> Subject: Query performance plain/text versus AES_DECRYPT(): LIKE %..%
>
> 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
>
> --
> / Humanique
> / Webstrategie en ontwikkeling
> / http://www.humanique.com/
>
> -
> Humanique zoekt een ervaren Web-ontwikkelaar (PHP).
> Bekijk de vacature op http://www.humanique.com/
> -
>

Yes, I did, and shutdowns between the tests and between reversing the tests.

--
/ Humanique
/ Webstrategie en ontwikkeling
/ http://www.humanique.com/

-
Humanique zoekt een ervaren Web-ontwikkelaar (PHP).
Bekijk de vacature op http://www.humanique.com/
-


Jerry Schwartz wrote:
> Have you tried reversing the order of your tests, to see if there is some
> influence from caching?
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>> -----Original Message-----
>> From: John Kraal [mailto:john@humanique.com]
>> Sent: Tuesday, October 23, 2007 8:51 AM
>> To: mysql@lists.mysql.com
>> Subject: Query performance plain/text versus AES_DECRYPT(): LIKE %..%
>>
>> 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
>>
>> --
>> / Humanique
>> / Webstrategie en ontwikkeling
>> / http://www.humanique.com/
>>
>> -
>> Humanique zoekt een ervaren Web-ontwikkelaar (PHP).
>> Bekijk de vacature op http://www.humanique.com/
>> -
>>
>
>
>
>

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

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
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>

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

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:
>
> >>>>>
> (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
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------
>>>>
>>>>
>>>>
>>
>