Attachment #99625 for bug #24695

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

Lines 548-558 sub execute_query { Link Here

(-)a/C4/Reports/Guided.pm (-5 / +3 lines)
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 !~ /^\sSELECT\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





=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);

    push(@errors, @{Koha::Reports->validate_sql($sql)});
        push @errors, {sqlerr => $1};
    }
    elsif ($sql !~ /^(SELECT)/i) {
        push @errors, {queryerr => "No SELECT"};
    }

    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
    push(@errors, @{Koha::Reports->validate_sql($sql)});
        push @errors, {sqlerr => $1};
    }
    elsif ($sql !~ /^(SELECT)/i) {
        push @errors, {queryerr => "No SELECT"};
    }

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





use Modern::Perl;

use Test::More tests => 5;

use Koha::Report;
use Koha::Reports;

is( Koha::Reports->search->count, $nb_of_reports + 1, 'Delete should have deleted the report' );

$schema->storage->txn_rollback;

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

Return to bug 24695

Lines 240-251 elsif ( $phase eq 'Update SQL'){ Link Here

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

Lines 35-40 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
38	=head3 _type	60	=head3 _type
39		61
40	Returns name of corresponding DBIC resultset	62	Returns name of corresponding DBIC resultset

Lines 17-23 Link Here

(-)a/t/db_dependent/Koha/Reports.t (-2 / +13 lines)
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	}