From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from firstgate.proxmox.com (firstgate.proxmox.com [212.224.123.68]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by lists.proxmox.com (Postfix) with ESMTPS id 72AE6B1DE for ; Wed, 23 Nov 2022 15:26:23 +0100 (CET) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id 49FE022D0E for ; Wed, 23 Nov 2022 15:26:23 +0100 (CET) Received: from proxmox-new.maurer-it.com (proxmox-new.maurer-it.com [94.136.29.106]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by firstgate.proxmox.com (Proxmox) with ESMTPS for ; Wed, 23 Nov 2022 15:26:22 +0100 (CET) Received: from proxmox-new.maurer-it.com (localhost.localdomain [127.0.0.1]) by proxmox-new.maurer-it.com (Proxmox) with ESMTP id B5C3D44D82; Wed, 23 Nov 2022 15:26:21 +0100 (CET) Message-ID: Date: Wed, 23 Nov 2022 15:26:20 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:108.0) Gecko/20100101 Thunderbird/108.0 Content-Language: en-US To: Stoiko Ivanov , pmg-devel@lists.proxmox.com References: <20221123092336.11423-1-s.ivanov@proxmox.com> <20221123092336.11423-9-s.ivanov@proxmox.com> From: Dominik Csapak In-Reply-To: <20221123092336.11423-9-s.ivanov@proxmox.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.015 Adjusted score from AWL reputation of From: address BAYES_00 -1.9 Bayes spam probability is 0 to 1% KAM_DMARC_STATUS 0.01 Test Rule for DKIM or SPF Failure with Strict Alignment NICE_REPLY_A -0.001 Looks like a legit reply (A) PROLO_LEO1 0.1 Meta Catches all Leo drug variations so far SPF_HELO_NONE 0.001 SPF: HELO does not publish an SPF Record SPF_PASS -0.001 SPF: sender matches SPF record Subject: Re: [pmg-devel] [PATCH pmg-api v3 8/8] statistics: handle utf8 data. X-BeenThere: pmg-devel@lists.proxmox.com X-Mailman-Version: 2.1.29 Precedence: list List-Id: Proxmox Mail Gateway development discussion List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 23 Nov 2022 14:26:23 -0000 again, a bit more commit message would be nice On 11/23/22 10:23, Stoiko Ivanov wrote: > Signed-off-by: Stoiko Ivanov > --- > src/PMG/Statistic.pm | 67 +++++++++++++++++++++++++++++++++----------- > 1 file changed, 50 insertions(+), 17 deletions(-) > > diff --git a/src/PMG/Statistic.pm b/src/PMG/Statistic.pm > index 6d27930..96ef61d 100755 > --- a/src/PMG/Statistic.pm > +++ b/src/PMG/Statistic.pm > @@ -3,6 +3,7 @@ package PMG::Statistic; > use strict; > use warnings; > use DBI; > +use Encode qw(encode); > use Time::Local; > use Time::Zone; > > @@ -545,6 +546,22 @@ my $compute_sql_orderby = sub { > return $orderby; > }; > > +sub user_stat_to_perlstring { > + my ($entry) = @_; > + > + my $res = { }; > + > + for my $a (keys %$entry) { > + if ($a eq 'receiver' || $a eq 'sender' || $a eq 'contact') { > + $res->{$a} = PMG::Utils::try_decode_utf8($entry->{$a}); > + } else { > + $res->{$a} = $entry->{$a}; > + } > + } > + > + return $res; > +} > + > sub user_stat_contact_details { > my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_; > > @@ -554,19 +571,21 @@ sub user_stat_contact_details { > > my $cond_good_mail = $self->query_cond_good_mail ($from, $to); > > + my $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")); > + > my $query = "SELECT * FROM CStatistic, CReceivers " . > "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail " . > "AND NOT direction AND sender != '' AND receiver = ? " . > - ($filter ? "AND sender like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . > + ($filter_pattern ? "AND sender like " . $filter_pattern . ' ' : '') . > "ORDER BY $orderby limit $limit"; > > my $sth = $rdb->{dbh}->prepare($query); > > - $sth->execute($receiver); > + $sth->execute(encode('UTF-8',$receiver)); > > my $res = []; > while (my $ref = $sth->fetchrow_hashref()) { > - push @$res, $ref; > + push @$res, user_stat_to_perlstring($ref); > } > > $sth->finish(); > @@ -583,11 +602,14 @@ sub user_stat_contact { > > my $cond_good_mail = $self->query_cond_good_mail($from, $to); > > + my $filter_pattern; > + $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter; > + > my $query = "SELECT receiver as contact, count(*) AS count, sum (bytes) AS bytes, " . > "count (virusinfo) as viruscount " . > "FROM CStatistic, CReceivers " . > "WHERE cid = cstatistic_cid AND rid = cstatistic_rid " . > - ($filter ? "AND receiver like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . > + ($filter_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') . > "AND $cond_good_mail AND NOT direction AND sender != '' "; > > if ($advfilter) { > @@ -603,7 +625,7 @@ sub user_stat_contact { > > my $res = []; > while (my $ref = $sth->fetchrow_hashref()) { > - push @$res, $ref; > + push @$res, user_stat_to_perlstring($ref); > } > > $sth->finish(); > @@ -620,20 +642,23 @@ sub user_stat_sender_details { > > my $cond_good_mail = $self->query_cond_good_mail($from, $to); > > + my $filter_pattern; > + $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter; > + > my $sth = $rdb->{dbh}->prepare( > "SELECT " . > "blocked, bytes, ptime, sender, receiver, spamlevel, time, virusinfo " . > "FROM CStatistic, CReceivers " . > "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND " . > "$cond_good_mail AND NOT direction AND sender = ? " . > - ($filter ? "AND receiver like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . > + ($filter_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') . > "ORDER BY $orderby limit $limit"); > > - $sth->execute($sender); > + $sth->execute(encode('UTF-8',$sender)); > > my $res = []; > while (my $ref = $sth->fetchrow_hashref()) { > - push @$res, $ref; > + push @$res, user_stat_to_perlstring($ref); > } > > $sth->finish(); > @@ -650,11 +675,14 @@ sub user_stat_sender { > > my $cond_good_mail = $self->query_cond_good_mail ($from, $to); > > + my $filter_pattern; > + $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter; > + > my $query = "SELECT sender,count(*) AS count, sum (bytes) AS bytes, " . > "count (virusinfo) as viruscount, " . > "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount " . > "FROM CStatistic WHERE $cond_good_mail AND NOT direction AND sender != '' " . > - ($filter ? "AND sender like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . > + ($filter_pattern ? "AND sender like " . $filter_pattern . ' ' : '') . > "GROUP BY sender ORDER BY $orderby limit $limit"; > > my $sth = $rdb->{dbh}->prepare($query); > @@ -662,7 +690,7 @@ sub user_stat_sender { > > my $res = []; > while (my $ref = $sth->fetchrow_hashref()) { > - push @$res, $ref; > + push @$res, user_stat_to_perlstring($ref); > } > > $sth->finish(); > @@ -679,18 +707,21 @@ sub user_stat_receiver_details { > > my $cond_good_mail = $self->query_cond_good_mail($from, $to); > > + my $filter_pattern; > + $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter; > + > my $sth = $rdb->{dbh}->prepare( > "SELECT blocked, bytes, ptime, sender, receiver, spamlevel, time, virusinfo " . > "FROM CStatistic, CReceivers " . > "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND receiver = ? " . > - ($filter ? "AND sender like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . > + ($filter_pattern ? "AND sender like " . $filter_pattern . ' ' : '') . > "ORDER BY $orderby limit $limit"); > > - $sth->execute($receiver); > + $sth->execute(encode('UTF-8',$receiver)); > > my $res = []; > while (my $ref = $sth->fetchrow_hashref()) { > - push @$res, $ref; > + push @$res, user_stat_to_perlstring($ref); > } > > $sth->finish(); > @@ -708,6 +739,9 @@ sub user_stat_receiver { > my $cond_good_mail = $self->query_cond_good_mail ($from, $to) . " AND " . > "receiver IS NOT NULL AND receiver != ''"; > > + my $filter_pattern; > + $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter; > + > my $query = "SELECT receiver, " . > "count(*) AS count, " . > "sum (bytes) AS bytes, " . > @@ -728,7 +762,7 @@ sub user_stat_receiver { > } > > $query .= "AND $cond_good_mail and direction " . > - ($filter ? "AND receiver like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . > + ($filter_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') . we have this pattern 6 times in this diff, wouldn't it be easier to do something like this: (naming is not optimal, just what came to my mind) sub sql_filter_text { my ($dbh, $field, $filter) = @_; my $filter_text = $filter ? "AND $field like ". $dbh->quote(...). " " : ''; return $filter_text } and call it in the functions with my $filter_text = sql_filter_text($rdb->{dbh}, 'receiver', $filter); and simply use it with: $query .= "...." . $filter_text . "..."; ? > "GROUP BY receiver ORDER BY $orderby LIMIT $limit"; > > my $sth = $rdb->{dbh}->prepare($query); > @@ -736,7 +770,7 @@ sub user_stat_receiver { > > my $res = []; > while (my $ref = $sth->fetchrow_hashref()) { > - push @$res, $ref; > + push @$res, user_stat_to_perlstring($ref); > } > > $sth->finish(); > @@ -873,9 +907,8 @@ sub recent_receivers { > my $sth = $rdb->{dbh}->prepare($cmd); > > $sth->execute ($from, $limit); > - > while (my $ref = $sth->fetchrow_hashref()) { > - push @$res, $ref; > + push @$res, user_stat_to_perlstring($ref); > } > $sth->finish(); >