strange recurrent SQL error

[vrocheUP]

Hello team,
I have a strange bug with GLPI 9.1. Extract from sql-errors.log :

2016-11-09 08:51:02 [@glpi]
*** MySQL query error:
SQL: INSERT
INTO glpi_ticketfollowups (content,tickets_id,users_id,requesttypes_id,is_private,date,date_creation,date_mod) VALUES ('Bonsoir,\nJe vous réponds pour vous donner la réponse à ma question secrète : xxxx,\nSi je me suis trompé, pourriez vous quand même envoyer l'identifiant sur la boite laposte.net ? \nVous remerciant d'avance,\nCordialement,\nA. François\n\nLe 4 novembre 2016 à 22:22, <helpdesk@univ-poitiers.fr> a écrit :\n\n \n\n','2602','0','0',0,'2016-11-09 08:51:01','2016-11-09 08:51:01','2016-11-09 08:51:01')

As you can see, the value of the field "is_private" is not delimited by simple quotes. There is a new line in sql-erros.log every 5 minutes (this is the time-step I choosed for cron) since #4 of november.

So I have 2 or 3 questions :

1. why is this request malformed ? It's the only one of this sort in sql-errors.log
2. why is GLPI still trying to insert this request after several trials ? Isn't there a time-out for such errors ? Today, I have 1033 trials !
3. I have restarted Apache, and even rebooted the server, thinking of something like a cache. No way, GLPI is still trying to insert this wrong request. Where is it stored ? How can I delete this request ?

Sincerely,
Valéry
config_GLPI.txt

[tomolimo]

Hello,
I tried this with an email with a quote in it with the 9.1/bugfixes branch, and I can't reproduce the issue.
Could you send to me the original email that cause this error? Or attach it to the issue?
Thank you,
Regards,
Tomolimo

[tomolimo]

To answer your question: if you want to cancel the integration of this email, you have to delete it from the mailbox. GLPi is still trying to insert it as it is kept in the mailbox

[tomolimo]

regarding the is_private field value: as the field is an integer, there is no need for the quotes, but I believe the real root cause is the ' in l'identifiant

[vrocheUP]

It seems that the collector mailbox is empty. Trying to investigate further more.

[tomolimo]

you mean on your mail server?

[vrocheUP]

Yes I confirm : the mailbox is empty. GLPI is configured with POP

[vrocheUP]

Sorry, the mailbox is not empty, I was looking in the wrong one. The message was in the user's mailbox corresponding to the "'Reply-to" field, as configured in GLPI. That's pure logic.
Sorry for the inconvenience. I've learned something today.

Regards,
Valéry

[vrocheUP]

Is it possible, in the future, to have quotes really escaped ?

[tomolimo]

they are really :), as I said, I cannot reproduce the issue, mine quotes are escaped.
that's why, if you send to me (or attach) the faulty email, I can try to reproduce the issue

[vrocheUP]

Oh sh... I have deleted the message, forgot your request.
Fortunately, terminal is still open, I have a "screen copy", see it attached
bogus_mail.txt

[remicollet]

Full error message needed, with the SQL error message and the backtrace.

is_private being not escaped IS NOT the issue.

[vrocheUP]

The full error message is at the top of this thread.

[vrocheUP]

Forgot the backtrace.

2016-11-09 10:26:01 [@glpi]
*** MySQL query error:
SQL: INSERT
INTO glpi_ticketfollowups (content,tickets_id,users_id,requesttypes_id,is_private,date,date_creation,date_mod) VALUES ('Bonsoir,\nJe vous réponds pour vous donner la réponse à ma question secrète : xxxxxxx,\nSi je me suis trompé, pourriez vous quand même envoyer l'identifiant sur la boite laposte.net ? \nVous remerciant d'avance,\nCordialement,\nA. François\n\nLe 4 novembre 2016 à 22:22, <helpdesk@univ-poitiers.fr> a écrit :\n\n \n\n','2602','0','0',0,'2016-11-09 10:26:01','2016-11-09 10:26:01','2016-11-09 10:26:01')
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identifiant sur la boite laposte.net ? \nVous remerciant d'avance,\nCordialement' at line 2
Backtrace :
inc/commondbtm.class.php:427
inc/commondbtm.class.php:838 CommonDBTM->addToDB()
inc/mailcollector.class.php:554 CommonDBTM->add()
inc/mailcollector.class.php:1614 MailCollector->collect()
: MailCollector::cronMailgate()
inc/crontask.class.php:803 call_user_func()
front/cron.php:88 CronTask::launch()

[tomolimo]

unfortunately, the attached text file is not a real email: it's an email representation!
could you try on your side to reproduce the issue (with a new email), and then to send to me the faulty email?

[remicollet]

The full error message is at the top of this thread.

It was not.

The important part if "near 'identifiant sur la boite laposte.net"
And indeed l'identificant* create this issue, the body not being escaped.

[tomolimo]

the body not being escaped.

???
For me the following code really does the escape (file: mailcollector.php, function buildTicket, line: 930):

     }

     $tkt = Toolbox::addslashes_deep($tkt);
     return $tkt;
  }

When sending an email with body: Test fu'p then I get Test fu\'p: the quote is really escaped.

[remicollet]

I didn't say the code does not escape the body ;)
I only said it is not escaped to above debug output.

[tomolimo]

ok :)

[remicollet]

In mailcollector, we have $tkt = Toolbox::addslashes_deep($tkt);

But in TicketFollowup::prepareInputForAdd
=> Ticket::setSimpleTextContent
=> Html::entity_decode_deep().
=> httml_entity_decode($value, ENT_QUOTES, "UTF-8"));

So, I think that if a mail contains an encoded quote ("'"), this can raise such issue.

[vrocheUP]

Hello,
Attached is a mail that can't be added to database, same error. It seems that this error occurs only with gmail accounts.
mail.txt

[trasher]

I've just tested adding a followup to a ticket with single quote from a "standard" mailer and from gmail on current 9.1/bugfixes branch (mostly 9.1.3); I cannot reproduce the issue.

[vrocheUP]

Hello team,
My GLPI version is 9.1.2. The problem is still occurring with messages sent from a GMail address :

• when writing to a receiver to create a ticket, everything works as expected, the ticket is created
• if replying to the notification, the collector has an error (same error as before) :
  2017-07-13 15:10:03 [@glpi]
  *** MySQL query error:
  SQL: INSERT
  INTO glpi_ticketfollowups (content,tickets_id,users_id,requesttypes_id,is_private,date,date_creation,date_mod) VALUES ('Cette fois on ne met pas d'apostrophes\n\nLe 13 juillet 2017 à 13:55, <helpdesk@univ-poitiers.fr> a écrit :\n\n \n\n','10727','0','0',0,'2017-07-13 15:10:01','2017-07-13 15:10:01','2017-07-13 15:10:01')
  Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'apostrophes\n\nLe 13 juillet 2017 à 13:55, <helpdesk@univ-poitiers.fr> a ' at line 2
  Backtrace :
  inc/commondbtm.class.php:436
  inc/commondbtm.class.php:852 CommonDBTM->addToDB()
  inc/mailcollector.class.php:554 CommonDBTM->add()
  inc/mailcollector.class.php:1614 MailCollector->collect()
  : MailCollector::cronMailgate()
  inc/crontask.class.php:832 call_user_func()
  front/cron.php:88 CronTask::launch()

Does the last version correct this problem ?

Bonne fin de journée :)

[vrocheUP]

Last comment : when going to "configuration -> receivers" and clicking on the "list of not imported emails" button, GLPI does import the emails with no errors.

[jcheger]

I have the same issue for emails coming from Gmail. Single quotes are HTML encoded (&#39;) and are not backslashed by Toolbox::addslashes_deep, and MySQL receive the INSERT with single quotes (converted to text), which creates an error.

I think it's a non sense to confuse and mix « rich text » and handling HTML encoded quotes. Incomig emails should be correctly handled, whatever users want in their UI. Requesters send emails with their favorite app, and do not care how GLPI is configured.

Here is a quick patch that will patch HTML quotes (inc/mailcollector.class.php:966):
$tkt = str_replace ("&#39;", "'", $tkt);
$tkt = Toolbox::addslashes_deep($tkt);

[trasher]

So, to resume; this issue happens when an ticket is opened without rich text and when there is something like ' in the content.
This is not directly related to the mail collector, same problem happens from the UI.

[lgigant]

Hi,

Not for me, this behavior occurs when a user reply to a ticket previusly opened and when the body of email contain an ' (apostrof) for example "L'Altet" in this case, we have on sql-errors.log the next message

" Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Altet - C/ dels Molins, 4 Apdo. de correos 914\n\n46870 Ontinyent / "

Only occurs on reply to tickets previusly opened, on new tickets this behavior not occur.

https://www.fileformat.info/info/unicode/char/27/index.htm

Regards

[orthagh]

To all participants, (maybe recent one btw, ping @lgigant & @jcheger).

Could you test the following change and let us know the resolution of this issue:
trasher@d9a58d2

[dethorpe]

Hey, I manually applied the changes from commit 0fbb2f0
(we are running 9.2.1 and waiting for 9.2.4 to upgrade) but the issue still occurs. Are there dependencies I am not aware of or perhaps another place this str_replace needs to occur?

2018-06-07 10:33:22 [33@GLPI]
*** MySQL query error:
SQL: INSERT
INTO glpi_tickets (content,name,urgency,requesttypes_id,entities_id,status,impact,priority,users_id_lastupdater,itilcategories_id,date,global_validation,type,date_creation,date_mod) VALUES ('Testing this function's functionality.\n\n','Re: [GLPI #6397] Closing of the Ticket Test ticket, please ignore','3','0','0','1','3','3','33','0','2018-06-07 10:33:21','1','1','2018-06-07 10:33:21','2018-06-07 10:33:21')
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's functionality.\n\n' at line 2
Backtrace :
inc/commondbtm.class.php:504
inc/commondbtm.class.php:928 CommonDBTM->addToDB()
inc/mailcollector.class.php:596 CommonDBTM->add()
front/mailcollector.form.php:81 MailCollector->collect()

[trasher]

Try with latest 9.2/bugfixes

[dethorpe]

I don't understand what you mean. That version is earlier then what I run, 9.2.1 and where this commit will be applied 9.2.4. Sorry if I am missing something obvious...

[orthagh]

@chaoservices, here is some nightly tarballs: https://forge.glpi-project.org/tarballs/

[bigben386]

It doesn't seem that this is completely fixed yet. I upgraded to 9.2.4 and changed back to plain text mode. I have a stuck message with the same error. The body has the word "can't" in it.

[bigben386]

@trasher Any way to reopen this? It is not fixed as of 9.2.4. Currently have 4 emails failing to import. All have a single quote. I looked at the patches for this and noticed that it attempts to remove &#39 but the gmail source shows &#39

[bigben386]

Ok I just confirmed. I changed
str_replace( [''', '&quot'], ["'", "'"], $value

To
str_replace( [''', '&quot'], ["'", "'"], $value
and all my emails imported properly.

[orthagh]

Thanks for your notice, above pr include the changes (#418)

[bigben386]

Thanks @orthagh