Osticket: Email ban issues OsTicket version 1.11

[PeopleInside]

Prerequisites

• Can you reproduce the problem in a fresh installation of the "develop" branch?
  I installed the released version 1.11 from the OsTicket website released yesterday but yes

The issue is on updating from OsTicket 1.10.4 to 1.11 , the issue is present. I will try to test (if will be possible for me) also in a new install. -> tested and able to reproduce also in a new install.

• Do you have any errors in the PHP error log, or javascript console?

[SELECT A1.* FROM `LVti_filter_rule` A1 WHERE A1.`id` = '31' AND A1.`` IS NULL]

Unknown column 'A1.' in 'where clause'<br />
<br />
---- Backtrace ----<br />
#0 (root)/include/mysqli.php(199): osTicket->logDBError('DB Error #1054', '[SELECT A1.* FR...')<br />
#1 (root)/include/class.orm.php(3454): db_query('SELECT A1.* FRO...', true, true)<br />
#2 (root)/include/class.orm.php(3501): MySqlExecutor->execute()<br />
#3 (root)/include/class.orm.php(1991): MySqlExecutor->getArray()<br />
#4 (root)/include/class.orm.php(2035): ModelInstanceManager->{closure}()<br />
#5 (root)/include/class.orm.php(2014): CallbackSimpleIterator->next()<br />
#6 (root)/include/class.orm.php(2023): CallbackSimpleIterator->rewind()<br />
#7 (root)/include/class.orm.php(1695): CallbackSimpleIterator->valid()<br />
#8 (root)/include/class.orm.php(1705): CachedResultSet->fillTo(9223372036854775807)<br />
#9 (root)/include/class.orm.php(1328): CachedResultSet->asArray()<br />
#10 (root)/include/class.orm.php(1351): QuerySet->all()<br />
#11 (root)/include/class.orm.php(606): QuerySet->one()<br />
#12 (root)/include/class.filter.php(203): VerySimpleModel::lookup(Array, NULL)<br />
#13 (root)/include/class.filter.php(199): Filter->getRuleById('31')<br />
#14 (root)/scp/banlist.php(28): Filter->getRule('31')<br />
#15 {main}

• Did you check the osTicket forums? yes
• Did you perform a cursory search to see if your bug or enhancement is already reported? yes

For more information on how to write a good bug report

Description

After upgrading from the previous version of OsTicket i see that banned email ... so email should not open a ticket... was not opening a ticket but email was flagged as read on my email client so i start to miss read emails.

I checked the OsTicket and ban list was empty. In the previous version (i restored now) two email address was present in the ban list. If an email was sent from this address no ticket was open and also email are not flagged as read (correct workflow). After the update the list of ban email was empty and a message banner on top say the system ban list filter service was deactivate. A link forward to filters that has no sense for me. Also if i create a filter for refuse email from the ban address mail are flagged as read on my email client when arrive and is processed by OsTicket.

Steps to Reproduce

The issue is present on migration from previous version to the current. I have no steps to write here.

Expected behavior: [What you expected to happen]

Ban list has the same banned email address as in the previous version and also i expect to do not see a banner that say something that has no sense... ban list filter deactivated and forward me to the filter list where i have only custom filter. There is no option to activate ban system filter list.

Actual behavior: [What actually happened]

Email list was empty after the upgrade. Also if a banned email is elaborated by OsTicket email is flagged as read. This should not happen if the email is banned, exuded from Osticket.

Versions

Admin panel -> Dashboard -> Information which also additionally gives you information about you server.

Web Server Software | Apache
-- | --
Versione MySQL | 5.6.43
Versione PHP | 7.3.1

TESTED ALSO WITH PHP 7.2

Also, please include the OS and what version of the OS you're running. As well as your browser and browser version.

Centos 7.X , Firefox 65.0

[PeopleInside]

Tested on a clean install of OsTicket version 1.11

Issue: Reject ticket flag email as read. This should not happening, i need OsTicket ignore some email address and leave messages in the status to read because this email will be managed manually.

Steps for reproduce.

1. Installed OsTicket and setup with a [email protected] email address
2. Activated the Fetching Email via IMAP or POP
3. Created an email address that should be ignored from OsTicket [email protected]
4. In the filter section i create a filter: filter name ban banned email address, execution order 1, filter status active, target channel emails, filters rules : user / email address match any equal to [email protected] , filter action: reject ticket
5. filter saved successfully.

(A) I sent an email from banned address to support
(B) I sent an email from another address to support.

What expect is:

• first email (A) will stay to read and no ticket is opened. I configured address A in thunderbird where i manage this email. I need open ticket if the email sender is not [email protected] and not open ticket (forum email) if the email is [email protected] - this was working in the previous OsTicket. Is not working on V 1.11
• Ticket is been created for email (B), no issue here, all work as expected.

osTicket Version | v1.11 (d4e240b)     —  Up to date
-- | --
Web Server Software | Apache
MySQL Version | 5.6.43
PHP Version | 7.3.1

gdlib | Used for image manipulation and PDF printing
imap | Used for email fetching
xml | XML API
xml-dom | Used for HTML email processing
json | Improves performance creating and processing JSON
mbstring | Highly recommended for non western european language content
phar | Highly recommended for plugins and language packs
intl | Highly recommended for non western european language content
fileinfo | Used to detect file types for uploads
APCu | Improves overall performance
Zend Opcache | Improves overall performance

cgi.fix_pathinfo | "1" is recommended if AJAX is not working
date.timezone | Europe/Rome

Schema | test_os (localhost)
Schema Signature | 00c949a623b82848baaf3480b51307e3
Space Used | 2.58 MiB
Space for Attachments | 0.03 MiB
Timezone | CET                      (Interpreted as Europe/Berlin)

I tried to add [email protected] as email and create a filter system email but the issue is the same.
As i create a filter as system email now i can see [email protected] in the ban list but as soon i click on this email address on ban list system go in error.

Let's try if the same issue occurs with PHP 7.2
Confirmed, the issue still be present with the supported PHP 7.2

osTicket Version | v1.11 (d4e240b)     —  Up to date
-- | --
Web Server Software | Apache
MySQL Version | 5.6.43
PHP Version | 7.2.14

[JediKev]

@PeopleInside

Here you go:
https://github.com/osTicket/osTicket/pull/4706

Cheers.

[PeopleInside]

Hi @JediKev this fix should avoid banned email is marked as read?
Also why updating from V 1.10.04 to 1.11 delete all ban email in the ban list? is this normal?

Now i am back to V 1.10.04 i really want update to the last version 1.11 but is important i can be able to ban an email address and see mails not flagged as read by OsTicket for those email. This fix will do this?

[JediKev]

@PeopleInside

No, this addresses the system error and adding new bans.

Cheers.

[PeopleInside]

So this will keep the issue that banned email will be flagged as read on my email client?
If so i cannot update.

With OsTicket 1.10.04 when i have banned an email i can see unread mail on my Thunderbird. With V 1.11 after some minutes ticket are not opened but email are flagged as read so i cannot manage well from Thunderbird.

Seems in version 1.11 banned email are ignored for opening ticket but are flagged as read.

[PeopleInside]

Not fully fixed.

[x] --- If you click an email address in the ban list -> error occur. Error has been pasted in precedent post.
[x] --- Banned email prevent the ticket to be opened but email is flagged as read. This is an issue that need to be resolved.

[JediKev]

@PeopleInside

I updated my pull request to fix the click on address in banlist error. Does not fix read issue.

Cheers.

[PeopleInside]

Hi @JediKev i can confirm the last fix, fix the click on ban email list! 👍 https://github.com/osTicket/osTicket/commit/43f11d1368f188d494e645bb0e262a5b90156cad
So just stay one issue 👍 to resolve, the read issue :D

[PeopleInside]

Hi, almost all problem solved. Before I can be able to upgrade to V 1.11 i really need fix the banned email read issue.

I think i am unable to fix this as i cannot understand the code.

I will monitor this issue in this week. Thank you very much for the help!

PS: I cannot also upgrade to version 1.10.05 because give me errors, maybe my PHP is superior to the minimum required so is not supported by 1.10.05 https://forum.osticket.com/d/93461-where-can-i-download-version-v1-10-4/3

[PeopleInside]

@JediKev any idea on what can be the cause in OsTicket 1.11 that flag banned email that should be ignored as read? Hope you with maybe the help of other team members will be able to help to fix this last issue... once resolved i will be able to upgrade so also have a more secure OsTicket version. For me is really important to keep always software updated.

I also installed a clean Osticket 1.11 just for test the fix so when this last important issue will be solved i can upgrade :D and stay relaxed ^_^

Thanks again for your great help!

[JediKev]

@PeopleInside

According to the code, it's always been like this.

Develop Branch (1.10.x):

Upon fetching the mail we run through the createTicket() function. The below lines say if createTicket() returns true then continue setting the flags to \\Seen (or Read).
https://github.com/osTicket/osTicket/blob/develop/include/class.mailfetch.php#L845-L853

In the createTicket() function, if the email is banned we return true so it will be marked as Read.
https://github.com/osTicket/osTicket/blob/develop/include/class.mailfetch.php#L654-L659

This code hasn't been touched in 5-7 years so it's safe to assume this is how it's always been with 1.10.x (and now v1.11), i.e. this is not an issue rather expected behavior.

Cheers.

[PeopleInside]

Hi @JediKev thanks for the reply. I understand what you mean... but i start to see the issue when i updated to V 1.11 with mine version 1.10.04 i have not this issue. If i ban an email i want this email is ignorated by OsTicket because i need manage in different way.

Can i fix this in my install just changing https://github.com/osTicket/osTicket/blob/develop/include/class.mailfetch.php#L654-L659 to false? Will this should work for fix the read issue (for me) on banned email?

[JediKev]

@PeopleInside

Yes, just change to false and it should not mark the mail as read. Doing this will not move the email to a folder though as t all in the same function.

Cheers.

[PeopleInside]

I will see if this can fix the issue. Difficult here explain the logic but i ban an email for made OsTicket not process it i need manage this kind email differently. Thanks.

[PeopleInside]

Hi @JediKev - sorry it's me again.

I upgraded to OsTicket v1.11 and changed the line https://github.com/osTicket/osTicket/blob/develop/include/class.mailfetch.php#L658 to false.

This seems to work: banned email are not flagged as read but i receive as soon the mail is processed a MailFetcher error: Excessive errors processing emails for xxx Please manually check the inbox. and this email are repeated every time mail fetcher checks for new emails.

Maybe set false is not the solution? How can avoid this email warning?
Thanks

At the moment for fix this issue i commented some lines:
$this->log($warn); 865
return $msgs; 870
$ost->logWarning(_S('Mail Fetcher'), $error); 875

[JediKev]

@PeopleInside

I'm thinking you can just move the banned mail to a separate folder after it has been processed the first time. This way the system doesn't try fetching it again.

Cheers.

[PeopleInside]

Move a banned email from inbox to another folder will not help me to manage new email well as is not more in inbox so maybe are not notified to me on mobile and on PC but also not clear how to do that... you can choose to move a mail when is processed in the email like [email protected] but this will work for all email not only banned.

I want just ignore banned email that should be not processed by OsTicket mean no flagged as read and no ticket opened.

Changing line https://github.com/osTicket/osTicket/blob/develop/include/class.mailfetch.php#L654-L659 to false and comment lines

$this->log($warn); 865
return $msgs; 870
$ost->logWarning(_S('Mail Fetcher'), $error); 875

seems to solve but what i am doing? Now if a mail fetch error occurs (other than email ban) i am not more alerted? I think is not a really good fix.

[JediKev]

@PeopleInside

This is just how the system works...if an email is in the Inbox, marked as Unread, and within the amount of emails you're allowed to fetch (Emails per fetch setting), the system will try to process it. There is no way to have an email in your mailbox that is unread and not be touched by osTicket.

Cheers.

[PeopleInside]

This is just how the system works...if an email is in the Inbox, marked as Unread, and within the amount of emails you're allowed to fetch (Emails per fetch setting), the system will try to process it. There is no way to have an email in your mailbox that is unread and not be touched by osTicket.

I know this, the issue is that email should be not processed if is banned.
Currently ban exclude only from a ticket to be opened.

I'm thinking you can just move the banned mail to a separate folder after it has been processed the first time. This way the system doesn't try fetching it again.

I can say to OsTicket to move just a banned email? How?

[ntozier]

email should be not processed if is banned.

As a side note: my understanding of the way email works is that email has to be read (and is marked read by the server) to find out what the email address of the email is to find out if it should be processed.

[PeopleInside]

Ok maybe it's me i should consider email managed by the OsTicket cannot be managed also from a email client. I will try to create another email address and try to change how the workflow is working on my side now. Thanks for the help, have a great time. :) Love OsTicket :)

[ntozier]

That would be my recommendation, and its what we did where I work. We created a itteam@ address for things that need to be handled by a human, and tickets@ for the ticket system. It seems to work pretty good.