diff options
author | Yorhel <git@yorhel.nl> | 2020-07-29 11:08:03 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2020-07-29 11:13:14 +0200 |
commit | 32b502511709afd45503779d5a1c38a1ada0f09c (patch) | |
tree | 88fcd97965eecca8a391bea81ff518b2d84a1b7f /lib | |
parent | daef605046269bcad26525c87d3e9a653db532c6 (diff) |
SQL: Fix escaping of backslash in LIKE patterns
Diffstat (limited to 'lib')
-rw-r--r-- | lib/VNWeb/Chars/Elm.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/DB.pm | 7 | ||||
-rw-r--r-- | lib/VNWeb/Discussions/Elm.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Discussions/Search.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Misc/ElmAnime.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Producers/Elm.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Staff/Elm.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Tags/Elm.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Traits/Elm.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/User/List.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/VN/Elm.pm | 2 |
11 files changed, 16 insertions, 11 deletions
diff --git a/lib/VNWeb/Chars/Elm.pm b/lib/VNWeb/Chars/Elm.pm index bdc37b35..ce14f490 100644 --- a/lib/VNWeb/Chars/Elm.pm +++ b/lib/VNWeb/Chars/Elm.pm @@ -4,7 +4,7 @@ use VNWeb::Prelude; elm_api Chars => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = $q =~ s/[%_]//gr; + my $qs = sql_like $q; my $l = tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT c.id, c.name, c.original, c.main, cm.name AS main_name, cm.original AS main_original diff --git a/lib/VNWeb/DB.pm b/lib/VNWeb/DB.pm index 2cc6421e..d3128b1c 100644 --- a/lib/VNWeb/DB.pm +++ b/lib/VNWeb/DB.pm @@ -10,7 +10,7 @@ use VNDB::Schema; our @EXPORT = qw/ sql - sql_identifier sql_join sql_comma sql_and sql_or sql_array sql_func sql_fromhex sql_tohex sql_fromtime sql_totime sql_user + sql_identifier sql_join sql_comma sql_and sql_or sql_array sql_func sql_fromhex sql_tohex sql_fromtime sql_totime sql_like sql_user enrich enrich_merge enrich_flatten enrich_obj db_entry db_edit /; @@ -95,6 +95,11 @@ sub sql_totime($) { sql "extract('epoch' from ", $_[0], ')'; } +# Escape a string to be used as a literal match in a LIKE pattern. +sub sql_like($) { + $_[0] =~ s/([%_\\])/\\$1/rg +} + # Returns a list of column names to fetch for displaying a username with HTML::user_(). # Arguments: Name of the 'users' table (default: 'u'), prefix for the fetched fields (default: 'user_'). # (This function returns a plain string so that old non-SQL-Interp functions can also use it) diff --git a/lib/VNWeb/Discussions/Elm.pm b/lib/VNWeb/Discussions/Elm.pm index 77944926..81fe7a9b 100644 --- a/lib/VNWeb/Discussions/Elm.pm +++ b/lib/VNWeb/Discussions/Elm.pm @@ -9,7 +9,7 @@ elm_api Boards => undef, { }, sub { return elm_Unauth if !auth->permBoard; my $q = shift->{search}; - my $qs = $q =~ s/[%_]//gr; + my $qs = sql_like $q; my sub subq { my($prio, $where) = @_; diff --git a/lib/VNWeb/Discussions/Search.pm b/lib/VNWeb/Discussions/Search.pm index 6b56b47b..73deba04 100644 --- a/lib/VNWeb/Discussions/Search.pm +++ b/lib/VNWeb/Discussions/Search.pm @@ -125,7 +125,7 @@ sub threads_ { my $where = sql_and $filt->{b}->@* < keys %BOARD_TYPE ? sql('t.id IN(SELECT tid FROM threads_boards WHERE type IN', $filt->{b}, ')') : (), - map sql('t.title ilike', \('%'.($_ =~ s/%//gr).'%')), grep length($_) > 0, split /[ -,._]/, $filt->{bq}; + map sql('t.title ilike', \('%'.sql_like($_).'%')), grep length($_) > 0, split /[ ,._-]/, $filt->{bq}; noresults_ if !threadlist_ where => $where, diff --git a/lib/VNWeb/Misc/ElmAnime.pm b/lib/VNWeb/Misc/ElmAnime.pm index cfcc1b1c..97260dd4 100644 --- a/lib/VNWeb/Misc/ElmAnime.pm +++ b/lib/VNWeb/Misc/ElmAnime.pm @@ -4,7 +4,7 @@ use VNWeb::Prelude; elm_api Anime => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = $q =~ s/[%_]//gr; + my $qs = sql_like $q; elm_AnimeResult tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT a.id, a.title_romaji AS title, coalesce(a.title_kanji, \'\') AS original diff --git a/lib/VNWeb/Producers/Elm.pm b/lib/VNWeb/Producers/Elm.pm index a41f831c..dae9709d 100644 --- a/lib/VNWeb/Producers/Elm.pm +++ b/lib/VNWeb/Producers/Elm.pm @@ -14,7 +14,7 @@ elm_api Producers => undef, { 'SELECT p.id, p.name, p.original, p.hidden FROM (', sql_join('UNION ALL', map { - my $qs = s/[%_]//gr; + my $qs = sql_like $_; ( /^$RE{pid}$/ ? sql('SELECT 1, id FROM producers WHERE id =', \"$+{id}") : (), sql('SELECT 1+substr_score(lower(name),' , \$qs, '), id FROM producers WHERE name ILIKE', \"%$qs%"), diff --git a/lib/VNWeb/Staff/Elm.pm b/lib/VNWeb/Staff/Elm.pm index 1a88b734..c4db154f 100644 --- a/lib/VNWeb/Staff/Elm.pm +++ b/lib/VNWeb/Staff/Elm.pm @@ -4,7 +4,7 @@ use VNWeb::Prelude; elm_api Staff => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = $q =~ s/[%_]//gr; + my $qs = sql_like $q; elm_StaffResult tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT s.id, sa.aid, sa.name, sa.original diff --git a/lib/VNWeb/Tags/Elm.pm b/lib/VNWeb/Tags/Elm.pm index 0f816bad..089487d7 100644 --- a/lib/VNWeb/Tags/Elm.pm +++ b/lib/VNWeb/Tags/Elm.pm @@ -4,7 +4,7 @@ use VNWeb::Prelude; elm_api Tags => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = $q =~ s/[%_]//gr; + my $qs = sql_like $q; elm_TagResult tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT t.id, t.name, t.searchable, t.applicable, t.state diff --git a/lib/VNWeb/Traits/Elm.pm b/lib/VNWeb/Traits/Elm.pm index c913f421..fc0d0207 100644 --- a/lib/VNWeb/Traits/Elm.pm +++ b/lib/VNWeb/Traits/Elm.pm @@ -4,7 +4,7 @@ use VNWeb::Prelude; elm_api Traits => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = $q =~ s/[%_]//gr; + my $qs = sql_like $q; elm_TraitResult tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT t.id, t.name, t.searchable, t.applicable, t.defaultspoil, t.state, g.id AS group_id, g.name AS group_name diff --git a/lib/VNWeb/User/List.pm b/lib/VNWeb/User/List.pm index 5033519a..16fdae76 100644 --- a/lib/VNWeb/User/List.pm +++ b/lib/VNWeb/User/List.pm @@ -70,7 +70,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub { $char eq 'all' ? () : $char eq '0' ? "ascii(username) not between ascii('a') and ascii('z')" : "username like '$char%'", $opt->{q} ? sql_or( $opt->{q} =~ /^u?([0-9]{1,6})$/ ? sql 'id =', \"$1" : (), - sql 'position(', \$opt->{q}, 'in username) > 0' + sql('username ILIKE', \('%'.sql_like($opt->{q}).'%')), ) : () ); diff --git a/lib/VNWeb/VN/Elm.pm b/lib/VNWeb/VN/Elm.pm index 3aded8e2..3bf02d59 100644 --- a/lib/VNWeb/VN/Elm.pm +++ b/lib/VNWeb/VN/Elm.pm @@ -14,7 +14,7 @@ elm_api VN => undef, { 'SELECT v.id, v.title, v.original, v.hidden FROM (', sql_join('UNION ALL', map { - my $qs = s/[%_]//gr; + my $qs = sql_like $_; my @qs = normalize_query $_; ( /^$RE{vid}$/ ? sql('SELECT 1, id FROM vn WHERE id =', \"$+{id}") : (), |