X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 40A1F9FA5FE for ; Mon, 17 Jul 2006 03:05:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 43526-08 for ; Mon, 17 Jul 2006 03:05:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtpgate3.pacific.net.sg (smtpgate3.pacific.net.sg [203.81.36.23]) by postgresql.org (Postfix) with SMTP id 6F73B9FA502 for ; Mon, 17 Jul 2006 03:05:41 -0300 (ADT) Received: (qmail 22617 invoked from network); 17 Jul 2006 06:05:39 -0000 Received: from ppp171.dyn70.pacific.net.sg (HELO ?127.0.0.1?) (debraneys@210.24.70.171) by smtpgate3.pacific.net.sg with (DHE-RSA-AES256-SHA encrypted) ESMTPA; 17 Jul 2006 06:05:36 -0000 Message-ID: <44BB28AC.3070302@debraneys.com> Date: Mon, 17 Jul 2006 14:05:32 +0800 From: John Tregea User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) MIME-Version: 1.0 To: Michael Fuhr CC: pgsql-sql@postgresql.org Subject: Re: Storing encrypted data? References: <44BAE3B0.8060403@debraneys.com> <20060717030705.GA87022@winnie.fuhr.org> In-Reply-To: <20060717030705.GA87022@winnie.fuhr.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/224 X-Sequence-Number: 25798 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 > >