diff options
| -rw-r--r-- | Bugzilla/DB.pm | 16 | ||||
| -rw-r--r-- | Bugzilla/DB/Mysql.pm | 14 | ||||
| -rw-r--r-- | Bugzilla/Search.pm | 23 | ||||
| -rw-r--r-- | Bugzilla/User.pm | 28 | ||||
| -rwxr-xr-x | checksetup.pl | 8 |
5 files changed, 58 insertions, 31 deletions
diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index db6c0b405..2634a7994 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -201,6 +201,12 @@ sub import { $Exporter::ExportLevel-- if $is_exporter; } +sub sql_position { + my ($self, $fragment, $text) = @_; + + return "POSITION($fragment IN $text)"; +} + # XXX - Needs to be documented. sub bz_server_version { my ($self) = @_; @@ -674,6 +680,16 @@ formatted SQL command have prefix C<sql_>. All other methods have prefix C<bz_>. (scalar) Returns: formatted SQL for interval function (scalar) +=item C<sql_position> + + Description: Outputs proper SQL syntax determinig position of a substring + (fragment) withing a string (text). Note: if the substring or + text are string constants, they must be properly quoted + (e.g. "'pattern'"). + Params: $fragment = the string fragment we are searching for (scalar) + $text = the text to search (scalar) + Returns: formatted SQL for substring search (scalar) + =item C<bz_lock_tables> Description: Performs a table lock operation on specified tables. diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index 4e0a3e5b2..ae24d13bc 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -117,6 +117,20 @@ sub sql_interval { return "INTERVAL $interval"; } +sub sql_position { + my ($self, $fragment, $text) = @_; + + # mysql 4.0.1 and lower do not support CAST + # mysql 3.*.* had a case-sensitive INSTR + # (checksetup has a check for unsupported versions) + my $server_version = $self->bz_server_version; + if ($server_version =~ /^3\./) { + return "INSTR($text, $fragment)"; + } else { + return "INSTR(CAST($text AS BINARY), CAST($fragment AS BINARY))"; + } +} + sub bz_lock_tables { my ($self, @tables) = @_; diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index 1a0cb9a7f..775778a9d 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -951,24 +951,16 @@ sub init { $term = "$ff != $q"; }, ",casesubstring" => sub { - # mysql 4.0.1 and lower do not support CAST - # mysql 3.*.* had a case-sensitive INSTR - # (checksetup has a check for unsupported versions) - my $server_version = $dbh->bz_server_version; - if ($dbh->isa('Bugzilla::DB::Mysql') && $server_version =~ /^3\./) { - $term = "INSTR($ff ,$q)"; - } else { - $term = "INSTR(CAST($ff AS BINARY), CAST($q AS BINARY))"; - } + $term = $dbh->sql_position($q, $ff); }, ",substring" => sub { - $term = "INSTR(LOWER($ff), " . lc($q) . ")"; + $term = $dbh->sql_position(lc($q), "LOWER($ff)"); }, ",substr" => sub { $funcsbykey{",substring"}->(); }, ",notsubstring" => sub { - $term = "INSTR(LOWER($ff), " . lc($q) . ") = 0"; + $term = $dbh->sql_position(lc($q), "LOWER($ff)") . " = 0"; }, ",regexp" => sub { $term = "LOWER($ff) " . $dbh->sql_regexp() . " $q"; @@ -1426,8 +1418,9 @@ sub ListIDsForEmail { } $list = join(',', @list); } elsif ($type eq 'substring') { - &::SendSQL("SELECT userid FROM profiles WHERE INSTR(login_name, " . - &::SqlQuote($email) . ") " . $dbh->sql_limit(51)); + &::SendSQL("SELECT userid FROM profiles WHERE " . + $dbh->sql_position(lc(::SqlQuote($email)), "LOWER(login_name)") . + " " . $dbh->sql_limit(51)); while (&::MoreSQLData()) { my ($id) = &::FetchSQLData(); push(@list, $id); @@ -1478,10 +1471,12 @@ sub GetByWordList { sub GetByWordListSubstr { my ($field, $strs) = (@_); my @list; + my $dbh = Bugzilla->dbh; foreach my $word (split(/[\s,]+/, $strs)) { if ($word ne "") { - push(@list, "INSTR(LOWER($field), " . lc(&::SqlQuote($word)) . ")"); + push(@list, $dbh->sql_position(lc(::SqlQuote($word)), + "LOWER($field)")); } } diff --git a/Bugzilla/User.pm b/Bugzilla/User.pm index 040836616..dc11822bc 100644 --- a/Bugzilla/User.pm +++ b/Bugzilla/User.pm @@ -618,23 +618,25 @@ sub match { my $sqlstr = &::SqlQuote(uc($str)); - my $query = "SELECT DISTINCT userid, realname, login_name " . - "FROM profiles "; + my $query = "SELECT DISTINCT userid, realname, login_name " . + "FROM profiles"; if (&::Param('usevisibilitygroups')) { - $query .= ", user_group_map "; + $query .= ", user_group_map"; } - $query .= "WHERE (INSTR(UPPER(login_name), $sqlstr) " . - "OR INSTR(UPPER(realname), $sqlstr)) "; + $query .= " WHERE " . $dbh->sql_position($sqlstr, + "UPPER(login_name)") . + " OR " . $dbh->sql_position($sqlstr, + "UPPER(realname)"); if (&::Param('usevisibilitygroups')) { - $query .= "AND user_group_map.user_id = userid " . - "AND isbless = 0 " . - "AND group_id IN(" . - join(', ', (-1, @{$user->visible_groups_inherited})) . ") " . - "AND grant_type <> " . GRANT_DERIVED; + $query .= " AND user_group_map.user_id = userid" . + " AND isbless = 0" . + " AND group_id IN(" . + join(', ', (-1, @{$user->visible_groups_inherited})) . ")" . + " AND grant_type <> " . GRANT_DERIVED; } - $query .= " AND disabledtext = '' " if $exclude_disabled; - $query .= "ORDER BY length(login_name) "; - $query .= $dbh->sql_limit($limit) if $limit; + $query .= " AND disabledtext = ''" if $exclude_disabled; + $query .= " ORDER BY length(login_name)"; + $query .= " " . $dbh->sql_limit($limit) if $limit; &::PushGlobalSQLState(); &::SendSQL($query); push(@users, new Bugzilla::User(&::FetchSQLData())) while &::MoreSQLData(); diff --git a/checksetup.pl b/checksetup.pl index 50791d0f4..99000abc8 100755 --- a/checksetup.pl +++ b/checksetup.pl @@ -4175,8 +4175,8 @@ if (!$dbh->bz_get_field_def('longdescs', 'already_wrapped')) { # at display-time. print "Fixing old, mis-wrapped comments...\n"; $dbh->do(q{UPDATE longdescs SET already_wrapped = 0 - WHERE ( POSITION('\n' IN thetext ) > 80 - OR POSITION('\n' IN thetext ) = 0 ) + WHERE (} . $dbh->sql_position(q{'\n'}, 'thetext') . q{ > 80 + OR } . $dbh->sql_position(q{'\n'}, 'thetext') . q{ = 0) AND SUBSTRING(thetext FROM 1 FOR 80) LIKE '% %'}); } @@ -4547,8 +4547,8 @@ if (!defined $dbh->bz_get_index_def('longdescs', 'thetext')) { print "Removing paths from filenames in attachments table...\n"; $sth = $dbh->prepare("SELECT attach_id, filename FROM attachments " . - "WHERE INSTR(filename, '/') " . - "OR INSTR(filename, '\\\\')"); + "WHERE " . $dbh->sql_position(q{'/'}, 'filename') . + " OR " . $dbh->sql_position(q{'\\\\'}, 'filename')); $sth->execute; while (my ($attach_id, $filename) = $sth->fetchrow_array) { |