From: John Tregea <john@debraneys.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Storing encrypted data?
Date: Mon, 17 Jul 2006 14:05:32 +0800
Message-ID: <44BB28AC.3070302@debraneys.com> (raw)
In-Reply-To: <20060717030705.GA87022@winnie.fuhr.org>
References: <A70AE049E81A6A40879D5DC0AC8C38C9069508ED@venus.infics.com>
<44BAE3B0.8060403@debraneys.com>
<20060717030705.GA87022@winnie.fuhr.org>
Hi Michael,
Thanks for your advice. I was looking at the bytea data type in the
PostgreSQL book I bought (Korry and Susan Douglas, second edition). I
was concerned that if I have to escape certain characters like the
single quote or the backslash, how do I guarantee that the escaped
version does not already appear in the encrypted string?
Should I use the octal value to escape the single quote (\047) and
backslash (\\134)?
Those character sequences are extremely unlikely to occur in an
encrypted string.
Is the the right approach ?
Also... I note that I cannot change the data type of my field from text
to bytea (I am using PGADMIN III). Do you know why?
Regards
John T
Michael Fuhr wrote:
> On Mon, Jul 17, 2006 at 09:11:12AM +0800, John Tregea wrote:
>
>> Does anyone have experience with storing encrypted data into pgSQL? I
>> have a pgSQL database which uses UTF8 encoding. I am encrypting plain
>> text in my (GUI) application and sending it to a field (with data type
>> 'text') in my database.
>>
>> I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"
>>
>
> Use bytea instead of text for binary data; alternatively, base64
> encode ("armor") the encrypted data and store it as text. If you
> insert binary data then you'll need to escape non-printable characters
> or use an API that can pass binary data to the backend (e.g., libpq's
> PQexecParams() or some language's interface to that function). Some
> APIs might automatically escape data if you use placeholders; check
> your API's documentation.
>
> http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
> http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html
>
>
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: john@debraneys.com, mike@fuhr.org
Subject: Re: Storing encrypted data?
In-Reply-To: <44BB28AC.3070302@debraneys.com>
* 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