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:
>>>>>
([EMAIL PROTECTED]) [~/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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]