MySQL weirdness with dots and underscores in email addresses

[Stefaans]

We are using an external page for new subscriptions that posts to the signup.php script. Today I found that a new user with an dot in his email address could not subscribe. There are no error messages, but the address is not present in the database either. I then found the database already contains the same address, but with an underscore instead of a dot. For some reason MySQL sees the two as the same address and therefore does not create a new subscriber.

To illustrate a bit more: first_last@domain.com already existed in the database. Trying to subscribe first.last@domain.com does nothing. However, after deleting first_last@domain.com from the database, I can add first.last@domain.com.

I debugged the signup.php script and can confirm that the INSERT statement uses the correct email address. Therefore, this is not a ListMail Pro bug but a MySQL weirdness.

Is there a setting in MySQL that causes this behaviour?

Thanks for your comments.

[DW]

Stephan,

This doesn't look like weirdness, this looks like a bug.  The MySQL "LIKE" operator apparently supports the underscore as a single wildcard character.  When checking for duplicates, I use the LIKE statement to avoid case sensitivity.

See:
http://www.brainbell.com/tutorials/MySQL/Using_The_LIKE_Operator.htm

It looks like I need to change

Code: [Select]

// check for duplicates
  $cmd = "select id,uid,cnf from $utable where list = '$list' and email like '".addslashes($email)."';";
to something like this:

Code: [Select]

// check for duplicates
  $cmd = "select id,uid,cnf from $utable where list = '$list' and LOWER(email) like '".strtolower(addslashes($email))."';";
Although now I wonder if the LOWER() function will slow things down.  I will have to do some testing with some queries on a large test database.  Once that's done I'll be adding it to the program.

Regards