1

I have a database with an email field, and it cycles through the database to grab all the transactions concerning a certain email address.

Users putting in lowercase letters when their email is stored with a couple capitals is causing it not to show their transactions. When I modify it to match perfect case with the other emails, it works.

How can I modify this so that it correctly compares with the email field and case doesn't matter? Is it going to be in changing how the email gets stored?

$result = mysql_query("SELECT * FROM `example_orders` WHERE `buyer_email`='$useremail';") or die(mysql_error());

Thanks ahead of time!

Improve this question
5
  • I can't believe the code you've shown even correctly finds using email Commented Apr 4, 2012 at 21:37
  • It works just fine, I took out the object oriented reference in the email field. I'm looking at it RIGHT NOW actually and it's finding all the transactions for a user's account. It just struggles with email case. Commented Apr 4, 2012 at 21:40
  • @Brenden Clerget: For now - of course it works. You changed the code. And previous revision was just wrong Commented Apr 4, 2012 at 21:41
  • Do you happen to know what an SQL injection is? Commented Apr 4, 2012 at 21:41
  • Sigh, yes, I know what it is. I removed all those things from the code to simplify what I'm doing. I didn't want it cluttered, the classes handle all of that on their own. It's all fine and dandy. The variable is declared from something that is already sanitized. Commented Apr 4, 2012 at 21:45

3 Answers 3

Reset to default
4

Uh... you realize that email addresses are case sensitive, right? From RFC 2821:

Verbs and argument values (e.g., "TO:" or "to:" in the RCPT command
and extension name keywords) are not case sensitive, with the sole
exception in this specification of a mailbox local-part (SMTP
Extensions may explicitly specify case-sensitive elements). That is, a command verb, an argument value other than a mailbox local-part,
and free form text MAY be encoded in upper case, lower case, or any
mixture of upper and lower case with no impact on its meaning. This
is NOT true of a mailbox local-part. The local-part of a mailbox
MUST BE treated as case sensitive. Therefore, SMTP implementations
MUST take care to preserve the case of mailbox local-parts. Mailbox
domains are not case sensitive. In particular, for some hosts the
user "smith" is different from the user "Smith". However, exploiting the case sensitivity of mailbox local-parts impedes interoperability
and is discouraged.

(emphasis added)

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I'll just make them lowercase when they go into the database and handle these issues by hand for now.
The point is that you CAN'T just change the case at will. You have to preserve case because you may be changing the address if you don't.
@BrendenClerget he's trying to explain you that they are case sensitive, so treating them case insensitive is wrong.
@BrendenClerget See bfavaretto's answer. Just use utf8_general_ci as collation for the email column. You will be able to store the emails in their original case, but comparisons will ignore the case ("_ci" = case-insensitive). This is exactly what you wanted! :-)
Is PayPal ignoring case? Have you verified that? They shouldn't be. [email protected] is not the same as [email protected]. They're not "shared" - they're different.
|
3

A mixed PHP/MySQL solution:

$result = mysql_query("
    SELECT * 
    FROM example_orders
    WHERE LOWER(buyer_email) = '" . strtolower($useremail) . "';
") or die(mysql_error());

What it does is converting both sides of the comparison to lowercase. This is not very efficient, because the use of LOWER will prevent MySQL from using indexes for searching.

A more efficient, pure SQL solution:

$result = mysql_query("
    SELECT * 
    FROM example_orders
    WHERE buyer_email = '$useremail' COLLATE utf8_general_ci;
") or die(mysql_error());

In this case, we are forcing the use of a case-insensitive collation for the comparison. You wouldn't need that if the column had a case-insensitive collation in the first place.

Here is how to change the column collation, as suggested by Basti in a comment:

ALTER TABLE `example_orders` 
CHANGE `buyer_email` `buyer_email` VARCHAR( 100 ) 
   CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

If you choose to do that, you can run the query without COLLATE utf8_general_ci.

Improve this answer

9 Comments

I upvoted this because currently it is the only proposal that would work, even though it is terribly inefficient. Though the better advice would be just to figure out what's wrong with collations
You could also update the column buyer_email to use this collation per default. :-)
@BrendenClerget, First solution converts both sides (database and input) to lowercase, but it's not very efficient. Second options does a case-insensitive comparison by forcing a case-insensitive collation.
@Basti actually he should do that, or he might end up with duplicate values.
@Basti I agree he should treat them case sensitive. That said, if for some reason something else (i.e. not an email address) had to be insensitive, then the correct way of doing it would be setting it correctly in the first place.
|
-1

If you do WHERE buyer_email LIKE '...' it'll by default do a case-insensitive match.

With e-mail fields, though, I prefer to lowercase the e-mail address when I insert it into the DB.

Improve this answer

5 Comments

LIKE behaviour, as well as = depends on the collation. They both may be case-sensitive or not
And actually advice for using LIKE for strict comparison instead of = is as bad, as LOWER()
Thanks, let me try this real quick
This one didn't work, tried it. Okay so what do I set the collation to?
Mine defaults to utf8_unicode_ci. Other encodings I can't speak for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.