Обсуждение: Fwd: How to encrypt a column

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.

>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, 
>encrypt(data, 'foo', 'bar'));
>PQexec(conn, query_string);

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


I'd check a previous critical point ... The encrypt() function output.
What is the range of characters in encrypt() output ?
Control, null or false escape characters, if not properly escaped, could be 
misleading either sprintf or the query processor in pg.


Helder M. Vieira

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.

On Thu, Aug 11, 2005 at 13:50:45 -0700, Owen Jacobson <ojacobson@osl.com> 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.

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