t/db_api_06_antiskid.t

   1 #!/usr/bin/perl
   2
   3 use Test::More tests => 2 + 3 + 3;
   4
   5 use DBI;
   6 use strict;
   7 use warnings;
   8
   9 my $dbh = DBI->connect('dbi:Pg:db=regano', undef, undef,
  10                        {AutoCommit => 1, RaiseError => 1})
  11   or BAIL_OUT $DBI::errstr;
  12
  13 ##
  14
  15 my ($TRUE, $FALSE) = $dbh->selectrow_array(q{SELECT TRUE, FALSE});
  16
  17 my %SESSIONS;
  18 {
  19   my $sth = $dbh->prepare
  20     (q{WITH open_sessions AS
  21          (SELECT s.*, dense_rank() OVER (PARTITION BY s.user_id
  22                                          ORDER BY s.activity DESC)
  23               FROM regano.sessions AS s)
  24         SELECT s.id, u.username, regano_api.session_check(s.id)
  25             FROM open_sessions AS s JOIN regano.users AS u
  26                 ON s.user_id = u.id
  27             WHERE dense_rank = 1});
  28   $sth->execute;
  29   my ($id, $username, $check);
  30   $sth->bind_columns(\($id, $username, $check));
  31   while ($sth->fetch) {
  32     $SESSIONS{$username} = $id if $check;
  33   }
  34 }
  35
  36 BAIL_OUT('No sessions in DB') unless scalar keys %SESSIONS;
  37
  38 ##
  39
  40 # clean up after a previous test run
  41 $dbh->do(q{TRUNCATE regano.antiskid_block});
  42 $dbh->do(q{TRUNCATE regano.antiskid_tally});
  43
  44 my $act_st = $dbh->prepare(q{SELECT regano_api.antiskid_check(?, ?)});
  45 my $achk_st = $dbh->prepare(q{SELECT regano_api.antiskid_session_check(?, ?)});
  46 my $schk_st = $dbh->prepare(q{SELECT regano_api.session_check(?)});
  47
  48 sub act  ($$) {$dbh->selectrow_array($act_st,  {}, @_)}
  49 sub achk ($$) {$dbh->selectrow_array($achk_st, {}, @_)}
  50 sub schk ($)  {$dbh->selectrow_array($schk_st, {}, @_)}
  51
  52 my $good_addr = '65.110.111.78';
  53 my $evil_addr = '69.118.49.108';
  54
  55 ok(achk($SESSIONS{test1}, $good_addr),
  56    q{Good client as 'test1' before actions});
  57 ok(achk($SESSIONS{test2}, $evil_addr),
  58    q{Evil client as 'test2' before actions});
  59
  60 ok(act(q{newuser}, $good_addr),
  61    q{Good client makes user account});
  62 ok(act(q{newuser}, $evil_addr),
  63    q{Evil client makes user account});
  64 ok(!act(q{newuser}, $evil_addr),
  65    q{Evil client attempts to make second user account});
  66
  67 ok(achk($SESSIONS{test1}, $good_addr),
  68    q{Good client as 'test1' after actions});
  69 ok(!achk($SESSIONS{test2}, $evil_addr),
  70    q{Evil client blocked after actions});
  71 ok(schk($SESSIONS{test2}) && !achk($SESSIONS{test1}, $evil_addr),
  72    q{Evil client still blocked with stolen session});
  73
  74 ##
  75
  76 $dbh->disconnect;
  77
  78 __END__