Hi guys,
Nice to meet you. I've been using Fabrik for quite a long time and this is the first time that I need to submit a ticket (thankfully
).
I have a search form with a redirect plugin to another list. Whenever I add a word with an apostrophe on the search form, I get a 500 error. Eg with "Alicia's"
500 - Fabrik has generated an incorrect query for the list Businesses: <br /><br /><pre>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 '') ) ORDER BY `cb_business`.`id` ASC, `cb_business`.`Featured` DESC, `cb_busin' at line 45 SQL=SELECT SQL_CALC_FOUND_ROWS `cb_business`.`id` AS `cb_business___id`, `cb_business`.`id` AS `cb_business___id_raw`, `cb_business`.`BusinessName` AS `cb_business___BusinessName`, `cb_business`.`BusinessName` AS `cb_business___BusinessName_raw`, `cb_business`.`BusinessType` AS `cb_business___BusinessType`, `cb_business`.`BusinessType` AS `cb_business___BusinessType_raw`, (SELECT GROUP_CONCAT(Photos SEPARATOR '//..*..//') FROM cb_business_repeat_Photos WHERE parent_id = `cb_business`.`id`) AS `cb_business___Photos`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM cb_business_repeat_Photos WHERE parent_id = `cb_business`.`id`) AS `cb_business___Photos_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM cb_business_repeat_Photos WHERE parent_id = `cb_business`.`id`) AS `cb_business___Photos___params`, `cb_business`.`FullAddress` AS `cb_business___FullAddress`, `cb_business`.`FullAddress` AS `cb_business___FullAddress_raw`, `cb_business`.`Neighborhood` AS `cb_business___Neighborhood`, `cb_business`.`Neighborhood` AS `cb_business___Neighborhood_raw`, `cb_business`.`CityName` AS `cb_business___CityName`, `cb_business`.`CityName` AS `cb_business___CityName_raw`, `cb_business`.`Zip` AS `cb_business___Zip`, `cb_business`.`Zip` AS `cb_business___Zip_raw`, `cb_business`.`StateCode` AS `cb_business___StateCode`, `cb_business`.`StateCode` AS `cb_business___StateCode_raw`, `cb_business`.`State` AS `cb_business___State`, `cb_business`.`State` AS `cb_business___State_raw`, `cb_business`.`Phone` AS `cb_business___Phone`, `cb_business`.`Phone` AS `cb_business___Phone_raw`, `cb_business`.`Map` AS `cb_business___Map`, `cb_business`.`Map` AS `cb_business___Map_raw`, `cb_business`.`UserId` AS `cb_business___UserId_raw`, `sh2it_users`.`name` AS `cb_business___UserId`, `cb_business`.`Featured` AS `cb_business___Featured`, `cb_business`.`Featured` AS `cb_business___Featured_raw`, `cb_business`.`Published` AS `cb_business___Published`, `cb_business`.`Published` AS `cb_business___Published_raw`, `cb_business`.`Rating` AS `cb_business___Rating`, `cb_business`.`Rating` AS `cb_business___Rating_raw`, `cb_business`.`RatingCounter` AS `cb_business___RatingCounter`, `cb_business`.`RatingCounter` AS `cb_business___RatingCounter_raw`, `cb_business`.`Categories` AS `cb_business___Categories`, `cb_business`.`Categories` AS `cb_business___Categories_raw`, `cb_business`.`Thumbnail` AS `cb_business___Thumbnail`, `cb_business`.`Thumbnail` AS `cb_business___Thumbnail_raw`, CONCAT_WS(':', `cb_business`.`id`, `cb_business`.`BusinessName`) AS slug , `cb_business`.`id` AS `__pk_val` FROM `cb_business` LEFT JOIN `sh2it_users` AS `sh2it_users` ON `sh2it_users`.`id` = `cb_business`.`UserId` WHERE ( (cb_business.Published = 1 OR cb_business.Published LIKE '[\"1\"%' OR cb_business.Published LIKE '%\"1\"%' OR cb_business.Published LIKE '%\"1\"]') ) AND ( `cb_business`.`SearchWhat` regexp LOWER('Alicia's') ) ORDER BY `cb_business`.`id` ASC, `cb_business`.`Featured` DESC, `cb_business`.`Rating` DESC, `cb_business`.`BusinessName` ASC LIMIT 0, 10</pre>
As you can see, the:
regexp LOWER('Alicia's')
breaks the SQL query.
However, if I try using the same field on the list form, it works OK, this only happens from the search box with a redirect plugin that has the following options:
BTW, the search form works fine without apostrophes.
I appreciate your help and your time and I take this opportunity to congratulate you on, based on my experience with Joomla, one of the best components ever made for this CMS.
Thanks,
Matias
Nice to meet you. I've been using Fabrik for quite a long time and this is the first time that I need to submit a ticket (thankfully
).I have a search form with a redirect plugin to another list. Whenever I add a word with an apostrophe on the search form, I get a 500 error. Eg with "Alicia's"
500 - Fabrik has generated an incorrect query for the list Businesses: <br /><br /><pre>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 '') ) ORDER BY `cb_business`.`id` ASC, `cb_business`.`Featured` DESC, `cb_busin' at line 45 SQL=SELECT SQL_CALC_FOUND_ROWS `cb_business`.`id` AS `cb_business___id`, `cb_business`.`id` AS `cb_business___id_raw`, `cb_business`.`BusinessName` AS `cb_business___BusinessName`, `cb_business`.`BusinessName` AS `cb_business___BusinessName_raw`, `cb_business`.`BusinessType` AS `cb_business___BusinessType`, `cb_business`.`BusinessType` AS `cb_business___BusinessType_raw`, (SELECT GROUP_CONCAT(Photos SEPARATOR '//..*..//') FROM cb_business_repeat_Photos WHERE parent_id = `cb_business`.`id`) AS `cb_business___Photos`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM cb_business_repeat_Photos WHERE parent_id = `cb_business`.`id`) AS `cb_business___Photos_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM cb_business_repeat_Photos WHERE parent_id = `cb_business`.`id`) AS `cb_business___Photos___params`, `cb_business`.`FullAddress` AS `cb_business___FullAddress`, `cb_business`.`FullAddress` AS `cb_business___FullAddress_raw`, `cb_business`.`Neighborhood` AS `cb_business___Neighborhood`, `cb_business`.`Neighborhood` AS `cb_business___Neighborhood_raw`, `cb_business`.`CityName` AS `cb_business___CityName`, `cb_business`.`CityName` AS `cb_business___CityName_raw`, `cb_business`.`Zip` AS `cb_business___Zip`, `cb_business`.`Zip` AS `cb_business___Zip_raw`, `cb_business`.`StateCode` AS `cb_business___StateCode`, `cb_business`.`StateCode` AS `cb_business___StateCode_raw`, `cb_business`.`State` AS `cb_business___State`, `cb_business`.`State` AS `cb_business___State_raw`, `cb_business`.`Phone` AS `cb_business___Phone`, `cb_business`.`Phone` AS `cb_business___Phone_raw`, `cb_business`.`Map` AS `cb_business___Map`, `cb_business`.`Map` AS `cb_business___Map_raw`, `cb_business`.`UserId` AS `cb_business___UserId_raw`, `sh2it_users`.`name` AS `cb_business___UserId`, `cb_business`.`Featured` AS `cb_business___Featured`, `cb_business`.`Featured` AS `cb_business___Featured_raw`, `cb_business`.`Published` AS `cb_business___Published`, `cb_business`.`Published` AS `cb_business___Published_raw`, `cb_business`.`Rating` AS `cb_business___Rating`, `cb_business`.`Rating` AS `cb_business___Rating_raw`, `cb_business`.`RatingCounter` AS `cb_business___RatingCounter`, `cb_business`.`RatingCounter` AS `cb_business___RatingCounter_raw`, `cb_business`.`Categories` AS `cb_business___Categories`, `cb_business`.`Categories` AS `cb_business___Categories_raw`, `cb_business`.`Thumbnail` AS `cb_business___Thumbnail`, `cb_business`.`Thumbnail` AS `cb_business___Thumbnail_raw`, CONCAT_WS(':', `cb_business`.`id`, `cb_business`.`BusinessName`) AS slug , `cb_business`.`id` AS `__pk_val` FROM `cb_business` LEFT JOIN `sh2it_users` AS `sh2it_users` ON `sh2it_users`.`id` = `cb_business`.`UserId` WHERE ( (cb_business.Published = 1 OR cb_business.Published LIKE '[\"1\"%' OR cb_business.Published LIKE '%\"1\"%' OR cb_business.Published LIKE '%\"1\"]') ) AND ( `cb_business`.`SearchWhat` regexp LOWER('Alicia's') ) ORDER BY `cb_business`.`id` ASC, `cb_business`.`Featured` DESC, `cb_business`.`Rating` DESC, `cb_business`.`BusinessName` ASC LIMIT 0, 10</pre>
As you can see, the:
regexp LOWER('Alicia's')
breaks the SQL query.
However, if I try using the same field on the list form, it works OK, this only happens from the search box with a redirect plugin that has the following options:
- Save & Next: No
- Append jump url with data: No
- Use as search data: Yes
BTW, the search form works fine without apostrophes.
I appreciate your help and your time and I take this opportunity to congratulate you on, based on my experience with Joomla, one of the best components ever made for this CMS.
Thanks,
Matias
