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

(-)a/C4/Reports/Guided.pm (-6 / +8 lines)
Lines 539-555 sub strip_limit { Link Here
539
539
540
sub execute_query {
540
sub execute_query {
541
541
542
    my ( $sql, $offset, $limit, $sql_params, $report_id ) = @_;
542
    my $params     = shift;
543
543
    my $sql        = $params->{sql};
544
    $sql_params = [] unless defined $sql_params;
544
    my $offset     = $params->{offset} || 0;
545
    my $limit      = $params->{limit}  || 999999;
546
    my $sql_params = defined $params->{sql_params} ? $params->{sql_params} : [];
547
    my $report_id  = $params->{report_id};
545
548
546
    # check parameters
549
    # check parameters
547
    unless ($sql) {
550
    unless ($sql) {
548
        carp "execute_query() called without SQL argument";
551
        carp "execute_query() called without SQL argument";
549
        return;
552
        return;
550
    }
553
    }
551
    $offset = 0    unless $offset;
552
    $limit  = 999999 unless $limit;
553
    $debug and print STDERR "execute_query($sql, $offset, $limit)\n";
554
    $debug and print STDERR "execute_query($sql, $offset, $limit)\n";
554
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
555
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
555
        return (undef, {  sqlerr => $1} );
556
        return (undef, {  sqlerr => $1} );
Lines 1033-1039 sub EmailReport { Link Here
1033
    my $sql = $report->savedsql;
1034
    my $sql = $report->savedsql;
1034
    return ( { FATAL => "NO_REPORT" } ) unless $sql;
1035
    return ( { FATAL => "NO_REPORT" } ) unless $sql;
1035
1036
1036
    my ( $sth, $errors ) = execute_query( $sql ); #don't pass offset or limit, hardcoded limit of 999,999 will be used
1037
    #don't pass offset or limit, hardcoded limit of 999,999 will be used
1038
    my ( $sth, $errors ) = execute_query( { sql => $sql, report_id => $report_id } );
1037
    return ( undef, [{ FATAL => "REPORT_FAIL" }] ) if $errors;
1039
    return ( undef, [{ FATAL => "REPORT_FAIL" }] ) if $errors;
1038
1040
1039
    my $counter = 1;
1041
    my $counter = 1;
(-)a/misc/cronjobs/runreport.pl (-1 / +7 lines)
Lines 269-275 foreach my $report_id (@ARGV) { Link Here
269
    my $params_needed = ( $sql =~ s/(<<[^>]+>>)/\?/g );
269
    my $params_needed = ( $sql =~ s/(<<[^>]+>>)/\?/g );
270
    die("You supplied ". scalar @params . " parameter(s) and $params_needed are required by the report") if scalar @params != $params_needed;
270
    die("You supplied ". scalar @params . " parameter(s) and $params_needed are required by the report") if scalar @params != $params_needed;
271
271
272
    my ($sth) = execute_query( $sql, undef, undef, \@params, $report_id );
272
    my ($sth) = execute_query(
273
        {
274
            sql        => $sql,
275
            sql_params => \@params,
276
            report_id  => $report_id,
277
        }
278
    );
273
    my $count = scalar($sth->rows);
279
    my $count = scalar($sth->rows);
274
    unless ($count) {
280
    unless ($count) {
275
        print "NO OUTPUT: 0 results from execute_query\n";
281
        print "NO OUTPUT: 0 results from execute_query\n";
(-)a/opac/svc/report (-3 / +9 lines)
Lines 56-62 if ($cache_active) { Link Here
56
}
56
}
57
57
58
unless ($json_text) {
58
unless ($json_text) {
59
    my $offset = 0;
60
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
59
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
61
    my $sql = $report_rec->savedsql;
60
    my $sql = $report_rec->savedsql;
62
61
Lines 64-71 unless ($json_text) { Link Here
64
    $sql =~ s/(<<.*?>>)/\?/g;
63
    $sql =~ s/(<<.*?>>)/\?/g;
65
    $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g;
64
    $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g;
66
65
67
    my ( $sth, $errors ) =
66
    my ( $sth, $errors ) = execute_query(
68
      execute_query( $sql, $offset, $limit, \@sql_params, $report_id );
67
        {
68
            sql        => $sql,
69
            offset     => 0,
70
            limit      => $limit,
71
            sql_params => \@sql_params,
72
            report_id  => $report_id,
73
        }
74
    );
69
    if ($sth) {
75
    if ($sth) {
70
        my $lines;
76
        my $lines;
71
        if ($report_annotation) {
77
        if ($report_annotation) {
(-)a/reports/guided_reports.pl (-3 / +10 lines)
Lines 829-835 elsif ($phase eq 'Run this report'){ Link Here
829
        } else {
829
        } else {
830
            my ($sql,$header_types) = get_prepped_report( $sql, \@param_names, \@sql_params);
830
            my ($sql,$header_types) = get_prepped_report( $sql, \@param_names, \@sql_params);
831
            $template->param(header_types => $header_types);
831
            $template->param(header_types => $header_types);
832
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
832
            my ( $sth, $errors ) = execute_query(
833
                {
834
                    sql        => $sql,
835
                    offset     => $offset,
836
                    limit      => $limit,
837
                    report_id  => $report_id,
838
                }
839
            );
833
            my $total = nb_rows($sql) || 0;
840
            my $total = nb_rows($sql) || 0;
834
            unless ($sth) {
841
            unless ($sth) {
835
                die "execute_query failed to return sth for report $report_id: $sql";
842
                die "execute_query failed to return sth for report $report_id: $sql";
Lines 841-847 elsif ($phase eq 'Run this report'){ Link Here
841
                    push @rows, { cells => \@cells };
848
                    push @rows, { cells => \@cells };
842
                }
849
                }
843
                if( $want_full_chart ){
850
                if( $want_full_chart ){
844
                    my ($sth2, $errors2) = execute_query($sql);
851
                    my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } );
845
                    while (my $row = $sth2->fetchrow_arrayref()) {
852
                    while (my $row = $sth2->fetchrow_arrayref()) {
846
                        my @cells = map { +{ cell => $_ } } @$row;
853
                        my @cells = map { +{ cell => $_ } } @$row;
847
                        push @allrows, { cells => \@cells };
854
                        push @allrows, { cells => \@cells };
Lines 893-899 elsif ($phase eq 'Export'){ Link Here
893
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
900
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
894
901
895
    ($sql, undef) = get_prepped_report( $sql, \@param_names, \@sql_params );
902
    ($sql, undef) = get_prepped_report( $sql, \@param_names, \@sql_params );
896
	my ($sth, $q_errors) = execute_query($sql);
903
	my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } );
897
    unless ($q_errors and @$q_errors) {
904
    unless ($q_errors and @$q_errors) {
898
        my ( $type, $content );
905
        my ( $type, $content );
899
        if ($format eq 'tab') {
906
        if ($format eq 'tab') {
(-)a/svc/report (-2 / +9 lines)
Lines 59-65 if ($cache_active) { Link Here
59
}
59
}
60
60
61
unless ($json_text) {
61
unless ($json_text) {
62
    my $offset = 0;
63
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
62
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
64
    my $sql = $report_rec->savedsql;
63
    my $sql = $report_rec->savedsql;
65
64
Lines 67-73 unless ($json_text) { Link Here
67
    $sql =~ s/(<<.*?>>)/\?/g;
66
    $sql =~ s/(<<.*?>>)/\?/g;
68
    $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g;
67
    $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g;
69
68
70
    my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, \@sql_params, $report_id );
69
    my ( $sth, $errors ) = execute_query(
70
        {
71
            sql        => $sql,
72
            offset     => 0,
73
            limit      => $limit,
74
            sql_params => \@sql_params,
75
            report_id  => $report_id,
76
        }
77
    );
71
    if ($sth) {
78
    if ($sth) {
72
        my $lines;
79
        my $lines;
73
        if ($report_annotation) {
80
        if ($report_annotation) {
(-)a/t/db_dependent/Reports/Guided.t (-13 / +27 lines)
Lines 234-249 subtest 'get_saved_reports' => sub { Link Here
234
    is( scalar @{ get_saved_reports() },
234
    is( scalar @{ get_saved_reports() },
235
        $count, "Report2 and report3 have been deleted" );
235
        $count, "Report2 and report3 have been deleted" );
236
236
237
    my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
237
    my $sth = execute_query(
238
        {
239
            sql    => 'SELECT COUNT(*) FROM systempreferences',
240
            offset => 0,
241
            limit  => 10,
242
        }
243
    );
238
    my $results = $sth->fetchall_arrayref;
244
    my $results = $sth->fetchall_arrayref;
239
    is(scalar @$results, 1, 'running a query returned a result');
245
    is(scalar @$results, 1, 'running a query returned a result');
240
246
241
    my $version = C4::Context->preference('Version');
247
    my $version = C4::Context->preference('Version');
242
    $sth = execute_query(
248
    $sth = execute_query(
243
        'SELECT value FROM systempreferences WHERE variable = ?',
249
        {
244
        0,
250
            sql        => 'SELECT value FROM systempreferences WHERE variable = ?',
245
        10,
251
            offset     => 0,
246
        [ 'Version' ],
252
            limit      => 10,
253
            sql_params => ['Version'],
254
        }
247
    );
255
    );
248
    $results = $sth->fetchall_arrayref;
256
    $results = $sth->fetchall_arrayref;
249
    is_deeply(
257
    is_deeply(
Lines 254-264 subtest 'get_saved_reports' => sub { Link Here
254
262
255
    # for next test, we want to let execute_query capture any SQL errors
263
    # for next test, we want to let execute_query capture any SQL errors
256
    my $errors;
264
    my $errors;
257
    warning_like {local $dbh->{RaiseError} = 0; ($sth, $errors) = execute_query(
265
    warning_like {
258
            'SELECT surname FRM borrowers',  # error in the query is intentional
266
        local $dbh->{RaiseError} = 0;
259
            0, 10 ) }
267
        ( $sth, $errors ) = execute_query(
260
            qr/DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
268
            {
261
            "Wrong SQL syntax raises warning";
269
                sql    => 'SELECT surname FRM borrowers',    # error in the query is intentional
270
                offset => 0,
271
                limit  => 10,
272
            }
273
        )
274
    }
275
    qr/DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
276
      "Wrong SQL syntax raises warning";
262
    ok(
277
    ok(
263
        defined($errors) && exists($errors->{queryerr}),
278
        defined($errors) && exists($errors->{queryerr}),
264
        'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
279
        'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
Lines 283-296 subtest 'Ensure last_run is populated' => sub { Link Here
283
298
284
    is( $report->last_run, undef, 'Newly created report has null last_run ' );
299
    is( $report->last_run, undef, 'Newly created report has null last_run ' );
285
300
286
    execute_query( $report->savedsql, undef, undef, undef, $report->id );
301
    execute_query( { sql => $report->savedsql, report_id => $report->id } );
287
    $report->discard_changes();
302
    $report->discard_changes();
288
303
289
    isnt( $report->last_run, undef, 'First run of report populates last_run' );
304
    isnt( $report->last_run, undef, 'First run of report populates last_run' );
290
305
291
    my $previous_last_run = $report->last_run;
306
    my $previous_last_run = $report->last_run;
292
    sleep(1); # last_run is stored to the second, so we need to ensure at least one second has passed between runs
307
    sleep(1); # last_run is stored to the second, so we need to ensure at least one second has passed between runs
293
    execute_query( $report->savedsql, undef, undef, undef, $report->id );
308
    execute_query( { sql => $report->savedsql, report_id => $report->id } );
294
    $report->discard_changes();
309
    $report->discard_changes();
295
310
296
    isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' );
311
    isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' );
297
- 

Return to bug 26669