• Files
• Commits
• Log
• Graph
• Stats

use App::Class;

class App::Utils;

use Data::Printer;
use Term::ANSIColor qw/RESET :constants :constants256/;
 
# modification of Local::Utils dump_query
method dump_query ($sql, @bind) { # p $sql; p @bind;
    # replace each '?' with next element of array:
    $sql =~ s/\Q?/$_/ for @bind;
    # replace all white-space (spaces, tabs, new-lines) with single space:
    $sql =~ s/\s+/ /g; # p $sql;

    if ($sql =~ /^SELECT/) {
        # all queries have a SELECT ... FROM ...
        my ($cols, $remainder) = $sql =~ /SELECT (.*) FROM (.*)/;  # p $cols;
        # new-lines after comma in SELECT statement, unless inside brackets; doesn't
        # work for brackets-within-brackets eg LEFT(ref.name, .... LOCATE(' ', ....))
        $cols =~ s/(\,(?!\s?\?|[^(]+\)))(?!\n)/$1\n /g;      # p $cols;
        $remainder =~ s/(LEFT OUTER|INNER|\sAND\s)/\n  $1/g; # p $from;
        # re-build query string:
        $sql = qq!SELECT\n  $cols\nFROM\n  $remainder!;
        $sql =~ s/\b(WHERE|ORDER BY|GROUP BY|HAVING|LIMIT|OFFSET)\b/\n$1/g;

        my $lh_parens = "\Q(";
        my $rh_parens = "\Q)";
        $sql =~ s/(OR $lh_parens)/\n\t$1/g;
        # $sql =~ s/($lh_parens|$rh_parens)/\n\t$1/g; # also works OK
        # $sql =~ s/(OR|AND)/\n\t$1/g; # doesn't really work
    }
    elsif ($sql =~ /^INSERT/) {
        #$sql =~ s/(VALUES|ON CONFLICT)/\n  $1/g; # new-lines before VALUES or ON CONFLICT
        # new-line & spaces after '(' unless "ON CONFLICT("
        $sql =~ s{(?<!ON CONFLICT)\(}{\(\n  }g;
        # new-line before ')' unless ") DO UPDATE"
        $sql =~ s{\)(?! DO UPDATE)}{\n\)}g;
        # new-line & spaces after comma or "UPDATE SET"
        $sql =~ s{(,|UPDATE SET)}{$1\n  }g;
    }
    # not using UPDATE or DELETE in this app
    $sql .= ';' unless $sql =~ /;$/; # p $sql;

    my $divider = '#' . '-' x 80 ;

    # say STDERR $divider;
    # say STDERR color($sql)->cyan; # eg color($var)->foreground->background
    say STDERR ITALIC FAINT YELLOW ON_GREY3 $sql, RESET;
    say STDERR $divider;
}

method validate_download_request ($dir, $filename, $filepath) {
    # Validation: reject empty, dot, or path traversal attempts
    return [ 'Invalid file name', 400 ] if (
        !defined $filename    ||
        $filename eq ''       ||
        $filename =~ m{[\\/]} ||
        $filename =~ m{\.\.|^\.|/$}
    );
    # p $filepath;
    # p $dir;
    # Ensure file is within the allowed directory
    return [ 'Access denied', 403 ] unless
        $dir->realpath->subsumes($filepath->realpath);
    # Ensure file exists
    return [ 'File not found', 404 ] unless -e $filepath;
    return 'OK'; # no error
}

1;