Date: Fri, 12 Aug 2005 19:04:54 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Jeff Lu <jeff_k_lu@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Fwd: How to encrypt a column
Message-ID: <20050813000454.GA28601@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Jeff Lu <jeff_k_lu@yahoo.com>, pgsql-sql@postgresql.org
References: <20050812021956.GA32662@wolff.to>
	<20050812215654.6343.qmail@web50602.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050812215654.6343.qmail@web50602.mail.yahoo.com>
User-Agent: Mutt/1.5.6i

Please keep replies posted to the list unless you have a specific reason
not to.

On Fri, Aug 12, 2005 at 14:56:54 -0700,
  Jeff Lu <jeff_k_lu@yahoo.com> wrote:
> I can't see why it's returning (null).  
>  
>  sprintf(query_string, "INSERT INTO mytable values('%s', '%s')", key, , encrypt('hello world', '0000AAAA', 'aes'));
>  PQexec(conn, query_string);

I don't know what your problem is for this example. However there is an extra
comma in what you typed that will cause problems if it is really in your code.

However, your method will not work in general because you can't be sure the
output of the AES encryption will be valid text. You should be recoding to
something safe to represent with ascii (such as base64).

>  
> I if do this in cygwin shell, it works
> 
> $ psql -h localhost intrapos --username postgres
> Welcome to psql 7.4.5, the PostgreSQL interactive terminal.
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
> intrapos=# select encrypt('hello world', '0000AAAA', 'aes') \g
>                       encrypt
> ---------------------------------------------------
>  \333\337\003\217\016\222WC\243\031\306\250`&\265Q
> (1 row)
> intrapos=#
>  
> I really appreciate your help, I could not figure this out
> 
> Bruno Wolff III <bruno@wolff.to> wrote:
> On Thu, Aug 11, 2005 at 13:50:45 -0700,
> Owen Jacobson wrote:
> > Jeff Lu wrote:
> > 
> > > 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.
> 
> There can't be any 0 bytes in the encrypted string or the string will be
> truncated.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
> 
> 		
> ---------------------------------
>  Start your day with Yahoo! - make it your home page