SQL Syntax in Pages

[PatrickRQ]

Hi IPS,

I use following code in Pages templates

{{$records = $recordClass::getItemsWithPermission( array(array('cms_custom_database_'.$category->database()->id.'.category_id=?', $category->id)), $category->database()->field_sort . ' ' . $category->database()->field_direction);}}

 

it generates following error:

SELECT cms_custom_database_5.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `igzxccn_cms_custom_database_5` AS `cms_custom_database_5`  LEFT JOIN `igzxccn_cms_database_categories` AS `cms_database_categories` ON cms_custom_database_5.category_id=cms_database_categories.category_id  LEFT JOIN `igzxccn_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('cms;records5;',cms_custom_database_5.primary_id_field))  LEFT JOIN `igzxccn_core_members` AS `author` ON author.member_id = cms_custom_database_5.member_id  LEFT JOIN `igzxccn_core_members` AS `last_commenter` ON last_commenter.member_id = cms_custom_database_5.record_last_comment_by WHERE cms_custom_database_5.category_id=24 and field_80< IS NULL AND ( category_can_view_others=1 OR cms_custom_database_5.member_id IS NULL ) AND cms_custom_database_5.record_approved=1 AND cms_custom_database_5.record_approved!=-2 AND cms_custom_database_5.record_approved!=-3 AND cms_custom_database_5.record_future_date=0 AND cms_custom_database_5.category_id IN(17,22,23,24,25) ORDER BY field_80 desc LIMIT 1
IPS\Db\Exception: 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 'IS NULL AND ( category_can_view_others=1 OR cms_custom_database_5.member_id IS N' at line 1 (1064)
#0 /srv/www/public_html/system/Db/Select.php(388): IPS\_Db->preparedQuery()
#1 /srv/www/public_html/system/Db/Select.php(446): IPS\Db\_Select->runQuery()
#2 [internal function]: IPS\Db\_Select->rewind()
#3 /srv/www/public_html/applications/cms/sources/Theme/Theme.php(610) : eval()'d code(2975): IteratorIterator->rewind()
#4 /srv/www/public_html/applications/cms/modules/front/database/record.php(350): IPS\Theme\class_cms_database_FXPages_FAQ_Display->record()
#5 /srv/www/public_html/system/Dispatcher/Controller.php(118): IPS\cms\modules\front\database\_record->manage()
#6 /srv/www/public_html/system/Content/Controller.php(50): IPS\Dispatcher\_Controller->execute()
#7 /srv/www/public_html/applications/cms/sources/Databases/Dispatcher.php(343): IPS\Content\_Controller->execute()
#8 /srv/www/public_html/system/Theme/Theme.php(4628) : eval()'d code(9): IPS\cms\Databases\_Dispatcher->run()
#9 /srv/www/public_html/applications/cms/sources/Pages/Page.php(1260): IPS\Theme\content_pages_172()
#10 /srv/www/public_html/applications/cms/sources/Pages/Page.php(2286): IPS\cms\Pages\_Page->getHtmlContent()
#11 /srv/www/public_html/applications/cms/modules/front/pages/page.php(116): IPS\cms\Pages\_Page->output()
#12 /srv/www/public_html/applications/cms/modules/front/pages/page.php(43): IPS\cms\modules\front\pages\_page->view()
#13 /srv/www/public_html/system/Dispatcher/Controller.php(118): IPS\cms\modules\front\pages\_page->manage()
#14 /srv/www/public_html/applications/cms/modules/front/pages/page.php(33): IPS\Dispatcher\_Controller->execute()
#15 /srv/www/public_html/system/Dispatcher/Dispatcher.php(153): IPS\cms\modules\front\pages\_page->execute()
#16 /srv/www/public_html/index.php(13): IPS\_Dispatcher->run()
#17 {main}

As you can see there is unexpected < in the middle of the query

[Marc]

Thank you for bringing this issue to our attention! I can confirm this should be further reviewed and I have logged an internal bug report for our development team to investigate and address as necessary, in a future maintenance release.