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' ) { |