MySQL regex to validate email not working - curly brace quantifier ignored

user_1512790 0
Viewed 739

I'm trying to use the following regular expression to validate emails in a MySQL database:

^[^@]+@[^@]+\.[^@]{2,}$

in a condition like this:

...and email REGEXP '^[^@]+@[^@]+\.[^@]{2,}$'

For the most part, the expression is working. But it's allowing single-character top level domains. For example, both the following emails pass validation:

something@hotmail.com and something@hotmail.c

The second case is clearly a typo. The {2,} of the regex should allow any string of characters other than the @ symbol, of length 2 or more, after the dot.

I've run the regex itself through multiple testers running different protocols, (Perl, TCL, etc.) and it works as expected every time, rejecting the single-character TLD version of the email address. It's only when I use this regex in a MySQL context that it fails.

I've checked, and there are no additional characters after the ".c" in the erroneous email address. Is there something inherent to MySQL or this version that could be preventing this from working?

Running MySQL version 5.5.61-cll

mysql regex
2 Answers

You may try using the following regex pattern:

^[^@]+@[^.]+[.][^.]{2,}([.][^.]{2,})*$

The rightmost portion of the pattern means:

[.]                match a literal dot
[^.]{2,}           followed by a domain component (any 2 or more characters but dot)
([.][^.]{2,})*     followed by dot and another component, zero or more times

Demo

So this would match:

jon.skeet@google.com
jon.skeet@google.co.uk

But would not match:

gordonlinoff@blah
user_18632290

By golly we are hackers and we validate stuff! What's more, we understand concepts like ACID compliance, data integrity, and single points of authority. So obviously, we should ensure that no invalid emails enter the DB. What's more? We have such a wonderful tool with which to do it: a proper schema with check constraints!

Unfortunately, emails are one of those details that are notoriously difficult to validate with simple regular expressions. It is possible, sure. No, actually, it's not. None of those links offer 100% compliance.

A much better approach is simply to test the address by sending it an activation email. David Gilbertson explains it far better than I'm going to in a concise SO answer, but the highlights:

  1. Don't even try validate.

  2. Just test the address with an actual email.

For my projects, both personal and professional, this is the regex I use for email address sanity checking prior to sending an activation/confirmation email:

\S+@\S+

This is extremely simple (and yep, still excludes some technically valid email addresses), extremely simple to debug, and works for any legitimate traffic to our sites. (I have yet to see an email address even close to something like #!$%&’*+-/=?^_{}|~@example.com in our logs.)

user_87301310
Related