From bc40bcabc425b99f3b79a5544684a6fe8674adfe Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 18 Apr 2016 12:43:32 +0200 Subject: [PATCH] (I)LIKE-Argumente immer mit Funktion SL::DBUtils::like erstellen --- SL/AP.pm | 2 +- SL/AR.pm | 4 ++-- SL/CP.pm | 2 +- SL/CT.pm | 18 +++++++++--------- SL/CVar.pm | 6 +++--- SL/Common.pm | 8 ++++---- SL/Controller/BankTransaction.pm | 13 +++++++------ SL/Controller/RecordLinks.pm | 9 +++++---- SL/Controller/TopQuickSearch/Contact.pm | 8 ++++---- SL/Controller/TopQuickSearch/GLTransaction.pm | 11 ++++++----- SL/DBUtils.pm | 6 +++--- SL/DN.pm | 8 ++++---- SL/DO.pm | 8 ++++---- SL/FU.pm | 4 ++-- SL/Form.pm | 10 +++++----- SL/IC.pm | 4 ++-- SL/IR.pm | 4 ++-- SL/IS.pm | 4 ++-- SL/OE.pm | 16 ++++++++-------- SL/PE.pm | 4 ++-- SL/RP.pm | 6 +++--- SL/SEPA.pm | 6 +++--- SL/VK.pm | 6 +++--- 23 files changed, 85 insertions(+), 82 deletions(-) diff --git a/SL/AP.pm b/SL/AP.pm index afd4a525e..9e108a1ca 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -458,7 +458,7 @@ sub ap_transactions { } if ($form->{"cp_name"}) { $where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; - push(@values, ('%' . trim($form->{"cp_name"}) . '%')x2); + push(@values, (like($form->{"cp_name"}))x2); } if ($form->{department}) { # ähnlich wie commit 0bbfb33b6aa8e38bb6c81d1684ab7d08e5b5c5af abteilung diff --git a/SL/AR.pm b/SL/AR.pm index dba6b5b80..d37b7fc75 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -511,7 +511,7 @@ sub ar_transactions { } if ($form->{"cp_name"}) { $where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; - push(@values, ('%' . trim($form->{"cp_name"}) . '%')x2); + push(@values, (like($form->{"cp_name"}))x2); } if ($form->{business_id}) { my $business_id = $form->{business_id}; @@ -524,7 +524,7 @@ sub ar_transactions { push(@values, $department_id); } if ($form->{department}) { - my $department = "%" . trim($form->{department}) . "%"; + my $department = like($form->{department}); $where .= " AND d.description ILIKE ?"; push(@values, $department); } diff --git a/SL/CP.pm b/SL/CP.pm index a2a247613..3a2952728 100644 --- a/SL/CP.pm +++ b/SL/CP.pm @@ -76,7 +76,7 @@ sub paymentaccounts { qq|FROM chart | . qq|WHERE link LIKE ? |. qq|ORDER BY accno|; - my $sth = prepare_execute_query($form, $dbh, $query, '%' . $ARAP . '%'); + my $sth = prepare_execute_query($form, $dbh, $query, like($ARAP)); $form->{PR}{ $form->{ARAP} } = (); $form->{PR}{"$form->{ARAP}_paid"} = (); diff --git a/SL/CT.pm b/SL/CT.pm index bec4a6d73..416ed6871 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -104,19 +104,19 @@ sub search { if ($form->{"${cv}number"}) { $where .= " AND ct.${cv}number ILIKE ?"; - push(@values, '%' . $form->{"${cv}number"} . '%'); + push(@values, like($form->{"${cv}number"})); } foreach my $key (qw(name contact email)) { if ($form->{$key}) { $where .= " AND ct.$key ILIKE ?"; - push(@values, '%' . $form->{$key} . '%'); + push(@values, like($form->{$key})); } } if ($form->{cp_name}) { $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))"; - push @values, '%' . $form->{cp_name} . '%'; + push @values, like($form->{cp_name}); } if ($form->{addr_street}) { @@ -127,7 +127,7 @@ sub search { qq| WHERE (sc.module = 'CT') | . qq| AND (sc.shiptostreet ILIKE ?) | . qq| ))) |; - push @values, ('%' . $form->{addr_street} . '%') x 2; + push @values, (like($form->{addr_street})) x 2; } if ($form->{addr_zipcode}) { @@ -138,7 +138,7 @@ sub search { qq| WHERE (sc.module = 'CT') | . qq| AND (sc.shiptozipcode ILIKE ?) | . qq| ))) |; - push @values, ('%' . $form->{addr_zipcode} . '%') x 2; + push @values, (like($form->{addr_zipcode})) x 2; } if ($form->{addr_city}) { @@ -151,7 +151,7 @@ sub search { AND (lower(sc.shiptocity) LIKE lower(?)) )) )"; - push @values, ('%' . $form->{addr_city} . '%') x 2; + push @values, (like($form->{addr_city})) x 2; } if ($form->{addr_country}) { @@ -164,7 +164,7 @@ sub search { AND (lower(so.shiptocountry) LIKE lower(?)) )) )"; - push @values, ('%' . $form->{addr_country} . '%') x 2; + push @values, (like($form->{addr_country})) x 2; } if ($form->{addr_gln}) { @@ -177,7 +177,7 @@ sub search { AND (lower(so.shiptogln) LIKE lower(?)) )) )"; - push @values, ('%' . $form->{addr_gln} . '%') x 2; + push @values, (like($form->{addr_gln})) x 2; } if ( $form->{status} eq 'orphaned' ) { @@ -431,7 +431,7 @@ sub search_contacts { 'cp.cp_name ILIKE ?', 'cp.cp_givenname ILIKE ?', 'cp.cp_email ILIKE ?'; - push @values, ('%' . $params{search_term} . '%') x 3; + push @values, (like($params{search_term})) x 3; if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) { my $number = $params{search_term}; diff --git a/SL/CVar.pm b/SL/CVar.pm index f13481207..3dba8b217 100644 --- a/SL/CVar.pm +++ b/SL/CVar.pm @@ -356,7 +356,7 @@ sub build_filter_query { next unless ($params{filter}->{$name}); push @sub_where, qq|cvar.text_value ILIKE ?|; - push @sub_values, '%' . trim($params{filter}->{$name}) . '%' + push @sub_values, like($params{filter}->{$name}); } elsif ($config->{type} eq 'select') { next unless ($params{filter}->{$name}); @@ -419,12 +419,12 @@ sub build_filter_query { my $table = $config->{type}; push @sub_where, qq|cvar.number_value * 1 IN (SELECT id FROM $table WHERE name ILIKE ?)|; - push @sub_values, "%" . trim($params{filter}->{$name}) . "%"; + push @sub_values, like($params{filter}->{$name}); } elsif ($config->{type} eq 'part') { next unless $params{filter}->{$name}; push @sub_where, qq|cvar.number_value * 1 IN (SELECT id FROM parts WHERE partnumber ILIKE ?)|; - push @sub_values, "%" . trim($params{filter}->{$name}) . "%"; + push @sub_values, like($params{filter}->{$name}); } if (@sub_where) { diff --git a/SL/Common.pm b/SL/Common.pm index b977c0059..f1aa53c2d 100644 --- a/SL/Common.pm +++ b/SL/Common.pm @@ -62,7 +62,7 @@ sub retrieve_parts { next unless $form->{$_}; $filter .= qq| AND ($_ ILIKE ?)|; - push @filter_values, '%' . $form->{$_} . '%'; + push @filter_values, like($form->{$_}); } if ($form->{no_assemblies}) { @@ -110,7 +110,7 @@ sub retrieve_customers_or_vendors { my (@filter_values, $filter); if ($form->{"name"}) { $filter .= " AND (TABLE.name ILIKE ?)"; - push(@filter_values, '%' . $form->{"name"} . '%'); + push(@filter_values, like($form->{"name"})); } if (!$form->{"obsolete"}) { $filter .= " AND NOT TABLE.obsolete"; @@ -173,7 +173,7 @@ sub retrieve_delivery_customer { my (@filter_values, $filter); if ($form->{"name"}) { $filter .= qq| (name ILIKE ?) AND|; - push(@filter_values, '%' . $form->{"name"} . '%'); + push(@filter_values, like($form->{"name"})); } $order_by =~ s/[^a-zA-Z_]//g; @@ -209,7 +209,7 @@ sub retrieve_vendor { my (@filter_values, $filter); if ($form->{"name"}) { $filter .= qq| (name ILIKE ?) AND|; - push(@filter_values, '%' . $form->{"name"} . '%'); + push(@filter_values, like($form->{"name"})); } $order_by =~ s/[^a-zA-Z_]//g; diff --git a/SL/Controller/BankTransaction.pm b/SL/Controller/BankTransaction.pm index bbe4edf2c..5e09aacc4 100644 --- a/SL/Controller/BankTransaction.pm +++ b/SL/Controller/BankTransaction.pm @@ -23,6 +23,7 @@ use SL::DB::AccTransaction; use SL::DB::Tax; use SL::DB::Draft; use SL::DB::BankAccount; +use SL::DBUtils qw(like); use SL::Presenter; use List::Util qw(max); @@ -277,8 +278,8 @@ sub action_ajax_add_list { my @where_purchase = (amount => { ne => \'paid' }); if ($::form->{invnumber}) { - push @where_sale, (invnumber => { ilike => '%' . $::form->{invnumber} . '%'}); - push @where_purchase, (invnumber => { ilike => '%' . $::form->{invnumber} . '%'}); + push @where_sale, (invnumber => { ilike => like($::form->{invnumber})}); + push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})}); } if ($::form->{amount}) { @@ -287,13 +288,13 @@ sub action_ajax_add_list { } if ($::form->{vcnumber}) { - push @where_sale, ('customer.customernumber' => { ilike => '%' . $::form->{vcnumber} . '%'}); - push @where_purchase, ('vendor.vendornumber' => { ilike => '%' . $::form->{vcnumber} . '%'}); + push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})}); + push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})}); } if ($::form->{vcname}) { - push @where_sale, ('customer.name' => { ilike => '%' . $::form->{vcname} . '%'}); - push @where_purchase, ('vendor.name' => { ilike => '%' . $::form->{vcname} . '%'}); + push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})}); + push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})}); } if ($::form->{transdatefrom}) { diff --git a/SL/Controller/RecordLinks.pm b/SL/Controller/RecordLinks.pm index 3d48b0586..9ff64e49e 100644 --- a/SL/Controller/RecordLinks.pm +++ b/SL/Controller/RecordLinks.pm @@ -14,6 +14,7 @@ use SL::DB::Letter; use SL::DB::PurchaseInvoice; use SL::DB::RecordLink; use SL::DB::RequirementSpec; +use SL::DBUtils qw(like); use SL::JSON; use SL::Locale::String; @@ -124,10 +125,10 @@ sub action_ajax_add_list { my $filter = $self->link_type_desc->{filter}; my @where = $filter && $manager->can($filter) ? $manager->$filter($self->link_type) : (); - push @where, ("${vc}.${vc}number" => { ilike => '%' . $::form->{vc_number} . '%' }) if $::form->{vc_number}; - push @where, ("${vc}.name" => { ilike => '%' . $::form->{vc_name} . '%' }) if $::form->{vc_name}; - push @where, ($description => { ilike => '%' . $::form->{transaction_description} . '%' }) if $::form->{transaction_description}; - push @where, ($project_id => $::form->{globalproject_id}) if $::form->{globalproject_id} && $manager->can($project_id); + push @where, ("${vc}.${vc}number" => { ilike => like($::form->{vc_number}) }) if $::form->{vc_number}; + push @where, ("${vc}.name" => { ilike => like($::form->{vc_name}) }) if $::form->{vc_name}; + push @where, ($description => { ilike => like($::form->{transaction_description}) }) if $::form->{transaction_description}; + push @where, ($project_id => $::form->{globalproject_id}) if $::form->{globalproject_id} && $manager->can($project_id); my @with_objects = ($vc); push @with_objects, $project if $manager->can($project_id); diff --git a/SL/Controller/TopQuickSearch/Contact.pm b/SL/Controller/TopQuickSearch/Contact.pm index 5607a5b82..e170524ab 100644 --- a/SL/Controller/TopQuickSearch/Contact.pm +++ b/SL/Controller/TopQuickSearch/Contact.pm @@ -5,7 +5,7 @@ use parent qw(SL::Controller::TopQuickSearch::Base); use SL::Controller::CustomerVendor; use SL::DB::Vendor; -use SL::DBUtils qw(selectfirst_array_query); +use SL::DBUtils qw(selectfirst_array_query like); use SL::Locale::String qw(t8); sub auth { 'customer_vendor_edit' } @@ -22,9 +22,9 @@ sub query_autocomplete { my $result = SL::DB::Manager::Contact->get_all( query => [ or => [ - cp_name => { ilike => "%$::form->{term}%" }, - cp_givenname => { ilike => "%$::form->{term}%" }, - cp_email => { ilike => "%$::form->{term}%" }, + cp_name => { ilike => like($::form->{term}) }, + cp_givenname => { ilike => like($::form->{term}) }, + cp_email => { ilike => like($::form->{term}) }, ], cp_cv_id => [ \'SELECT id FROM customer UNION SELECT id FROM vendor' ], ], diff --git a/SL/Controller/TopQuickSearch/GLTransaction.pm b/SL/Controller/TopQuickSearch/GLTransaction.pm index 77854ff4c..8eead24de 100644 --- a/SL/Controller/TopQuickSearch/GLTransaction.pm +++ b/SL/Controller/TopQuickSearch/GLTransaction.pm @@ -8,6 +8,7 @@ use SL::DB::Invoice; use SL::DB::PurchaseInvoice; use SL::DB::AccTransaction; use SL::Locale::String qw(t8); +use SL::DBUtils qw(like); use List::Util qw(sum); sub auth { 'general_ledger' } @@ -24,11 +25,11 @@ sub query_autocomplete { my $limit = $::form->{limit} || 40; # max number of results per type (AR/AP/GL) my $term = $::form->{term} || ''; - my $descriptionquery = { ilike => '%' . $term . '%' }; - my $referencequery = { ilike => '%' . $term . '%' }; - my $apinvnumberquery = { ilike => '%' . $term . '%' }; - my $namequery = { ilike => '%' . $term . '%' }; - my $arinvnumberquery = { ilike => '%' . $term }; + my $descriptionquery = { ilike => like($term) }; + my $referencequery = { ilike => like($term) }; + my $apinvnumberquery = { ilike => like($term) }; + my $namequery = { ilike => like($term) }; + my $arinvnumberquery = { ilike => '%' . SL::Util::trim($term) }; # ar match is more restrictive. Left fuzzy beginning so it also matches "Storno zu $INVNUMBER" # and numbers like 000123 if you only enter 123. # When used in quicksearch short numbers like 1 or 11 won't match because of the diff --git a/SL/DBUtils.pm b/SL/DBUtils.pm index 200f421e6..d060bcb8e 100644 --- a/SL/DBUtils.pm +++ b/SL/DBUtils.pm @@ -348,9 +348,9 @@ sub add_token { id => \&conv_i, bool => \&conv_b, date => \&conv_date, - start => sub { $_[0] . '%' }, - end => sub { '%' . $_[0] }, - substr => sub { '%' . $_[0] . '%' }, + start => sub { trim($_[0]) . '%' }, + end => sub { '%' . trim($_[0]) }, + substr => sub { like($_[0]) }, ); my $_long_token = sub { diff --git a/SL/DN.pm b/SL/DN.pm index e7959e0f1..1804959f9 100644 --- a/SL/DN.pm +++ b/SL/DN.pm @@ -492,7 +492,7 @@ sub get_invoices { } elsif ($form->{customer}) { $where .= qq| AND (ct.name ILIKE ?)|; - push(@values, '%' . trim($form->{customer}) . '%'); + push(@values, like($form->{customer})); } my %columns = ( @@ -504,7 +504,7 @@ sub get_invoices { foreach my $key (keys(%columns)) { next unless ($form->{$key}); $where .= qq| AND $columns{$key} ILIKE ?|; - push(@values, '%' . trim($form->{$key}) . '%'); + push(@values, like($form->{$key})); } if ($form->{dunning_level}) { @@ -619,7 +619,7 @@ sub get_dunning { } elsif ($form->{customer}) { $where .= qq| AND (ct.name ILIKE ?)|; - push(@values, '%' . $form->{customer} . '%'); + push(@values, like($form->{customer})); } my %columns = ( @@ -630,7 +630,7 @@ sub get_dunning { foreach my $key (keys(%columns)) { next unless ($form->{$key}); $where .= qq| AND $columns{$key} ILIKE ?|; - push(@values, '%' . $form->{$key} . '%'); + push(@values, like($form->{$key})); } if ($form->{dunning_level}) { diff --git a/SL/DO.pm b/SL/DO.pm index a1bfd0017..ee62af223 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -111,12 +111,12 @@ sub transactions { } elsif ($form->{$vc}) { push @where, qq|ct.name ILIKE ?|; - push @values, '%' . trim($form->{$vc}) . '%'; + push @values, like($form->{$vc}); } if ($form->{"cp_name"}) { push @where, "(cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; - push @values, ('%' . trim($form->{"cp_name"}) . '%')x2; + push @values, (like($form->{"cp_name"}))x2; } foreach my $item (qw(employee_id salesman_id)) { @@ -132,7 +132,7 @@ sub transactions { foreach my $item (qw(donumber ordnumber cusordnumber transaction_description)) { next unless ($form->{$item}); push @where, qq|dord.$item ILIKE ?|; - push @values, '%' . trim($form->{$item}) . '%'; + push @values, like($form->{$item}); } if (($form->{open} || $form->{closed}) && @@ -147,7 +147,7 @@ sub transactions { if ($form->{serialnumber}) { push @where, 'dord.id IN (SELECT doi.delivery_order_id FROM delivery_order_items doi WHERE doi.serialnumber LIKE ?)'; - push @values, '%' . trim($form->{serialnumber}) . '%'; + push @values, like($form->{serialnumber}); } if($form->{transdatefrom}) { diff --git a/SL/FU.pm b/SL/FU.pm index 55f58f0e5..a9715dca0 100644 --- a/SL/FU.pm +++ b/SL/FU.pm @@ -210,7 +210,7 @@ sub follow_ups { foreach my $item (qw(subject body)) { next unless ($params{$item}); $where .= qq| AND (n.${item} ILIKE ?)|; - push @values, '%' . $params{$item} . '%'; + push @values, like($params{$item}); } if ($params{reference}) { @@ -219,7 +219,7 @@ sub follow_ups { WHERE (ful.follow_up_id = fu.id) AND (ful.trans_info ILIKE ?) LIMIT 1)|; - push @values, '%' . $params{reference} . '%'; + push @values, like($params{reference}); } if ($params{follow_up_date_from}) { diff --git a/SL/Form.pm b/SL/Form.pm index 41cc49f03..41cb8612d 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -2499,10 +2499,10 @@ sub get_name { my $where; if ($self->{customernumber} ne "") { $where = qq|(vc.customernumber ILIKE ?)|; - push(@values, '%' . $self->{customernumber} . '%'); + push(@values, like($self->{customernumber})); } else { $where = qq|(vc.name ILIKE ?)|; - push(@values, '%' . $self->{$table} . '%'); + push(@values, like($self->{$table})); } $query = @@ -2519,7 +2519,7 @@ sub get_name { JOIN $table vc ON (a.${table}_id = vc.id) WHERE NOT (a.amount = a.paid) AND (vc.name ILIKE ?) ORDER BY vc.name~; - push(@values, '%' . $self->{$table} . '%'); + push(@values, like($self->{$table})); } $self->{name_list} = selectall_hashref_query($self, $dbh, $query, @values); @@ -2744,7 +2744,7 @@ sub create_links { $sth = $dbh->prepare($query); - do_statement($self, $sth, $query, '%' . $module . '%'); + do_statement($self, $sth, $query, like($module)); $self->{accounts} = ""; while ($ref = $sth->fetchrow_hashref("NAME_lc")) { @@ -2819,7 +2819,7 @@ sub create_links { ORDER BY c.accno|; $sth = $dbh->prepare($query); - do_statement($self, $sth, $query, "%$module%"); + do_statement($self, $sth, $query, like($module)); $self->{accounts} = ""; while ($ref = $sth->fetchrow_hashref("NAME_lc")) { diff --git a/SL/IC.pm b/SL/IC.pm index 8341f1888..8f265029f 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -878,7 +878,7 @@ sub all_parts { if ($form->{all}) { $form->{"l_$_"} = 1 for qw(partnumber description unit sellprice lastcost cvar_packaging linetotal); push @where_tokens, "p.partnumber ILIKE ? OR p.description ILIKE ?"; - push @bind_vars, "%$form->{all}%", "%$form->{all}%"; + push @bind_vars, (like($form->{all})) x 2; } # special case insertdate @@ -1342,7 +1342,7 @@ sub create_links { # connect to database my $dbh = $form->get_standard_dbh; - my @values = ('%' . $module . '%'); + my @values = like($module); my $query; if ($form->{id}) { diff --git a/SL/IR.pm b/SL/IR.pm index ae3e45b09..aa2ed316e 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -1234,7 +1234,7 @@ sub retrieve_item { my $field = (split m{\.}, $table_column)[1]; next unless $form->{"${field}_${i}"}; $where .= " AND lower(${table_column}) LIKE lower(?)"; - push @values, '%' . $form->{"${field}_${i}"} . '%'; + push @values, like($form->{"${field}_${i}"}); } my (%mm_by_id); @@ -1248,7 +1248,7 @@ sub retrieve_item { LEFT JOIN parts ON parts.id = parts_id WHERE NOT parts.obsolete AND model ILIKE ? AND (make IS NULL OR make = ?); |; - my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%', $::form->{vendor_id}); + my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, like($form->{"partnumber_$i"}), $::form->{vendor_id}); my @mm_ids = map { $_->{parts_id} } @$mm_results; push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results; diff --git a/SL/IS.pm b/SL/IS.pm index 566d1546b..caa2c3469 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -2227,7 +2227,7 @@ sub retrieve_item { my ($table, $field) = split m/\./, $column; next if !$form->{"${field}_${i}"}; $where .= qq| AND lower(${column}) ILIKE ?|; - push @values, '%' . $form->{"${field}_${i}"} . '%'; + push @values, like($form->{"${field}_${i}"}); } my (%mm_by_id); @@ -2239,7 +2239,7 @@ sub retrieve_item { my $mm_query = qq| SELECT parts_id, model FROM makemodel LEFT JOIN parts ON parts.id = parts_id WHERE NOT parts.obsolete AND model ILIKE ?; |; - my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%'); + my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, like($form->{"partnumber_$i"})); my @mm_ids = map { $_->{parts_id} } @$mm_results; push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results; diff --git a/SL/OE.pm b/SL/OE.pm index 149a6cf1a..0282f569e 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -159,7 +159,7 @@ sub transactions { WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id )) SQL - push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ; + push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"}); } if ($form->{"business_id"}) { @@ -173,12 +173,12 @@ SQL } elsif ($form->{$vc}) { $query .= " AND ct.name ILIKE ?"; - push(@values, '%' . trim($form->{$vc}) . '%'); + push(@values, like($form->{$vc})); } if ($form->{"cp_name"}) { $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; - push(@values, ('%' . trim($form->{"cp_name"}) . '%')x2); + push(@values, (like($form->{"cp_name"}))x2); } if (!$main::auth->assert('sales_all_edit', 1)) { @@ -209,12 +209,12 @@ SQL if ($form->{$ordnumber}) { $query .= qq| AND o.$ordnumber ILIKE ?|; - push(@values, '%' . trim($form->{$ordnumber}) . '%'); + push(@values, like($form->{$ordnumber})); } if ($form->{cusordnumber}) { $query .= qq| AND o.cusordnumber ILIKE ?|; - push(@values, '%' . trim($form->{cusordnumber}) . '%'); + push(@values, like($form->{cusordnumber})); } if($form->{transdatefrom}) { @@ -249,7 +249,7 @@ SQL if ($form->{shippingpoint}) { $query .= qq| AND o.shippingpoint ILIKE ?|; - push(@values, '%' . trim($form->{shippingpoint}) . '%'); + push(@values, like($form->{shippingpoint})); } if ($form->{taxzone_id} ne '') { # taxzone_id could be 0 @@ -259,7 +259,7 @@ SQL if ($form->{transaction_description}) { $query .= qq| AND o.transaction_description ILIKE ?|; - push(@values, '%' . trim($form->{transaction_description}) . '%'); + push(@values, like($form->{transaction_description})); } if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) { @@ -296,7 +296,7 @@ SQL ct.name ILIKE ? OR o.transaction_description ILIKE ? )| for @tokens; - push @values, ("%$_%")x4 for @tokens; + push @values, (like($_))x4 for @tokens; } my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT', diff --git a/SL/PE.pm b/SL/PE.pm index 4d7176dd2..2eb720150 100644 --- a/SL/PE.pm +++ b/SL/PE.pm @@ -52,7 +52,7 @@ sub partsgroups { if ($form->{partsgroup}) { $where .= qq| AND partsgroup ILIKE ?|; - push(@values, '%' . $form->{partsgroup} . '%'); + push(@values, like($form->{partsgroup})); } if ($form->{status} eq 'orphaned') { @@ -174,7 +174,7 @@ sub pricegroups { if ($form->{pricegroup}) { $where .= qq| AND pricegroup ILIKE ?|; - push(@values, '%' . $form->{pricegroup} . '%'); + push(@values, like($form->{pricegroup})); } if ($form->{status} eq 'orphaned') { diff --git a/SL/RP.pm b/SL/RP.pm index 1154f4eba..a18452e67 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -1521,15 +1521,15 @@ sub payments { my $invnumber; my $reference; if ($form->{reference}) { - $reference = $dbh->quote('%' . $form->{reference} . '%'); + $reference = $dbh->quote(like($form->{reference})); $invnumber = " AND (a.invnumber LIKE $reference)"; $reference = " AND (a.reference LIKE $reference)"; } if ($form->{source}) { - $where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") "; + $where .= " AND (ac.source ILIKE " . $dbh->quote(like($form->{source})) . ") "; } if ($form->{memo}) { - $where .= " AND (ac.memo ILIKE " . $dbh->quote('%' . $form->{memo} . '%') . ") "; + $where .= " AND (ac.memo ILIKE " . $dbh->quote(like($form->{memo})) . ") "; } my %sort_columns = ( diff --git a/SL/SEPA.pm b/SL/SEPA.pm index 2b5738d6e..280906307 100644 --- a/SL/SEPA.pm +++ b/SL/SEPA.pm @@ -322,12 +322,12 @@ sub list_exports { if ($filter->{invnumber}) { push @where_sub, "arap.invnumber ILIKE ?"; - push @values_sub, '%' . $filter->{invnumber} . '%'; + push @values_sub, like($filter->{invnumber}); $joins_sub{$arap} = 1; } if ($filter->{message_id}) { - push @values, '%' . $filter->{message_id} . '%'; + push @values, like($filter->{message_id}); push @where, <{vc}) { push @where_sub, "vc.name ILIKE ?"; - push @values_sub, '%' . $filter->{vc} . '%'; + push @values_sub, like($filter->{vc}); $joins_sub{$arap} = 1; $joins_sub{vc} = 1; } diff --git a/SL/VK.pm b/SL/VK.pm index 45c3f63e6..f5fb03f21 100644 --- a/SL/VK.pm +++ b/SL/VK.pm @@ -120,7 +120,7 @@ sub invoice_transactions { } if ($form->{partnumber}) { $where .= qq| AND (p.partnumber ILIKE ?)|; - push(@values, '%' . $form->{partnumber} . '%'); + push(@values, like($form->{partnumber})); } if ($form->{partsgroup_id}) { $where .= qq| AND (pg.id = ?)|; @@ -128,7 +128,7 @@ sub invoice_transactions { } if ($form->{country}) { $where .= qq| AND (ct.country ILIKE ?)|; - push(@values, '%' . $form->{country} . '%'); + push(@values, like($form->{country})); } # when filtering for parts by description we probably want to filter by the description of the part as per the master data @@ -136,7 +136,7 @@ sub invoice_transactions { # at least in the translation case we probably want the report to also include translated articles, so we have to filter via parts.description if ($form->{description}) { $where .= qq| AND (p.description ILIKE ?)|; - push(@values, '%' . $form->{description} . '%'); + push(@values, like($form->{description})); } if ($form->{transdatefrom}) { $where .= " AND ar.transdate >= ?"; -- 2.20.1