-- HikaShop version -- : 2.5.0
-- Joomla version -- : 4.3.4
-- PHP version -- : 5.4.16
-- Error-message(debug-mod must be tuned on) -- : N/A
We noticed that many of our price ACLs were non-functional and defaulting to the "all" price after upgrading from HikaShop 2.4.0 to 2.5.0.
However, if we added some extra user groups into the ACL, sometimes it would work.
Enabling Joomla profiling on the product page showed the following queries (slightly sanitized):
Code:
SELECT *
FROM prefix_hikashop_price
WHERE price_currency_id IN (2)
AND price_product_id IN (701,702,703)
AND (price_access = 'all' OR price_access LIKE '%,2,%' OR price_access LIKE '%,7,%' OR price_access LIKE '%,8,%' OR price_access LIKE '%,9,%' OR price_access LIKE '%,12,%' OR price_access LIKE '%,15,%' OR price_access LIKE '%,16,%' OR price_access LIKE '%,19,%' OR price_access LIKE '%,20,%')
ORDER BY price_site_id ASC, price_value DESC
The flaw here is with the LIKE portions of the query. In our case, I get the following output when I run the query:
Code:
MariaDB [dbname]> SELECT *
-> FROM prefix_hikashop_price
-> WHERE price_currency_id IN (2)
-> AND price_product_id IN (701,702,703)
-> AND (price_access = 'all' OR price_access LIKE '%,2,%' OR price_access LIKE '%,7,%' OR price_access LIKE '%,8,%' OR price_access LIKE '%,9,%' OR price_access LIKE '%,12,%' OR price_access LIKE '%,15,%' OR price_access LIKE '%,16,%' OR price_access LIKE '%,19,%' OR price_access LIKE '%,20,%')
-> ORDER BY price_site_id ASC, price_value DESC;
+----------+-------------------+------------------+-------------+--------------------+--------------+---------------+
| price_id | price_currency_id | price_product_id | price_value | price_min_quantity | price_access | price_site_id |
+----------+-------------------+------------------+-------------+--------------------+--------------+---------------+
| 587 | 2 | 703 | 74.00000 | 0 | all | |
| 585 | 2 | 702 | 34.00000 | 0 | all | |
+----------+-------------------+------------------+-------------+--------------------+--------------+---------------+
2 rows in set (0.00 sec)
However, I have two prices with ACLs on them which are not showing up in the query. If I just select the prices for the set of product IDs above, I get the following:
Code:
MariaDB [dbname]> SELECT * FROM prefix_hikashop_price WHERE price_product_id IN (701,702,703);
+----------+-------------------+------------------+-------------+--------------------+--------------+---------------+
| price_id | price_currency_id | price_product_id | price_value | price_min_quantity | price_access | price_site_id |
+----------+-------------------+------------------+-------------+--------------------+--------------+---------------+
| 584 | 2 | 702 | 32.00000 | 0 | 16,15 | |
| 585 | 2 | 702 | 34.00000 | 0 | all | |
| 586 | 2 | 703 | 72.00000 | 0 | 16,15 | |
| 587 | 2 | 703 | 74.00000 | 0 | all | |
+----------+-------------------+------------------+-------------+--------------------+--------------+---------------+
4 rows in set (0.00 sec)
The problem is pretty apparent. The LIKE clause is looking for an ACL number, with a comma on either side. That means the highest and lowest usergroup is effectively dropped from the ACL query. This seems like a whopping big bug to me. Any ideas here other than downgrading to 2.4.0 where this wasn't occurring?
