charset problem with Greek

[ghost]

Hi,
In status.php my greek characters (utf8) appear as questionmarks ??????? in subject.
Setting:
self::$link->set_charset($charset);
self::$link->query("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
in database.php, solves my problem. I don't know the collation before 50503 to make a proper diff
but this works for me (self::$link->server_version 50505)

Check out:
http://php.net/manual/en/mysqli.set-charset.php#121067
http://php.net/manual/en/mysqli.set-charset.php#121647

Strange thing that in details.php (in specific message) subject appears correct...

regards,

Giannis

[Skywalker-11]

Can you fill out the questions from here: https://github.com/mailwatch/MailWatch/blob/1.2/.github/ISSUE_TEMPLATE.md

[ghost]

Issue summary
In status.php my greek characters (utf8) appear as questionmarks ??????? in subject.

Steps to reproduce
send mail with greek utf8 subject like: ΤΕΣΤ ΜΑΙΛ - τεστ μαιλ

Expected result
Subject ΤΕΣΤ ΜΑΙΛ - τεστ μαιλ

Actual result
??????

Installation
Version and method

MailWatch Version: MailWatch for MailScanner v1.2.7-dev running on EFA-3.0.2.6

Reading the build script it fetches: https://dl.efa-project.org/build/3.0.2.6/MailWatch-develop-GIT-7f70aa3.zip
Install type: from EFA
Updated from an older MailWatch or fresh install: Fresh install

Server configuration

Operation System: CentOS 
PHP version: php-5.3.3-49.el6.x86_64
Database: MariaDB-server-10.2.15-1.el7.centos.x86_64
Mail Server: postfix-3.1.3-1.efa.el6.x86_64
Used web server: httpd-2.2.15-60.el6.centos.6.x86_64

Client configuration

Operation System: Fedora 27
Browser: firefox-60.0.2-1.fc27.x86_64

[Skywalker-11]

Can you run SHOW CREATE TABLE maillog as mysql query?

[ghost]

MariaDB [mailscanner]> SHOW CREATE TABLE maillog \G
*************************** 1. row ***************************
Table: maillog
Create Table: CREATE TABLE maillog (
timestamp timestamp NULL DEFAULT NULL,
id longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
size bigint(20) DEFAULT 0,
from_address longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
from_domain longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
to_address longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
to_domain longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
subject longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
clientip longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
archive longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
isspam tinyint(1) DEFAULT 0,
ishighspam tinyint(1) DEFAULT 0,
issaspam tinyint(1) DEFAULT 0,
isrblspam tinyint(1) DEFAULT 0,
isfp tinyint(1) DEFAULT 0,
isfn tinyint(1) DEFAULT 0,
spamwhitelisted tinyint(1) DEFAULT 0,
spamblacklisted tinyint(1) DEFAULT 0,
sascore decimal(7,2) DEFAULT 0.00,
spamreport longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
virusinfected tinyint(1) DEFAULT 0,
nameinfected tinyint(1) DEFAULT 0,
otherinfected tinyint(1) DEFAULT 0,
report longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
ismcp tinyint(1) DEFAULT 0,
ishighmcp tinyint(1) DEFAULT 0,
issamcp tinyint(1) DEFAULT 0,
mcpwhitelisted tinyint(1) DEFAULT 0,
mcpblacklisted tinyint(1) DEFAULT 0,
mcpsascore decimal(7,2) DEFAULT 0.00,
mcpreport longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
hostname longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
date date DEFAULT NULL,
time time DEFAULT NULL,
headers longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
quarantined tinyint(1) DEFAULT 0,
maillog_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
rblspamreport longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
token char(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
released tinyint(1) DEFAULT 0,
last_update timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
salearn tinyint(1) DEFAULT 0,
PRIMARY KEY (maillog_id),
KEY maillog_datetime_idx (date,time),
KEY maillog_id_idx (id(20)),
KEY maillog_clientip_idx (clientip(20)),
KEY maillog_host (hostname(30)),
KEY from_domain_idx (from_domain(50)),
KEY to_domain_idx (to_domain(50)),
KEY maillog_quarantined (quarantined),
KEY maillog_from_idx (from_address(191)),
KEY maillog_to_idx (to_address(191)),
KEY timestamp_idx (timestamp)
) ENGINE=InnoDB AUTO_INCREMENT=651 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

[Skywalker-11]

Can you also post the raw subject from the mail headers? Eg. by showing the mails source code in your mail client

[ghost]

This is from headers
Subject: =?utf-8?B?UkU6IM6gz4HPjM6yzrvOt868zrEgz4PPjc69zrTOtc+DzrfPgiDPg8+Ezr8g?=
=?utf-8?Q?eclass?=

From DB:
MariaDB [mailscanner]> select subject from maillog where id='41FyGN5v65z5vNG.AAE9C';
+-----------------------------------------------------+
| subject |
+-----------------------------------------------------+
| RE: Πρόβλημα σύνδεσης στο eclass |
+-----------------------------------------------------+

[Skywalker-11]

Can't reproduce with MariaDB 10.1.26, Debian 9, MW v1.2.7 or v1.2.9 , PHP 7.0.27 or 7.2

Are there any errors in the webserver log?

[ghost]

no errors at all

Maybe it's a combination with the php version.
You use a recent version. Centos6 ships with 5.3

[ghost]

Don't know if it's related but after my proposed fix I had mysql log errors.
I believe MailWatch.pm child died cause it stopped logging after this:

Jun 27 17:30:26 mail2 MailScanner[9640]: Could not use Custom Function code MailScanner::CustomConfig::InitMailWatchLogging, it could not be "eval"ed. Make sure the module is correct with perl -wc (Error: DBD::mysql::st execute failed: Incorrect string value: '\xF0\x9F\x9A\xA7 \xCE...' for column 'subject' at row 1 at /usr/share/MailScanner/perl/custom/MailWatch.pm line 185, line 64.

I'm reverting back my change to see how it goes with logging.
seems similar to #428 but with 'subject' and not 'headers'

[Skywalker-11]

It looks like an encoding problem. Maybe DBD::mysql is too old like in #779
Can you post your DBD::mysql version as described here https://docs.mailwatch.org/install/getting-started.html

[ghost]

On 28/06/18 00:28, Michael Pietsch wrote: It looks like an encoding problem. Maybe DBD::mysql is too old like in #779 <#779> Can you post your DBD::mysql version as described here https://docs.mailwatch.org/install/getting-started.html

4.013 Don't know if it's related also with Denial of Service error messages I get like in #430 I'll upgrade tomorrow DBD::mysql and see how it goes. thanks, G

[ghost]

updating DBD::mysql to 4.046_01 fixed my problem with:

Jun 27 17:30:26 mail2 MailScanner[9640]: Could not use Custom Function code MailScanner::CustomConfig::InitMailWatchLogging, it could not be "eval"ed. Make sure the module is correct with perl -wc (Error: DBD::mysql::st execute failed: Incorrect string value: '\xF0\x9F\x9A\xA7 \xCE...' for column 'subject' at row 1 at /usr/share/MailScanner/perl/custom/MailWatch.pm line 185, line 64.

The subject had an emoticon that crashed the MailWatch SQL
Thanks for this.

I've re-applied
self::$link->query("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
on database.php since it's irrelevant with the perl MailWatch.pm

[ghost]

About the subject in status.php
I see in database that in headers subject is written as:
"=?UTF-8?B?RndkOiBSZTogzrTOt868zrnOv8+Fz4HOs86vzrEgzrvOv86zzrHPgc65?= =?UTF-8?B?zrHPg868zr/PjSDPg8+Ezr8gQ2xhc3N3ZWI=?="
on the other hand subject is written as UTF-8 directly:
"Fwd: Re: δημιουργία λογαριασμού στο Classweb"

If I replace subject with same value as in headers then I see it normaly without SET NAMES

[Skywalker-11]

As described in http://php.net/manual/en/mysqli.set-charset.php#121647 the set_charset function can fail with too old php versions. PR #1080 should fix this by checking its return code and calling the SET NAMES ... query in case it failed

[endelwar]

Nice catch!

[ghost]

On 02/07/18 20:22, Michael Pietsch wrote: As described in http://php.net/manual/en/mysqli.set-charset.php#121647 the set_charset function can fail with too old php versions. PR #1080 <#1080> should fix this by checking its return code and calling the |SET NAMES ...| query in case it failed

Verified, thanks :) G