CODEWITHSUNDEEP
phpsql
gator
gator

Reputation: 3523

SQL assume case sensitive despite ci collation?

In my users table, I have a row with username = Q. When I try to log-in as "q" instead, it states the user does not exist, however with "Q" it works as intended. Here's the login check code.

$username = $_POST["name"];
$password = $_POST["pass"];
$pahash = crypt($password, $username);
$logcheck = $db->prepare("
    SELECT *
        FROM users
        WHERE username = :username
        AND password = :pahash
");
$logcheck->execute(array(
    ':username' => $username,
    ':pahash' => $pahash));

The collation for pretty much every single column in my database is "latin1_swedish_ci", which is case insensitive. Why is it, then, that it seems to be working as case sensitive?

If it matters, the type for the user column is char(20).

Upvotes: 1

Views: 74

Answers (3)

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

you could do:

$username = $_POST["name"];
$password = $_POST["pass"];
$username = strtolower($username);
$pahash = crypt($password, $username);
$logcheck = $db->prepare("
    SELECT *
        FROM users
        WHERE LOWER(username) = :username
        AND password = :pahash
");
$logcheck->execute(array(
    ':username' => $username,
    ':pahash' => $pahash));

Upvotes: 2

Xint0
Xint0

Reputation: 5389

The problem is that crypt is not case insensitive. So the value of $pahash is different for the same value of $password when the value of $username changes from Q to q.

Upvotes: 1

Marina
Marina

Reputation: 491

Use "LIKE" instead of "=" for username:

SELECT *
    FROM users
    WHERE username LIKE :username
    AND password = :pahash

Upvotes: 1

Related Questions