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

(-)a/C4/Reports/Guided.pm (-2 / +2 lines)
Lines 552-559 sub execute_query { Link Here
552
    $limit  = 999999 unless $limit;
552
    $limit  = 999999 unless $limit;
553
    $debug and print STDERR "execute_query($sql, $offset, $limit)\n";
553
    $debug and print STDERR "execute_query($sql, $offset, $limit)\n";
554
554
555
    my $errors = Koha::Reports->validate_sql($sql);
555
    my ( $is_sql_valid, $errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
556
    return (undef, @{$errors}[0]) if (scalar(@$errors));
556
    return (undef, @{$errors}[0]) unless $is_sql_valid;
557
557
558
    foreach my $sql_param ( @$sql_params ){
558
    foreach my $sql_param ( @$sql_params ){
559
        if ( $sql_param =~ m/\n/ ){
559
        if ( $sql_param =~ m/\n/ ){
(-)a/Koha/Report.pm (+30 lines)
Lines 36-41 Koha::Report - Koha Report Object class Link Here
36
36
37
=cut
37
=cut
38
38
39
# FIXME We could only return an error code instead of the arrayref
40
# Only 1 error is returned
41
# TODO Koha::Report->store should check this before saving
42
=head3 is_sql_valid
43
44
my ( $is_sql_valid, $errors ) = $report->is_sql_valid;
45
46
$errors is a arrayref of hashrefs, keys can be sqlerr or queryerr.
47
48
Validate SQL query string so it only contains a select,
49
not any of the harmful queries.
50
51
=cut
52
53
sub is_sql_valid {
54
    my ($self) = @_;
55
56
    my $sql = $self->savedsql;
57
    $sql //= '';
58
    my @errors = ();
59
60
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
61
        push @errors, { sqlerr => $1 };
62
    } elsif ($sql !~ /^\s*SELECT\b\s*/i) {
63
        push @errors, { queryerr => 'Missing SELECT' };
64
    }
65
66
    return ( @errors ? 0 : 1, \@errors );
67
}
68
39
=head3 get_search_info
69
=head3 get_search_info
40
70
41
Return search info
71
Return search info
(-)a/Koha/Reports.pm (-22 lines)
Lines 35-62 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
60
=head3 _type
38
=head3 _type
61
39
62
Returns name of corresponding DBIC resultset
40
Returns name of corresponding DBIC resultset
(-)a/reports/guided_reports.pl (-2 / +4 lines)
Lines 239-245 elsif ( $phase eq 'Update SQL'){ Link Here
239
239
240
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
240
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
241
241
242
    push(@errors, @{Koha::Reports->validate_sql($sql)});
242
    my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
243
    push(@errors, @$validation_errors) unless $is_sql_valid;
243
244
244
    if (@errors) {
245
    if (@errors) {
245
        $template->param(
246
        $template->param(
Lines 595-601 elsif ( $phase eq 'Save Report' ) { Link Here
595
596
596
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
597
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
597
    ## 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
598
    push(@errors, @{Koha::Reports->validate_sql($sql)});
599
    my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
600
    push(@errors, @$validation_errors) unless $is_sql_valid;
599
601
600
    if (@errors) {
602
    if (@errors) {
601
        $template->param(
603
        $template->param(
(-)a/t/db_dependent/Koha/Reports.t (-10 / +36 lines)
Lines 71-84 subtest 'prep_report' => sub { Link Here
71
71
72
$schema->storage->txn_rollback;
72
$schema->storage->txn_rollback;
73
73
74
subtest 'validate_sql' => sub {
74
subtest 'is_sql_valid' => sub {
75
    plan tests => 3 + 6*2;
75
    plan tests => 3 + 6 * 2;
76
    my @badwords = ('UPDATE', 'DELETE', 'DROP', 'INSERT', 'SHOW', 'CREATE');
76
    my @badwords = ( 'UPDATE', 'DELETE', 'DROP', 'INSERT', 'SHOW', 'CREATE' );
77
    is_deeply( Koha::Reports->validate_sql(), [{ queryerr => 'Missing SELECT'}], 'Empty sql is missing SELECT' );
77
    is_deeply(
78
    is_deeply( Koha::Reports->validate_sql('FOO'), [{ queryerr => 'Missing SELECT'}], 'Nonsense sql is missing SELECT' );
78
        [ Koha::Report->new( { savedsql => '' } )->is_sql_valid ],
79
    is_deeply( Koha::Reports->validate_sql('select FOO'), [], 'select FOO is good' );
79
        [ 0, [ { queryerr => 'Missing SELECT' } ] ],
80
        'Empty sql is missing SELECT'
81
    );
82
    is_deeply(
83
        [ Koha::Report->new( { savedsql => 'FOO' } )->is_sql_valid ],
84
        [ 0, [ { queryerr => 'Missing SELECT' } ] ],
85
        'Nonsense sql is missing SELECT'
86
    );
87
    is_deeply(
88
        [ Koha::Report->new( { savedsql => 'select FOO' } )->is_sql_valid ],
89
        [ 1, [] ],
90
        'select FOO is good'
91
    );
80
    foreach my $word (@badwords) {
92
    foreach my $word (@badwords) {
81
        is_deeply( Koha::Reports->validate_sql('select FOO;'.$word.' BAR'), [{ sqlerr => $word}], 'select FOO with '.$word.' BAR' );
93
        is_deeply(
82
        is_deeply( Koha::Reports->validate_sql($word.' qux'), [{ sqlerr => $word}], $word.' qux' );
94
            [
95
                Koha::Report->new(
96
                    { savedsql => 'select FOO;' . $word . ' BAR' }
97
                )->is_sql_valid
98
            ],
99
            [ 0, [ { sqlerr => $word } ] ],
100
            'select FOO with ' . $word . ' BAR'
101
        );
102
        is_deeply(
103
            [
104
                Koha::Report->new( { savedsql => $word . ' qux' } )
105
                  ->is_sql_valid
106
            ],
107
            [ 0, [ { sqlerr => $word } ] ],
108
            $word . ' qux'
109
        );
83
    }
110
    }
84
}
111
  }
85
- 

Return to bug 24695