Database-BI
view release on metacpan or search on metacpan
button to drop a single condition without clearing all filters.
[ Bug Fixes ]
- Opening the line graph panel after the pie chart panel (or vice versa)
now correctly hides the previously open panel. The graph panel's
otherPanels list was missing 'pie-panel'.
0.006.0 Tue Sep 15 08:03:20 PM EDT 2026
[ Enhancements ]
- Added a 'home' link on the table display page
- XLSX files are now supported directly via DBD::Excel (each worksheet
is a separate table). The upload endpoint, filesystem browser, and
drag-and-drop drop zone all accept .xlsx files. DBD::Excel added to
TEST_REQUIRES.
- "Combine data" now allows a file to be drag and dropped
- Multi-table left joins are now delegated to Database::Join (>= 0.003.0)
instead of the bespoke _left_join helper. Column collision handling
(prefixing right-table columns with "label.col" when a name clashes) is
provided by Database::Join's new collision_prefix parameter.
DataSource now exposes selectall_arrayref() and an improved columns()
fallback so that DataSource objects can be passed directly to
Database::Join as component databases.
[ Bug Fixes ]
- When attempting to drag and drop a file larger than MAX_UPLOAD_BYTES,
a better error message is displayed which stays on the screen for
3 seconds
- CSV and PSV files whose first line is blank (e.g. a file containing only
a single newline character) are now treated as empty files rather than
croaking "no column with a safe identifier name". _detect_file_info now
returns the _file_is_empty sentinel when the parsed first line yields
zero column names, causing _init_backend to skip Database::Abstraction
entirely and fetch_all to return []. The /open endpoint renders the
standard empty-table view ("No records found") instead of an error page.
Tests added to t/edge_cases.t.
- Files whose names contain spaces (e.g. "transactions for Nigel.xlsx")
now open correctly. Database::Abstraction validates its dbname argument
as a SQL identifier and rejected names with spaces with an "unsafe dbname"
error at fetch time. DataSource::_init_backend now creates a temporary
directory with a symlink using the sanitized name whenever the raw
filename stem contains illegal SQL characters, so D::A sees only the
safe name. Regression test added as Transaction 27.
- The "Line graph..." toolbar button was silently broken: clicking it did
lib/Database/BI/Controller/Dashboard.pm view on Meta::CPAN
Readonly my $MAX_PER_PAGE => 2_000;
Readonly my %SCATTER_SCHEMES => map { $_ => 1 } qw(tableau10 category10 set2 set3 paired);
# Valid aggregate function vocabulary shared by _measure, _grouped_totals,
# and the /pivot measure-spec parser (Phase 2). Do not add functions here
# without a corresponding implementation in _grouped_totals's Perl fold AND
# a corresponding entry in the lower-layer aggregate() implementations.
Readonly my @MEASURE_FNS => qw(sum avg min max count count_distinct);
Readonly my %MEASURE_FN_OK => map { $_ => 1 } @MEASURE_FNS;
# Allowed strptime-style date format patterns for the /dates/save endpoint.
Readonly my %ALLOWED_DATE_PATTERNS => map { $_ => 1 }
qw(%m/%d/%Y %d/%m/%Y %d-%b-%Y);
# ---------------------------------------------------------------------------
# Protected helpers
# ---------------------------------------------------------------------------
# _pagination($self) -> ($page, $per_page, $offset)
#
# Reads ?page= and ?per_page= from the current request, clamps them to sane
lib/Database/BI/Controller/Dashboard.pm view on Meta::CPAN
# network-layer egress firewall. This function handles the Perl-layer
# interception for the most common patterns that operators cannot easily
# filter at the network level: bare loopback aliases and literal private IPs
# hard-coded by an attacker.
#
# Blocked targets:
# localhost / 127.0.0.0/8 / 0.0.0.0 / ::1 -- loopback aliases
# 10.0.0.0/8 -- RFC 1918 private (literal IP)
# 172.16.0.0/12 -- RFC 1918 private (literal IP)
# 192.168.0.0/16-- RFC 1918 private (literal IP)
# 169.254.0.0/16-- link-local; AWS/GCP/Azure metadata endpoint (literal IP)
# 100.64.0.0/10 -- CGNAT / Tailscale shared space (literal IP)
#
# Hostname-based targets (e.g. http://internal.corp.example.com/) are allowed
# at this layer; block them with egress firewall rules instead.
sub _is_safe_url {
my $url = $_[0];
return 0 unless $url =~ m{\Ahttps?://([^/:?\[\]#]+)}i;
my $host = lc $1;
lib/Database/BI/Controller/Dashboard.pm view on Meta::CPAN
POST /dates/save l=path:/data/sales.csv&col=sale_date&pattern=%m/%d/%Y
-> 200 { "ok": 1 }
GET /dates/list?l=path:/data/sales.csv
-> 200 { "patterns": { "sale_date": "%m/%d/%Y" } }
=cut
=head1 DESCRIPTION
All user-facing routes in C<Database::BI> are handled by this controller.
See the individual action POD above for per-endpoint documentation.
=head2 Filter operators and the quick-filter bar
Filters are expressed as C<f=col:op:val> query parameters and are applied
server-side after all joins. Multiple C<f=> params are applied in order
(AND semantics).
The colon separator is split with a limit of 3, so values may themselves
contain colons (e.g. C<f=sale_date:eq:2025-01-15>, or a time value like
C<f=start_time:eq:14:30:00>).
t/cgi_security.t view on Meta::CPAN
subtest '/import -- http://127.0.0.1/ is rejected (SSRF loopback)' => sub {
# Exploit mechanism: bare loopback IPv4 bypasses hostname-based blocklists.
# Proof: $host =~ /\A127\./ check in _is_safe_url() catches all of 127/8.
$t->get_ok('/import?url=' . url_escape('http://127.0.0.1/'))
->status_is(200)
->content_like(qr/private or reserved/i,
'127.0.0.1 is rejected as a loopback address');
};
subtest '/import -- http://169.254.169.254/ is rejected (SSRF cloud metadata)' => sub {
# Exploit mechanism: AWS/GCP/Azure metadata endpoint returns IAM credentials
# and instance metadata; no authentication is required from the instance.
# Proof: 169.254/16 is in the link-local block checked by _is_safe_url().
$t->get_ok('/import?url=' . url_escape('http://169.254.169.254/latest/meta-data/'))
->status_is(200)
->content_like(qr/private or reserved/i,
'169.254.169.254 (cloud metadata) is rejected');
};
# ---------------------------------------------------------------------------
# Attack vector 6: Join left-spec injection
t/cgi_security.t view on Meta::CPAN
# Proof: /\b([a-z]{2})\b/ extracts at most 2 chars; "<script>" has no
# two-letter match in a word-boundary context => $lang is undef => $default.
$t->get_ok('/', {
'Accept-Language' => '<script>alert(1)</script>'
})->status_is(200)
->content_unlike(qr/<script>alert\(1\)<\/script>/,
'XSS in Accept-Language is not reflected');
};
# ---------------------------------------------------------------------------
# Attack vector 11: API endpoint parameter hardening
# ---------------------------------------------------------------------------
subtest 'GET /api/stat -- null byte path returns exists:false, not a crash' => sub {
# Exploit mechanism: "/tmp/file.csv\x00../../etc" -- C-library realpath(3)
# truncates at null; Perl 5.12+ eval catches the exception or the truncated
# path is non-existent. Either way, defined $file is false => {exists:false}.
# Proof: response is HTTP 200 (no crash) with JSON exists:false.
my $null = "/tmp/file.csv\x00../../etc";
$t->get_ok('/api/stat?path=' . url_escape($null))
->status_is(200)
t/edge_cases.t view on Meta::CPAN
};
$t->get_ok('/open?path=' . url_escape($path->to_string))
->status_is(200)
->content_like(qr/Could not open/i, 'error_file_open message in error page');
restore_all();
};
# ---------------------------------------------------------------------------
# Section 10: SSRF via GET /import endpoint -- HTTP-level verification
#
# For blocked IPs the controller short-circuits before any LWP call, so
# these are fast and deterministic (no network required).
# ---------------------------------------------------------------------------
subtest 'GET /import -- private IP in URL triggers SSRF error response' => sub {
for my $ip ('127.0.0.1', '10.0.0.1', '192.168.1.1', '172.16.0.1', '169.254.169.254', '100.64.0.0') {
$t->get_ok("/import?url=" . url_escape("http://$ip/"))
->status_is(200)
->content_like(qr/private or reserved address/i,
t/edge_cases.t view on Meta::CPAN
my $dir = tempdir(CLEANUP => 1);
Mojo::File->new("$dir/newlinepsv.psv")->spew("\n");
my $src;
lives_ok {
$src = Database::BI::Model::DataSource->new(directory => $dir, table => 'newlinepsv');
} 'newline-only PSV: constructor succeeds (no croak)';
my $rows = $src->fetch_all;
is_deeply $rows, [], 'newline-only PSV: fetch_all returns []';
};
# 0-byte CSV via HTTP /open endpoint
subtest 'HTTP -- 0-byte CSV via /open: 200 with empty-table message' => sub {
my $dir = tempdir(CLEANUP => 1);
my $path = "$dir/emptyhttp.csv";
Mojo::File->new($path)->spew('');
$t->get_ok('/open?path=' . url_escape($path))
->status_is(200, '0-byte CSV: /open returns 200')
->content_like(qr/No records found/i, '0-byte CSV: empty-state message present');
};
# 0-byte PSV via HTTP /open endpoint
subtest 'HTTP -- 0-byte PSV via /open: 200 with empty-table message' => sub {
my $dir = tempdir(CLEANUP => 1);
my $path = "$dir/emptyhttppsv.psv";
Mojo::File->new($path)->spew('');
$t->get_ok('/open?path=' . url_escape($path))
->status_is(200, '0-byte PSV: /open returns 200')
->content_like(qr/No records found/i, '0-byte PSV: empty-state message present');
};
# Newline-only CSV via HTTP /open endpoint
subtest 'HTTP -- newline-only CSV via /open: 200 with empty-table message' => sub {
# Regression guard: before the _file_is_empty fix, a blank first line caused
# _init_backend to croak "no safe identifier", and the controller rendered a
# 200 error page — but the error message was confusing for what is simply an
# empty file. After the fix, the response is a clean empty-table view.
my $dir = tempdir(CLEANUP => 1);
my $path = "$dir/nlcsv.csv";
Mojo::File->new($path)->spew("\n");
$t->get_ok('/open?path=' . url_escape($path))
->status_is(200, 'newline-only CSV: /open returns 200')
->content_like(qr/No records found/i, 'newline-only CSV: empty-state message present');
};
# Newline-only PSV via HTTP /open endpoint
subtest 'HTTP -- newline-only PSV via /open: 200 with empty-table message' => sub {
my $dir = tempdir(CLEANUP => 1);
my $path = "$dir/nlpsv.psv";
Mojo::File->new($path)->spew("\n");
$t->get_ok('/open?path=' . url_escape($path))
->status_is(200, 'newline-only PSV: /open returns 200')
->content_like(qr/No records found/i, 'newline-only PSV: empty-state message present');
};
# ---------------------------------------------------------------------------
t/function.t view on Meta::CPAN
ok !$fn->('http://10.255.255.255/'), '10.255.255.255 blocked (10/8 boundary)';
ok !$fn->('http://172.16.0.1/'), '172.16.0.1 blocked (172.16/12)';
ok !$fn->('http://172.31.255.255/'), '172.31.255.255 blocked (172.16/12 boundary)';
ok !$fn->('http://192.168.0.1/'), '192.168.0.1 blocked (192.168/16)';
ok !$fn->('http://192.168.255.255/'), '192.168.255.255 blocked (192.168/16 boundary)';
};
subtest '_is_safe_url -- link-local and CGNAT ranges are blocked' => sub {
my $fn = \&Database::BI::Controller::Dashboard::_is_safe_url;
ok !$fn->('http://169.254.169.254/'), 'AWS metadata endpoint blocked (169.254/16)';
ok !$fn->('http://169.254.0.1/'), '169.254.0.1 blocked (link-local)';
ok !$fn->('http://100.64.0.1/'), '100.64.0.1 blocked (CGNAT 100.64/10)';
ok !$fn->('http://100.127.255.255/'), '100.127.255.255 blocked (CGNAT boundary)';
};
subtest '_is_safe_url -- public addresses are allowed' => sub {
my $fn = \&Database::BI::Controller::Dashboard::_is_safe_url;
ok $fn->('http://8.8.8.8/'), '8.8.8.8 (Google DNS) allowed';
ok $fn->('http://1.1.1.1/'), '1.1.1.1 (Cloudflare) allowed';
t/integration.t view on Meta::CPAN
# ---------------------------------------------------------------------------
# Integration test suite for Database::BI
#
# Focus: cross-module, multi-step workflows where Database::BI (routing/config),
# Database::BI::Controller::Dashboard (HTTP actions), and
# Database::BI::Model::DataSource (data access) all interact.
#
# What is NOT here (covered by dedicated suites):
# - Individual filter operators -> t/filter.t
# - Single-endpoint status codes -> t/unit.t
# - Internal helpers (white-box) -> t/function.t
# - Security / hostile inputs -> t/cgi_security.t
# ---------------------------------------------------------------------------
use Test::Most;
use Test::Mojo;
use Test::Without::Module;
use Readonly;
use File::Spec ();
use File::Temp qw(tempdir tempfile);
subtest '_is_safe_url path-I: 172.16-31.x.x (RFC 1918) -> 0' => sub {
ok !$IS_SAFE->('http://172.16.0.1/'), 'path I: 172.16.x.x blocked';
ok !$IS_SAFE->('http://172.31.255.254/'), 'path I: 172.31.x.x upper bound blocked';
};
subtest '_is_safe_url path-J: 192.168.x.x (RFC 1918) -> 0' => sub {
ok !$IS_SAFE->('http://192.168.0.1/'), 'path J: 192.168.x.x blocked';
};
subtest '_is_safe_url path-K: 169.254.x.x (link-local / metadata) -> 0' => sub {
ok !$IS_SAFE->('http://169.254.169.254/latest/meta-data/'), 'path K: AWS metadata endpoint blocked';
};
subtest '_is_safe_url path-L: 100.64.x.x (CGNAT / Tailscale) -> 0' => sub {
ok !$IS_SAFE->('http://100.64.0.1/'), 'path L: CGNAT range blocked';
};
subtest '_is_safe_url path-M: public IP 8.8.8.8 -> 1 (no private range matches)' => sub {
ok $IS_SAFE->('http://8.8.8.8/'), 'path M: public IP 8.8.8.8 allowed';
};
t/transaction.t view on Meta::CPAN
$t->get_ok('/export?l=table:sales&format=csv&f=amount:gt:1500&f=region:eq:North')
->status_is(200);
is count_csv_rows($t->tx->res->body), $NORTH_GT1500,
'Commutativity: reversed filter order produces same S2 count';
};
# ======================================================================
# TRANSACTION 7: Columns API -> Join coordination
#
# The columns_api endpoint feeds the join panel UI with column names.
# This transaction verifies that:
# Phase 1 GET /api/columns?table=sales -> columns list
# Phase 2 The "region" column from the API can serve as a join key
# Phase 3 GET /join using that key -> merged result
# Phase 4 Column count in result is correct
# ======================================================================
subtest 'Transaction 7: Columns API -> join coordination' => sub {
# ------------------------------------------------------------------
# Phase 1: fetch columns for the sales table.
t/transaction.t view on Meta::CPAN
# Verify alpha records do not contain beta values and vice versa.
ok !(grep { ($_->{value} // '') =~ /^X/ } @$alpha_recs),
'alpha: records contain no XXX values from beta';
ok !(grep { ($_->{value} // '') =~ /^A/ } @$beta_recs),
'beta: records contain no AAA values from alpha';
};
# ======================================================================
# TRANSACTION 11: Combine data lifecycle (cats + dogs)
#
# Verifies the /combine endpoint stacks rows from two heterogeneous files
# into a single unified view with the union of all columns.
#
# cats.csv columns: Species,Name,Color,Breed,Eye Color,Environment
# dogs.csv columns: Species,Name,Color,Breed,Eye Color,Sex,Fixed
# combined columns: Species,Name,Color,Breed,Eye Color,Environment,Sex,Fixed
#
# State invariant:
# combined_rows = cat_rows + dog_rows
# combined_cols = union(cat_cols, dog_cols) = 8
# cat rows have blank Sex,Fixed; dog rows have blank Environment
t/transaction.t view on Meta::CPAN
# Dashboard.pm passes backend => 'auto' to every Database::Join->new()
# call (added in 0.008.0). For datasets larger than max_array_rows
# (default 10,000 combined rows) Database::Join spills to a temporary
# SQLite file; smaller datasets use the existing in-memory array path.
#
# Phase 1 Create left/right CSV fixtures in tempdir
# Phase 2 Database::Join backend => 'array' (control group)
# Phase 3 Database::Join backend => 'sqlite' (SQLite disk-spill path)
# Phase 4 Results from Phases 2 and 3 are identical (sorted by row)
# Phase 5 columns() output identical on both backends
# Phase 6 GET /join endpoint returns 200 with merged data
# (backend => 'auto' is active via Dashboard.pm)
# Phase 7 Source-code guard: Dashboard.pm passes backend => 'auto'
# in exactly 2 Database::Join->new() call sites
# ======================================================================
subtest 'Transaction 32 -- Database::Join backend => auto in join pipeline' => sub {
my $has_dj = eval { require Database::Join; 1 };
SKIP: {
skip 'Database::Join not available', 9 unless $has_dj;
( run in 1.656 second using v1.01-cache-2.11-cpan-16aabcbfbcb )