summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--Bugzilla/DB.pm16
-rw-r--r--Bugzilla/DB/Mysql.pm14
-rw-r--r--Bugzilla/Search.pm23
-rw-r--r--Bugzilla/User.pm28
-rwxr-xr-xchecksetup.pl8
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) {