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

(-)a/C4/Reports/Guided.pm (-12 / +234 lines)
Lines 45-52 BEGIN { Link Here
45
      nb_rows update_sql
45
      nb_rows update_sql
46
      GetReservedAuthorisedValues
46
      GetReservedAuthorisedValues
47
      GetParametersFromSQL
47
      GetParametersFromSQL
48
      GetSQLAuthValueResult
49
      GetPreppedSQLReport
48
      IsAuthorisedValueValid
50
      IsAuthorisedValueValid
49
      ValidateSQLParameters
51
      ValidateSQLParameters
52
      ValidateSQLQuery
50
      nb_rows update_sql
53
      nb_rows update_sql
51
    );
54
    );
52
}
55
}
Lines 508-518 sub execute_query { Link Here
508
    $offset = 0    unless $offset;
511
    $offset = 0    unless $offset;
509
    $limit  = 999999 unless $limit;
512
    $limit  = 999999 unless $limit;
510
    $debug and print STDERR "execute_query($sql, $offset, $limit)\n";
513
    $debug and print STDERR "execute_query($sql, $offset, $limit)\n";
511
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
514
512
        return (undef, {  sqlerr => $1} );
515
    my $errors = ValidateSQLQuery($sql);
513
    } elsif ($sql !~ /^\s*SELECT\b\s*/i) {
516
    return (undef, @{$errors}[0]) if (scalar(@$errors));
514
        return (undef, { queryerr => 'Missing SELECT'} );
515
    }
516
517
517
    my ($useroffset, $userlimit);
518
    my ($useroffset, $userlimit);
518
519
Lines 838-843 sub _get_column_defs { Link Here
838
    return \%columns;
839
    return \%columns;
839
}
840
}
840
841
842
sub _authval_resultvalue_authval {
843
    my $parm = shift;
844
    my %tmp = ( 'input' => $parm->{'authval'} );
845
    return \%tmp;
846
}
847
848
sub _authval_resultvalue_branches {
849
    my $parm = shift;
850
851
    my @authorised_values;
852
    my %authorised_lib;
853
854
    # builds list, depending on authorised value...
855
    my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } );
856
    while ( my $library = $libraries->next ) {
857
        push @authorised_values, $library->branchcode;
858
        $authorised_lib{$library->branchcode} = $library->branchname;
859
    }
860
861
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
862
    return \%tmp;
863
}
864
865
sub _authval_resultvalue_itemtypes {
866
    my $parm = shift;
867
868
    my @authorised_values;
869
    my %authorised_lib;
870
    my $dbh=C4::Context->dbh;
871
    # builds list, depending on authorised value...
872
    my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
873
    $sth->execute;
874
    while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
875
        push @authorised_values, $itemtype;
876
        $authorised_lib{$itemtype} = $description;
877
    }
878
879
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
880
    return \%tmp;
881
}
882
883
sub _authval_resultvalue_biblio_framework {
884
    my $parm = shift;
885
886
    my @authorised_values;
887
    my %authorised_lib;
888
889
    # builds list, depending on authorised value...
890
    my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] });
891
    my $default_source = '';
892
    push @authorised_values,$default_source;
893
    $authorised_lib{$default_source} = 'Default';
894
    foreach my $framework (@frameworks) {
895
        push @authorised_values, $framework->frameworkcode;
896
        $authorised_lib{$framework->frameworkcode} = $framework->frameworktext;
897
    }
898
899
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
900
    return \%tmp;
901
}
902
903
sub _authval_resultvalue_biblio_cn_source {
904
    my $parm = shift;
905
906
    my @authorised_values;
907
    my %authorised_lib;
908
909
    # builds list, depending on authorised value...
910
    my $class_sources = GetClassSources();
911
    my $default_source = C4::Context->preference("DefaultClassificationSource");
912
    foreach my $class_source (sort keys %$class_sources) {
913
        next unless $class_sources->{$class_source}->{'used'} or
914
            ($class_source eq $default_source);
915
        push @authorised_values, $class_source;
916
        $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
917
    }
918
919
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
920
    return \%tmp;
921
}
922
923
sub _authval_resultvalue_biblio_categorycode {
924
    my $parm = shift;
925
926
    my @authorised_values;
927
    my %authorised_lib;
928
929
    # builds list, depending on authorised value...
930
    my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']});
931
    %authorised_lib = map { $_->categorycode => $_->description } @patron_categories;
932
    push @authorised_values, $_->categorycode for @patron_categories;
933
934
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
935
    return \%tmp;
936
}
937
938
sub _authval_prepvalue_date {
939
    my ($parm, $quoted) = @_;
940
    $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted;
941
    return $quoted;
942
}
943
944
# reserved_savedsql_auth_values contains the special replacement authority values
945
# used in saved SQL queries.
946
#
947
# The 'auth' function takes a hashref - one element returned from GetParametersFromSQL - and
948
# must return a hashref with one of:
949
# - 'values' and 'labels'
950
#      (for a dropdown menu)
951
# - 'input'
952
#      (for an input field)
953
# - 'auth_val_error' and 'data'
954
#      (in case of error)
955
#
956
# The 'prep' function is used to reformat the input we got from the user, so it can
957
# be used in SQL query. It takes in a hashref (one element from GetParametersFromSQL) and
958
# the input string, and must return the correctly formatted string which will be used in
959
# the SQL query.
960
#
961
my %reserved_savedsql_auth_values = (
962
    'date' => {'auth' => \&_authval_resultvalue_authval, 'prep' => \&_authval_prepvalue_date},
963
    'text' => {'auth' => \&_authval_resultvalue_authval},
964
    'branches' => {'auth' => \&_authval_resultvalue_branches},
965
    'itemtypes' => {'auth' => \&_authval_resultvalue_itemtypes},
966
    'cn_source' => {'auth' => \&_authval_resultvalue_biblio_cn_source},
967
    'categorycode' => {'auth' => \&_authval_resultvalue_biblio_categorycode},
968
    'biblio_framework' => {'auth' => \&_authval_resultvalue_biblio_framework},
969
    );
970
841
=head2 GetReservedAuthorisedValues
971
=head2 GetReservedAuthorisedValues
842
972
843
    my %reserved_authorised_values = GetReservedAuthorisedValues();
973
    my %reserved_authorised_values = GetReservedAuthorisedValues();
Lines 848-863 Returns a hash containig all reserved words Link Here
848
978
849
sub GetReservedAuthorisedValues {
979
sub GetReservedAuthorisedValues {
850
    my %reserved_authorised_values =
980
    my %reserved_authorised_values =
851
            map { $_ => 1 } ( 'date',
981
            map { $_ => 1 } ( keys(%reserved_savedsql_auth_values) );
852
                              'branches',
853
                              'itemtypes',
854
                              'cn_source',
855
                              'categorycode',
856
                              'biblio_framework' );
857
982
858
   return \%reserved_authorised_values;
983
   return \%reserved_authorised_values;
859
}
984
}
860
985
986
=head2 GetSQLAuthValueResult
987
988
  my $params = GetParametersFromSQL($sql);
989
  my $authdata = GetSQLAuthValueResult(@{$params}[0]);
990
991
=cut
992
993
sub GetSQLAuthValueResult {
994
    my $authparam = shift;
995
    my $authorised_value = $authparam->{'authval'};
996
    my %ret;
997
998
    if (defined($reserved_savedsql_auth_values{$authorised_value})) {
999
        return &{$reserved_savedsql_auth_values{$authorised_value}{'auth'}}($authparam);
1000
    } elsif ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) {
1001
        my @authorised_values;
1002
        my %authorised_lib;
1003
        my $query = '
1004
                    SELECT authorised_value,lib
1005
                    FROM authorised_values
1006
                    WHERE category=?
1007
                    ORDER BY lib
1008
                    ';
1009
        my $dbh=C4::Context->dbh;
1010
        my $authorised_values_sth = $dbh->prepare($query);
1011
        $authorised_values_sth->execute( $authorised_value);
1012
1013
        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
1014
            push @authorised_values, $value;
1015
            $authorised_lib{$value} = $lib;
1016
        }
1017
        %ret = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
1018
    } else {
1019
        %ret = ( 'auth_val_error' => 1,
1020
                 'data' => {
1021
                     'entry' => $authparam->{'name'},
1022
                     'auth_val' => $authorised_value
1023
                 } );
1024
    }
1025
    return \%ret;
1026
}
861
1027
862
=head2 IsAuthorisedValueValid
1028
=head2 IsAuthorisedValueValid
863
1029
Lines 895-908 sub GetParametersFromSQL { Link Here
895
    my @split = split(/<<|>>/,$sql);
1061
    my @split = split(/<<|>>/,$sql);
896
    my @sql_parameters = ();
1062
    my @sql_parameters = ();
897
1063
1064
    # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
898
    for ( my $i = 0; $i < ($#split/2) ; $i++ ) {
1065
    for ( my $i = 0; $i < ($#split/2) ; $i++ ) {
899
        my ($name,$authval) = split(/\|/,$split[$i*2+1]);
1066
        my ($name,$authval) = split(/\|/,$split[$i*2+1]);
900
        push @sql_parameters, { 'name' => $name, 'authval' => $authval };
1067
        $authval ||= 'text';
1068
        push @sql_parameters, { 'name' => $name, 'authval' => $authval, 'rawparam' => $split[$i*2+1] };
901
    }
1069
    }
902
1070
903
    return \@sql_parameters;
1071
    return \@sql_parameters;
904
}
1072
}
905
1073
1074
=head2 GetPreppedSQLReport
1075
1076
    my $sql = GetPreppedSQLReport( $sql, \@param_names, \@sql_params );
1077
1078
Returns an executable query
1079
1080
=cut
1081
1082
sub GetPreppedSQLReport {
1083
    my ($sql, $param_names, $sql_params ) = @_;
1084
    my %lookup;
1085
    @lookup{@$param_names} = @$sql_params;
1086
    my $split = GetParametersFromSQL( $sql );
1087
    my @tmpl_parameters;
1088
    my $i = 0;
1089
    foreach my $parm (@$split) {
1090
        my $raw = $parm->{'rawparam'};
1091
        my $quoted = @$param_names ? $lookup{ $raw } : @$sql_params[$i];
1092
        # if there are special regexp chars, we must \ them
1093
        $raw =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
1094
        if (defined($reserved_savedsql_auth_values{$parm->{'authval'}}{'prep'})) {
1095
            $quoted = &{$reserved_savedsql_auth_values{$parm->{'authval'}}{'prep'}}($parm, $quoted);
1096
        }
1097
        $quoted = C4::Context->dbh->quote($quoted);
1098
        $sql =~ s/<<$raw>>/$quoted/;
1099
        $i++;
1100
    }
1101
    return $sql;
1102
}
1103
906
=head2 ValidateSQLParameters
1104
=head2 ValidateSQLParameters
907
1105
908
    my @problematic_parameters = ValidateSQLParameters($sql)
1106
    my @problematic_parameters = ValidateSQLParameters($sql)
Lines 928-933 sub ValidateSQLParameters { Link Here
928
    return \@problematic_parameters;
1126
    return \@problematic_parameters;
929
}
1127
}
930
1128
1129
=head2 ValidateSQLQuery
1130
1131
    my $errors = ValidateSQLQuery( $sql );
1132
1133
Validates SQL query string so it only contains a select,
1134
not any of the harmful queries.
1135
1136
=cut
1137
1138
sub ValidateSQLQuery {
1139
    my $sql = shift;
1140
1141
    my @errors;
1142
1143
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
1144
        push @errors, {sqlerr => $1};
1145
    }
1146
    elsif ($sql !~ /^(SELECT)/i) {
1147
        push @errors, {queryerr => "No SELECT"};
1148
    }
1149
1150
    return \@errors;
1151
}
1152
931
sub _get_display_value {
1153
sub _get_display_value {
932
    my ( $original_value, $column ) = @_;
1154
    my ( $original_value, $column ) = @_;
933
    if ( $column eq 'periodicity' ) {
1155
    if ( $column eq 'periodicity' ) {
(-)a/reports/guided_reports.pl (-121 / +26 lines)
Lines 232-243 elsif ( $phase eq 'Update SQL'){ Link Here
232
232
233
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
233
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
234
234
235
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
235
    push(@errors, @{ValidateSQLQuery($sql)});
236
        push @errors, {sqlerr => $1};
237
    }
238
    elsif ($sql !~ /^(SELECT)/i) {
239
        push @errors, {queryerr => "No SELECT"};
240
    }
241
236
242
    if (@errors) {
237
    if (@errors) {
243
        $template->param(
238
        $template->param(
Lines 594-605 elsif ( $phase eq 'Save Report' ) { Link Here
594
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
589
    create_non_existing_group_and_subgroup($input, $group, $subgroup);
595
590
596
    ## FIXME this is AFTER entering a name to save the report under
591
    ## FIXME this is AFTER entering a name to save the report under
597
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
592
    push(@errors, @{ValidateSQLQuery($sql)});
598
        push @errors, {sqlerr => $1};
599
    }
600
    elsif ($sql !~ /^(SELECT)/i) {
601
        push @errors, {queryerr => "No SELECT"};
602
    }
603
593
604
    if (@errors) {
594
    if (@errors) {
605
        $template->param(
595
        $template->param(
Lines 694-801 elsif ($phase eq 'Run this report'){ Link Here
694
684
695
        my @rows = ();
685
        my @rows = ();
696
        my @allrows = ();
686
        my @allrows = ();
687
        my $split = GetParametersFromSQL( $sql );
697
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
688
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
698
        if ($sql =~ /<</ && !@sql_params) {
689
        if (scalar(@$split) && !@sql_params) {
699
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
700
            my @split = split /<<|>>/,$sql;
701
            my @tmpl_parameters;
690
            my @tmpl_parameters;
702
            my @authval_errors;
691
            my @authval_errors;
703
            my %uniq_params;
692
            my %uniq_params;
704
            for(my $i=0;$i<($#split/2);$i++) {
693
            my $i = 0;
705
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
694
            foreach my $parm (@$split) {
695
                my $text = $parm->{'name'};
696
                my $authorised_value = $parm->{'authval'};
706
                my $sep = $authorised_value ? "|" : "";
697
                my $sep = $authorised_value ? "|" : "";
698
                $i++;
707
                if( defined $uniq_params{$text.$sep.$authorised_value} ){
699
                if( defined $uniq_params{$text.$sep.$authorised_value} ){
708
                    next;
700
                    next;
709
                } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; }
701
                } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; }
710
                my $input;
702
                my $input;
711
                my $labelid;
703
                my $labelid;
712
                if ( not defined $authorised_value ) {
704
713
                    # no authorised value input, provide a text box
705
                my $ret = GetSQLAuthValueResult($parm);
714
                    $input = "text";
706
715
                } elsif ( $authorised_value eq "date" ) {
707
                if (defined($ret->{'values'})) {
716
                    # require a date, provide a date picker
717
                    $input = 'date';
718
                } else {
719
                    # defined $authorised_value, and not 'date'
720
                    my $dbh=C4::Context->dbh;
721
                    my @authorised_values;
722
                    my %authorised_lib;
723
                    # builds list, depending on authorised value...
724
                    if ( $authorised_value eq "branches" ) {
725
                        my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } );
726
                        while ( my $library = $libraries->next ) {
727
                            push @authorised_values, $library->branchcode;
728
                            $authorised_lib{$library->branchcode} = $library->branchname;
729
                        }
730
                    }
731
                    elsif ( $authorised_value eq "itemtypes" ) {
732
                        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
733
                        $sth->execute;
734
                        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
735
                            push @authorised_values, $itemtype;
736
                            $authorised_lib{$itemtype} = $description;
737
                        }
738
                    }
739
                    elsif ( $authorised_value eq "biblio_framework" ) {
740
                        my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] });
741
                        my $default_source = '';
742
                        push @authorised_values,$default_source;
743
                        $authorised_lib{$default_source} = 'Default';
744
                        foreach my $framework (@frameworks) {
745
                            push @authorised_values, $framework->frameworkcode;
746
                            $authorised_lib{$framework->frameworkcode} = $framework->frameworktext;
747
                        }
748
                    }
749
                    elsif ( $authorised_value eq "cn_source" ) {
750
                        my $class_sources = GetClassSources();
751
                        my $default_source = C4::Context->preference("DefaultClassificationSource");
752
                        foreach my $class_source (sort keys %$class_sources) {
753
                            next unless $class_sources->{$class_source}->{'used'} or
754
                                        ($class_source eq $default_source);
755
                            push @authorised_values, $class_source;
756
                            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
757
                        }
758
                    }
759
                    elsif ( $authorised_value eq "categorycode" ) {
760
                        my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']});
761
                        %authorised_lib = map { $_->categorycode => $_->description } @patron_categories;
762
                        push @authorised_values, $_->categorycode for @patron_categories;
763
                    }
764
                    else {
765
                        if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) {
766
                            my $query = '
767
                            SELECT authorised_value,lib
768
                            FROM authorised_values
769
                            WHERE category=?
770
                            ORDER BY lib
771
                            ';
772
                            my $authorised_values_sth = $dbh->prepare($query);
773
                            $authorised_values_sth->execute( $authorised_value);
774
775
                            while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
776
                                push @authorised_values, $value;
777
                                $authorised_lib{$value} = $lib;
778
                                # For item location, we show the code and the libelle
779
                                $authorised_lib{$value} = $lib;
780
                            }
781
                        } else {
782
                            # not exists $authorised_value_categories{$authorised_value})
783
                            push @authval_errors, {'entry' => $text,
784
                                                   'auth_val' => $authorised_value };
785
                            # tell the template there's an error
786
                            $template->param( auth_val_error => 1 );
787
                            # skip scrolling list creation and params push
788
                            next;
789
                        }
790
                    }
791
                    $labelid = $text;
708
                    $labelid = $text;
792
                    $labelid =~ s/\W//g;
709
                    $labelid =~ s/\W//g;
793
                    $input = {
710
                    $input = {
794
                        name    => "sql_params",
711
                        name    => "sql_params",
795
                        id      => "sql_params_".$labelid,
712
                        id      => "sql_params_".$labelid,
796
                        values  => \@authorised_values,
713
                        values  => $ret->{'values'},
797
                        labels  => \%authorised_lib,
714
                        labels  => $ret->{'labels'},
798
                    };
715
                    };
716
                } elsif (defined($ret->{'auth_val_error'})) {
717
                    push(@authval_errors, $ret->{'data'});
718
                    $template->param( auth_val_error => 1 );
719
                    next;
720
                } else {
721
                    $input = $ret->{'input'};
799
                }
722
                }
800
723
801
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value };
724
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value };
Lines 808-819 elsif ($phase eq 'Run this report'){ Link Here
808
                            'reports'      => $report_id,
731
                            'reports'      => $report_id,
809
                            );
732
                            );
810
        } else {
733
        } else {
811
            my $sql = get_prepped_report( $sql, \@param_names, \@sql_params);
734
            my $sql = GetPreppedSQLReport( $sql, \@param_names, \@sql_params);
812
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
735
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
813
            my ($sth2, $errors2) = execute_query($sql);
736
            my ($sth2, $errors2) = execute_query($sql);
814
            my $total = nb_rows($sql) || 0;
737
            my $total = nb_rows($sql) || 0;
815
            unless ($sth) {
738
            unless ($sth) {
816
                die "execute_query failed to return sth for report $report_id: $sql";
739
                if (!defined($errors)) {
740
                    die "execute_query failed to return sth for report $report_id: $sql";
741
                }
817
            } else {
742
            } else {
818
                my $headers = header_cell_loop($sth);
743
                my $headers = header_cell_loop($sth);
819
                $template->param(header_row => $headers);
744
                $template->param(header_row => $headers);
Lines 866-872 elsif ($phase eq 'Export'){ Link Here
866
    my $reportname     = $input->param('reportname');
791
    my $reportname     = $input->param('reportname');
867
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
792
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
868
793
869
    $sql = get_prepped_report( $sql, \@param_names, \@sql_params );
794
    $sql = GetPreppedSQLReport( $sql, \@param_names, \@sql_params );
870
	my ($sth, $q_errors) = execute_query($sql);
795
	my ($sth, $q_errors) = execute_query($sql);
871
    unless ($q_errors and @$q_errors) {
796
    unless ($q_errors and @$q_errors) {
872
        my ( $type, $content );
797
        my ( $type, $content );
Lines 1059-1081 sub create_non_existing_group_and_subgroup { Link Here
1059
        }
984
        }
1060
    }
985
    }
1061
}
986
}
1062
1063
# pass $sth and sql_params, get back an executable query
1064
sub get_prepped_report {
1065
    my ($sql, $param_names, $sql_params ) = @_;
1066
    my %lookup;
1067
    @lookup{@$param_names} = @$sql_params;
1068
    my @split = split /<<|>>/,$sql;
1069
    my @tmpl_parameters;
1070
    for(my $i=0;$i<$#split/2;$i++) {
1071
        my $quoted = @$param_names ? $lookup{ $split[$i*2+1] } : @$sql_params[$i];
1072
        # if there are special regexp chars, we must \ them
1073
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
1074
        if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
1075
            $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted;
1076
        }
1077
        $quoted = C4::Context->dbh->quote($quoted);
1078
        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
1079
    }
1080
    return $sql;
1081
}
(-)a/t/db_dependent/Reports/Guided.t (-7 / +127 lines)
Lines 18-24 Link Here
18
18
19
use Modern::Perl;
19
use Modern::Perl;
20
20
21
use Test::More tests => 9;
21
use Test::More tests => 12;
22
use Test::Warn;
22
use Test::Warn;
23
23
24
use t::lib::TestBuilder;
24
use t::lib::TestBuilder;
Lines 110-115 subtest 'GetReservedAuthorisedValues' => sub { Link Here
110
    # to GetReservedAuthorisedValues
110
    # to GetReservedAuthorisedValues
111
    my %test_authval = (
111
    my %test_authval = (
112
        'date' => 1,
112
        'date' => 1,
113
        'text' => 1,
113
        'branches' => 1,
114
        'branches' => 1,
114
        'itemtypes' => 1,
115
        'itemtypes' => 1,
115
        'cn_source' => 1,
116
        'cn_source' => 1,
Lines 123-129 subtest 'GetReservedAuthorisedValues' => sub { Link Here
123
};
124
};
124
125
125
subtest 'IsAuthorisedValueValid' => sub {
126
subtest 'IsAuthorisedValueValid' => sub {
126
    plan tests => 8;
127
    plan tests => 9;
127
    ok( IsAuthorisedValueValid('LOC'),
128
    ok( IsAuthorisedValueValid('LOC'),
128
        'User defined authorised value category is valid');
129
        'User defined authorised value category is valid');
129
130
Lines 137-142 subtest 'IsAuthorisedValueValid' => sub { Link Here
137
    }
138
    }
138
};
139
};
139
140
141
subtest 'ValidateSQLQuery' => sub {
142
    plan tests => 2 + 6 * 2;
143
144
    is_deeply( ValidateSQLQuery("SELECT * FROM bar"), [], 'Query starts with select');
145
146
    my @result = ( { queryerr => "No SELECT"} );
147
    is_deeply( ValidateSQLQuery("ELECT * FROM bar"), \@result, 'Query does not start with select');
148
149
    my @banned_words = ("UPDATE", "DELETE", "DROP", "INSERT", "SHOW", "CREATE");
150
    foreach my $banned (@banned_words) {
151
        @result = ( { sqlerr => $banned } );
152
        my $sql = $banned . " foo WHERE bar";
153
        is_deeply( ValidateSQLQuery($sql), \@result, 'Query starts with ' . $banned);
154
155
        @result = ( { sqlerr => $banned } );
156
        $sql = "baz;" . $banned . " foo WHERE bar";
157
        is_deeply( ValidateSQLQuery($sql), \@result, 'Query has colon and ' . $banned);
158
    }
159
};
160
140
subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub  {
161
subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub  {
141
    plan tests => 3;
162
    plan tests => 3;
142
    my $test_query_1 = "
163
    my $test_query_1 = "
Lines 148-163 subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub { Link Here
148
    ";
169
    ";
149
170
150
    my @test_parameters_with_custom_list = (
171
    my @test_parameters_with_custom_list = (
151
        { 'name' => 'Year', 'authval' => 'custom_list' },
172
        { 'name' => 'Year', 'authval' => 'custom_list', 'rawparam' => 'Year|custom_list' },
152
        { 'name' => 'Branch', 'authval' => 'branches' },
173
        { 'name' => 'Branch', 'authval' => 'branches', 'rawparam' => 'Branch|branches' },
153
        { 'name' => 'Borrower', 'authval' => undef }
174
        { 'name' => 'Borrower', 'authval' => 'text', 'rawparam' => 'Borrower' }
154
    );
175
    );
155
176
156
    is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
177
    is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
157
        'SQL params are correctly parsed');
178
        'SQL params are correctly parsed');
158
179
159
    my @problematic_parameters = ();
180
    my @problematic_parameters = ();
160
    push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
181
    push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list', 'rawparam' => 'Year|custom_list' };
161
    is_deeply( ValidateSQLParameters( $test_query_1 ),
182
    is_deeply( ValidateSQLParameters( $test_query_1 ),
162
               \@problematic_parameters,
183
               \@problematic_parameters,
163
               '\'custom_list\' not a valid category' );
184
               '\'custom_list\' not a valid category' );
Lines 176-181 subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub { Link Here
176
    );
197
    );
177
};
198
};
178
199
200
subtest 'GetSQLAuthValueResult' => sub {
201
    plan tests => 4;
202
203
    my $test_query_1 = "<<Name>> <<Surname|text>> foo <<Year|date>> <<Branch|branches>> <<Temp|LOC>> <<bar|baz>>";
204
    my $params = GetParametersFromSQL( $test_query_1 );
205
206
207
    my %test_1_return = ( 'input' => 'text' );
208
209
    is_deeply( GetSQLAuthValueResult(@{$params}[0]),
210
               \%test_1_return,
211
               'Returned implicit text parameter correctly');
212
213
214
    my %test_2_return = ( 'input' => 'text' );
215
216
    is_deeply( GetSQLAuthValueResult(@{$params}[1]),
217
               \%test_1_return,
218
               'Returned explicit text parameter correctly');
219
220
221
    my %test_3_return = ( 'input' => 'date' );
222
223
    is_deeply( GetSQLAuthValueResult(@{$params}[2]),
224
               \%test_3_return,
225
               'Returned date parameter correctly');
226
227
228
    # same as _authval_resultvalue_branches() in C4/Reports/Guided.pm
229
    my @authorised_values;
230
    my %authorised_lib;
231
    my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } );
232
    while ( my $library = $libraries->next ) {
233
        push @authorised_values, $library->branchcode;
234
        $authorised_lib{$library->branchcode} = $library->branchname;
235
    }
236
    my %test_4_return = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
237
238
    is_deeply( GetSQLAuthValueResult(@{$params}[3]),
239
               \%test_4_return,
240
               'Returned branches parameter correctly');
241
};
242
243
subtest 'GetPreppedSQLReport' => sub {
244
    plan tests => 5;
245
246
    # Test without parameters
247
    my @paramnames = ();
248
    my @sql_params = ();
249
250
    my $test_query_1 = "SELECT foo FROM bar WHERE qux";
251
252
    is( GetPreppedSQLReport( $test_query_1, \@paramnames, \@sql_params ),  $test_query_1,
253
        'Returns exact same SQL when no parameters to substitute');
254
255
256
    # Test completely unknown parameters
257
    my $test_query_2 = "SELECT foo FROM bar WHERE <<qux>>";
258
259
    is( GetPreppedSQLReport( $test_query_2, \@paramnames, \@sql_params ),
260
        "SELECT foo FROM bar WHERE NULL",
261
        'Returns completely unknown parameters as NULLs');
262
263
264
    # Test unknown parameters
265
    @paramnames = ( "qux" );
266
    my $test_query_3 = "SELECT foo FROM bar WHERE <<qux>>";
267
268
    is( GetPreppedSQLReport( $test_query_3, \@paramnames, \@sql_params ),
269
        "SELECT foo FROM bar WHERE NULL",
270
        'Returns parameters with unknown sql_param as NULLs');
271
272
273
274
    # Test parameter substitution
275
    @paramnames = ( "qux" );
276
    @sql_params = ( "XXXX" );
277
278
    my $test_query_4 = "foo qux <<qux>> bar";
279
    my $test_query_4_temp = "foo qux ? bar";
280
281
    my $dbh = C4::Context->dbh;
282
    my $test_query_4_ok = "foo qux " . $dbh->quote($sql_params[0]) . " bar";
283
284
    is( GetPreppedSQLReport( $test_query_4, \@paramnames, \@sql_params ),
285
        $test_query_4_ok,
286
        'Returns parameter substituted');
287
288
289
    # Test character escaping (and NULL params, too)
290
    @paramnames = ( "qu*x" );
291
    @sql_params = ( "XXX" );
292
    my $test_query_5 = "foo <<qx|text>> <<quux>> <<qu*x>> bar";
293
    my $test_query_5_ok = "foo NULL NULL " . $dbh->quote($sql_params[0]) . " bar";
294
295
    is( GetPreppedSQLReport( $test_query_5, \@paramnames, \@sql_params ),
296
        $test_query_5_ok,
297
        'Handles escaping parameters');
298
};
299
179
subtest 'get_saved_reports' => sub {
300
subtest 'get_saved_reports' => sub {
180
    plan tests => 16;
301
    plan tests => 16;
181
    my $dbh = C4::Context->dbh;
302
    my $dbh = C4::Context->dbh;
182
- 

Return to bug 21215