Jump to content

Issue Information

  • #000622

  • 0 - None Assigned

  • New

  • 2.3.3

  • -

Issue Confirmations

  • Yes (0)No (0)

potential to create MySQL errors in splitPageResults class mysql, error, splitpageresults

Posted by wdepot on 29 August 2013 - 18:37

While updating the hide categories contribution to work with osCommerce 2.3.x I ran into an error created by the split page results count when testing products to be sure they don't exist only in a hidden category. The error received looks like this:

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 '*) as total from products p left join manufacturers m on (p.manufacturers_id = ' at line 1

select count(distinct *) as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and p.products_id = p2c.products_id and pd.language_id = '1' and (not (p2c.categories_id in (22,23,24,25,26,27,28,29,30,31,32)))

In looking at the code for split page results I find this:

if (strpos($this->sql_query, 'distinct') || ($pos_group_by != false)) {
$count_string = 'distinct ' . tep_db_input($count_key);
} else {
$count_string = tep_db_input($count_key);

Since the products_new.php file didn't pass a count key to the splitPageResults class it defaulted to * but since the new query included a group by clause to prevent the possiblity of listing the same product more than once since products can be linked to multiple categories the splitPageResults class automatically added the term distinct which doesn't work with *. The splitPageResults should never use the term distinct when the count key is * so the if statement should actually be worded as follows:

if (($count_key != '*') && (strpos($this->sql_query, 'distinct') || ($pos_group_by != false))) {
$count_string = 'distinct ' . tep_db_input($count_key);
} else {
$count_string = tep_db_input($count_key);

This has the potential to return an incorrect count for original queries that use distinct or group by clauses but it will prevent the possibilty of creating an SQL error.