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