Re: [GENERAL] encrypted field
От | Ross J. Reedstrom |
---|---|
Тема | Re: [GENERAL] encrypted field |
Дата | |
Msg-id | 19990917131234.A3394@wallace.ece.rice.edu обсуждение исходный текст |
Ответ на | Re: [GENERAL] encrypted field (Henrique Pantarotto <scanner@cepa.com.br>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: