Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

My 5 last forms (requester) and My 5 last forms (validator) are not displayed. #3242

Closed
miqonee opened this issue Apr 5, 2023 · 4 comments
Closed

My 5 last forms (requester) and My 5 last forms (validator) are not displayed. #3242

miqonee opened this issue Apr 5, 2023 · 4 comments

Comments

@miqonee
Copy link

miqonee commented Apr 5, 2023

Describe the bug

If we go to the forms to see my latest forms from the self-service account, instead of "My 5 last forms (requester)" and "My 5 last forms (validator)" we see the message "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 '46,17)) AND ( glpi_plugin_formcreator_formanswers.entities_id IN ('6') ...' at line 11" and "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 '46,17)) AND ( glpi_plugin_formcreator_formanswers.entities_id IN ('6') ...' at line 15" respectively.
Moreover, this window is normally shown when logging in from the super-admin, by changing the rights, I realized that the error is affected by the parameter for changing general settings, self-service does not have it, and if you issue that window, it is displayed, but as you understand this is not an option

Expected behavior
I expect everything should work like in the screenshot
image

Screenshots
at the moment what self-service sees
image

GLPI / Plugins (please complete the following information):
formcreator Name: Form Creator Version: 2.13.5 State: Enabled
Install Method: Marketplace

  • GLPI 10.0.6

Additional context
Debug: SQL REQUEST

SELECT DISTINCT `glpi_plugin_formcreator_formanswers`.`id` AS id, 'm.goloshchanov' AS currentuser, `glpi_plugin_formcreator_formanswers`.`entities_id`, `glpi_plugin_formcreator_formanswers`.`is_recursive`, `glpi_plugin_formcreator_formanswers`.`name` AS `ITEM_PluginFormcreatorFormAnswer_1`, `glpi_plugin_formcreator_formanswers`.`id` AS `ITEM_PluginFormcreatorFormAnswer_1_id`, `glpi_plugin_formcreator_formanswers`.`id` AS `ITEM_PluginFormcreatorFormAnswer_2`, `glpi_plugin_formcreator_formanswers`.`id` AS `ITEM_PluginFormcreatorFormAnswer_2_id`, `glpi_plugin_formcreator_formanswers`.`request_date` AS `ITEM_PluginFormcreatorFormAnswer_6`, `glpi_plugin_formcreator_formanswers`.`status` AS `ITEM_PluginFormcreatorFormAnswer_8`, `glpi_users_requester_id`.`name` AS `ITEM_PluginFormcreatorFormAnswer_4`, `glpi_users_requester_id`.`realname` AS `ITEM_PluginFormcreatorFormAnswer_4_realname`, `glpi_users_requester_id`.`id` AS `ITEM_PluginFormcreatorFormAnswer_4_id`, `glpi_users_requester_id`.`firstname` AS `ITEM_PluginFormcreatorFormAnswer_4_firstname`
FROM `glpi_plugin_formcreator_formanswers`
LEFT JOIN `glpi_users` AS `glpi_users_requester_id` ON (`glpi_plugin_formcreator_formanswers`.`requester_id` = `glpi_users_requester_id`.`id` )
WHERE `glpi_plugin_formcreator_formanswers`.`requester_id` = 11 OR (`glpi_plugin_formcreator_formanswers`.`users_id_validator` = 11 OR `glpi_plugin_formcreator_formanswers`.`groups_id_validator` IN (,46,17)) AND ( `glpi_plugin_formcreator_formanswers`.`entities_id` IN ('6') OR (`glpi_plugin_formcreator_formanswers`.`is_recursive`='1' AND `glpi_plugin_formcreator_formanswers`.`entities_id` IN (0)) ) AND ( (`glpi_users_requester_id`.`id` = '11') )
ORDER BY `ITEM_PluginFormcreatorFormAnswer_6` DESC

Have errors "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 '46,17)) AND ( glpi_plugin_formcreator_formanswers.entities_id IN ('6') ...' at line 11"
screen
image

Debug: SQL REQUEST

SELECT DISTINCT `glpi_plugin_formcreator_formanswers`.`id` AS id, 'm.goloshchanov' AS currentuser, `glpi_plugin_formcreator_formanswers`.`entities_id`, `glpi_plugin_formcreator_formanswers`.`is_recursive`, `glpi_plugin_formcreator_formanswers`.`name` AS `ITEM_PluginFormcreatorFormAnswer_1`, `glpi_plugin_formcreator_formanswers`.`id` AS `ITEM_PluginFormcreatorFormAnswer_1_id`, `glpi_plugin_formcreator_formanswers`.`id` AS `ITEM_PluginFormcreatorFormAnswer_2`, `glpi_plugin_formcreator_formanswers`.`id` AS `ITEM_PluginFormcreatorFormAnswer_2_id`, `glpi_plugin_formcreator_formanswers`.`request_date` AS `ITEM_PluginFormcreatorFormAnswer_6`, `glpi_plugin_formcreator_formanswers`.`status` AS `ITEM_PluginFormcreatorFormAnswer_8`, GROUP_CONCAT(DISTINCT `glpi_users_users_id_validator`.`id` SEPARATOR '$$##$$') AS `ITEM_PluginFormcreatorFormAnswer_5`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_groups_groups_id_validator`.`completename`, '$#$' , `glpi_groups_groups_id_validator`.`id`)
ORDER BY `glpi_groups_groups_id_validator`.`id` SEPARATOR '$$##$$') AS `ITEM_PluginFormcreatorFormAnswer_7`
FROM `glpi_plugin_formcreator_formanswers`
LEFT JOIN `glpi_users` AS `glpi_users_users_id_validator` ON (`glpi_plugin_formcreator_formanswers`.`users_id_validator` = `glpi_users_users_id_validator`.`id` )
LEFT JOIN `glpi_groups` AS `glpi_groups_groups_id_validator` ON (`glpi_plugin_formcreator_formanswers`.`groups_id_validator` = `glpi_groups_groups_id_validator`.`id` )
WHERE `glpi_plugin_formcreator_formanswers`.`requester_id` = 11 OR (`glpi_plugin_formcreator_formanswers`.`users_id_validator` = 11 OR `glpi_plugin_formcreator_formanswers`.`groups_id_validator` IN (,46,17)) AND ( `glpi_plugin_formcreator_formanswers`.`entities_id` IN ('6') OR (`glpi_plugin_formcreator_formanswers`.`is_recursive`='1' AND `glpi_plugin_formcreator_formanswers`.`entities_id` IN (0)) ) AND ( (`glpi_users_users_id_validator`.`id` = '11') OR (`glpi_groups_groups_id_validator`.`id` IN ('17','46')) ) GROUP BY `glpi_plugin_formcreator_formanswers`.`id`
ORDER BY `ITEM_PluginFormcreatorFormAnswer_6` DESC

Have errors "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 '46,17)) AND ( glpi_plugin_formcreator_formanswers.entities_id IN ('6') ...' at line 15"
screen
image
@btry
Copy link
Collaborator

btry commented Apr 11, 2023

Hi

I'm suspecting that something is wrong in your database.

Please run the following SQL query, replacing the user ID 2 by the ID of the user impacted by the issue

SELECT
  `glpi_groups`.id as GID,
  `glpi_groups_users`.`id` AS `IDD`,
  `glpi_groups_users`.`id` AS `linkid`,
  `glpi_groups_users`.`is_dynamic` AS `is_dynamic`,
  `glpi_groups_users`.`is_manager` AS `is_manager`,
  `glpi_groups_users`.`is_userdelegate` AS `is_userdelegate`
FROM
  `glpi_groups_users`
  LEFT JOIN `glpi_groups` ON (
    `glpi_groups`.`id` = `glpi_groups_users`.`groups_id`
  )
WHERE
  `glpi_groups_users`.`users_id` = '2'
ORDER BY
  `glpi_groups`.`name`

@btry
Copy link
Collaborator

btry commented Apr 11, 2023

If my assumption is right, the following fix may solve your problem.

diff --git a/hook.php b/hook.php
index 4f83b9abf..08a56cec4 100644
--- a/hook.php
+++ b/hook.php
@@ -265,6 +265,9 @@ function plugin_formcreator_addDefaultWhere($itemtype) {
 
             $groupIDs = [];
             foreach ($groups as $group) {
+               if ($group['id'] === null) {
+                  continue;
+               }
                $groupIDs[] = $group['id'];
             }
             $groupIDs = implode(',', $groupIDs);

@miqonee miqonee closed this as completed Apr 12, 2023
@miqonee miqonee reopened this Apr 12, 2023
@miqonee
Copy link
Author

miqonee commented Apr 12, 2023

I will try and answer

@miqonee
Copy link
Author

miqonee commented Apr 19, 2023

Awesome solution

@miqonee miqonee closed this as completed Apr 19, 2023
btry added a commit that referenced this issue Apr 19, 2023
@btry
fix: SQL error when inconsistency in DB
1af7872 
if a user is linked to a not existing group, a SQL IN statement begins with a comma

fix #3242
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@btry @miqonee