SQL syntax error after update to 3.2.2

[postkat]

Hi all,

I updated to Virtuemart 3.2.2 yesterday and now I get a SQL syntax error on the product detail page.

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 'WHERE i.`published` = "1" AND j.`element` = "weight_countries" AND (i.`virtuema' at line 6 

Joomla 3.7.X
PHP version 7.0.19
Database version 10.1.23-MariaDB

Help please!

Regards, Dorine

[Milbo]

I am sorry, I need a bit more info. The sql is in vmpsplugin.php around line 533. The shown sql is without errors, as far I can see. When I have the full sql,...

[panagiotiss]

Hi I get the following sql error  after upgraded to vm 3.2.2

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 'ASC FROM `#__virtuemart_products` as p INNER JOIN `#__virtuemart_products_el' at line 1 SQL=SELECT p.`virtuemart_product_id`, `l`.`product_name`,virtuemart_product_id ASC FROM `#__virtuemart_products` as p INNER JOIN `#__virtuemart_products_el_gr` as l ON l.`virtuemart_product_id` = p.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` WHERE ( `pc`.`virtuemart_category_id` = 26 AND ( `ps`.`virtuemart_shoppergroup_id`= "1" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`="1" ) AND p.`virtuemart_product_id`!="364" AND virtuemart_product_id ASC >= "ΒΡΑΧΙΟΛΙ ΣΤΟΧΟΣ ΑΠΟ ΑΣΗΜΙ 925° ΜΕ ΖΙΡΓΚΟΝ" ORDER BY `p`.virtuemart_product_id ASC LIMIT 1

[Milbo]

We did not work on this sql, very strange that you get suddenly an error there. But maybe I found already a solution. Please edit your product model (/administrator/components/com_virtuemart/models/product.php) around line 1841 move the line

Code Select Expand


$orderByNameMain = str_replace(array('DESC','ASC'), '',$orderByNameMain);


to line 1843, so that it looks for the lines 1836 - 1843 like this

Code Select Expand


if($cpos = strpos($orderByName,',')!==false){
$t = explode(',',$orderByName);
if(!empty($t[0])){
$orderByNameMain = $t[0];
}
}
$orderByNameMain = str_replace(array('DESC','ASC'), '',$orderByNameMain);
$orderByNameMain = trim(str_replace('`','',$orderByNameMain));


[panagiotiss]

that works for me

what about when update, are you going to put it into next update?

[Milbo]

of course. The next update is planned in some days, when we evaluated the final problems with j3.7.1

[panagiotiss]

thanks

[rado85]

In my case it was because of shipment by payment plugin from VP.


regards