Product price ACL - Apparent SQL bug

Start
Prev
1
Next
End

Llarian
Topic Author
Offline

Posts: 48
Thank you received: 1

10 years 2 months ago #207150

-- 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):

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:

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:

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?

Last edit: 10 years 2 months ago by Llarian.

Please Log in or Create an account to join the conversation.

Llarian
Topic Author
Offline

Posts: 48
Thank you received: 1

10 years 2 months ago #207152

One thing to add, it looks like this may actually be a bug with the product variant edit page in 2.5.0, not the product listing query.

If I look at an item with no variant, and a single group ACL, the query looks like the following:

MariaDB [weareecs_www]> SELECT * 
    ->   FROM qmeyq_hikashop_price 
    ->   WHERE price_currency_id IN (2) 
    ->   AND price_product_id IN (673) 
    ->   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 |
+----------+-------------------+------------------+-------------+--------------------+--------------+---------------+
|      546 |                 2 |              673 |    10.00000 |                  0 | ,15,         |               |
+----------+-------------------+------------------+-------------+--------------------+--------------+---------------+
1 row in set (0.00 sec)

So the price access field is being populated in such a way that the above query would work. (Commas at the beginning and end of the string), but my variants with two configured access groups are not being inserted into the price table that way.

Please Log in or Create an account to join the conversation.

nicolas
Away

Posts: 84298
Thank you received: 13694
MODERATOR

10 years 2 months ago #207154

Hi,

Thank you for your feedback. The bug has been fixed. Please download again the 2.5.0 install package on our website and install it on yours and that will fix the problem thanks to the patch we added in it for that.

Please Log in or Create an account to join the conversation.

Llarian
Topic Author
Offline

Posts: 48
Thank you received: 1

10 years 2 months ago #207265

Hm. I tried re-downloading and installing. No change. Is there anything else I need to do other than just install the latest 2.5.0?

I removed and re-added the ACLed prices, but they're still showing up in XX,YY format. The SQL query is still doing LIKE '%,XX,%';

Please Log in or Create an account to join the conversation.