Re: Storing encrypted data?

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