From: "Owen Jacobson" <ojacobson@osl.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: Fwd: How to encrypt a column
Date: Thu, 11 Aug 2005 13:50:45 -0700
Message-ID: <000901c59eb6$5977b120$9b00015a@osl.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <20050811203922.84270.qmail@web50602.mail.yahoo.com>

Jeff Lu wrote:

> Hi,
>
> I'm interested in encrypting an column in table.  Are there any
> example using "C" to create the encrypted column, inserting and
> retreiving data  to/from it?
>
> the table is:
> CREATE TABLE mytable (
>      id SERIAL PRIMARY KEY,
>      crypted_content BYTEA
> );
>
> I'm getting (null) in the field with the following sql statement:
>
> strcpy(data, "data to be encrypted");
> sprintf(query_buff, "insert into mytable values('%s', '%s')", key,

Try printing out the contents of 'query_buff' here.  You shouldn't be
getting NULLs in the table, but you may well be getting '' (the empty
string) if encrypt(data, 'foo', 'bar') returns "" or (char *) (0).

> encrypt(data, 'foo', 'bar'));
> PQexec(conn, query_string);

Even better, don't do it this way.  This is potentially open to SQL
injection attacks and definitely open to problems with "interesting" values
of key.

Look up PQprepare/PQexecParams for separating the query string from the
values.  I use ODBC indirectly, so I can't help you directly, but the docs
at
<http://www.postgresql.org/docs/current/static/libpq-exec.html#LIBPQ-EXEC-MA
IN>
should help.

> Another question is can the encrypted column be of type "text" ?

Can't see any reason why not, so long as the encrypted data is represented
as text.