summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-07-29 11:08:03 +0200
committerYorhel <git@yorhel.nl>2020-07-29 11:13:14 +0200
commit32b502511709afd45503779d5a1c38a1ada0f09c (patch)
tree88fcd97965eecca8a391bea81ff518b2d84a1b7f /lib
parentdaef605046269bcad26525c87d3e9a653db532c6 (diff)
SQL: Fix escaping of backslash in LIKE patterns
Diffstat (limited to 'lib')
-rw-r--r--lib/VNWeb/Chars/Elm.pm2
-rw-r--r--lib/VNWeb/DB.pm7
-rw-r--r--lib/VNWeb/Discussions/Elm.pm2
-rw-r--r--lib/VNWeb/Discussions/Search.pm2
-rw-r--r--lib/VNWeb/Misc/ElmAnime.pm2
-rw-r--r--lib/VNWeb/Producers/Elm.pm2
-rw-r--r--lib/VNWeb/Staff/Elm.pm2
-rw-r--r--lib/VNWeb/Tags/Elm.pm2
-rw-r--r--lib/VNWeb/Traits/Elm.pm2
-rw-r--r--lib/VNWeb/User/List.pm2
-rw-r--r--lib/VNWeb/VN/Elm.pm2
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}") : (),