pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Daniel Struck <struck.d@retrovirology.lu>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Sarah Tanembaum <sarahtanembaum@yahoo.com>
Cc: pgsql-php@postgresql.org
Cc: pgsql-admin@postgresql.org
Cc: pgsql-hackers-win32@postgresql.org
Cc: pgadmin-support@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [PHP] Secure DB Systems - How to
Date: Tue, 13 Jul 2004 11:35:57 +0200
Message-ID: <20040713113557.31e9d6dc@localhost> (raw)
In-Reply-To: <20040709152722.GA20935@wolff.to>
References: <ccjra4$pgl$1@sea.gmane.org>
	<20040709152722.GA20935@wolff.to>

> 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.d@retrovirology.lu

view thread (17+ messages)  latest in thread

Message-ID: <20040713113557.31e9d6dc@localhost>
Permalink:  ../20040713113557.31e9d6dc@localhost/
Also on:    postgresql.org/message-id/20040713113557.31e9d6dc@localhost

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: struck.d@retrovirology.lu, bruno@wolff.to, sarahtanembaum@yahoo.com, pgsql-php@postgresql.org, pgsql-admin@postgresql.org, pgsql-hackers-win32@postgresql.org, pgadmin-support@postgresql.org
  Subject: Re: [PHP] Secure DB Systems - How to
  In-Reply-To: <20040713113557.31e9d6dc@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox