summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2022-08-24 14:06:00 +0200
committerYorhel <git@yorhel.nl>2022-08-24 14:06:02 +0200
commitb3daafde4a7a1cb4076b9a218e3258d34db313dd (patch)
tree1b7dbc3a008e533520498c7633ac0c98f372958c /lib
parent0f2d0f3b1bd6e0ef43f268c3bb14a79a824e1e44 (diff)
Use libloc to add IP location information to the DB
This should save me considerable time in finding duplicate account voters.
Diffstat (limited to 'lib')
-rw-r--r--lib/VNDB/Config.pm3
-rw-r--r--lib/VNDB/Schema.pm3
-rw-r--r--lib/VNWeb/Auth.pm2
-rw-r--r--lib/VNWeb/Misc/Reports.pm4
-rw-r--r--lib/VNWeb/User/Register.pm2
-rw-r--r--lib/VNWeb/Validation.pm20
6 files changed, 27 insertions, 7 deletions
diff --git a/lib/VNDB/Config.pm b/lib/VNDB/Config.pm
index b5f489ee..87b6caad 100644
--- a/lib/VNDB/Config.pm
+++ b/lib/VNDB/Config.pm
@@ -32,6 +32,9 @@ my $config = {
# Put the site in full read-only mode; Login is disabled and nothing is written to the DB. Handy for migrations.
read_only => 0,
+ password_db => undef, # Optional path to a database for password quality checking (see lib/PWLookup.pm)
+ location_db => undef, # Optional path to a libloc database for IP geolocation
+
scr_size => [ 136, 102 ], # w*h of screenshot thumbnails
ch_size => [ 256, 300 ], # max. w*h of char images
cv_size => [ 256, 400 ], # max. w*h of cover images
diff --git a/lib/VNDB/Schema.pm b/lib/VNDB/Schema.pm
index 9d713864..ba303f4a 100644
--- a/lib/VNDB/Schema.pm
+++ b/lib/VNDB/Schema.pm
@@ -67,9 +67,6 @@ sub schema {
}
$col->{decl} = "\"$col->{name}\" $_";
$col->{type} = lc s/^([^ ]+)\s.+/$1/r;
-
- } else {
- die "Unrecognized line in schema.sql: $_\n";
}
}
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm
index cd3a8978..3c0a71ba 100644
--- a/lib/VNWeb/Auth.pm
+++ b/lib/VNWeb/Auth.pm
@@ -299,7 +299,7 @@ sub audit {
tuwf->dbExeci('INSERT INTO audit_log', {
by_uid => $self->uid(),
by_name => $self->{user}{user_name},
- by_ip => tuwf->reqIP(),
+ by_ip => VNWeb::Validation::ipinfo(),
affected_uid => $affected_uid||undef,
affected_name => $affected_uid ? sql('(SELECT username FROM users WHERE id =', \$affected_uid, ')') : undef,
action => $action,
diff --git a/lib/VNWeb/Misc/Reports.pm b/lib/VNWeb/Misc/Reports.pm
index 5792cb76..9e2589e0 100644
--- a/lib/VNWeb/Misc/Reports.pm
+++ b/lib/VNWeb/Misc/Reports.pm
@@ -45,7 +45,7 @@ sub obj_ {
sub is_throttled {
- tuwf->dbVali('SELECT COUNT(*) FROM reports WHERE date > NOW()-\'1 day\'::interval AND', auth ? ('uid =', \auth->uid) : ('ip =', \tuwf->reqIP)) >= $reportsperday
+ tuwf->dbVali('SELECT COUNT(*) FROM reports WHERE date > NOW()-\'1 day\'::interval AND', auth ? ('uid =', \auth->uid) : ('(ip).ip =', \tuwf->reqIP)) >= $reportsperday
}
@@ -66,7 +66,7 @@ elm_api Report => undef, $FORM, sub {
tuwf->dbExeci('INSERT INTO reports', {
uid => auth->uid,
- ip => auth ? undef : tuwf->reqIP,
+ ip => auth ? undef : ipinfo(),
object => $data->{object},
objectnum=> $data->{objectnum},
reason => $data->{reason},
diff --git a/lib/VNWeb/User/Register.pm b/lib/VNWeb/User/Register.pm
index 2931364e..4f0cf262 100644
--- a/lib/VNWeb/User/Register.pm
+++ b/lib/VNWeb/User/Register.pm
@@ -38,7 +38,7 @@ elm_api UserRegister => undef, {
my $id = tuwf->dbVali('INSERT INTO users', {username => $data->{username}}, 'RETURNING id');
tuwf->dbExeci('INSERT INTO users_prefs', {id => $id});
- tuwf->dbExeci('INSERT INTO users_shadow', {id => $id, ip => $ip, mail => $data->{email}});
+ tuwf->dbExeci('INSERT INTO users_shadow', {id => $id, ip => ipinfo(), mail => $data->{email}});
my(undef, $token) = auth->resetpass($data->{email});
diff --git a/lib/VNWeb/Validation.pm b/lib/VNWeb/Validation.pm
index ae2b34b1..69bd5041 100644
--- a/lib/VNWeb/Validation.pm
+++ b/lib/VNWeb/Validation.pm
@@ -16,6 +16,7 @@ our @EXPORT = qw/
samesite
is_insecurepass
is_unique_username
+ ipinfo
form_compile
form_changed
validate_dbid
@@ -115,6 +116,25 @@ sub is_unique_username {
}
+# Lookup IP and return an 'ipinfo' DB string.
+sub ipinfo {
+ my $ip = shift || tuwf->reqIP;
+ state $db = config->{location_db} && do {
+ require Location;
+ Location::init(config->{location_db});
+ };
+ sub esc { ($_[0]//'') =~ s/([,()\\'"])/\\$1/rg }
+ return sprintf "(%s,,,,,,,)", esc $ip if !$db;
+
+ my sub f { Location::lookup_network_has_flag($db, $ip, "LOC_NETWORK_FLAG_$_[0]") ? 't' : 'f' }
+ my $asn = Location::lookup_asn($db, $ip);
+ sprintf "(%s,%s,%d,%s,%s,%s,%s,%s)", esc($ip),
+ esc(Location::lookup_country_code($db,$ip)),
+ $asn, esc(Location::get_as_name($db,$asn)),
+ f('ANONYMOUS_PROXY'), f('SATELLITE_PROVIDER'), f('ANYCAST'), f('DROP');
+}
+
+
# Recursively remove keys from hashes that have a '_when' key that doesn't
# match $when. This is a quick and dirty way to create multiple validation
# schemas from a single schema. For example: