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?

Answer 1

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.