X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B1DA752A22 for ; Thu, 11 Aug 2005 18:55:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86980-03 for ; Thu, 11 Aug 2005 21:55:19 +0000 (GMT) Received: from web50609.mail.yahoo.com (web50609.mail.yahoo.com [206.190.38.248]) by svr1.postgresql.org (Postfix) with SMTP id 077BF52997 for ; Thu, 11 Aug 2005 18:55:18 -0300 (ADT) Received: (qmail 38625 invoked by uid 60001); 11 Aug 2005 21:55:20 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=FS251EF//qkrDVYvOkUtsictXHcEZR/08OE/ev1VFD3TuqhDtY2vSYZlfAPcZrsQc2mAKUsGdWzE4S4lhFqyN1n7Zw7ROEDhyydqTwDta/g8j7WenZu+2tTqoE7qHskdzTbAEjonjpe3P4DMuRQBuyl6Q7vg0dsbUGp65SU/GPM= ; Message-ID: <20050811215520.38623.qmail@web50609.mail.yahoo.com> Received: from [68.209.250.7] by web50609.mail.yahoo.com via HTTP; Thu, 11 Aug 2005 14:55:20 PDT Date: Thu, 11 Aug 2005 14:55:20 -0700 (PDT) From: Jeff Lu Subject: Re: Fwd: How to encrypt a column To: Owen Jacobson Cc: pgsql-sql@postgresql.org In-Reply-To: <000901c59eb6$5977b120$9b00015a@osl.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-2081264692-1123797320=:37961" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.25 tagged_above=0 required=5 tests=[AWL=-0.794, DNS_FROM_RFC_ABUSE=0.374, HTML_20_30=0.504, HTML_ATTR_UNIQUE=1.165, HTML_MESSAGE=0.001] X-Spam-Level: * X-Archive-Number: 200508/60 X-Sequence-Number: 22288 --0-2081264692-1123797320=:37961 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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) Thank you Owen Jacobson 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 > ); > > 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 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. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings --------------------------------- Start your day with Yahoo! - make it your home page --0-2081264692-1123797320=:37961 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit

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
 
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)

Thank you
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
> );
>
> 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 i t 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
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.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Start your day with Yahoo! - make it your home page --0-2081264692-1123797320=:37961--