search

mailwatch / MailWatch

MailWatch for MailScanner is a web-based front-end to MailScanner
http://mailwatch.org/
GNU General Public License v2.0
117 stars 66 forks source link

Error: DBD::mysql::st execute failed:Incorrect string value: '\xF3n@fin...' #1252

Closed jarias-irontec closed 2 months ago

jarias-irontec commented 2 years ago

Issue summary

Hello! I have a problem when inserting data in the maillog table:

Steps to reproduce

E-mail received

Expected result

Log in MySQL

Actual result

Error: 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: '\xF3n@fin...' for column 'to_address' at row 1 at /usr/share/MailScanner/perl/custom/MailWatch.pm line 226, line 729.

Installation

Version and method

Server configuration

Maillog table

1. row Table: maillog Create Table: CREATE TABLE maillog ( maillog_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, timestamp timestamp NULL DEFAULT NULL, id longtext COLLATE utf8mb4_unicode_ci, size bigint(20) DEFAULT '0', from_address longtext COLLATE utf8mb4_unicode_ci, from_domain longtext COLLATE utf8mb4_unicode_ci, to_address longtext COLLATE utf8mb4_unicode_ci, to_domain longtext COLLATE utf8mb4_unicode_ci, subject longtext COLLATE utf8mb4_unicode_ci, clientip longtext COLLATE utf8mb4_unicode_ci, archive longtext COLLATE utf8mb4_unicode_ci, 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, virusinfected tinyint(1) DEFAULT '0', nameinfected tinyint(2) DEFAULT '0', otherinfected tinyint(1) DEFAULT '0', report longtext COLLATE utf8mb4_unicode_ci, 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, hostname longtext COLLATE utf8mb4_unicode_ci, date date DEFAULT NULL, time time DEFAULT NULL, headers longtext COLLATE utf8mb4_unicode_ci, messageid longtext COLLATE utf8mb4_unicode_ci, quarantined tinyint(1) DEFAULT '0', rblspamreport longtext COLLATE utf8mb4_unicode_ci, token char(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, released tinyint(1) DEFAULT '0', salearn tinyint(1) DEFAULT '0', last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (maillog_id), KEY maillog_datetime_idx (date,time), KEY maillog_id_idx (id(20)), KEY maillog_clientip_idx (clientip(20)), KEY maillog_from_idx (from_address(191)), KEY maillog_to_idx (to_address(191)), KEY maillog_host (hostname(30)), KEY from_domain_idx (from_domain(50)), KEY to_domain_idx (to_domain(50)), KEY maillog_quarantined (quarantined), KEY timestamp_idx (timestamp), FULLTEXT KEY subject_idx (subject) ) ENGINE=InnoDB AUTO_INCREMENT=62896 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0,01 sec)

shawniverson commented 2 years ago

\xF3 on its own is not valid in multi-byte unicode

shawniverson commented 2 years ago

any characters in an email address that aren't ASCII must be encoded UTF8, see https://www.rfc-editor.org/rfc/rfc6532#section-3.2

jarias-irontec commented 2 years ago

@shawniverson what's de correct estructure of table maillog? utf8_unicode_ci or utf8mb4_unicode_ci? Can you tell me the result of the structure of the table and its fields? Thanks!

shawniverson commented 2 years ago

utf8mb4_unicode_ci is preferred if the database supports it. In the case of email addresses, they must conform to standard ASCII and UTF8.