Re: [GENERAL] encrypted field

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(at)rice(dot)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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005