Samy_rio has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I am using Mysql as backend for maintaining user name & password and the password is encrypted using Mysql PASSWORD() function.

When I get password from the user through CGI, I want to compare password with the password stored in the database (Mysql).

Eventhough the user gives the correct password, i am not getting correct answer. Because the password in the database is in encrypted form. How to decypt the password stored in the database to match the password given by the user.

Regards,
Velusamy R.

Replies are listed 'Best First'.
Re: DBI password
by Cody Pendant (Prior) on Jul 02, 2005 at 11:09 UTC
    > How to decypt the password stored in the database to match the password given by the user.

    That's not how it's done. Encrypt the one they give you, then see if the two encrypted ones match.


    ($_='kkvvttuu bbooppuuiiffss qqffssmm iibbddllffss')
    =~y~b-v~a-z~s; print
[reply]

      Hi, how to encrypt the password given by the user?

      That is, I need to encrypt the password as of PASSWORD() function in MySql. Then only, the comparison will work.

      Regards,
      Velusamy R.

[reply]
Re: DBI password
by Tomte (Priest) on Jul 02, 2005 at 13:39 UTC

    Another Solution: 

    SELECT CASE WHEN PASSWORD(?) = t1.passwd THEN 'true' ELSE 'false' END 
+FROM auth_table AS t1

    [download]

    There are a million ways to do it, the key is to let mysql encrypt the given password and compare that with the stored one.

    Note, that if you are using MySQL >= 4.0.2, you should use the SHA1() function to encrypt passwords.

    regards,
    tomte

    An intellectual is someone whose mind watches itself.
    -- Albert Camus

[reply]
[d/l]
Re: DBI password
by neniro (Priest) on Jul 02, 2005 at 12:47 UTC
    AFAIR but untested: 
    SELECT COUNT(*) AS is_authenticated 
WHERE PASSWORD(?) = table.passwd 
AND table.uid = ?

    [download]
[reply]
[d/l]
Re: DBI password
by fmerges (Chaplain) on Jul 02, 2005 at 12:28 UTC

    Hi,

    You must encrypt the use supplied password to the same algorithm that the DB use, and compare it with the stored in the DB.

    Take a look here.

    Regards,

    :-)
[reply]
Re: DBI password
by nedals (Deacon) on Jul 02, 2005 at 19:12 UTC

    You probably need to check both the username and password. (Don't forget to check the user's input for expected valid charactures.)

    my $valid_user = $dbh->selectrow_array("SELECT COUNT(*) FROM usertable
+ WHERE username='$username' AND password=PASSWORD($password)");

or using placeholders

my $sth = $dbh->prepare("SELECT COUNT(*) FROM usertable WHERE username
+=? AND password=PASSWORD(?)");
$sth->execute($username,$password);
my ($valid_user) = $sth->fetchrow_array();

    [download]
[reply]
[d/l]

      Hi Monks,

      Thanks to all.

      Regards,
      Velusamy R.

[reply]

Back to Seekers of Perl Wisdom