SQL-Error in admin_ticket when kb-articles have been suggested

[morphisto.zet]

Script URL: admin/admin_ticket.php
Version of script: 3.2.5
Hosting company: own server
URL of phpinfo.php: --
URL of session_test.php: --
What terms did you try when SEARCHING for a solution:
searched for SQL-Errors here in this forum

Write your message below:

If a ticked had articles listed in the articles field of the tickets-table, that had been removed, the admin_ticket.php only shows the message and break with an SQL-Error nessage after this without loading the rest of the site (notes, answers, params-block, etc.).

When the articles have been removed manualy from the DB the admin_ticket.php was loaded correctly.

I'll upgrade to HESK 3.3.1 this week and will test this error there as well

[morphisto.zet]

Screenshot of the error-handling:

[Klemen]

This doesn't happen in my tests. It also shouldn't happen because Hesk just searches for articles by ID and lists only those found in the database.

What error message do you get if you turn Debug mode ON in Admin > Settings > Help desk > under "Features" then try again?

[morphisto.zet]

Hi Klemen,

Debug mode says that SQL cannot be executed:

SQL kann nicht ausgeführt werden.: SELECT `id`,`subject` FROM `hesk_kb_articles` WHERE `id` IN (41,143,207,45,339,180,225,80,150,295,259,139,326,192,195,350,190,85,184,86,117,146,239,253,43,56,49,92,28,65,93,240,201,347,161,75,219,457,211,142,30,136,456,181,68,40,353,76,109,208,110,31,173,232,472,60,63,62,183,51,272,64,124,37,175,66,116,185,137,149,)

MySQL antwortet:
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 ')' at line 1

Upgraded to HESK 3.3.1: same error.

[morphisto.zet]

Tested the SQL-Statement. The last "," produced the error.

Since this is one of our oldest tickets (from 21.04.2015): maybe an old version of hesk stored the articles with an ending ,?

I've edited the admin_ticket.php to remove the needless ",":

Code: Select all

// Get article info from the database
                if (substr($ticket['articles'], -1) == "," )  {
                    $articles = hesk_dbQuery("SELECT `id`,`subject` FROM `".hesk_dbEscape($hesk_settings['db_pfix'])."kb_articles` WHERE `id` IN (".preg_replace('/[^0-9\,]/', '', substr($ticket['articles'], 0, -1)).")");
                }
                else{
                    $articles = hesk_dbQuery("SELECT `id`,`subject` FROM `".hesk_dbEscape($hesk_settings['db_pfix'])."kb_articles` WHERE `id` IN (".preg_replace('/[^0-9\,]/', '', $ticket['articles']).")");
                }

[Klemen]

This probably happened because you are suggesting too many articles to your customers (no one will bother to read all those; it's better to suggest a few and those relevant) and probably hit the 255 chars limit for the suggested articles list in the database.