Perl crypt seemingly not working

Question

I am trying to use the crypt function to verify a user's details in database:

    my $sql = $dbh->prepare("SELECT COUNT(id) FROM tblUsers WHERE UPPER(Username)=UPPER(?) AND Password=?");
    my $count;
    $sql->execute(
        $args{login_username},
        crypt($args{login_password},$args{login_username})
        );

but it returns 0 rows (with the password definitely correct)

the line:

    $sql->execute($args{login_username},$args{login_password});

works perfectly.

Any ideas as to what might be going wrong?

Is the password stored `crypt`ed in your database? (Doesn't look like it.) — Mat, Apr 13 '12 at 06:15
How do you know it's "definitely" correct? From the Perl program, output the crypted string. Compare that, by hand, to the record in the database. — brian d foy, Apr 13 '12 at 07:01
@brian: when he says the alternative `execute` line works perfectly I presume it's matching the password field unencrypted. — Borodin, Apr 13 '12 at 07:13
I try not to presume, which is why I asked. "Definitely" in debugging usually means "not really". :) — brian d foy, Apr 13 '12 at 15:56

score 2 · Accepted Answer · answered Apr 13 '12 at 07:07

It looks like the password stored in the database unencrypted. To compare the values in encrypted form you need to encrypt them at the database side as well.

MySQL has an ENCRYPT function, so you can write

my $sql= $dbh->prepare(<<SQL);
SELECT COUNT(id)
FROM tblUsers
WHERE UPPER(Username) = UPPER(?)
AND ENCRYPT(Password, Username) = ?
SQL

$sql->execute($args{login_username}, crypt($args{login_password},$args{login_username}));

but the exact syntax depends on the platform you are using.

@Borodin-`ENCRYPT` doesn't work on Windows.It returns `NULL`. — , Apr 13 '12 at 11:25

Perl crypt seemingly not working

1 Answers1