From 4f25eedf9065f28badf1e5e1df6c925062d8279e Mon Sep 17 00:00:00 2001 From: "mkanat%kerio.com" <> Date: Fri, 8 Jul 2005 09:31:41 +0000 Subject: Bug 285695: [PostgreSQL] Username checks for login, etc. need to be case insensitive Patch By Max Kanat-Alexander r=LpSolit, a=justdave --- Bugzilla/Auth/Login/WWW/Env.pm | 3 ++- Bugzilla/Auth/Verify/DB.pm | 20 ++++++-------------- Bugzilla/Auth/Verify/LDAP.pm | 3 ++- Bugzilla/DB.pm | 40 ++++++++++++++++++++++++++++++++++++++++ Bugzilla/DB/Mysql.pm | 6 ++++++ Bugzilla/Token.pm | 2 +- Bugzilla/User.pm | 17 ++++++++++------- checksetup.pl | 9 +++++---- contrib/BugzillaEmail.pm | 11 ++++++++--- contrib/bug_email.pl | 7 +++++-- contrib/bugzilla_email_append.pl | 5 ++++- contrib/syncLDAP.pl | 12 +++++++++--- request.cgi | 6 ++++-- token.cgi | 4 +++- 14 files changed, 105 insertions(+), 40 deletions(-) diff --git a/Bugzilla/Auth/Login/WWW/Env.pm b/Bugzilla/Auth/Login/WWW/Env.pm index 2f29d570f..985393206 100644 --- a/Bugzilla/Auth/Login/WWW/Env.pm +++ b/Bugzilla/Auth/Login/WWW/Env.pm @@ -84,7 +84,8 @@ sub login { # also sent), and the id, so that we have a way of telling that we # got something instead of a bunch of NULLs $sth = $dbh->prepare("SELECT extern_id, userid, disabledtext " . - "FROM profiles WHERE login_name=?"); + "FROM profiles WHERE " . + $dbh->sql_istrcmp('login_name', '?')); $sth->execute($env_email); $sth->execute(); diff --git a/Bugzilla/Auth/Verify/DB.pm b/Bugzilla/Auth/Verify/DB.pm index 1d5c6850c..4a45e81e7 100644 --- a/Bugzilla/Auth/Verify/DB.pm +++ b/Bugzilla/Auth/Verify/DB.pm @@ -34,6 +34,10 @@ use strict; use Bugzilla::Config; use Bugzilla::Constants; use Bugzilla::Util; +# Because of the screwy way that Auth works, it thinks +# that we're redefining subroutines if we "use" anything +# that "uses" Bugzilla::Auth. +require Bugzilla::User; my $edit_options = { 'new' => 1, @@ -52,11 +56,8 @@ sub authenticate { return (AUTH_NODATA) unless defined $username && defined $passwd; - # We're just testing against the db: any value is ok - trick_taint($username); - - my $userid = $class->get_id_from_username($username); - return (AUTH_LOGINFAILED) unless defined $userid; + my $userid = Bugzilla::User::login_to_id($username); + return (AUTH_LOGINFAILED) unless $userid; return (AUTH_LOGINFAILED, $userid) unless $class->check_password($userid, $passwd); @@ -74,15 +75,6 @@ sub authenticate { return (AUTH_OK, $userid); } -sub get_id_from_username { - my ($class, $username) = @_; - my $dbh = Bugzilla->dbh; - my $sth = $dbh->prepare_cached("SELECT userid FROM profiles " . - "WHERE login_name=?"); - my ($userid) = $dbh->selectrow_array($sth, undef, $username); - return $userid; -} - sub get_disabled { my ($class, $userid) = @_; my $dbh = Bugzilla->dbh; diff --git a/Bugzilla/Auth/Verify/LDAP.pm b/Bugzilla/Auth/Verify/LDAP.pm index 551a70f45..ee58f9d7e 100644 --- a/Bugzilla/Auth/Verify/LDAP.pm +++ b/Bugzilla/Auth/Verify/LDAP.pm @@ -137,7 +137,8 @@ sub authenticate { my $dbh = Bugzilla->dbh; my $sth = $dbh->prepare_cached("SELECT userid, disabledtext " . "FROM profiles " . - "WHERE login_name=?"); + "WHERE " . + $dbh->sql_istrcmp('login_name', '?')); my ($userid, $disabledtext) = $dbh->selectrow_array($sth, undef, diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index e11d52592..76e090d6c 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -218,6 +218,19 @@ sub import { $Exporter::ExportLevel-- if $is_exporter; } +sub sql_istrcmp { + my ($self, $left, $right, $op) = @_; + $op ||= "="; + + return $self->sql_istring($left) . " $op " . $self->sql_istring($right); +} + +sub sql_istring { + my ($self, $string) = @_; + + return "LOWER($string)"; +} + sub sql_position { my ($self, $fragment, $text) = @_; @@ -1153,6 +1166,33 @@ formatted SQL command have prefix C. All other methods have prefix C. $text = text to search for (scalar) Returns: formatted SQL for for full text search +=item C + + Description: Returns SQL for a case-insensitive string comparison. + Params: $left - What should be on the left-hand-side of the + operation. + $right - What should be on the right-hand-side of the + operation. + $op (optional) - What the operation is. Should be a + valid ANSI SQL comparison operator, like "=", "<", + "LIKE", etc. Defaults to "=" if not specified. + Returns: A SQL statement that will run the comparison in + a case-insensitive fashion. + Note: Uses sql_istring, so it has the same performance concerns. + Try to avoid using this function unless absolutely necessary. + Subclass Implementors: Override sql_istring instead of this + function, most of the time (this function uses sql_istring). + +=item C + + Description: Returns SQL syntax "preparing" a string or text column for + case-insensitive comparison. + Params: $string - string to convert (scalar) + Returns: formatted SQL making the string case insensitive + Note: The default implementation simply calls LOWER on the parameter. + If this is used to search on a text column with index, the index + will not be usually used unless it was created as LOWER(column). + =item C Description: Performs a table lock operation on specified tables. diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index 3472a351d..983cb3b06 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -111,6 +111,12 @@ sub sql_fulltext_search { return "MATCH($column) AGAINST($text)"; } +sub sql_istring { + my ($self, $string) = @_; + + return $string; +} + sub sql_to_days { my ($self, $date) = @_; diff --git a/Bugzilla/Token.pm b/Bugzilla/Token.pm index 2262e3e73..fe72915a3 100644 --- a/Bugzilla/Token.pm +++ b/Bugzilla/Token.pm @@ -104,7 +104,7 @@ sub IssuePasswordToken { AND tokens.tokentype = 'password' AND tokens.issuedate > NOW() - " . $dbh->sql_interval('10 MINUTE') . " - WHERE login_name = $quotedloginname"); + WHERE " . $dbh->sql_istrcmp('login_name', $quotedloginname)); my ($userid, $toosoon) = &::FetchSQLData(); if ($toosoon) { diff --git a/Bugzilla/User.pm b/Bugzilla/User.pm index ff3d38721..0c2de0f4c 100644 --- a/Bugzilla/User.pm +++ b/Bugzilla/User.pm @@ -84,7 +84,8 @@ sub new { # in the id its already had to validate (or the User.pm object, of course) sub new_from_login { my $invocant = shift; - return $invocant->_create("login_name=?", @_); + my $dbh = Bugzilla->dbh; + return $invocant->_create($dbh->sql_istrcmp('login_name', '?'), @_); } # Internal helper for the above |new| methods @@ -631,14 +632,15 @@ sub match { # Build the query. my $sqlstr = &::SqlQuote($wildstr); - my $query = "SELECT DISTINCT userid, realname, login_name, " . + my $query = "SELECT DISTINCT userid, realname, login_name, " . "LENGTH(login_name) AS namelength " . "FROM profiles "; if (&::Param('usevisibilitygroups')) { $query .= ", user_group_map "; } - $query .= "WHERE (login_name LIKE $sqlstr " . - "OR realname LIKE $sqlstr) "; + $query .= "WHERE (" + . $dbh->sql_istrcmp('login_name', $sqlstr, "LIKE") . " OR " . + $dbh->sql_istrcmp('realname', $sqlstr, "LIKE") . ") "; if (&::Param('usevisibilitygroups')) { $query .= "AND user_group_map.user_id = userid " . "AND isbless = 0 " . @@ -664,7 +666,7 @@ sub match { my $sqlstr = &::SqlQuote($str); my $query = "SELECT userid, realname, login_name " . "FROM profiles " . - "WHERE login_name = $sqlstr "; + "WHERE " . $dbh->sql_istrcmp('login_name', $sqlstr); # Exact matches don't care if a user is disabled. &::PushGlobalSQLState(); @@ -1213,8 +1215,9 @@ sub login_to_id ($) { my $dbh = Bugzilla->dbh; # $login will only be used by the following SELECT statement, so it's safe. trick_taint($login); - my $user_id = $dbh->selectrow_array( - "SELECT userid FROM profiles WHERE login_name = ?", undef, $login); + my $user_id = $dbh->selectrow_array("SELECT userid FROM profiles WHERE " . + $dbh->sql_istrcmp('login_name', '?'), + undef, $login); if ($user_id) { return $user_id; } else { diff --git a/checksetup.pl b/checksetup.pl index cd991d561..42582fa65 100755 --- a/checksetup.pl +++ b/checksetup.pl @@ -4155,7 +4155,7 @@ if ($sth->rows == 0) { } } $sth = $dbh->prepare("SELECT login_name FROM profiles " . - "WHERE login_name = ?"); + "WHERE " . $dbh->sql_istrcmp('login_name', '?')); $sth->execute($login); if ($sth->rows > 0) { print "$login already has an account.\n"; @@ -4258,9 +4258,10 @@ if ($sth->rows == 0) { } # Put the admin in each group if not already - my $userid = $dbh->selectrow_array( - "SELECT userid FROM profiles WHERE login_name = ?", undef, $login); - + my $userid = $dbh->selectrow_array("SELECT userid FROM profiles WHERE " . + $dbh->sql_istrcmp('login_name', '?'), + undef, $login); + # Admins get explicit membership and bless capability for the admin group my ($admingroupid) = $dbh->selectrow_array("SELECT id FROM groups WHERE name = 'admin'"); diff --git a/contrib/BugzillaEmail.pm b/contrib/BugzillaEmail.pm index 48602cdb7..473169c9e 100644 --- a/contrib/BugzillaEmail.pm +++ b/contrib/BugzillaEmail.pm @@ -31,6 +31,8 @@ require "globals.pl"; use strict; +my $dbh = Bugzilla->dbh; + my $EMAIL_TRANSFORM_NONE = "email_transform_none"; my $EMAIL_TRANSFORM_BASE_DOMAIN = "email_transform_base_domain"; my $EMAIL_TRANSFORM_NAME_ONLY = "email_transform_name_only"; @@ -45,13 +47,15 @@ sub findUser($) { my ($address) = @_; # if $email_transform is $EMAIL_TRANSFORM_NONE, return the address, otherwise, return undef if ($email_transform eq $EMAIL_TRANSFORM_NONE) { - my $stmt = "SELECT login_name FROM profiles WHERE profiles.login_name = \'$address\';"; + my $stmt = "SELECT login_name FROM profiles WHERE " . + $dbh->sql_istrcmp('login_name', $dbh->quote($address)); SendSQL($stmt); my $found_address = FetchOneColumn(); return $found_address; } elsif ($email_transform eq $EMAIL_TRANSFORM_BASE_DOMAIN) { my ($username) = ($address =~ /(.+)@/); - my $stmt = "SELECT login_name FROM profiles WHERE profiles.login_name RLIKE \'$username\';"; + my $stmt = "SELECT login_name FROM profiles WHERE " . $dbh->sql_istrcmp( + 'login_name', $dbh->quote($username), $dbh->sql_regexp()); SendSQL($stmt); my $domain; @@ -68,7 +72,8 @@ sub findUser($) { return $new_address; } elsif ($email_transform eq $EMAIL_TRANSFORM_NAME_ONLY) { my ($username) = ($address =~ /(.+)@/); - my $stmt = "SELECT login_name FROM profiles WHERE profiles.login_name RLIKE \'$username\';"; + my $stmt = "SELECT login_name FROM profiles WHERE " .$dbh->sql_istrcmp( + 'login_name', $dbh->quote($username), $dbh->sql_regexp()); SendSQL($stmt); my $found_address = FetchOneColumn(); return $found_address; diff --git a/contrib/bug_email.pl b/contrib/bug_email.pl index 46c23c3c3..1590387e6 100755 --- a/contrib/bug_email.pl +++ b/contrib/bug_email.pl @@ -38,7 +38,7 @@ # # You need to work with bug_email.pl the MIME::Parser installed. # -# $Id: bug_email.pl,v 1.27 2005/05/12 19:13:56 lpsolit%gmail.com Exp $ +# $Id: bug_email.pl,v 1.28 2005/07/08 02:31:43 mkanat%kerio.com Exp $ ############################################################### # 02/12/2000 (SML) @@ -112,6 +112,8 @@ my $restricted = 0; my $SenderShort; my $Message_ID; +my $dbh = Bugzilla->dbh; + # change to use default product / component functionality my $DEFAULT_PRODUCT = "PENDING"; my $DEFAULT_COMPONENT = "PENDING"; @@ -1149,7 +1151,8 @@ END $query .= $state . ", \'$bug_when\', \'$bug_when\', $ever_confirmed)\n"; # $query .= SqlQuote( "NEW" ) . ", now(), " . SqlQuote($comment) . " )\n"; - SendSQL("SELECT userid FROM profiles WHERE login_name=\'$reporter\'"); + SendSQL("SELECT userid FROM profiles WHERE " . + $dbh->sql_istrcmp('login_name', $dbh->quote($reporter))); my $userid = FetchOneColumn(); my $id; diff --git a/contrib/bugzilla_email_append.pl b/contrib/bugzilla_email_append.pl index fee9b62ac..e409f0862 100755 --- a/contrib/bugzilla_email_append.pl +++ b/contrib/bugzilla_email_append.pl @@ -42,6 +42,8 @@ use BugzillaEmail; use Bugzilla::Config qw(:DEFAULT $datadir); use Bugzilla::BugMail; +my $dbh = Bugzilla->dbh; + # Create a new MIME parser: my $parser = new MIME::Parser; @@ -101,7 +103,8 @@ if (!defined($found_id)) { } # get the user id -SendSQL("SELECT userid FROM profiles WHERE login_name = \'$SenderShort\';"); +SendSQL("SELECT userid FROM profiles WHERE " . + $dbh->sql_istrcmp('login_name', $dbh->quote($SenderShort))); my $userid = FetchOneColumn(); if (!defined($userid)) { DealWithError("Userid not found for $SenderShort"); diff --git a/contrib/syncLDAP.pl b/contrib/syncLDAP.pl index b9d3e8a5f..14ba1402c 100755 --- a/contrib/syncLDAP.pl +++ b/contrib/syncLDAP.pl @@ -30,6 +30,7 @@ use lib qw(.); use Net::LDAP; my $cgi = Bugzilla->cgi; +my $dbh = Bugzilla->dbh; my $readonly = 0; my $nodisable = 0; @@ -237,7 +238,9 @@ if($readonly == 0) { print "Performing DB update:\nPhase 1: disabling not-existing users... " unless $quiet; if($nodisable == 0) { while( my ($key, $value) = each(%disable_users) ) { - SendSQL("UPDATE profiles SET disabledtext = 'auto-disabled by ldap sync' WHERE login_name='$key'" ); + SendSQL("UPDATE profiles SET disabledtext = 'auto-disabled by ldap " . + "sync' WHERE " . $dbh->sql_istrcmp('login_name', + $dbh->quote($key))); } print "done!\n" unless $quiet; } @@ -249,9 +252,12 @@ if($readonly == 0) { if($noupdate == 0) { while( my ($key, $value) = each(%update_users) ) { if(defined @$value{'new_login_name'}) { - SendSQL("UPDATE profiles SET login_name = '" . @$value{'new_login_name'} . "' WHERE login_name='$key'" ); + SendSQL("UPDATE profiles SET login_name = '" . + @$value{'new_login_name'} . "' WHERE " . + $dbh->sql_istrcmp('login_name', $dbh->quote($key))); } else { - SendSQL("UPDATE profiles SET realname = '" . @$value{'realname'} . "' WHERE login_name='$key'" ); + SendSQL("UPDATE profiles SET realname = '" . @$value{'realname'} . + "' WHERE " . $dbh->sql_istrcmp('login_name', $dbh->quote($key))); } } print "done!\n" unless $quiet; diff --git a/request.cgi b/request.cgi index bc5eefa9a..4c6e7600f 100755 --- a/request.cgi +++ b/request.cgi @@ -145,12 +145,14 @@ sub queue { # Filter results by exact email address of requester or requestee. if (defined $cgi->param('requester') && $cgi->param('requester') ne "") { - push(@criteria, "requesters.login_name = " . SqlQuote($cgi->param('requester'))); + push(@criteria, $dbh->sql_istrcmp('requesters.login_name', + SqlQuote($cgi->param('requester')))); push(@excluded_columns, 'requester') unless $cgi->param('do_union'); } if (defined $cgi->param('requestee') && $cgi->param('requestee') ne "") { if ($cgi->param('requestee') ne "-") { - push(@criteria, "requestees.login_name = " . SqlQuote($cgi->param('requestee'))); + push(@criteria, $dbh->sql_istrcmp('requestees.login_name', + SqlQuote($cgi->param('requestee')))); } else { push(@criteria, "flags.requestee_id IS NULL") } push(@excluded_columns, 'requestee') unless $cgi->param('do_union'); diff --git a/token.cgi b/token.cgi index d8c3fe288..64bf8e364 100755 --- a/token.cgi +++ b/token.cgi @@ -36,6 +36,7 @@ use Bugzilla::Constants; use Bugzilla::Auth; my $cgi = Bugzilla->cgi; +my $dbh = Bugzilla->dbh; # Include the Bugzilla CGI and general utility library. require "CGI.pl"; @@ -114,7 +115,8 @@ if ( $::action eq 'reqpw' ) { CheckEmailSyntax($cgi->param('loginname')); my $quotedloginname = SqlQuote($cgi->param('loginname')); - SendSQL("SELECT userid FROM profiles WHERE login_name = $quotedloginname"); + SendSQL("SELECT userid FROM profiles WHERE " . + $dbh->sql_istrcmp('login_name', $quotedloginname)); FetchSQLData() || ThrowUserError("account_inexistent"); } -- cgit v1.2.3-24-g4f1b