From: "Owen Jacobson" <ojacobson@osl.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: Fwd: How to encrypt a column
Date: Thu, 11 Aug 2005 15:09:17 -0700
Message-ID: <000a01c59ec1$51da39a0$9b00015a@osl.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <20050811215520.38623.qmail@web50609.mail.yahoo.com>

Jeff Lu wrote:
> Owen Jacobson <ojacobson@osl.com> wrote:
>> 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

Consider making this column NOT NULL (crypted_content BYTEA NOT NULL).

>>> );
>>>
>>> I'm getting (null) in the field with the following sql statement:

See below: you're getting the string '(null)' and not a NULL string.

>>> 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);
>
> I do get a couple of warnings at compile time:
> intrasend.c:496:37: warning: multi-character character constant
> intrasend.c:496:44: warning: multi-character character constant
> intrasend.c:517:84: warning: character constant too long for its type

Somewhere in your code you've used '' instead of "" to delimit a string
literal.  Those line number and column number references will tell you
where.  The code you originally posted used the ' ' characters for 'foo' and
'bar', for instance.

(Yeah, it's inconvenient using different quoting conventions in the same
program.)

> Here's what is in the variable
> "data" = "0018`0018`64045`08112005`64045`1````Discount=0.00;``2``~"
>
> printf("%s", encrypt(data, "foo", "bar"))
> prints out (null)

Well, there's the problem.  The encrypt function is returning the string
"(null)", which sprintf is (correctly) inserting into your query, and which
postgresql is then (correctly) interpreting as the literal string '(null)'
when storing the data.

Your problem is either in the encrypt() function you've written or in the
way you're using it.