Server Info:
osTicket Version v1.10.4 (035fd0a) — v1.10.6 is available
Web Server Software Apache
MySQL Version 5.7.25
PHP Version 5.6.38-2+ubuntu16.04.1+deb.sury.org+1

I currently have two users in the same department with the same access & permissions unable to see tickets in their "My Ticket" queue. Next to the queue, it displays a number count, stating that they have tickets in there.

When you click on it, the queue is completely blank.

Looking in the "Open" queue, they can find the tickets that are assigned to them and should be in the "My Tickets" queue.

I've triple checked their permissions and privileges and looked at the tickets to make sure they weren't transferred or assigned to someone else or another department.

Everything checks out fine.

I found the following error in the logs:

[SELECT COUNT(*) FROM (SELECT A1.`ticket_id` FROM `ost_ticket` A1 JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`) WHERE (A1.`staff_id` = 48 OR (A1.`staff_id` = 0 AND A1.`team_id` IN ())) AND A2.`state` = 'open' AND ((A2.`state` = 'open' AND A1.`staff_id` = 48) OR A1.`dept_id` IN ('16'))) __] 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 '))) AND A2.`state` = 'open' AND ((A2.`state` = 'open' AND A1.`staff_id` = 48) OR' at line 1

 ---- Backtrace ----
 #0 (root)/include/mysqli.php(204): osTicket->logDBError('DB Error #1064', '[SELECT COUNT(*...')
 #1 (root)/include/class.orm.php(3136): db_query('SELECT COUNT(*)...', true, true)
 #2 (root)/include/class.orm.php(3192): MySqlExecutor->execute()
 #3 (root)/include/class.orm.php(2647): MySqlExecutor->getRow()
 #4 (root)/include/class.orm.php(1193): MySqlCompiler->compileCount(Object(QuerySet))
 #5 (root)/include/staff/tickets.inc.php(240): QuerySet->count()
 #6 (root)/scp/tickets.php(495): require_once('/var/www/ostick...')
 #7 {main}

I checked inside the database to see who Staff ID #48 was, based on above error. That staff ID corresponds to one of the users who has the "My Ticket" queue issue.

Do any of the devs know what the underlying problem is here? Any ideas? Anyone else experience this before.

Is this a coding issue or is this a MySQL issue? I see it says the syntax is wrong.... but this error is only occurring on two users at the moment. All other users in my system have functioning "My Tickets" queues.

@Pietro_Aretino

The issue is that there are no Team IDs populated in the query.

It states ... check for the right syntax to use near '))) AND A2.state' .... If you look for that part in the query it is this part (A1.staff_id = 0 AND A1.team_id IN ())) AND A2.state. As you can see there are no team_ids to match against so it fails with a syntax error. Why this occurs I have no idea as I don’t have access to your system.

I would recommend upgrading to the latest maintenance release of v1.10.6 to see if we addressed this issue already. You should upgrade regardless simply because v1.10.4 is vulnerable to SQL injection which is end game for your system, data, and database

If the issue still occurs with v1.10.6 then you should definitely do some debugging. Maybe see if they belong to a team or multiple teams, etc.

Cheers.

    KevinTheJedi

    Thank you sir.

    It's a production intranet server at the moment, heavily customized.

    I'm solo managing it in its entirety.

    I am slowly but surely going to be migrating it to a MariaDB server with the newest version of osTicket 1.12.

    Just stuck on it for the moment.

    I will try playing with the user's team permissions/privileges.

    Thank you for spotting that. Much appreciated sir.

      Write a Reply...