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> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ojacobson@osl.com
Subject: Re: Fwd: How to encrypt a column
In-Reply-To: <000a01c59ec1$51da39a0$9b00015a@osl.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox