diff options
| -rw-r--r-- | Bugzilla/DB.pm | 92 | ||||
| -rw-r--r-- | Bugzilla/DB/Pg.pm | 69 | ||||
| -rw-r--r-- | Bugzilla/Search.pm | 24 |
3 files changed, 171 insertions, 14 deletions
diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 6c9a29ebb..5bc83f9d6 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -363,6 +363,31 @@ sub sql_position { return "POSITION($fragment IN $text)"; } +sub sql_like { + my ($self, $fragment, $column) = @_; + + my $quoted = $self->quote($fragment); + + return $self->sql_position($quoted, $column) . " > 0"; +} + +sub sql_ilike { + my ($self, $fragment, $column) = @_; + + my $quoted = $self->quote($fragment); + + return $self->sql_iposition($quoted, $column) . " > 0"; +} + +sub sql_not_ilike { + my ($self, $fragment, $column) = @_; + + my $quoted = $self->quote($fragment); + + return $self->sql_iposition($quoted, $column) . " = 0"; +} + + sub sql_group_by { my ($self, $needed_columns, $optional_columns) = @_; @@ -2014,6 +2039,73 @@ Formatted SQL for substring search (scalar) Just like L</sql_position>, but case-insensitive. +=item C<sql_like> + +=over + +=item B<Description> + +Outputs SQL to search for an instance of a string (fragment) +in a table column (column). + +Note that the fragment must not be quoted. L</sql_like> will +quote the fragment itself. + +This is a case sensitive search. + +Note: This does not necessarily generate an ANSI LIKE statement, but +could be overridden to do so in a database subclass if required. + +=item B<Params> + +=over + +=item C<$fragment> - the string fragment that we are searching for (scalar) + +=item C<$column> - the column to search + +=back + +=item B<Returns> + +Formatted SQL to return results from columns that contain the fragment. + +=back + +=item C<sql_ilike> + +Just like L</sql_like>, but case-insensitive. + +=item C<sql_not_ilike> + +=over + +=item B<Description> + +Outputs SQL to search for columns (column) that I<do not> contain +instances of the string (fragment). + +Note that the fragment must not be quoted. L</sql_not_ilike> will +quote the fragment itself. + +This is a case insensitive search. + +=item B<Params> + +=over + +=item C<$fragment> - the string fragment that we are searching for (scalar) + +=item C<$column> - the column to search + +=back + +=item B<Returns> + +Formated sql to return results from columns that do not contain the fragment + +=back + =item C<sql_group_by> =over diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm index a950c575f..cbf8d7af1 100644 --- a/Bugzilla/DB/Pg.pm +++ b/Bugzilla/DB/Pg.pm @@ -126,6 +126,36 @@ sub sql_position { return "POSITION(${fragment}::text IN ${text}::text)"; } +sub sql_like { + my ($self, $fragment, $column, $not) = @_; + $not //= ''; + + return "${column}::text $not LIKE " . $self->sql_like_escape($fragment) . " ESCAPE '|'"; +} + +sub sql_ilike { + my ($self, $fragment, $column, $not) = @_; + $not //= ''; + + return "${column}::text $not ILIKE " . $self->sql_like_escape($fragment) . " ESCAPE '|'"; +} + +sub sql_not_ilike { + return shift->sql_ilike(@_, 'NOT'); +} + +# Escapes any % or _ characters which are special in a LIKE match. +# Also performs a $dbh->quote to escape any quote characters. +sub sql_like_escape { + my ($self, $fragment) = @_; + + $fragment =~ s/\|/\|\|/g; # escape the escape character if it appears + $fragment =~ s/%/\|%/g; # percent and underscore are the special match + $fragment =~ s/_/\|_/g; # characters in SQL. + + return $self->quote("%$fragment%"); +} + sub sql_regexp { my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; $real_pattern ||= $pattern; @@ -448,6 +478,39 @@ sub bz_table_list_real { 1; +=head2 Functions + +=over + +=item C<sql_like_escape> + +=over + +=item B<Description> + +The postgres versions of the sql_like methods use the ANSI SQL LIKE +statements to perform substring searching. To prevent issues with +users attempting to search for strings containing special characters +associated with LIKE, we escape them out so they don't affect the search +terms. + +=item B<Params> + +=over + +=item C<$fragment> - The string fragment in need of escaping and quoting + +=back + +=item B<Returns> + +The fragment with any pre existing %,_,| characters escaped out, wrapped in +percent characters and quoted. + +=back + +=back + =head1 B<Methods in need of POD> =over @@ -462,6 +525,12 @@ sub bz_table_list_real { =item sql_position +=item sql_like + +=item sql_ilike + +=item sql_not_ilike + =item sql_limit =item sql_not_regexp diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index 8097d5fb8..0785a7e67 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -2126,9 +2126,7 @@ sub _substring_terms { # split each term on spaces and commas anyway. my @words = split(/[\s,]+/, $args->{value}); @words = grep { defined $_ and $_ ne '' } @words; - @words = map { $dbh->quote($_) } @words; - my @terms = map { $dbh->sql_iposition($_, $args->{full_field}) . " > 0" } - @words; + my @terms = map { $dbh->sql_ilike($_, $args->{full_field}) } @words; return @terms; } @@ -3154,28 +3152,26 @@ sub _simple_operator { sub _casesubstring { my ($self, $args) = @_; - my ($full_field, $quoted) = @$args{qw(full_field quoted)}; + my ($full_field, $value) = @$args{qw(full_field value)}; my $dbh = Bugzilla->dbh; - - $args->{term} = $dbh->sql_position($quoted, $full_field) . " > 0"; + + $args->{term} = $dbh->sql_like($value, $full_field); } sub _substring { my ($self, $args) = @_; - my ($full_field, $quoted) = @$args{qw(full_field quoted)}; + my ($full_field, $value) = @$args{qw(full_field value)}; my $dbh = Bugzilla->dbh; - - # XXX This should probably be changed to just use LIKE - $args->{term} = $dbh->sql_iposition($quoted, $full_field) . " > 0"; + + $args->{term} = $dbh->sql_ilike($value, $full_field); } sub _notsubstring { my ($self, $args) = @_; - my ($full_field, $quoted) = @$args{qw(full_field quoted)}; + my ($full_field, $value) = @$args{qw(full_field value)}; my $dbh = Bugzilla->dbh; - - # XXX This should probably be changed to just use NOT LIKE - $args->{term} = $dbh->sql_iposition($quoted, $full_field) . " = 0"; + + $args->{term} = $dbh->sql_not_ilike($value, $full_field); } sub _regexp { |