Database-BI
view release on metacpan or search on metacpan
lib/Database/BI/Controller/Dashboard.pm view on Meta::CPAN
error_table_invalid => 'Invalid table name "%s"',
error_table_open => 'Could not open table "%s": %s',
error_file_open => 'Could not open "%s": %s',
error_no_path => 'No path specified',
error_not_found => 'File or directory not found: %s',
error_dir_not_found => 'Directory not found: %s',
error_write_failed => 'Write failed: %s',
error_ext_required => 'Use a .csv or .sql filename extension',
error_upload_none => 'No file received',
error_upload_ext => 'Unsupported file type. Accepted: CSV, PSV, XML, SQLite (.sql)',
error_upload_too_large => 'File too large (maximum %s MiB)',
error_path_required => '"path" parameter is required',
error_url_required => 'Please enter a URL',
error_url_invalid => '"%s" is not a valid http:// or https:// URL',
error_url_fetch => 'Could not load HTML table from "%s": %s',
error_url_ssrf => '"%s" resolves to a private or reserved address and cannot be fetched',
);
# Maximum accepted upload body size. Enforced both here (application layer)
# and via Mojolicious max_request_size (transport layer) set in startup().
# Must match $MAX_REQUEST_SIZE in BI.pm.
Readonly my $MAX_UPLOAD_MIB => 50;
Readonly my $MAX_UPLOAD_BYTES => $MAX_UPLOAD_MIB * 1_048_576;
# ---------------------------------------------------------------------------
# Private helpers
# ---------------------------------------------------------------------------
# _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 :Private ($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) = @_;
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;
}
# _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 :Private ($self) {
my $conf = $self->app->config;
my $platform = $conf->{platform} // 'web';
my $default = $conf->{language} // 'en';
my $language = $self->_resolve_language($default);
return ($platform, $language);
}
# _resolve_language($self, $default) -> $language_code
#
# Purpose: Extract the first two-letter language code from the Accept-Language
# request header, then validate that a template directory exists for
# that language. Falls back to $default when the header is absent,
# unparseable, or points to a non-existent template directory.
# Entry: $default is a non-empty string (e.g. 'en').
# Exit: Returns a two-letter ISO 639-1 language code string.
# Side Effects: Filesystem stat for the template directory.
sub _resolve_language :Private ($self, $default) {
my $accept = $self->req->headers->accept_language // '';
my ($lang) = $accept =~ /
\b
( [a-z]{2} ) # ISO 639-1 primary language subtag (exactly 2 lowercase letters)
(?: - [A-Z]{2} )? # optional ISO 3166-1 region subtag: hyphen + 2 uppercase letters
lib/Database/BI/Controller/Dashboard.pm view on Meta::CPAN
# Browser
http://localhost:3000/join?l=table:sales&j=table:products|product|name
# The j= parameter is: right-table-spec | left-key | right-key
# You can chain multiple joins:
http://localhost:3000/join?l=table:sales&j=table:products|product|name&j=table:regions|region|id
B<Open a file anywhere on the filesystem (not just in data/):>
http://localhost:3000/open?path=/home/user/reports/q3.csv
B<Download the current view as a CSV file:>
# Uses the same l=, j=, f= parameters as /join
http://localhost:3000/export?l=table:sales&format=csv
# Download as a SQLite database file instead:
http://localhost:3000/export?l=table:sales&format=sqlite
# Download a filtered + joined result:
http://localhost:3000/export?l=table:sales&j=table:products|product|name&f=region:eq:North&format=csv
B<Save the current view to a file on the server (instead of downloading):>
# POST with form fields; format is inferred from the filename extension
curl -X POST http://localhost:3000/export \
-F l=table:sales \
-F dir=/home/user/exports \
-F filename=report.csv
# Save as SQLite:
curl -X POST http://localhost:3000/export \
-F l=table:sales \
-F dir=/home/user/exports \
-F filename=report.sql
B<Get the column list for a table (used by the join UI):>
curl http://localhost:3000/api/columns?table=sales
# Returns: {"columns":["product","region","amount","date"]}
B<Check when a file was last modified (used by the tooltip on the home page):>
curl 'http://localhost:3000/api/stat?path=/data/sales.csv'
# Returns: {"exists":true,"path":"/data/sales.csv","mtime":1700000000,"size":1234}
B<Browse the filesystem to find a data file:>
http://localhost:3000/browse
http://localhost:3000/browse?path=/home/user/data
B<Upload a data file by dropping it onto the page (multipart form POST):>
curl -X POST http://localhost:3000/upload \
-F file=@/home/user/data/sales.csv
# Returns: {"url":"/open?path=/.../.uploads/.../sales.csv","path":"/.../.uploads/.../sales.csv"}
=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
The C<f=col:op:val> filter spec supports:
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)
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>).
=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>),
the filter still works because the C<col:op:val> spec is split on the B<first
two> colons only -- the rest of the string becomes the value.
# This correctly matches "14:30:00" in the start_time column:
?f=start_time:eq:14:30:00
=item B<Left join keeps only the FIRST matching right-table row>
When the right table has two rows with the same join key, only the first one
(in file order) is used. The second is silently ignored. If you need all
matches, consider pre-processing your data so join keys are unique.
=item B<Export format comes from the filename extension, not a Content-Type header>
When using C<POST /export> to save a file to disk, the format (CSV or SQLite)
is determined by the extension of the C<filename> parameter. C<.csv> produces
a CSV file; C<.sql> produces a SQLite database. Any other extension returns
HTTP 415 (Unsupported Media Type). The C<Content-Type> request header is
ignored entirely.
=item B<Template Toolkit variables starting with underscore are silently dropped>
If you add a stash variable with a name starting with C<_> (for example,
( run in 1.911 second using v1.01-cache-2.11-cpan-9789f410c06 )