Attachment #119422 for bug #24695

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

Lines 552-559 sub execute_query { Link Here

(-)a/C4/Reports/Guided.pm (-2 / +2 lines)
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/ ){





=cut

# FIXME We could only return an error code instead of the arrayref
# Only 1 error is returned
# TODO Koha::Report->store should check this before saving
=head3 is_sql_valid

my ( $is_sql_valid, $errors ) = $report->is_sql_valid;

$errors is a arrayref of hashrefs, keys can be sqlerr or queryerr.

Validate SQL query string so it only contains a select,
not any of the harmful queries.

=cut

sub is_sql_valid {
    my ($self) = @_;

    my $sql = $self->savedsql;
    $sql //= '';
    my @errors = ();

    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
        push @errors, { sqlerr => $1 };
    } elsif ($sql !~ /^\s*SELECT\b\s*/i) {
        push @errors, { queryerr => 'Missing SELECT' };
    }

    return ( @errors ? 0 : 1, \@errors );
}

=head3 get_search_info

Return search info





=cut

=head3 validate_sql

Validate SQL query string so it only contains a select,
not any of the harmful queries.

=cut

sub validate_sql {
    my ($self, $sql) = @_;

    $sql //= '';
    my @errors = ();

    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
        push @errors, { sqlerr => $1 };
    } elsif ($sql !~ /^\s*SELECT\b\s*/i) {
        push @errors, { queryerr => 'Missing SELECT' };
    }

    return \@errors;
}

=head3 _type

Returns name of corresponding DBIC resultset





    create_non_existing_group_and_subgroup($input, $group, $subgroup);

    my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
    push(@errors, @$validation_errors) unless $is_sql_valid;

    if (@errors) {
        $template->param(


    create_non_existing_group_and_subgroup($input, $group, $subgroup);
    ## FIXME this is AFTER entering a name to save the report under
    my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
    push(@errors, @$validation_errors) unless $is_sql_valid;

    if (@errors) {
        $template->param(





$schema->storage->txn_rollback;

subtest 'is_sql_valid' => sub {
    plan tests => 3 + 6 * 2;
    my @badwords = ( 'UPDATE', 'DELETE', 'DROP', 'INSERT', 'SHOW', 'CREATE' );
    is_deeply(
        [ Koha::Report->new( { savedsql => '' } )->is_sql_valid ],
        [ 0, [ { queryerr => 'Missing SELECT' } ] ],
        'Empty sql is missing SELECT'
    );
    is_deeply(
        [ Koha::Report->new( { savedsql => 'FOO' } )->is_sql_valid ],
        [ 0, [ { queryerr => 'Missing SELECT' } ] ],
        'Nonsense sql is missing SELECT'
    );
    is_deeply(
        [ Koha::Report->new( { savedsql => 'select FOO' } )->is_sql_valid ],
        [ 1, [] ],
        'select FOO is good'
    );
    foreach my $word (@badwords) {
        is_deeply(
            [
                Koha::Report->new(
                    { savedsql => 'select FOO;' . $word . ' BAR' }
                )->is_sql_valid
            ],
            [ 0, [ { sqlerr => $word } ] ],
            'select FOO with ' . $word . ' BAR'
        );
        is_deeply(
            [
                Koha::Report->new( { savedsql => $word . ' qux' } )
                  ->is_sql_valid
            ],
            [ 0, [ { sqlerr => $word } ] ],
            $word . ' qux'
        );
    }
  }
- 

Return to bug 24695

Lines 239-245 elsif ( $phase eq 'Update SQL'){ Link Here

(-)a/reports/guided_reports.pl (-2 / +4 lines)
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(

Lines 71-84 subtest 'prep_report' => sub { Link Here

(-)a/t/db_dependent/Koha/Reports.t (-10 / +36 lines)
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	-

Lines 36-41 Koha::Report - Koha Report Object class Link Here

(-)a/Koha/Report.pm (+30 lines)
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 !~ /^\sSELECT\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

Lines 35-62 Koha::Reports - Koha Report Object set class Link Here

(-)a/Koha/Reports.pm (-22 lines)
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 !~ /^\sSELECT\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