127862 - Sanity Check fails if regex excludes email-adresses with uppercase letters

Status: RESOLVED FIXED

(Bugzilla :: Administration, task, P3)

Description

[Andreas Höfler]

The Sanity Check reports every lowercase emailadress as invalid, if the regexp
in the parameters is set to allow no uppercase letters in e-mail-addresses.

emailregexp: ^[^@A-Z]+@[^@A-Z]+\.[^@A-Z]+$

An e-mail-address like "[e-mail]" results in following
line(s) when sanity-checking:

...
Checking profile logins
Bad profile email address, id=1, <[e-mail]>.
...

Comment 1

[Dave Miller [:justdave]]

Status("Checking profile logins");

my $emailregexp = Param("emailregexp");
$emailregexp =~ s/'/\\'/g;
SendSQL("SELECT userid, login_name FROM profiles " .
        "WHERE login_name NOT REGEXP '" . $emailregexp . "'");

We're passing the regexp to the SQL....

MySQL is NOT case sensitive.  so you're effectively blocking all alphabetical
characters of either case.

Do we use MySQL's regexp everywhere else in Bugzilla where we check the
emailregexp, or are we using Perl's regexp?  If we're using Perl's regexp
everywhere else, this is inconsistent...

Comment 2

[Andreas Höfler]

On the "Create Account"-Page the regexp gets parsed correctly, so only
e-mail-addresses with all chars lowercase can be used to create an account. Only
the sanitycheck has cried so far for me.

Comment 3

[Bradley Baetz (:bbaetz)]

http://www.mysql.com/doc/P/a/Pattern_matching.html

After we require > 3.23.4, we can use BINARY to be case sensitive.

Comment 4

[Andreas Höfler]

Why don't just use the same procedure as it is used in CheckEmailSyntax? This is
the function which is used to check the syntax when creating an BZ-account.
In this function the line(s) (in CGI.pl)
...
    my $match = Param('emailregexp');
    if ($addr !~ /$match/ || $addr =~ /[\\\(\)<>&,;:"\[\] \t\r\n]/) {
...
do the whole magic.
I don't think that it is necessary to shift the whole work to MySQL.

Comment 5

[Andreas Höfler]

Accidentially blew up the dependency, correcting it. Sorry for the spam...

Comment 6

[Matthew Tuck [:CodeMachine]]

Theoretically moving this to MySQL could be faster but I doubt in practice it
would be.  I think BINARY is the way to go, and put it in an appropriate sub
when we support PgSQL if it's different.

Comment 7

[Joel Peshkin]

Attachment: Patch - use perl for regexp

It just doesn't take that long to do this in perl.  This is sanitycheck.cgi,
not buglist.cgi.  This makes it compatible once and for all and doesn't set us
up for problems with other types of databases later.

Comment 8

[Joel Peshkin]

Comment on attachment 144721 [details] [diff] [review]
Patch - use perl for regexp

doh!  goofed

Comment 9

[Joel Peshkin]

Attachment: The right patch

Comment 10

[Vlad Dascalu]

Comment on attachment 144723 [details] [diff] [review]
The right patch

There is an extra quoted space at the end of the SendSQL line:

+SendSQL("SELECT userid, login_name FROM profiles ");

r=vlad with this change.

Comment 11

[Joel Peshkin]

Checking in sanitycheck.cgi;
/cvsroot/mozilla/webtools/bugzilla/sanitycheck.cgi,v  <--  sanitycheck.cgi
new revision: 1.70; previous revision: 1.69
done

Comment 12

[Dave Miller [:justdave]]

*** Bug 241172 has been marked as a duplicate of this bug. ***