Fatal error: Uncaught mysqli_sql_exception from mailwatch_quarantine_report.php

[dbworth]

Issue summary

I try to run the PHP script to generate a Quarantine report.

It fails with:

Fatal error: Uncaught mysqli_sql_exception: 
Expression #1 of ORDER BY clause is not in SELECT list, 
references column 'mailscanner.a.date' which is not in SELECT list; 
this is incompatible with DISTINCT in /var/www/html/MyMailWatchSystem/functions.php:1041

The full output is below.

Steps to reproduce

1. MailWatch has 1 user called "MyAdminUser"

2. In the User Manager, I tick the box "Send Daily Report" and set the recipient to an email address e.g. [email protected]

3. From the command line I run the script: $ php /usr/local/bin/mailwatch_quarantine_report.php

Expected result

I expect the script would finish with no error.

The "PHP Deprecated" warnings can be ignored, as mentioned here: https://github.com/mailwatch/MailWatch/issues/1176

Actual result

An exception is thrown:

 === Generating report for MyAdminUser type=A

 ==== Recipient e-mail address is [email protected]

PHP Deprecated:  Function get_magic_quotes_gpc() is deprecated in /var/www/html/MyMailWatchSystem/functions.php on line 1106

Deprecated: Function get_magic_quotes_gpc() is deprecated in /var/www/html/MyMailWatchSystem/functions.php on line 1106

 ==== Building list for 

PHP Fatal error:  Uncaught mysqli_sql_exception: Expression #1 of ORDER BY clause is not in SELECT list, references column 'mailscanner.a.date' which is not in SELECT list; this is incompatible with DISTINCT in /var/www/html/MyMailWatchSystem/functions.php:1041

Stack trace:

#0 /var/www/html/MyMailWatchSystem/functions.php(1041): mysqli->query()
#1 /var/www/html/MyMailWatchSystem/quarantine_report.inc.php(465): dbquery()
#2 /var/www/html/MyMailWatchSystem/quarantine_report.inc.php(374): Quarantine_Report::return_quarantine_list_array()
#3 /var/www/html/MyMailWatchSystem/quarantine_report.inc.php(321): Quarantine_Report::send_reports_for_user()
#4 /usr/local/bin/mailwatch_quarantine_report.php(48): Quarantine_Report->send_quarantine_reports()
#5 {main}

  thrown in /var/www/html/MyMailWatchSystem/functions.php on line 1041

Fatal error: Uncaught mysqli_sql_exception: Expression #1 of ORDER BY clause is not in SELECT list, references column 'mailscanner.a.date' which is not in SELECT list; this is incompatible with DISTINCT in /var/www/html/MyMailWatchSystem/functions.php:1041

Stack trace:

#0 /var/www/html/MyMailWatchSystem/functions.php(1041): mysqli->query()
#1 /var/www/html/MyMailWatchSystem/quarantine_report.inc.php(465): dbquery()
#2 /var/www/html/MyMailWatchSystem/quarantine_report.inc.php(374): Quarantine_Report::return_quarantine_list_array()
#3 /var/www/html/MyMailWatchSystem/quarantine_report.inc.php(321): Quarantine_Report::send_reports_for_user()
#4 /usr/local/bin/mailwatch_quarantine_report.php(48): Quarantine_Report->send_quarantine_reports()
#5 {main}

  thrown in /var/www/html/MyMailWatchSystem/functions.php on line 1041

Installation

Version and method

• MailWatch stable 1.2.16
• Installed from tarball
• Fresh install

Server configuration

• Ubuntu 20.04.2
• PHP 7.4.3
• MySQL 8.0.23-0
• Postfix 3.4.13
• Apache 2.4.41

Client configuration

• Operation System: N/A
• Browser: N/A

[dbworth]

The SQL query is generated by the function get_report_sql() here: https://github.com/mailwatch/MailWatch/blob/1.2/mailscanner/quarantine_report.inc.php#L165

The query is:

SELECT DISTINCT
a.id AS id,
DATE_FORMAT(timestamp,'%%d/%%m/%%y <br/>%%H:%%i:%%s') AS datetime,
a.timestamp AS timestamp,
a.to_address AS to_address,
a.from_address AS from_address,
a.subject AS subject,
a.token AS token,
CASE
 WHEN a.virusinfected>0 THEN 'Virus'
 WHEN a.nameinfected>0 THEN 'Bad Content'
 WHEN a.otherinfected>0 THEN 'Infected'
 WHEN a.ishighspam>0 THEN 'Spam'
 WHEN a.issaspam>0 THEN 'Spam'
 WHEN a.isrblspam>0 THEN 'Spam'
 WHEN a.spamblacklisted>0 THEN 'Blacklisted'
 WHEN a.isspam THEN 'Spam'
 WHEN a.ismcp>0 THEN 'Policy'
 WHEN a.ishighmcp>0 THEN 'Policy'
 WHEN a.issamcp>0 THEN 'Policy'
 WHEN a.mcpblacklisted>0 THEN 'Policy'
 WHEN a.isspam>0 THEN 'Spam'
 ELSE 'UNKNOWN'
END AS reason
FROM
 maillog a
WHERE
 a.quarantined = 1
AND
 a.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY a.date DESC, a.time DESC

[dbworth]

I think the solution is to change the SQL query to use GROUP BY instead of SELECT DISTINCT.

I couldn't think why DISTINCT was used, maybe someone was being extra cautious. Therefore my solution was to simply remove it, so line 168 becomes simply:

SELECT

[dbworth]

Here are a few other related things to fix:

• Put a try-catch block around calls to dbquery(), to catch any exceptions thrown by SQL.

• Fix discrepancy, where user settings says "Send Daily Report" whereas conf.php can have QUARANTINE_REPORT_DAYS equal to any number. The "daily report" received via email is not a daily report with 24 hours of quarantined messages.

• The report received by email has gray-colored rows. It would be nice to add the colors e.g. dark red, light red, black.

If there's any consensus, I'm happy to submit a PR.

[endelwar]

@dbworth yes, please, I'll be glad to review your PRs!

[greenaar]

Just hit this in 1.2.21, removing DISTINCT worked for sending the email.