search

philbertphotos / osticket-multildap-auth-plugin

Plugin for OS Ticket that allows for authentication with multiple domains.
GNU General Public License v3.0
28 stars 17 forks source link

500 Internal Server Error (multiple objects in the database matched the query) #66

Closed kapcom01 closed 1 year ago

kapcom01 commented 1 year ago

Hello and thank you for this great plugin!

I am using this plugin for the clients authentication and the official LDAP plugin for the staff. I am on osticket v1.15.8 and PHP v7.3.31.

Everything was working as expected when suddenly I am getting 500 Internal Server Error when some clients try to login. If I delete these clients from osticket then they can login again. But this is not an adequate solution because some of them have already opened tickets and I will lose them.

I should note that I changed the following line in the plugin to $info['email'] = $info['username'] . "@MY-DOMAIN.gr"; because our users in AD don't have email address. https://github.com/philbertphotos/osticket-multildap-auth/blob/dea65810f9a8e8f168992904fd84534bbb0c82ec/multi-ldap/auth.php#L696

/var/log/apache2/error.log:

[Mon Feb 13 09:55:00.091196 2023] [php7:error] [pid 9311] [client 10.1.100.85:58680] PHP Fatal error:  Uncaught ObjectNotUnique: One object was expected; however multiple objects in the database matched the query. In fact, there are 2 matching objects. in /var/www/osticket/include/class.orm.php:1363\nStack trace:\n#0 /var/www/osticket/include/class.orm.php(606): QuerySet->one()\n#1 /var/www/osticket/include/class.orm.php(381): VerySimpleModel::lookup(Array)\n#2 /var/www/osticket/include/class.orm.php(417): VerySimpleModel->get('user', NULL)\n#3 /var/www/osticket/include/class.user.php(1155): VerySimpleModel->__get('user')\n#4 /var/www/osticket/include/plugins/multi-ldap/auth.php(689): UserAccount->getUser()\n#5 /var/www/osticket/include/plugins/multi-ldap/auth.php(582): LDAPMultiAuthentication->authOrCreate('USERNAME_WAS_HERE')\n#6 /var/www/osticket/include/plugins/multi-ldap/auth.php(896): LDAPMultiAuthentication->authenticate('USERNAME_WAS_HERE', 'PASSWORD_WAS_HERE')\n#7 /var/www/osticket/include/class.auth.php(249): ClientLDAPMultiAuthentication->authenticate('USERNAME_WAS_HERE', 'PASSWORD_WAS_HERE')\n#8 /var/www/osticket/login.php(51): AuthenticationBackend::process( in /var/www/osticket/include/class.orm.php on line 1363, referer: http://osticket.MY-DOMAIN.gr/login.php

/var/log/mysql/mysql.log:

230213  9:55:00     52 Connect  root@localhost as anonymous on
                    52 Query    SET  NAMES utf8,  CHARACTER SET utf8, SESSION COLLATION_CONNECTION = utf8_general_ci, SESSION SQL_MODE = '', SESSION TIME_ZONE = SYSTEM
                    52 Query    SET NAMES utf8
                    52 Query    SET AUTOCOMMIT=1
                    52 Init DB  osTicket
                    52 Query    SELECT A1.*, A1.`session_expire` < NOW() AS `is_expired` FROM `ost_session` A1 WHERE A1.`session_id` = 'nvk7m51ranm613q85g1sojfv8r'
                    52 Query    SELECT COUNT(*) FROM (SELECT A1.`id` FROM `ost_config` A1 WHERE A1.`namespace` = 'core') __
                    52 Query    SELECT A1.* FROM `ost_config` A1 WHERE A1.`namespace` = 'core'
                    52 Query    SELECT * FROM ost_plugin ORDER BY name
                    52 Query    SELECT * FROM ost_plugin WHERE
            `id`=1
                    52 Query    SELECT * FROM ost_plugin WHERE
            `id`=2
                    52 Query    SELECT * FROM ost_plugin WHERE
            `id`=4
                    52 Query    SELECT * FROM ost_plugin WHERE
            `id`=3
                    52 Query    SELECT * FROM ost_plugin WHERE
            `id`=5
                    52 Query    SELECT COUNT(*) FROM (SELECT A1.`id` FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.1') __
                    52 Query    SELECT A1.* FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.1'
                    52 Query    SELECT COUNT(*) FROM (SELECT A1.`id` FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.2') __
                    52 Query    SELECT A1.* FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.2'
                    52 Query    SELECT COUNT(*) FROM (SELECT A1.`id` FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.4') __
                    52 Query    SELECT A1.* FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.4'
                    52 Query    SELECT COUNT(*) FROM (SELECT A1.`id` FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.3') __
                    52 Query    SELECT A1.* FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.3'
                    52 Query    SHOW TABLES LIKE 'ost_ldap_sync'
                    52 Query    SELECT version FROM ost_plugin WHERE `name` LIKE '%Multi LDAP%'
                    52 Query    SELECT * FROM `ost_plugin` WHERE `isactive`=1 AND `id`=5
                    52 Query    SELECT COUNT(*) FROM (SELECT A1.`id` FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.5') __
                    52 Query    SELECT A1.* FROM `ost_config` A1 WHERE A1.`namespace` = 'plugin.5'
                    52 Query    SELECT email_id,email,name FROM ost_email email ORDER by name
                    52 Query    SELECT value FROM `ost_config` WHERE `key` = 'default_timezone'
                    52 Query    SELECT value FROM `ost_config` WHERE `key` = 'default_timezone'
                    52 Query    SELECT COUNT(*) FROM (SELECT A1.`id` FROM `ost_config` A1 WHERE A1.`namespace` = 'mysqlsearch') __
                    52 Query    SELECT A1.* FROM `ost_config` A1 WHERE A1.`namespace` = 'mysqlsearch'
                    52 Query    SELECT A1.backend FROM ost_user_account A1 INNER JOIN ost_user_email A2 ON (A2.user_id = A1.user_id) WHERE backend IS NOT NULL  AND (A1.username='USERNAME_WAS_HERE' OR A2.`address`='USERNAME_WAS_HERE')
                    52 Query    SELECT A1.`ticket_id`, A1.`ticket_pid`, A1.`number`, A1.`user_id`, A1.`user_email_id`, A1.`status_id`, A1.`dept_id`, A1.`sla_id`, A1.`topic_id`, A1.`staff_id`, A1.`team_id`, A1.`email_id`, A1.`lock_id`, A1.`flags`, A1.`sort`, A1.`ip_address`, A1.`source`, A1.`source_extra`, A1.`isoverdue`, A1.`isanswered`, A1.`duedate`, A1.`est_duedate`, A1.`reopened`, A1.`closed`, A1.`lastupdate`, A1.`created`, A1.`updated`, A2.`topic_id`, A2.`topic_pid`, A2.`ispublic`, A2.`noautoresp`, A2.`flags`, A2.`status_id`, A2.`priority_id`, A2.`dept_id`, A2.`staff_id`, A2.`team_id`, A2.`sla_id`, A2.`page_id`, A2.`sequence_id`, A2.`sort`, A2.`topic`, A2.`number_format`, A2.`notes`, A2.`created`, A2.`updated`, A3.`staff_id`, A3.`dept_id`, A3.`role_id`, A3.`username`, A3.`firstname`, A3.`lastname`, A3.`passwd`, A3.`backend`, A3.`email`, A3.`phone`, A3.`phone_ext`, A3.`mobile`, A3.`signature`, A3.`lang`, A3.`timezone`, A3.`locale`, A3.`notes`, A3.`isactive`, A3.`isadmin`, A3.`isvisible`, A3.`onvacation`, A3.`assigned_only`, A3.`show_assigned_tickets`, A3.`change_passwd`, A3.`max_page_size`, A3.`auto_refresh_rate`, A3.`default_signature_type`, A3.`default_paper_size`, A3.`extra`, A3.`permissions`, A3.`created`, A3.`lastlogin`, A3.`passwdreset`, A3.`updated`, A4.`id`, A4.`org_id`, A4.`default_email_id`, A4.`status`, A4.`name`, A4.`created`, A4.`updated`, A5.`team_id`, A5.`lead_id`, A5.`flags`, A5.`name`, A5.`notes`, A5.`created`, A5.`updated`, A6.`id`, A6.`pid`, A6.`tpl_id`, A6.`sla_id`, A6.`schedule_id`, A6.`email_id`, A6.`autoresp_email_id`, A6.`manager_id`, A6.`flags`, A6.`name`, A6.`signature`, A6.`ispublic`, A6.`group_membership`, A6.`ticket_auto_response`, A6.`message_auto_response`, A6.`path`, A6.`updated`, A6.`created`, A7.`id`, A7.`schedule_id`, A7.`flags`, A7.`grace_period`, A7.`name`, A7.`notes`, A7.`created`, A7.`updated`, A8.`id`, A8.`object_id`, A8.`object_type`, A8.`extra`, A8.`lastresponse`, A8.`lastmessage`, A8.`created`, B0.`id`, B0.`object_id`, B0.`object_type`, B0.`extra`, B0.`lastresponse`, B0.`lastmessage`, B0.`created`, B1.`id`, B1.`user_id`, B1.`flags`, B1.`address`, B2.`id`, B2.`name`, B2.`state`, B2.`mode`, B2.`flags`, B2.`sort`, B2.`properties`, B2.`created`, B2.`updated` FROM `ost_ticket` A1 LEFT JOIN `ost_help_topic` A2 ON (A1.`topic_id` = A2.`topic_id`) LEFT JOIN `ost_staff` A3 ON (A1.`staff_id` = A3.`staff_id`) LEFT JOIN `ost_user` A4 ON (A1.`user_id` = A4.`id`) LEFT JOIN `ost_team` A5 ON (A1.`team_id` = A5.`team_id`) LEFT JOIN `ost_department` A6 ON (A1.`dept_id` = A6.`id`) LEFT JOIN `ost_sla` A7 ON (A1.`sla_id` = A7.`id`) LEFT JOIN `ost_thread` A8 ON (A8.`object_type` = 'T' AND A1.`ticket_id` = A8.`object_id`) LEFT JOIN `ost_thread` B0 ON (A1.`ticket_id` = B0.`object_id` AND B0.`object_type` = 'C') LEFT JOIN `ost_user_email` B1 ON (A4.`default_email_id` = B1.`id`) JOIN `ost_ticket_status` B2 ON (A1.`status_id` = B2.`id`) WHERE A1.`number` = 'PASSWORD_WAS_HERE' LIMIT 1
                    52 Query    INSERT INTO ost_syslog SET created=NOW(), updated=NOW() ,title='ldap login (USERNAME_WAS_HERE)',log_type='Warning',log='User authenticated on (MY-DOMAIN)',ip_address='10.1.100.85'
                    52 Query    INSERT INTO ost_syslog SET created=NOW(), updated=NOW() ,title='ldap search(USERNAME_WAS_HERE)',log_type='Debug',log='[{\"sAMAccountName\":{\"count\":1,\"0\":\"USERNAME_WAS_HERE\"},\"sn\":{\"count\":1,\"0\":\"SURNAME_WAS_HERE\"},\"givenName\":{\"count\":1,\"0\":\"NAME_WAS_HERE\"},\"displayName\":{\"count\":1,\"0\":\"NAME_WAS_HERE SURNAME_WAS_HERE\"},\"mail\":{\"count\":1,\"0\":\"USERNAME_WAS_HERE@MY-DOMAIN.gr\"},\"telephoneNumber\":null,\"distinguishedName\":{\"count\":1,\"0\":\"CN=NAME_WAS_HERE SURNAME_WAS_HERE,OU=Users,OU=ADDRESS_WAS_HERE,OU=MY-DOMAIN,DC=MY-DOMAIN,DC=gr\"}}]',ip_address='10.1.100.85'
                    52 Query    INSERT INTO ost_syslog SET created=NOW(), updated=NOW() ,title='ldap-search (USERNAME_WAS_HERE)',log_type='Debug',log='{\"username\":\"USERNAME_WAS_HERE\",\"first\":\"NAME_WAS_HERE\",\"last\":\"SURNAME_WAS_HERE\",\"full\":\"NAME_WAS_HERE SURNAME_WAS_HERE\",\"email\":\"USERNAME_WAS_HERE@MY-DOMAIN.gr\",\"phone\":null,\"dn\":\"CN=NAME_WAS_HERE SURNAME_WAS_HERE,OU=Users,OU=ADDRESS_WAS_HERE,OU=MY-DOMAIN,DC=MY-DOMAIN,DC=gr\"}',ip_address='10.1.100.85'
                    52 Query    SELECT A1.* FROM `ost_user_account` A1 WHERE A1.`username` = 'USERNAME_WAS_HERE'
                    52 Query    SELECT A1.`id`, A1.`org_id`, A1.`default_email_id`, A1.`status`, A1.`name`, A1.`created`, A1.`updated`, A2.`id`, A2.`user_id`, A2.`flags`, A2.`address`, A3.`id`, A3.`name`, A3.`manager`, A3.`status`, A3.`domain`, A3.`extra`, A3.`created`, A3.`updated`, A4.`id`, A4.`user_id`, A4.`status`, A4.`timezone`, A4.`lang`, A4.`username`, A4.`passwd`, A4.`backend`, A4.`extra`, A4.`registered` FROM `ost_user` A1 LEFT JOIN `ost_user_email` A2 ON (A1.`default_email_id` = A2.`id`) LEFT JOIN `ost_organization` A3 ON (A1.`org_id` = A3.`id`) LEFT JOIN `ost_user_account` A4 ON (A1.`id` = A4.`user_id`) WHERE A1.`id` = 6
                    52 Query    UPDATE `ost_session` SET `session_data` = 'csrf|a:2:{s:5:\"token\";s:40:\"fbf5fb1f99ecad08da9b7a19e5bc607091c43833\";s:4:\"time\";i:1676274900;}_auth|a:2:{s:5:\"staff\";N;s:4:\"user\";N;}', `session_expire` = (NOW() + INTERVAL 86400 SECOND) WHERE `ost_session`.`session_id` = 'nvk7m51ranm613q85g1sojfv8r' LIMIT 1
                    52 Query    SELECT A1.`id`, A1.`name` FROM `ost_event` A1
                    52 Quit
philbertphotos commented 1 year ago

Here is the issue ... with this and other login plugins the username and email has to be unique it cannot create a user without a email address. While the modification you made is sensible the plugin cant find the email from that because it does not exist in AD. for its point of view it is blank.

I dont know how its finding multiple objects with the same information osticket but it is ... and AD objects should have a email attribute even if its internal only but the plugin syncs this to send users email from OSticket but it can be turn off manually. The error does not make any sense.

You need to find and remove any object that conflicts. Seems you have some house cleaning to do.

kapcom01 commented 1 year ago

I made some tests, and I cannot replicate the state which caused the "multiple objects in the database" error. I am not sure it is related to this plugin or the modifications I made.

There were double records in the user_account table with the same user_id one of which had NULL in every other field except id and user_id. I deleted the ones with the NULL fields and now everything seems to be working OK. I noticed by the way that these records were about users who had been registered before the installation of this plugin.

Anyway I decided to disable the official LDAP plugin and use exclusively this one with the following strategy because client users don't have an email address in AD. The staff members are created manually by the administrator and we just need to check for their password in AD.

image

The error does not make any sense.

I agree. I am closing this issue for now :) Thanks.