SQL Syntax Error In DataTypeDatalist.getEntries on director_datalist_entry for empty datalist_id

[oxzi]

This is a cross post from the Icinga Community forum:

• https://community.icinga.com/t/icingaweb2-sqlstate-syntax-error-on-modify-a-service-for-director/14375
• https://community.icinga.com/t/icingaweb2-director-sqlstate-42000-syntax-error-on-editing-services/14382/1

The reporter experienced the following error when using Icinga Director:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 ((allowed_roles IS NULL OR allowed_roles LIKE '%\"Administrators\"%' OR a' at line 1, query was: SELECT director_datalist_entry.entry_name, director_datalist_entry.entry_value FROM director_datalist_entry WHERE (list_id = ?) AND ((allowed_roles IS NULL OR allowed_roles LIKE '%\"Administrators\"%' OR allowed_roles LIKE '%\"nops\"%')) ORDER BY entry_value ASC

As outlined in the second post, $this->getSetting('datalist_id') seems to be empty in their setup, resulting in an invalid query. The reporter's quick fix was by removing the WHERE clause from the query.

icingaweb2-module-director/library/Director/DataType/DataTypeDatalist.php

Lines 98 to 101 in de8fe10

	$select = $db->select()
	->from('director_datalist_entry', ['entry_name', 'entry_value'])
	->where('list_id = ?', $this->getSetting('datalist_id'))
	->order('entry_value ASC');

[MisterMountain]

I dont think that quick fix is a good idea, as it removes the filtering on director_datalist_entry table - so you are getting all possible datalist entries from the director_datalist_entry table. To me it looks like somehow list_id is not getting populated with the datalist_id.