diff options
author | Yorhel <git@yorhel.nl> | 2022-08-24 14:06:00 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2022-08-24 14:06:02 +0200 |
commit | b3daafde4a7a1cb4076b9a218e3258d34db313dd (patch) | |
tree | 1b7dbc3a008e533520498c7633ac0c98f372958c /lib | |
parent | 0f2d0f3b1bd6e0ef43f268c3bb14a79a824e1e44 (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.pm | 3 | ||||
-rw-r--r-- | lib/VNDB/Schema.pm | 3 | ||||
-rw-r--r-- | lib/VNWeb/Auth.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Misc/Reports.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/User/Register.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Validation.pm | 20 |
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: |