Suitecrm: problem email_cache update (crash import email job)

[bmwtourer]

import email exit from process, process return to normal with subsequent jobs

found and error in the log file indicating "String or binary data would be truncated."
The whole log entry is below.

Wed Nov 14 07:42:39 2018 [5072][1 ][FATAL] UPDATE email_cache SET subject = '=?utf-8?Q?Mivision_update:_270,000_Australians_To_Experience_Vision?= =?utf-8?Q?_Loss_by_2020_|_Key-Hole_Punch_Breakthrough_For_Corneal?= =?utf-8?Q?_Transplants_|_Doctor?= =?utf-8?Q?=e2=80=99s_Mental_Health_Depends_On_Changes_To?= =?utf-8?Q?_Mandatory_Reporting_Law...and_much_more?=',message_id = '[e-mail]',imap_uid = '33339',msgno = '529',recent = '0',flagged = '0',answered = '0',deleted = '0',seen = '1',draft = '0' WHERE ie_id = 'be0aa142-77a7-5d41-5a09-560d8521ea1d' AND mbox = 'Deleted Items' AND imap_uid = '33339' Query Failed:UPDATE email_cache SET subject = N'=?utf-8?Q?Mivision_update:_270,000_Australians_To_Experience_Vision?= =?utf-8?Q?_Loss_by_2020_|_Key-Hole_Punch_Breakthrough_For_Corneal?= =?utf-8?Q?_Transplants_|_Doctor?= =?utf-8?Q?=e2=80=99s_Mental_Health_Depends_On_Changes_To?= =?utf-8?Q?_Mandatory_Reporting_Law...and_much_more?=',message_id = N'[e-mail]',imap_uid = '33339',msgno = '529',recent = '0',flagged = '0',answered = '0',deleted = '0',seen = '1',draft = '0' WHERE ie_id = N'be0aa142-77a7-5d41-5a09-560d8521ea1d' AND mbox = N'Deleted Items' AND imap_uid = '33339'::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]String or binary data would be truncated.

I checked the length of the subject text line to be updated and it shows 287 characters while the fields accepts 255.
Note that I am using SuiteCRM 7.8.13 running on IIS, php 7.2 and MSSQL server.

Possible Fix

control the length of the field to be updated, with a simple left() function in the sql query to prevent from such error like :
UPDATE email_cache SET subject = left('=?utf-8?Q?Mivision_u...',255)

Steps to Reproduce

might be difficult, I do not know email client that will permit more than 250 char.

I know this is stupid from the sender (or a bug on their end) but it crashes the import email process and I believe we should protect this from happening.

[Mausino]

@bmwtourer should you create pull request that will review by @salesagility team and we also test it that is will works or not :)

[bmwtourer]

@Mausino "create a pull request" ? sorry what do you mean?

[Mausino]

@bmwtourer
if you create pull request with fix https://help.github.com/articles/creating-a-pull-request/

Everybody can look on it and test if your solution is working https://docs.suitecrm.com/community/contributing-code/test-pull-requests/

[bmwtourer]

ok but I did not modify my system, I took the information straight from the error log reported above...
and debugged the query whisch is trying to
UPDATE email_cache SET subject = '_=?utf-8?Q?Mivision_update:_270,000_Australians_To_Experience_Vision?= =?utf-8?Q?Loss_by_2020|_Key-Hole_Punch_Breakthrough_For_Corneal?= =?utf-8?Q?Transplants|_Doctor?= =?utf-8?Q?=e2=80=99s_Mental_Health_Depends_On_Changes_To?= =?utf-8?Q?_Mandatory_Reporting_Law...and_much_more?=_', ...
Notice the set subject assignation the whole text is 281 char... while the sql table field definition is set to 255.
My sql knowledge proposed the left function to control external entries such as email to crash our system.

I do not know where the sql query reside in the system... I do not mind testing it if you point me to the right file containing the query...
I would even propose to escape all database updates from external source to make our system solid... not permitting external inputs to crash our system.

[pgorod]

I suggest, instead of trimming the string in the SQL with Left(s, 255), doing it earlier, in PHP, and trim it a bit smaller to 250 or something, and add an ellipsis ... to indicate that it was truncated.

So really long string ending in glory would be trimmed to really long string ending in...

[bmwtourer]

good idea doing it like this will keep the database compatibility.
I like the ellipsis idea too!

[pgorod]

@bmwtourer these issues you're closing, it's because they are no longer a problem on the newer versions?