Thread: encrypted field

encrypted field

From
Gregoire Pichon
Date:
Hello,

How can I crypt the field of a table?
This field will contain secret data, I need therefore
to crypt this field to avoid those data to be stored
on the disk unprotected.

Where can I found documentation on this topic?

Thanks
Greg

__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com

Re: [GENERAL] encrypted field

From
Henrique Pantarotto
Date:
** Sorry folks.. before anyone flames me: this is not a "stupid C trigger", it
is only a "stupid C *function*".. ;-)  Since I am implementing a couple of
triggers, I have this word in my mind..  you know?  Sorry...
So you do: s/trigger/function/g below, okay?  ;-)
**

Hello Greg,

I've created a stupid little C trigger that is kinda like MySQL's "encrypt"
function.  I use it to store passwords in the UNIX crypt format.

Here's the encrypt.c:

--------------------------------------------------------
/*
*
*  Henrique Pantarotto (scanner@cepa.com.br)
*  Funcao para encriptar senhas (Function to encrypt passwords)
*  September 1999
*
*  Create trigger like this:
*  create function encrypt(text) returns text as
*  '/usr/local/pgsql/hpmail/encrypt.so' language 'c';
*
*/

#include <stdio.h>
#include <strings.h>
#include <unistd.h>

#include <postgres.h>
#include <utils/builtins.h>

text *encrypt (text *user);

text *encrypt(text *user)
{
 char *password;

 password = crypt(textout(user), "HP");

 return textin(password);

}
----------------------------------------------------------


Compile using something like this:

1: gcc -I/down/postgresql-6.5.1/src/include -I$/down/postgresql-6.5.1/src/backend -O2 -Wall -Wmissing-prototypes -fpic
-I/down/postgresql-6.5.1/src/include-c -o encrypt.o encrypt.c 
2: gcc -shared -o encrypt.so encrypt.o

(my postgres sources are in /down/postgresql-6.5.1, you'll need to change this
path)

And last, you create the trigger in PostgreSQL using this:

create function encrypt(text) returns text as '/usr/local/pgsql/encrypt.so' language 'c';


If everything is okay, you'll probably have: select encrypt('secret') working
and showing:

encrypt
------------
HPK1Jt2NX21G.
(1 row)

blabla=>


PS: Note that all crypted passwords are created with salt "HP" (my name
initials..) You can change that, or if you know C, you can do in a way that it
will pick two random characters (the way it should really be).

I'm no experience C programmer, nor an experienced PostgreSQL user, so maybe
there's a smarter way to do this same thing.. (there might be even a built in
function that I don't know).


Good luck and regards from Brazil,

Henrique Pantarotto
Sao Paulo, SP - Brasil
scanner@cepa.com.br


On sex, 17 set 1999, Gregoire Pichon wrote:
> Hello,
>
> How can I crypt the field of a table?
> This field will contain secret data, I need therefore
> to crypt this field to avoid those data to be stored
> on the disk unprotected.
>
> Where can I found documentation on this topic?
>
> Thanks
> Greg
>
> __________________________________________________
> Do You Yahoo!?
> Bid and sell for free at http://auctions.yahoo.com
>
> ************
--
Henrique Pantarotto
CEPAnet Internet Provider
webmaster / analista de sistemas
Email: scanner@cepa.com.br
Tel: (011) 5506-8477
Cel: (011) 9706-3444
LINUX FRIEND

Re: [GENERAL] encrypted field

From
Henrique Pantarotto
Date:
** Sorry folks.. before anyone flames me: this is not a "stupid C trigger", it
is only a "stupid C *function*".. ;-)  Since I am implementing a couple of
triggers, I have this word in my mind..  you know?  Sorry...
So you do: s/trigger/function/g below, okay?  ;-)
**

Hello Greg,

I've created a stupid little C trigger that is kinda like MySQL's "encrypt"
function.  I use it to store passwords in the UNIX crypt format.

Here's the encrypt.c:

--------------------------------------------------------
/*
*
*  Henrique Pantarotto (scanner@cepa.com.br)
*  Funcao para encriptar senhas (Function to encrypt passwords)
*  September 1999
*
*  Create trigger like this:
*  create function encrypt(text) returns text as
*  '/usr/local/pgsql/hpmail/encrypt.so' language 'c';
*
*/

#include <stdio.h>
#include <strings.h>
#include <unistd.h>

#include <postgres.h>
#include <utils/builtins.h>

text *encrypt (text *user);

text *encrypt(text *user)
{
 char *password;

 password = crypt(textout(user), "HP");

 return textin(password);

}
----------------------------------------------------------


Compile using something like this:

1: gcc -I/down/postgresql-6.5.1/src/include -I$/down/postgresql-6.5.1/src/backend -O2 -Wall -Wmissing-prototypes -fpic
-I/down/postgresql-6.5.1/src/include-c -o encrypt.o encrypt.c 
2: gcc -shared -o encrypt.so encrypt.o

(my postgres sources are in /down/postgresql-6.5.1, you'll need to change this
path)

And last, you create the trigger in PostgreSQL using this:

create function encrypt(text) returns text as '/usr/local/pgsql/encrypt.so' language 'c';


If everything is okay, you'll probably have: select encrypt('secret') working
and showing:

encrypt
------------
HPK1Jt2NX21G.
(1 row)

blabla=>


PS: Note that all crypted passwords are created with salt "HP" (my name
initials..) You can change that, or if you know C, you can do in a way that it
will pick two random characters (the way it should really be).

I'm no experience C programmer, nor an experienced PostgreSQL user, so maybe
there's a smarter way to do this same thing.. (there might be even a built in
function that I don't know).


Good luck and regards from Brazil,

Henrique Pantarotto
Sao Paulo, SP - Brasil
scanner@cepa.com.br


On sex, 17 set 1999, Gregoire Pichon wrote:
> Hello,
>
> How can I crypt the field of a table?
> This field will contain secret data, I need therefore
> to crypt this field to avoid those data to be stored
> on the disk unprotected.
>
> Where can I found documentation on this topic?
>
> Thanks
> Greg
>
> __________________________________________________
> Do You Yahoo!?
> Bid and sell for free at http://auctions.yahoo.com
>
> ************
--
Henrique Pantarotto
CEPAnet Internet Provider
webmaster / analista de sistemas
Email: scanner@cepa.com.br
Tel: (011) 5506-8477
Cel: (011) 9706-3444
LINUX FRIEND

Re: [GENERAL] encrypted field

From
"Ross J. Reedstrom"
Date:
On Fri, Sep 17, 1999 at 09:03:58AM -0300, Henrique Pantarotto wrote:
<snipped Henrique's crypt function>

> PS: Note that all crypted passwords are created with salt "HP" (my name
> initials..) You can change that, or if you know C, you can do in a way that it
> will pick two random characters (the way it should really be).

I've got a similar function, just a generation later. Note that I don't
remember what trigger code I used the framework from. Apparently, (from
looking at Henrique's code) there are some text convenience functions
I don't know about: I did all the memory allocation explictly (i.e. the
hard way). I also ran into a 'gotcha': crypt expects zero terminated
strings, pg text type is a counted string. Took me too long to find the
problem, since from pgsql, I seemed to get a new (zeroed) buffer, most of
the time. So there might be lots of extra bzero()s and memcpy()s in the
following. If anyone has any suggestions for improvments, I'm all ears!

I compiled it as so:

cc  -shared -I /usr/include/postgresql/ -o sqlcrypt.so sqlcrypt.c

And created the functions as described in the comments in the file.
This gives you two functions, sqlcrypt(text) and sqlcrypt(text,text)

The first form will pick a random salt, the second uses a given salt. I
use them from some web-based middleware, which has no crypt() function
(ColdFusion), as so:

with a table:

logins (userid serial, password char(13), username text)


SELECT userid FROM logins WHERE
    username= '#name_entered#' and
    password=sqlcrypt('#pass_entered#',substr(password,1,2))



--------------------------8<----------------------------------------
/* sqlcrypt functions: wrapper around standard unix crypt call.
* Copyright 1999, Ross J. Reedstrom (reedstrm@rice.edu)
* I hereby place this code under the same copyright restrictions as
* PostgreSQL.
*/

#define _XOPEN_SOURCE
#include <postgres.h>
#include <unistd.h>
#include <string.h>
#include <stdlib.h>
#include <sys/time.h>


text *sqlcrypt(text *key, text *salt);
/*
* Create functions:
*
* sql create function sqlcrypt(text,text) returns text
*         as 'DESTLIB' language 'c'*/
* sql create function sqlcrypt(text) returns text
*         as 'select sqlcrypt($1,'''')' language 'SQL'
*
*/

char *crypt(const char *key, const char *salt);
int rand(void);
void srand(unsigned int seed);


text *sqlcrypt(text *key, text *salt)
{
  text *ret;
  char pass[] = "123456789";
  char s[] = "...";
  char salts[] = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789./";
  /* as per crypt(3): [a-zA-Z0-9./] */
  int j,k;
  struct timeval tv;


  s[2]=0;
  bzero(pass,9);

  /* test for not-valid salt: if not, pick randomly. I'm only testing
  size, should also make sure the first two characters are in the valid
  set. Anyone have a better way to get a pseudo random number? I brought
  in gettimeofday to seed rand*/

  if ((VARSIZE(salt)-VARHDRSZ) < 2)
    {
    gettimeofday(&tv,0);
    srand((unsigned int)(tv.tv_usec));
    s[0]=salts[(rand() % 64)];
    s[1]=salts[(rand() % 64)];

    }
  else
    {
    memcpy(s,VARDATA(salt),2);
    }
  ret = palloc(VARHDRSZ + 13);
  bzero(ret,VARHDRSZ + 13);
  VARSIZE(ret) = (VARHDRSZ + 13);

  /* don't copy any garbage from the input, but only get the first eight */

  if ((VARSIZE(key)-VARHDRSZ) < 8)
  {
      memcpy(pass,VARDATA(key),VARSIZE(key)-VARHDRSZ);
  }
  else
  {
      memcpy(pass,VARDATA(key),8) ;
  }

  memcpy(VARDATA(ret), crypt(pass,s),13);

  return ret;
}

--------------------------8<----------------------------------------

>
> I'm no experience C programmer, nor an experienced PostgreSQL user, so maybe
> there's a smarter way to do this same thing.. (there might be even a built in
> function that I don't know).
>

Ditto for me: again, anyone have any improvements, let me know, my users will
thank you, if only they knew...

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: [GENERAL] encrypted field

From
Stephane Alnet
Date:
> > PS: Note that all crypted passwords are created with salt "HP" (my name
> > initials..) You can change that, or if you know C, you can do in a way that it
> > will pick two random characters (the way it should really be).

One quick remark: if you are serious about using hash (not encryption, strictly
speaking) as a protection: having a *random* salt is an important part of using
crypt(). "Static salt" (sounds weird, huh) reduces the strength of the algorithm
a lot.

> I also ran into a 'gotcha': crypt expects zero terminated
> strings, pg text type is a counted string.
> [...] anyone have any improvements, let me know

To avoid this "gotcha" and at the same time provide quality "hash", you may want
to look into functions like MD5 or SHA-1 which will accept anything as input (a
bit stream!) and generate a fixed-length, ASCII-text result.

Stéphane

PS: I have a working implementation of SHA-1 in C, if someone has the time /
experience / need to write a wrapper for pgsql... ;)

Re: [GENERAL] encrypted field

From
Stephane Alnet
Date:
> > PS: Note that all crypted passwords are created with salt "HP" (my name
> > initials..) You can change that, or if you know C, you can do in a way that it
> > will pick two random characters (the way it should really be).

One quick remark: if you are serious about using hash (not encryption, strictly
speaking) as a protection: having a *random* salt is an important part of using
crypt(). "Static salt" (sounds weird, huh) reduces the strength of the algorithm
a lot.

> I also ran into a 'gotcha': crypt expects zero terminated
> strings, pg text type is a counted string.
> [...] anyone have any improvements, let me know

To avoid this "gotcha" and at the same time provide quality "hash", you may want
to look into functions like MD5 or SHA-1 which will accept anything as input (a
bit stream!) and generate a fixed-length, ASCII-text result.

Stéphane

PS: I have a working implementation of SHA-1 in C, if someone has the time /
experience / need to write a wrapper for pgsql... ;)

cancel <37F15A2A.82C50D3B@u-picardie.fr>

From
Stephane Alnet
Date:
This message was cancelled from within Mozilla.

Re: [GENERAL] encrypted field

From
Stephane Alnet
Date:
> > PS: Note that all crypted passwords are created with salt "HP" (my name
> > initials..) You can change that, or if you know C, you can do in a way that it
> > will pick two random characters (the way it should really be).

One quick remark: if you are serious about using hash (not encryption, strictly
speaking) as a protection: having a *random* salt is an important part of using
crypt(). "Static salt" (sounds weird, huh) reduces the strength of the algorithm
a lot.

> I also ran into a 'gotcha': crypt expects zero terminated
> strings, pg text type is a counted string.
> [...] anyone have any improvements, let me know

To avoid this "gotcha" and at the same time provide quality "hash", you may want
to look into functions like MD5 or SHA-1 which will accept anything as input (a
bit stream!) and generate a fixed-length, ASCII-text result.

Stéphane

PS: I have a working implementation of SHA-1 in C, if someone has the time /
experience / need to write a wrapper for pgsql... ;)