From 3b8b70c4e899642bd388ea81ed7781181118815c Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Thu, 4 Mar 2021 21:08:42 +0000 Subject: [PATCH] changed to sql placeholders and made boostrap optional --- Open-ILS/src/support-scripts/patron_loader.pl.in | 211 ++++++++++++++--------- 1 file changed, 134 insertions(+), 77 deletions(-) diff --git a/Open-ILS/src/support-scripts/patron_loader.pl.in b/Open-ILS/src/support-scripts/patron_loader.pl.in index 1717544a6a..551a0c8fb1 100755 --- a/Open-ILS/src/support-scripts/patron_loader.pl.in +++ b/Open-ILS/src/support-scripts/patron_loader.pl.in @@ -19,6 +19,7 @@ use strict; use warnings; use DBI; +use DBI qw(:sql_types); use Getopt::Long; use Text::CSV; use Data::Dumper; @@ -48,6 +49,8 @@ my $print_au_id = 0; my $session = time(); my $h; my $help; +my $nobootstrap; +my @parameters; my $ret = GetOptions( 'db:s' => \$db, @@ -70,14 +73,20 @@ my $ret = GetOptions( 'home_ou:s' => \$home_ou, 'org_unit:s' => \$org_unit, 'h' => \$h, - 'help' => \$help + 'help' => \$help, + 'nobootstrap' => \$nobootstrap ); if ($h or $help) { print_help(); } -my $bootstrap = '@sysconfdir@/opensrf_core.xml'; -OpenSRF::System->bootstrap_client(config_file => $bootstrap); -my $sc = OpenSRF::Utils::SettingsClient->new; +my $bootstrap; +my $sc; + +if (!defined $nobootstrap) { + $bootstrap = '@sysconfdir@/opensrf_core.xml'; + OpenSRF::System->bootstrap_client(config_file => $bootstrap); + $sc = OpenSRF::Utils::SettingsClient->new; +} my $dbh = connect_db( $db || $sc->config_value( reporter => setup => database => 'db' ), @@ -89,14 +98,12 @@ my @results; my $query; open(my $fh, '<', $file) or abort("Could not open $file!"); - if ($matchpoint ne 'usrname' and $matchpoint ne 'cardnumber') { abort('invalid matchpoint defined'); } - if (!defined $org_unit) { abort('no org_unit defined'); } -my $prepped_org_unit = sql_wrap_text($org_unit); if ($debug) { print "beginning load for $org_unit\n"; } -@results = sql_return($dbh,"SELECT id FROM actor.org_unit WHERE shortname = $prepped_org_unit;"); +@parameters = ("$org_unit"); +@results = sql_return($dbh,"SELECT id FROM actor.org_unit WHERE shortname = ?;",\@parameters,$debug); if ($results[0]) { $org_id = $results[0]; } else { abort('invalid command line org unit'); } if ($debug) @@ -109,7 +116,8 @@ else } # initialize a bunch of stuff rather than go back to the db over and over -my @valid_orgs = sql_return($dbh,"SELECT id FROM actor.org_unit_descendants($org_id);"); +@parameters = ($org_id); +my @valid_orgs = sql_return($dbh,"SELECT id FROM actor.org_unit_descendants(?);",\@parameters,$debug); my %original_pgt = hash_from_sql($dbh,"SELECT name, id FROM permission.grp_tree;"); my %mapped_pgt = hash_from_sql($dbh,"SELECT import_value, native_value FROM config.patron_loader_value_map WHERE mapping_type = 'profile' AND org_unit = $org_id;"); my %original_libs = hash_from_sql($dbh,"SELECT shortname, id FROM actor.org_unit;"); @@ -179,7 +187,8 @@ while (my $line = <$fh>) { while (my ($col,$pos) = each %column_positions) { if ($column_positions{$col} != -1) { next; } my $sql_col = sql_wrap_text($col); - @results = sql_return($dbh,"SELECT import_header FROM config.patron_loader_header_map WHERE default_header = $sql_col;"); + @parameters = ("$sql_col"); + @results = sql_return($dbh,"SELECT import_header FROM config.patron_loader_header_map WHERE default_header = ?;",\@parameters,$debug); if ($results[0]) { $column_positions{$col} = first_index { lc($_) eq lc($results[0]) } @fields; } } #no need to keep fields not in here so ... byebye @@ -250,11 +259,13 @@ while (my $line = <$fh>) { ### after checking we don't need the sql strings separate from hash anymore so they become the hash values for convenience ### then we skip if value is 0 since barcode and username can't exist on separate users if ($matchpoint eq 'usrname') { - $query = "SELECT id FROM actor.usr WHERE usrname = $prepped_usrname;"; + @parameters = ("$prepped_usrname"); + $query = "SELECT id FROM actor.usr WHERE usrname = ?;"; } else { - $query = "SELECT usr FROM actor.card WHERE barcode = $prepped_cardnumber;"; + @parameters = ("$prepped_cardnumber"); + $query = "SELECT usr FROM actor.card WHERE barcode = ?;"; } - @results = sql_return($dbh,$query); + @results = sql_return($dbh,$query,\@parameters,$debug); my $au_id = $results[0]; my $valid_barcode = check_barcode($dbh,$au_id,$prepped_cardnumber); my $valid_usrname = check_usrname($dbh,$au_id,$prepped_usrname); @@ -266,13 +277,14 @@ while (my $line = <$fh>) { ### finally, we do stuff, if au_id then there is a matching user, update it, if not insert ### functions will create the update and insert strings to handle actor.usr and actor.card here ### we always set the alert message with an update since it's fed as a parameter and not in hash, makes a bit more db churn - my $update_usr_str; - my $insert_usr_str; + my $insert_usr_flag; if ($au_id) { - if ($valid_barcode == 1) { - sql_no_return($dbh,$session,$org_id,"UPDATE actor.card SET active = TRUE WHERE barcode = $prepped_cardnumber;",$debug); + if ($valid_barcode == 1) { + @parameters = ("$prepped_cardnumber"); + sql_no_return($dbh,$session,$org_id,"UPDATE actor.card SET active = TRUE WHERE barcode = ?;",\@parameters,$debug); } else { - sql_no_return($dbh,$session,$org_id,"INSERT INTO actor.card (usr,barcode) VALUES ($au_id,$prepped_cardnumber);",$debug); + @parameters = ("$au_id","$prepped_cardnumber"); + sql_no_return($dbh,$session,$org_id,"INSERT INTO actor.card (usr,barcode) VALUES (?,?);",\@parameters,$debug); } if (!defined $column_positions{'family_name'} or !defined $column_positions{'first_given_name'} @@ -285,62 +297,67 @@ while (my $line = <$fh>) { log_event($dbh,$session,$msg,undef,$org_id,$debug); if ($debug) { print "$msg\n" } } - $update_usr_str = update_au_sql($au_id,%column_values); - sql_no_return($dbh,$session,$org_id,$update_usr_str,$debug); + ($query,@parameters) = update_au_sql($au_id,%column_values); + sql_no_return($dbh,$session,$org_id,$query,\@parameters,$debug); } else { - $insert_usr_str = insert_au_sql($dbh,%column_values); - sql_no_return($dbh,$session,$org_id,$insert_usr_str,$debug); - @results = sql_return($dbh,"SELECT id FROM actor.usr WHERE usrname = $prepped_usrname;"); + $insert_usr_flag = 1; + ($query,@parameters) = insert_au_sql(%column_values); + sql_no_return($dbh,$session,$org_id,$query,\@parameters,$debug); + @parameters = ("$prepped_usrname"); + @results = sql_return($dbh,"SELECT id FROM actor.usr WHERE usrname = ?;",\@parameters,$debug); if (!defined $debug) { $au_id = $results[0]; } else { $au_id = 0; } #if here the card number shouldn't be in use so we have to make it - sql_no_return($dbh,$session,$org_id,"INSERT INTO actor.card (usr,barcode) VALUES ($au_id,$prepped_cardnumber);",$debug); + @parameters=("$au_id","$prepped_cardnumber"); + sql_no_return($dbh,$session,$org_id,"INSERT INTO actor.card (usr,barcode) VALUES (?,?);",\@parameters,$debug); } $query = "SELECT id FROM actor.card WHERE barcode = $prepped_cardnumber;"; if (!defined $debug) { - @results = sql_return($dbh,"SELECT id FROM actor.card WHERE barcode = $prepped_cardnumber;"); + @parameters = ("$prepped_cardnumber"); + @results = sql_return($dbh,"SELECT id FROM actor.card WHERE barcode = ?;",\@parameters,$debug); } else { print "$query\n"; } my $acard_id; if (!defined $debug) { $acard_id = $results[0]; } else { $acard_id = 'debug'; } - $query = "UPDATE actor.usr SET card = $acard_id WHERE id = $au_id;"; - sql_no_return($dbh,$session,$org_id,$query,$debug); - if ($alert_message) { - $query = "UPDATE actor.usr SET alert_message = CONCAT_WS(';',alert_message,$alert_title,$alert_message) WHERE id = $au_id;"; - sql_no_return($dbh,$session,$org_id,$query,$debug); + @parameters = ($acard_id,$au_id); + sql_no_return($dbh,$session,$org_id,"UPDATE actor.usr SET card = ? WHERE id = ?;",\@parameters,$debug); + if ($alert_message) { + sql_alert_message($dbh,$session,$org_id,$alert_title,$alert_message,$au_id,$debug); } ############################################################################################################## ### make sure password is salted; if inserting we check and create one if needed, then if we have one we salt it ### whether we created or updated userd my @set = ('0' ..'9', 'a' .. 'z', 'A' .. 'Z'); my $prepped_password; - if ($insert_usr_str) { + if ($insert_usr_flag) { if (!defined $column_values{'passwd'} or $column_values{'passwd'} eq '') { $column_values{'passwd'} = join '' => map $set[rand @set], 1 .. 16; } } if ($column_values{'passwd'}) { $prepped_password = sql_wrap_text($column_values{'passwd'}); - $query = "SELECT * FROM actor.change_password($au_id,$prepped_password);"; - sql_no_return($dbh,$session,$org_id,$query,$debug); + @parameters = ($au_id,"$prepped_password"); + sql_no_return($dbh,$session,$org_id, "SELECT * FROM actor.change_password(?,?);",\@parameters,$debug); } ############################################################################################################## ### address fun, first if either address exists and then don't assume just b/c there is an add2 there is an add1 - if ($column_values{add1_street1} or $column_values{add2_street1}) { - sql_no_return($dbh,$session,$org_id,"UPDATE actor.usr SET mailing_address = NULL WHERE id = $au_id;",$debug); - sql_no_return($dbh,$session,$org_id,"DELETE FROM actor.usr_address WHERE usr = $au_id AND address_type = 'MAILING';",$debug); + if ($column_values{add1_street1} or $column_values{add2_street1}) { + @parameters = ($au_id); + sql_no_return($dbh,$session,$org_id,"UPDATE actor.usr SET mailing_address = NULL WHERE id = ?;",\@parameters,$debug); + sql_no_return($dbh,$session,$org_id,"DELETE FROM actor.usr_address WHERE usr = ? AND address_type = 'MAILING';",\@parameters,$debug); } if ($column_values{add2_street1}) { - $query = insert_addr_sql($au_id,2,%column_values); - sql_no_return($dbh,$session,$org_id,$query,$debug); + ($query,@parameters) = insert_addr_sql($au_id,2,%column_values); + sql_no_return($dbh,$session,$org_id,$query,\@parameters,$debug); } if ($column_values{add1_street1}) { - $query = insert_addr_sql($au_id,1,%column_values); - sql_no_return($dbh,$session,$org_id,$query,$debug); + ($query,@parameters) = insert_addr_sql($au_id,1,%column_values); + sql_no_return($dbh,$session,$org_id,$query,\@parameters,$debug); } if ($column_values{add1_street1} or $column_values{add2_street1}) { - $query = "WITH x AS (SELECT MAX(id) AS id, usr FROM actor.usr_address WHERE usr = $au_id GROUP BY 2) UPDATE actor.usr au SET mailing_address = x.id FROM x WHERE x.usr = au.id;"; - sql_no_return($dbh,$session,$org_id,$query,$debug); + @parameters = ($au_id,$au_id); + $query = "WITH x AS (SELECT MAX(id) AS id, usr FROM actor.usr_address WHERE usr = ? GROUP BY 2) UPDATE actor.usr au SET mailing_address = x.id FROM x WHERE x.usr = ?;"; + sql_no_return($dbh,$session,$org_id,$query,\@parameters,$debug); } ############################################################################################################## ### now for the stat cats @@ -362,19 +379,24 @@ while (my $line = <$fh>) { my $statcat_entry_usr_map_id; if ($column_values{$statcat}) { $statcat_name = sql_wrap_text($column_values{$statcat}); - @results = sql_return($dbh,"SELECT id FROM actor.stat_cat WHERE name = $statcat_name AND owner IN (SELECT id FROM actor.org_unit_ancestors($prepped_home_ou_id));",$debug); + @parameters = ("$statcat_name",$prepped_home_ou_id); + @results = sql_return($dbh,"SELECT id FROM actor.stat_cat WHERE name = ? AND owner IN (SELECT id FROM actor.org_unit_ancestors(?));",\@parameters,$debug); $statcat_name_id = $results[0]; #potential issue here, hopefully there is only one of a given name in an ancestor chain but potentially this could be an issue if ($statcat_name_id) { - @results = sql_return($dbh,"SELECT id FROM actor.stat_cat_entry_usr_map WHERE stat_cat = $statcat_name_id AND target_usr = $au_id;",$debug); + @parameters = ($statcat_name_id,$au_id); + @results = sql_return($dbh,"SELECT id FROM actor.stat_cat_entry_usr_map WHERE stat_cat = ? AND target_usr = ?;",\@parameters,$debug); $statcat_entry_usr_map_id = $results[0]; if ($statcat_entry_usr_map_id and !defined $statcat_value) { - sql_no_return($dbh,$session,$org_id,"DELETE FROM actor.stat_cat_entry_usr_map WHERE id = $statcat_entry_usr_map_id;",$debug); + @parameters = ($statcat_entry_usr_map_id); + sql_no_return($dbh,$session,$org_id,"DELETE FROM actor.stat_cat_entry_usr_map WHERE id = ?;",\@parameters,$debug); next; } if ($statcat_entry_usr_map_id and $statcat_value) { - sql_no_return($dbh,$session,$org_id,"UPDATE actor.stat_cat_entry_usr_map SET stat_cat_entry = $statcat_value WHERE id = $statcat_entry_usr_map_id;",$debug); + @parameters = ("$statcat_value",$statcat_entry_usr_map_id); + sql_no_return($dbh,$session,$org_id,"UPDATE actor.stat_cat_entry_usr_map SET stat_cat_entry = ? WHERE id = ?;",\@parameters,$debug); } else { - sql_no_return($dbh,$session,$org_id,"INSERT INTO actor.stat_cat_entry_usr_map (stat_cat,stat_cat_entry,target_usr) VALUES ($statcat_name_id,$statcat_value,$au_id);",$debug); + @parameters = ($statcat_name_id,"$statcat_value",$au_id); + sql_no_return($dbh,$session,$org_id,"INSERT INTO actor.stat_cat_entry_usr_map (stat_cat,stat_cat_entry,target_usr) VALUES (?,?,?);",\@parameters,$debug); } } } @@ -407,7 +429,8 @@ sub abort { sub check_barcode { my ($dbh,$au_id,$barcode) = @_; if (!defined $au_id) { $au_id = -1; } - my @results = sql_return($dbh,"SELECT usr FROM actor.card WHERE barcode = $barcode;"); + my @parameters = ("$barcode"); + my @results = sql_return($dbh,"SELECT usr FROM actor.card WHERE barcode = ?;",\@parameters); if (!defined $results[0]) { return 2; } if ($results[0] == $au_id) { return 1; } return 0; @@ -416,7 +439,8 @@ sub check_barcode { sub check_usrname { my ($dbh,$au_id,$usrname) = @_; if (!defined $au_id) { $au_id = -1; } - my @results = sql_return($dbh,"SELECT id FROM actor.usr WHERE usrname = $usrname;"); + my @parameters = ("$usrname"); + my @results = sql_return($dbh,"SELECT id FROM actor.usr WHERE usrname = ?;",\@parameters); if (!defined $results[0]) { return 2; } if ($results[0] == $au_id) { return 1; } return 0; @@ -471,12 +495,13 @@ sub insert_addr_sql { my $country = sql_wrap_empty_text($column_values{join('','add',$x,'_country')} // ''); my $post_code = sql_wrap_empty_text($column_values{join('','add',$x,'_post_code')} // ''); my $query; - if ($street1) { $query = "INSERT INTO actor.usr_address (usr,street1,street2,city,county,state,country,post_code) VALUES ($au_id,$street1,$street2,$city,$county,$state,$country,$post_code);"; } - return $query; + my @parameters = ($au_id,"$street1","$street2","$city","$county","$state","$country","$post_code"); + if ($street1) { $query = "INSERT INTO actor.usr_address (usr,street1,street2,city,county,state,country,post_code) VALUES (?,?,?,?,?,?,?,?);"; } + return ($query,@parameters); } sub insert_au_sql { - my ($au_id,%column_values) = @_; + my (%column_values) = @_; my $start = 'INSERT INTO actor.usr ('; my $col_str; my $middle = ') VALUES ('; @@ -506,27 +531,28 @@ sub insert_au_sql { if ($col_str) { $col_str = join(',',$col_str,$ic); } else { $col_str = $ic; } } foreach my $iv (@insert_values) { - if ($val_str) { $val_str = join(',',$val_str,$iv); } else { $val_str = $iv; } + if ($val_str) { $val_str = join(',',$val_str,'?'); } else { $val_str = '?'; } } my $query = join('',$start,$col_str,$middle,$val_str,$end); - return $query; + return ($query,@insert_values); } sub log_event { my ($dbh,$session,$event,$record_count,$org_id,$debug) = @_; my $sth; $event = sql_wrap_text($event); - if (!defined $record_count) { $record_count = 'NULL'; } - my $sql = "INSERT INTO actor.patron_loader_log (session,event,record_count,org_unit) VALUES ($session,$event,$record_count,$org_id);"; + if (!defined $record_count) { $record_count = 0; } + my $sql = "INSERT INTO actor.patron_loader_log (session,event,record_count,org_unit) VALUES (?,?,?,?);"; if ($debug) { - print "$sql\n"; + print "$sql\n"; + print "parameters: $session,$event,$record_count,$org_id\n"; } else { $sth = $dbh->prepare($sql); - $sth->execute(); + $sth->execute("$session","$event","$record_count","$org_id"); } } @@ -577,41 +603,63 @@ sub sql_date { if (!defined $date_format) { $date_format = 'YYYY/MM/DD'; } $date = sql_wrap_text($date); $date_format = sql_wrap_text($date_format); - my $query = "SELECT TO_DATE($date,$date_format);"; - my @results = sql_return($dbh,$query); + my $query = "SELECT TO_DATE(?,?);"; + my @parameters = ("$date","$date_format"); + my @results = sql_return($dbh,$query,\@parameters); return $results[0]; } +sub sql_alert_message { + my ($dbh,$session,$org_id,$alert_title,$alert_message,$au_id,$debug) = @_; + my $sth; + my $parameters_string = join(', ',$alert_title,$alert_message,$au_id); + my $statement = "UPDATE actor.usr SET alert_message = CONCAT_WS('; ', alert_message, ?, ?) WHERE id = ?;"; + if (!defined $debug) { + eval { + $sth = $dbh->prepare($statement); + $sth->bind_param(1, $alert_title, SQL_VARCHAR); + $sth->bind_param(2, $alert_message, SQL_VARCHAR); + $sth->bind_param(3, $au_id, SQL_INTEGER); + $sth->execute(); + } + } else { + print "$statement\n"; + print "Parameters : $parameters_string\n"; + } + if ($@) { + $statement =~ s/'//g; + $parameters_string =~ s/'//g; + log_event($dbh,$session,"failed statement: $statement | parameters: $parameters_string",undef,$org_id,$debug); + } + return; +} sub sql_no_return { - my $dbh = shift; - my $session = shift; - my $org_id = shift; - my $statement = shift; - my $debug = shift; + my ($dbh,$session,$org_id,$statement,$parameters,$debug) = @_; my $sth; + my $parameters_string = join(', ',@$parameters); if (!defined $debug) { eval { $sth = $dbh->prepare($statement); - $sth->execute(); + $sth->execute(@$parameters); } } else { print "$statement\n"; + print "Parameters : $parameters_string\n"; } if ($@) { $statement =~ s/'//g; - log_event($dbh,$session,"failed statement $statement",undef,$org_id,$debug); + $parameters_string =~ s/'//g; + log_event($dbh,$session,"failed statement: $statement | parameters: $parameters_string",undef,$org_id,$debug); } return; } sub sql_return { - my $dbh = shift; - my $query = shift; - my $debug = shift; + my ($dbh, $query, $parameters, $debug) = @_; my @results; my $sth = $dbh->prepare($query); - $sth->execute(); + $sth->execute(@$parameters); while (my @row = $sth->fetchrow_array) { push @results, @row; } return @results; } @@ -628,7 +676,8 @@ sub sql_wrap_text { if (!defined $str) { return; } $str =~ s/^\s+|\s+$//g; $str =~ s/'/''/g; - if ($str) { $str = '\'' . $str . '\''; } else { $str = 'NULL'; } + #if ($str) { $str = "$_$" . $str . "$_$"; } else { $str = 'NULL'; } + #if ($str) { $str = '\'' . $str . '\''; } else { $str = 'NULL'; } return $str; } @@ -636,9 +685,10 @@ sub update_au_sql { my ($au_id,%column_values) = @_; my $start = 'UPDATE actor.usr SET '; my $middle; - my $end = " WHERE id = $au_id;"; + my @parameters; + my $end = ' WHERE id = ?;'; #$au_id #wrap strings but skip calculated ones and booleans - while (my ($col,$val) = each %column_values) { + while (my ($col,$val) = each %column_values) { if (!defined $val) { next; } if ($col =~ m/add1/ or $col =~ m/add2/ or $col =~ m/stat/ or $col eq 'cardnumber') { next; } #skip columns not in actor.usr itself my $dontwrap = 0; @@ -646,9 +696,16 @@ sub update_au_sql { if ($col eq 'home_library' or $col eq 'profile' or $col eq 'ident_type') { $dontwrap = 1; } if ($dontwrap == 0) { $val = sql_wrap_text($val); } if ($col eq 'home_library') { $col = 'home_ou'; } - if (!defined $middle) { $middle = "$col = $val"; } else { $middle = join(', ', $middle, "$col = $val"); } + if (!defined $middle) { + $middle = "$col = ?"; + push @parameters, $val; + } else { + $middle = join(', ', $middle, "$col = ?"); + push @parameters, $val; + } } - my $query = join('',$start,$middle,$end); - return $query; + push @parameters, $au_id; + my $statement = join('',$start,$middle,$end); + return ($statement,@parameters); } -- 2.11.0