view release on metacpan or  search on metacpan

lib/Database/BI/Controller/Dashboard.pm  view on Meta::CPAN

Readonly my $MAX_UPLOAD_MIB      => 50;
Readonly my $MAX_UPLOAD_BYTES    => $MAX_UPLOAD_MIB * 1_048_576;
Readonly my $DEFAULT_JOIN_MAX_ROWS => 10_000;
Readonly my $MAX_PREFIX_LEN        => 20;
Readonly my $DEFAULT_PER_PAGE      => 200;
Readonly my $MAX_PER_PAGE          => 2_000;

# ---------------------------------------------------------------------------
# Protected helpers
# ---------------------------------------------------------------------------

# _pagination($self) -> ($page, $per_page, $offset)
#
# Reads ?page= and ?per_page= from the current request, clamps them to sane
# ranges, and returns the 1-based page number, the page size, and the
# 0-based row offset.  Pagination is optional: when neither param is present
# per_page defaults to DEFAULT_PER_PAGE.
sub _pagination {
	my ($self) = @_;
	my $per_page = int($self->param('per_page') // $DEFAULT_PER_PAGE);
	$per_page = 1         if $per_page < 1;
	$per_page = $MAX_PER_PAGE if $per_page > $MAX_PER_PAGE;
	my $page = int($self->param('page') // 1);
	$page = 1 if $page < 1;
	my $offset = ($page - 1) * $per_page;
	return ($page, $per_page, $offset);
}

# _i18n($self, $key, @sprintf_args) -> $string
#
# Purpose: Look up a user-visible string from %MESSAGES and apply sprintf
#          for positional arguments.  The entry point for future i18n
#          backend integration (e.g. Locale::Maketext).
# Entry:   $key must be a key in %MESSAGES; @args are sprintf positionals.
# Exit:    Returns the formatted string, or a fallback containing $key.
sub _i18n :Protected ($self, $key, @args) {
	my $tmpl = $MESSAGES{$key} // return "Internal error: unknown message key '$key'";
	return @args ? sprintf($tmpl, @args) : $tmpl;
}

# _is_safe_url($url) -> bool
#
# Purpose: SSRF guard.  Blocks the most dangerous classes of Server-Side Request
#          Forgery targets: loopback aliases (localhost, 127/8) and literal
#          private/link-local/CGNAT IPv4 addresses in the URL host component.
#
# Design rationale for scope:
#   Resolving hostnames via DNS and checking the result is ineffective: a
#   separate DNS lookup happens at LWP connect time (TOCTOU / DNS rebinding
#   window).  The authoritative defence against hostname-based SSRF is a
#   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;

	# Block well-known loopback aliases.
	return 0 if $host eq 'localhost'
	          || $host =~ /\A127\./
	          || $host eq '0.0.0.0'
	          || $host eq '::1';

	# For literal IPv4 addresses only: check private/link-local/CGNAT ranges.
	# Skipping DNS for hostname targets avoids a live network call in tests and
	# removes the TOCTOU window that makes DNS-resolved checks illusory anyway.
	return 1 unless $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/;

	my $packed = inet_aton($host) or return 1;
	my $n = unpack 'N', $packed;

	return 0 if ($n & 0xFF000000) == 0x0A000000;	# 10/8
	return 0 if ($n & 0xFFF00000) == 0xAC100000;	# 172.16/12
	return 0 if ($n & 0xFFFF0000) == 0xC0A80000;	# 192.168/16
	return 0 if ($n & 0xFFFF0000) == 0xA9FE0000;	# 169.254/16 (link-local / metadata)
	return 0 if ($n & 0xFFC00000) == 0x64400000;	# 100.64/10 (CGNAT)
	return 1;
}

# _safe_back_url($url) -> $url | undef
#
# Purpose: Sanitise a user-supplied back-link URL before it is passed to the
#          template and rendered inside an href="" attribute.  Template Toolkit's
#          | html filter encodes <>&" but does NOT block the javascript: or
#          data: URI schemes, so a raw param('back') = "javascript:alert(1)"
#          would be rendered verbatim inside the href and execute on click.
#
# Rule:    Only absolute http(s) URLs and root-relative paths (starting with /)
#          are permitted.  Anything else (javascript:, data:, vbscript:, empty
#          strings, undef) returns undef so the template renders no href.
#
sub _safe_back_url {
	my ($url) = @_;
	return undef unless defined $url && length($url);
	return $url if $url =~ m{\A(?:/(?!/)|https?://)}i;
	return undef;
}

# _resolve_template($self) -> ($platform, $language)
#
# Purpose: Read platform and language from config, then resolve the Accept-Language
#          header to a language code -- falling back to the config default when
#          no template directory exists for the resolved language.
# Exit:    Returns ($platform, $language) -- both guaranteed non-empty strings.
sub _resolve_template :Protected ($self) {
	my $conf         = $self->app->config();
	my $cfg_platform = $conf->{platform} // 'web';

lib/Database/BI/Controller/Dashboard.pm  view on Meta::CPAN

  Column not found: <name>               Named column absent from the result set.
  Could not open data source             Left-table spec unresolvable or 404.
  No plottable data: ...                 All rows lack a valid numeric value.

=head3 EXAMPLE

  GET /pie?l=table:sales&cat=region&val=amount&back=/view/sales

Renders a pie chart of total C<amount> per C<region>.  Clicking the
"North" slice navigates to C</view/sales?f=region:eq:North>.

With a donut hole:

  GET /pie?l=table:sales&cat=region&val=amount&donut=1

=head3 FORMAL SPECIFICATION

  pie_view : Controller x Params -> HTML | Error

  let pipeline = run_export_pipeline(l, j, f, d)
  let totals   = { cat => sum { val(row) | row in pipeline.records,
                                           cat(row) != '', num(val(row)) != bot }
                 | cat in range(cat_col) }

  pre  cat in params /\ val in params     else 400
  pre  cat in pipeline.cols               else 400
  pre  val in pipeline.cols               else 400
  pre  totals != {}                        else 200 "No plottable data"
  post HTML::D3->render_pie_chart_snippet(slices) embedded in TT layout
       where slices = sort_desc { |s.value| | s in totals }

=cut

=head2 clear_uploads

C<POST /uploads/clear> -- Delete every file from the C<.uploads/> staging
directory and return the total disk space recovered.

The C<.uploads/> directory accumulates files from drag-and-drop uploads.
It grows indefinitely; this action lets users reclaim that space without
needing shell access.

=head3 API SPECIFICATION

=head4 INPUT

No parameters.  The C<.uploads/> path is always the one under C<app->home>.

=head4 OUTPUT

  200 application/json   { "freed": <bytes>, "count": <n> }

C<freed> is the total number of bytes removed.  C<count> is the number of
files deleted.  Both are 0 when the directory is absent or already empty.

=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>).

B<Supported operators:>

  eq        case-insensitive string equality
  ne        case-insensitive string inequality
  contains  case-insensitive substring match
  starts    case-insensitive prefix match
  lt        numeric less-than
  le        numeric less-than-or-equal
  gt        numeric greater-than
  ge        numeric greater-than-or-equal
  empty     cell is undef or empty string (val ignored)
  notempty  cell is defined and non-empty (val ignored)

B<Quick-filter bar (table view):>

A filter row below the toolbar lets the user pick a column, operator, and
value and press "Apply filter" (or Enter) to add a condition without
opening any panel.  The current URL is updated with the new C<f=> param
and the page reloads; all other URL parameters (joins, dedup, etc.) are
preserved automatically via the browser C<URL> API.

Active filter chips appear in the toolbar.  Each chip has an individual
x-button to remove just that condition.  The "x Clear filters" link
removes all C<f=> params at once.

B<Drill-down from pie chart:>

Clicking a pie slice or legend entry on C</pie> navigates to the table
view with C<f=cat_col:eq:label> appended, so the user can inspect the
rows behind any slice without manually typing a filter.

=head1 COMMON PITFALLS

These are the most common mistakes when working with this controller.

=over 4

=item B<SQLite files must use the .sql extension, not .sqlite>

C<Database::Abstraction> (the data-reading library) probes for a file called
C<tablename.sql> when it wants to open a SQLite database.  It does B<not>
look for C<.sqlite> or C<.db3>.  If your file is called C<inventory.sqlite>,
rename it to C<inventory.sql> or it will not appear in the file browser and
will return 404 when opened.

=item B<Filter values that contain a colon still work>

A date like C<2025-01-15> contains hyphens, not colons, so it is fine.  But
if your value itself contains a colon (for example, a time like C<14:30:00>),