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