CODEWITHSUNDEEP
phpmysqlcase-sensitive
Chris
Chris

Reputation: 282

Why is this query MySQL query running case-sensitive?

For some reason, this query is running case-sensitive: 

$stmt = $db->prepare("SELECT * FROM people WHERE email = :email LIMIT 1");

It simply returns whether or not it found the user: 

$stmt->bindParam(':email', $email);
$stmt->execute();
$row = $stmt->fetch();
if($row['email'] == $email)
{
    return "<span style='color: red;'>User found.</span><br>";
} else {
    return "<span style='color: red;'>User not found.</span><br>";   
}

(By the way, this is all just staging. There will be password hashing as soon as I see this is working properly).

It finds the user no problem if I use the same case as the database entry.

Here is my table, so you can see it's all defined ci:

Database view in phpmyadmin

Table view in phpmyadmin

This is actually an existing site that I built when I didn't know much about php, so I'm totally re-writing a lot, and setting up proper password hashing and https. This was all working fine before I wrote the new function and nothing in the database has changed...

So right now it only checks the email entered, just to see if the query is functioning and we're getting results from the database, later on we'll check the password and add actual login functionality.

Here is the data in the database:

Data in table

Now if I fill out my username as "chris", and run the function, it returns "User Found", so I know the query was successful. If I fill it in as "Chris", however, it returns "User not found.", so I know it was unsuccessful.

Found the issue, though. Posted as an answer.

Upvotes: 3

Views: 86

Answers (2)

Rohit Gupta
Rohit Gupta

Reputation: 4193

To make the string comparison case inssensitive requires one of three approaches

  1. Use lower (this will effect performance)

    $stmt = $db->prepare("SELECT * FROM people WHERE LOWER(email) = LOWER(:email) LIMIT 1");

  2. Another way is to use collation

  3. And a way that I use is that I store the email as lower case to begin with and convert the search string to lower before doing the search.

Upvotes: 0

Chris
Chris

Reputation: 282

The issue is with: 

if($row['email'] == $email)

The "==" comparison of the strings is case sensitive. A better way to do this would be to use: 

if(!empty($row['email']))

If $row['email'] is not empty, then the query returned a result and was successful, else the query failed, which would be caused by using an email address which does not match any in the database.

Upvotes: 2

Related Questions