View | Details | Raw Unified | Return to bug 24695
Collapse All | Expand All

(-)a/C4/Reports/Guided.pm (-5 / +3 lines)
Lines 548-558 sub execute_query { Link Here
548
    $offset = 0    unless $offset;
548
    $offset = 0    unless $offset;
549
    $limit  = 999999 unless $limit;
549
    $limit  = 999999 unless $limit;
550
    $debug and print STDERR "execute_query($sql, $offset, $limit)\n";
550
    $debug and print STDERR "execute_query($sql, $offset, $limit)\n";
551
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
551
552
        return (undef, {  sqlerr => $1} );
552
    my $errors = Koha::Reports->validate_sql($sql);
553
    } elsif ($sql !~ /^\s*SELECT\b\s*/i) {
553
    return (undef, @{$errors}[0]) if (scalar(@$errors));
554
        return (undef, { queryerr => 'Missing SELECT'} );
555
    }
556
554
557
    my ($useroffset, $userlimit);
555
    my ($useroffset, $userlimit);
558
556
(-)a/Koha/Reports.pm (+22 lines)
Lines 35-40 Koha::Reports - Koha Report Object set class Link Here
35
35
36
=cut
36
=cut
37
37
38
=head3 validate_sql
39
40
Validate SQL query string so it only contains a select,
41
not any of the harmful queries.
42
43
=cut
44
45
sub validate_sql {
46
    my ($self, $sql) = @_;
47
48
    $sql //= '';
49
    my @errors = ();
50
51
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
52
        push @errors, { sqlerr => $1 };
53
    } elsif ($sql !~ /^\s*SELECT\b\s*/i) {
54
        push @errors, { queryerr => 'Missing SELECT' };
55
    }
56
57
    return \@errors;
58
}
59
38
=head3 _type
60
=head3 _type
39
61
40
Returns name of corresponding DBIC resultset
62
Returns name of corresponding DBIC resultset
(-)a/reports/guided_reports.pl (-12 / +2 lines)
Lines 240-251 elsif ( $phase eq 'Update SQL'){ Link Here
240
240
241
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
241
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
242
242
243
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
243
    push(@errors, @{Koha::Reports->validate_sql($sql)});
244
        push @errors, {sqlerr => $1};
245
    }
246
    elsif ($sql !~ /^(SELECT)/i) {
247
        push @errors, {queryerr => "No SELECT"};
248
    }
249
244
250
    if (@errors) {
245
    if (@errors) {
251
        $template->param(
246
        $template->param(
Lines 601-612 elsif ( $phase eq 'Save Report' ) { Link Here
601
596
602
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
597
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
603
    ## FIXME this is AFTER entering a name to save the report under
598
    ## FIXME this is AFTER entering a name to save the report under
604
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
599
    push(@errors, @{Koha::Reports->validate_sql($sql)});
605
        push @errors, {sqlerr => $1};
606
    }
607
    elsif ($sql !~ /^(SELECT)/i) {
608
        push @errors, {queryerr => "No SELECT"};
609
    }
610
600
611
    if (@errors) {
601
    if (@errors) {
612
        $template->param(
602
        $template->param(
(-)a/t/db_dependent/Koha/Reports.t (-2 / +13 lines)
Lines 17-23 Link Here
17
17
18
use Modern::Perl;
18
use Modern::Perl;
19
19
20
use Test::More tests => 4;
20
use Test::More tests => 5;
21
21
22
use Koha::Report;
22
use Koha::Report;
23
use Koha::Reports;
23
use Koha::Reports;
Lines 49-51 $retrieved_report_1->delete; Link Here
49
is( Koha::Reports->search->count, $nb_of_reports + 1, 'Delete should have deleted the report' );
49
is( Koha::Reports->search->count, $nb_of_reports + 1, 'Delete should have deleted the report' );
50
50
51
$schema->storage->txn_rollback;
51
$schema->storage->txn_rollback;
52
- 
52
53
subtest 'validate_sql' => sub {
54
    plan tests => 3 + 6*2;
55
    my @badwords = ('UPDATE', 'DELETE', 'DROP', 'INSERT', 'SHOW', 'CREATE');
56
    is_deeply( Koha::Reports->validate_sql(), [{ queryerr => 'Missing SELECT'}], 'Empty sql is missing SELECT' );
57
    is_deeply( Koha::Reports->validate_sql('FOO'), [{ queryerr => 'Missing SELECT'}], 'Nonsense sql is missing SELECT' );
58
    is_deeply( Koha::Reports->validate_sql('select FOO'), [], 'select FOO is good' );
59
    foreach my $word (@badwords) {
60
        is_deeply( Koha::Reports->validate_sql('select FOO;'.$word.' BAR'), [{ sqlerr => $word}], 'select FOO with '.$word.' BAR' );
61
        is_deeply( Koha::Reports->validate_sql($word.' qux'), [{ sqlerr => $word}], $word.' qux' );
62
    }
63
}

Return to bug 24695