SQL Error when updating a form issue that has a single quote character in name

[keguira]

Describe the bug

No user complained but i may have miss something and i'm doing a review of the logs.

I can see a lots of error like this one :

SQL: UPDATE `glpi_plugin_formcreator_issues` SET `status` = '101', `name` = 'Demande d'accès - APP', `comment` = '<h2>Modification d'accès</h2>
.....
 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 'accès - APP', `comment` = '<h2>Modification d'' at line 1

the single quote generate an error and i can see that the single quote is not html-escaped or backslahed.

To Reproduce Steps to reproduce the behavior:

1. Create a form with a target that has a "ticket title" and a description containing a single quote in it.
2. Use it

Expected behavior No SQL error should be generated

Screenshots

GLPI / Plugins (please complete the following information):

• GLPI 10.0.10

• list of all plugins :

  timelineticket Name: Chronologie des tickets Version: 10.0+1.1 State: Enabled
  Install Method: Marketplace behaviors Name: Comportements Version: 2.7.2 State: Enabled
  Install Method: Marketplace escalade Name: Escalades Version: 2.9.2 State: Enabled
  Install Method: Marketplace formcreator Name: Form Creator Version: 2.13.7 State: Enabled
  Install Method: Marketplace glpiinventory Name: GLPI Inventory Version: 1.3.1 State: Enabled
  Install Method: Marketplace oauthimap Name: Oauth IMAP Version: 1.4.3 State: Enabled
  Install Method: Marketplace mreporting Name: Plus de rapports Version: 1.8.5 State: Enabled
  Install Method: Marketplace

[btry]

Hi

I think this error occurs when a ticket is updated. You will find a backtrace with the error. Please show it. I need it to locate where the faulty code. I think it will be easy to fix.

[btry]

The PR currently uses a better method for quote escaping; but I'm not sure I worked in the right place. I'm waiting for your backtrace.

[keguira]

Hi

I think this error occurs when a ticket is updated. You will find a backtrace with the error. Please show it. I need it to locate where the faulty code. I think it will be easy to fix.

sorry, it was a bad copy past, here is the full backtrace :

  Backtrace :
  src/DBmysql.php:1468
  src/CommonDBTM.php:686                             DBmysql->update()
  src/CommonDBTM.php:1675                            CommonDBTM->updateInDB()
  marketplace/formcreator/hook.php:607               CommonDBTM->update()
  src/Plugin.php:1665                                plugin_formcreator_hook_update_ticketvalidation()
  src/CommonDBTM.php:1680                            Plugin::doHook()
  front/commonitilvalidation.form.php:113            CommonDBTM->update()
  front/ticketvalidation.form.php:40                 include()
  public/index.php:82                                require()
  {"user":"1237@glpi-server"}

[btry]

I updated the above PR. You may try it and tell if the issue is solved.

It appears that the error occurs when the someone updates the validation of a ticket.

[keguira]

Ok thank you, i'll try to test it this week-end or next week

[keguira]

Ok, tested this morning. I do not have the SQL error anymore But I cannot see anything in the app that should the diff. As i'm not fully aware of the domain impact, what is the purpose of this query and what does it handle / change ? I would also validate that's it's doing it correctly and communicate adequately to the end users.

[btry]

The query updates the status of the issue associated to a ticket when this ticket gets a validation status update.

[btry]

More details : the query is created by updating more columns than needed. Unfortunately, some values were not properly escaped. The fix changes the query to update only the status, which is enough in the context of a ticket validation update.

[keguira]

ok, i do not see anything wrong. I've created multiple tickets and my status are ok

[btry]

Great, I merge the fix, it will available in the next release. Thank you for your feedback.