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 67B41B006 for ; Wed, 23 Nov 2022 10:24:14 +0100 (CET) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id B7DAD20206 for ; Wed, 23 Nov 2022 10:23:49 +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 10:23:46 +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 A484344445 for ; Wed, 23 Nov 2022 10:23:44 +0100 (CET) From: Stoiko Ivanov To: pmg-devel@lists.proxmox.com Date: Wed, 23 Nov 2022 10:23:34 +0100 Message-Id: <20221123092336.11423-9-s.ivanov@proxmox.com> X-Mailer: git-send-email 2.30.2 In-Reply-To: <20221123092336.11423-1-s.ivanov@proxmox.com> References: <20221123092336.11423-1-s.ivanov@proxmox.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.115 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 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: [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 09:24:14 -0000 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 . ' ' : '') . "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(); -- 2.30.2