Skip site navigation (1) Skip section navigation (2)

Re: [PHP] Secure DB Systems - How to

From: Daniel Struck <struck(dot)d(at)retrovirology(dot)lu>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: Sarah Tanembaum <sarahtanembaum(at)yahoo(dot)com>,pgsql-php(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org,pgsql-hackers-win32(at)postgresql(dot)org, pgadmin-support(at)postgresql(dot)org,pgsql-sql(at)postgresql(dot)org
Subject: Re: [PHP] Secure DB Systems - How to
Date: 2004-07-13 09:35:57
Message-ID: 20040713113557.31e9d6dc@localhost (view raw or flat)
Thread:
Lists: pgadmin-supportpgsql-adminpgsql-hackers-win32pgsql-phppgsql-sql
> Keeping the system administrator from seeing the data while making it
> searchable is difficult. To do this you need to encrypt the data on
> the client side using a key the client has (and this key has to be
> protected from loss) and the only searches you can do are equality
> searches using a hash or encrypted value.

You can also perform regex searches.

Here is an example to get you started:

CREATE TABLE crypto (
id                 SERIAL PRIMARY KEY,
title              VARCHAR(50),
crypted_content    BYTEA
);


INSERT INTO  crypto VALUES (1,'test1',encrypt_iv('daniel','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
INSERT INTO  crypto VALUES (2,'test2',encrypt_iv('test','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
INSERT INTO  crypto VALUES (3,'test3',encrypt_iv('struck','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));

SELECT *,decrypt_iv(crypted_content, 'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes') FROM crypto;

-- equality search
SELECT *,decrypt_iv(crypted_content,'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes')
FROM crypto WHERE decrypt_iv(crypted_content, 'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes')='struck';

-- regex search
SELECT *,decrypt_iv(crypted_content,'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes')
FROM crypto WHERE encode(decrypt_iv(crypted_content, 'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes'),'escape')
~* 'daniel';


"fooz" is the password and "9MlPeZtpuxKo5m4O4+pd4g==" is the IV (initialization vector) stored in base64 format. I choose base64 because it is more convenient to create queries with it.

In the real database I do use a different IV for every row, so I do also store the IV with the row.
In my case I do generate the IV by PHP with /dev/urandom as a random source.


Greetings,

Daniel Struck

-- 
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck(dot)d(at)retrovirology(dot)lu

In response to

Responses

pgsql-php by date

Next:From: Bruno Wolff IIIDate: 2004-07-13 12:38:17
Subject: Re: [PHP] Secure DB Systems - How to
Previous:From: Sarah TanembaumDate: 2004-07-12 22:09:59
Subject: Re: Secure DB Systems - How to

pgsql-admin by date

Next:From: Simon RiggsDate: 2004-07-13 10:31:37
Subject: Re: are there ways for 'idle timeout'?
Previous:From: Markus BertheauDate: 2004-07-13 08:07:33
Subject: Re: statistics collector: number of function calls

pgadmin-support by date

Next:From: Bruno Wolff IIIDate: 2004-07-13 12:38:17
Subject: Re: [PHP] Secure DB Systems - How to
Previous:From: Sarah TanembaumDate: 2004-07-12 22:09:59
Subject: Re: Secure DB Systems - How to

pgsql-hackers-win32 by date

Next:From: Bruno Wolff IIIDate: 2004-07-13 12:38:17
Subject: Re: [PHP] Secure DB Systems - How to
Previous:From: Sarah TanembaumDate: 2004-07-12 22:09:59
Subject: Re: Secure DB Systems - How to

pgsql-sql by date

Next:From: StuDate: 2004-07-13 10:54:44
Subject: Re: Sorting problem
Previous:From: Achilleus MantziosDate: 2004-07-13 06:36:46
Subject: Re: Comparing tsearch2 vectors.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group