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 931669FA1BB for ; Mon, 17 Jul 2006 00:07:17 -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 28502-02 for ; Mon, 17 Jul 2006 00:07:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id EDE7C9FA27F for ; Mon, 17 Jul 2006 00:07:12 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.7/8.13.7) with ESMTP id k6H375vh004332 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Sun, 16 Jul 2006 21:07:08 -0600 (MDT) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.6/8.13.6) with ESMTP id k6H375lt087148; Sun, 16 Jul 2006 21:07:05 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.6/8.13.6/Submit) id k6H375Fa087147; Sun, 16 Jul 2006 21:07:05 -0600 (MDT) (envelope-from mfuhr) Date: Sun, 16 Jul 2006 21:07:05 -0600 From: Michael Fuhr To: John Tregea Cc: pgsql-sql@postgresql.org Subject: Re: Storing encrypted data? Message-ID: <20060717030705.GA87022@winnie.fuhr.org> References: <44BAE3B0.8060403@debraneys.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <44BAE3B0.8060403@debraneys.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/223 X-Sequence-Number: 25797 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 -- Michael Fuhr