On 7/05/2010 2:31 AM, akp geek wrote:
> Dear all -
>
> I am writing function to handle the passwords. Currently
> the crypt is being used to store the password in the database. what I
> need to do is, when the user wants to change the password, I need to
> check if that password is not being used before up to 5 times, If not
> then then records should be inserted to the database.
>
> The problem where i am running into, when I capture the
> password that user entered, I can't compare to the one in database ,
> because each time the function crypt gives different one. Is there any
> way that I can achieve this?
Extract the salt from each stored password and re-encrypt the new
password with the same salt when comparing it to the old one.
eg:
craig=> create table password_history ( password text not null );
CREATE TABLE
craig=> insert into password_history(password) values ( crypt('fred',
gen_salt('md5')) );
INSERT 0 1
craig=> insert into password_history(password) values ( crypt('bob',
gen_salt('md5')) );
INSERT 0 1
craig=> insert into password_history(password) values (
crypt('smeghead', gen_salt('md5')) );
INSERT 0 1
craig=> create or replace function extract_salt(text) returns text as $$
craig$> select (regexp_matches($1, E'^(\\$[^\\$]+\\$[^\\$]+)\\$'))[1];
craig$> $$ language sql immutable;
CREATE FUNCTION
craig=> select extract_salt(password), password from password_history;
extract_salt | password
--------------+------------------------------------
$1$p3AMpr5s | $1$p3AMpr5s$BtNTSXwIJbHrdnJEZ4NFg.
$1$FKySMIXg | $1$FKySMIXg$xFM5osjqclTuaJIUiGvU3.
$1$MUwd2dGt | $1$MUwd2dGt$w06IEIvJ1lROXw7WGb3dw.
(3 rows)
craig=> select exists (select 1 from password_history where
crypt('fred', extract_salt(password)) = password);
?column?
----------
t
(1 row)
craig=> select exists (select 1 from password_history where crypt('bob',
extract_salt(password)) = password);
?column?
----------
t
(1 row)
craig=> select exists (select 1 from password_history where
crypt('nosuch', extract_salt(password)) = password);
?column?
----------
f
(1 row)
Make sure to generate a new salt value if you accept the password and
want to store it, though.
( Perhaps pgcrypto needs a function to extract the salt? )
--
Craig Ringer
