Fixing WooCommerce WOOF Products Filter Price Range After WP 4.8.2 Update

Recently our WordPress price range broke and we decided to share our solution for fixing it. The WordPress WOOF – WooCommerce Products Filter plugin (Version 2.1.4.2) was displaying a “0” for both the minimum and maximum prices. After a couple hours of debugging we finally discovered why it was broken and how to fix it.

Checking the error log indicated the culprit was the WOOF_HELPER::get_max_price() and WOOF_HELPER::get_min_price() functions in the file woocommerce-products-filter/classes/helper.php. After extensively testing the query we realized that the query string returned by $wpdb->prepare() was the problem.

// Original WOOF code

// Get MAX price
$max = ceil($wpdb->get_var(
    $wpdb->prepare('
         SELECT max(meta_value + 0)
         FROM %1$s
         LEFT JOIN %2$s ON %1$s.ID = %2$s.post_id
	 WHERE meta_key IN ("' . implode('","', apply_filters('woocommerce_price_filter_meta_keys', array('_price'))) . '")
    ', $wpdb->posts, $wpdb->postmeta, '_price')
));

// ...
// ...
// ...

// Get MIN price
$min = floor($wpdb->get_var(
    $wpdb->prepare('
         SELECT min(meta_value + 0)
         FROM %1$s
         LEFT JOIN %2$s ON %1$s.ID = %2$s.post_id
         WHERE meta_key IN ("' . implode('","', apply_filters('woocommerce_price_filter_meta_keys', array('_price', '_min_variation_price'))) . '")
         AND meta_value != ""
    ', $wpdb->posts, $wpdb->postmeta)
));

The function $wpdb->prepare() was not replacing the placeholder %1$s and %2$s format values with the table names (wp_posts and wp_postmeta) as expected. This is due to a security upgrade in WordPress version 4.8.2 that intended to make $wpdb->prepare() more resistant to SQL injection. (WordPress 4.8.2 Release)

To solve the problem we patched the WOOF plugin by replacing the format value placeholders for the table names with the actual table names themselves.

// Modified WOOF code

// Get MAX price
$max = ceil($wpdb->get_var(
    $wpdb->prepare('
         SELECT max(meta_value + 0)
         FROM '.$wpdb->posts.'
         LEFT JOIN '.$wpdb->postmeta.' ON '.$wpdb->posts.'.ID = '.$wpdb->postmeta.'.post_id
         WHERE meta_key IN ("' . implode('","', apply_filters('woocommerce_price_filter_meta_keys', array('_price'))) . '")
    ')
));

// ...
// ...
// ...

// Get MIN price
$min = floor($wpdb->get_var(
    $wpdb->prepare('
         SELECT min(meta_value + 0)
         FROM '.$wpdb->posts.'
         LEFT JOIN '.$wpdb->postmeta.' ON '.$wpdb->posts.'.ID = '.$wpdb->postmeta.'.post_id
         WHERE meta_key IN ("' . implode('","', apply_filters('woocommerce_price_filter_meta_keys', array('_price', '_min_variation_price'))) . '")
         AND meta_value != ""
     ')
));

After replacing the format values with the actual table names the WOOF price range started working normally again. These are not the only queries that might break the range slider, but these queries are the only ones used by our price range. In general, it is better to have the original plugin author deploy an official plugin update to solve problems like this than for web developers to jump in and start modifying plugin code. But if you encounter this problem in a query other than the queries listed above you should be able to solve it in the same way by replacing the format values with the actual table names.

WordPress database error 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 '%1$s\n\t\t\t\t\tLEFT JOIN %2$s ON %1$s.ID = %2$s.post_id\n\t\t\t\t\tWHERE meta_key IN ("_pri' at line 2 for query \n\t\t\t\t\tSELECT max(meta_value + 0)\n\t\t\t\t\tFROM %1$s\n\t\t\t\t\tLEFT JOIN %2$s ON %1$s.ID = %2$s.post_id\n\t\t\t\t\tWHERE meta_key IN ("_price")\n\t\t\t\t made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/plugins/pl-platform/engine/lib/pl-base.php'), pl_hook, do_action('pl_after_template'), WP_Hook->do_action, WP_Hook->apply_filters, PL_Integration->get_integration_output, pl_primary_template, pl_template_hook, do_action('pl_region_template'), WP_Hook->do_action, WP_Hook->apply_filters, PL_UI_Site->process_template, PL_Factory->process_region, PL_Factory->render_section, PL_Section->render, PL_Section->section_template_load, PL_Container->section_template, pl_render_nested_sections, PL_Factory->render_section, PL_Section->render, PL_Section->section_template_load, PL_Widgets->section_template, PL_Widgets->get_widgets, pl_draw_sidebar, dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, WOOF->woof_shortcode, WOOF->render_html, include('/plugins/woocommerce-products-filter/views/woof.php'), woof_print_item_by_key, do_shortcode, preg_replace_callback, do_shortcode_tag, WOOF->woof_price_filter, WOOF->render_html, include('/plugins/woocommerce-products-filter/views/shortcodes/woof_price_filter_slider.php'), WOOF_HELPER::get_max_price