WordPress.org

Make WordPress Core

Opened 4 months ago

Last modified 4 months ago

#50871 new defect (bug)

When exact is true and orderby set to relevance, there is a DB error on search results page

Reported by: 5um17 Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Query Keywords: has-patch dev-feedback
Focuses: Cc:

Description

In search query, when exact is set to true and orderby set to relevance there is DB error

WordPress database error: [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 'DESC, wp_posts.post_date DESC LIMIT 0, 10' at line 1]

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE 'hello') OR (wp_posts.post_excerpt LIKE 'hello') OR (wp_posts.post_content LIKE 'hello'))) AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') ORDER BY DESC, wp_posts.post_date DESC LIMIT 0, 10

It is clear that both options together has no meaning. But it is compatibility issue between WP Extended Search and WooCommerce

WP Extended search has a feature to match exact sentence so it sets exact to true and later WooCommerce adds orderby => relevance causing this SQL error.

How to reproduce with just WP

  • Add this code to theme or plugin
    <?php
    add_action('pre_get_posts', function ( $query ){
            $query->set( 'exact', true );
            $query->set( 'orderby', 'relevance' );
            $query->set( 'order', 'DESC' );
    });
    
  • Go to front-end and make a search, you will see the error.

Proposed fix

Here https://core.trac.wordpress.org/browser/tags/5.4.2/src/wp-includes/class-wp-query.php#L2357
We checking if ! empty( $q['search_orderby_title'] ) is not empty but we allow to call parse_search_order() when 'relevance' === $q['orderby'] causing ORDER BY DESC in SQL query without column name.
IMHO, we should not call parse_search_order() when search_orderby_title is empty regardless of orderby.

Attachments (1)

50871.patch (787 bytes) - added by 5um17 4 months ago.

Download all attachments as: .zip

Change History (6)

@5um17
4 months ago

#1 @5um17
4 months ago

  • Keywords has-patch dev-feedback added

#2 in reply to: ↑ description @SergeyBiryukov
4 months ago

Thanks for the ticket!

IMHO, we should not call parse_search_order() when search_orderby_title is empty regardless of orderby.

As far as I can tell, this is already the case in the current condition:

if ( ! empty( $q['search_orderby_title'] )
	&& ( empty( $q['orderby'] ) && ! $this->is_feed )
		|| ( isset( $q['orderby'] ) && 'relevance' === $q['orderby'] )
) {
	$search_orderby = $this->parse_search_order( $q );
}

$this->parse_search_order( $q ) is only called if:

  • $q['search_orderby_title'] is not empty.
  • AND either $q['orderby'] is empty and this is not a feed, OR $q['orderby'] is set to relevance.

If $q['search_orderby_title'] is empty, the first part of the condition is not satisfied, so ::parse_search_order() is not called.

So the analysis seems correct, but it seems like that patch won't work as expected.

Version 0, edited 4 months ago by SergeyBiryukov (next)

#3 @5um17
4 months ago

Thanks for checking @SergeyBiryukov

As far as I can tell, this is already the case in the current condition:

No actually this is not true.

There is no parenthesis in the current condition so it will results in

<?php
var_dump( false && false || true );

Which will result in true.

In patch I am adding parenthesis

<?php
var_dump( false && ( false || true ) );

Please correct me if I am wrong?

#4 @SergeyBiryukov
4 months ago

As far as I can tell, there are parentheses in the current condition:

( false && ( false || true ) )

With the patch, that becomes:

( false && ( ( false || true ) )

but the logic is the same.

#5 @5um17
4 months ago

Could you please take another look at https://core.trac.wordpress.org/browser/trunk/src/wp-includes/class-wp-query.php#L2368 ?

I am sorry but I don't see any braces wrapping last two condition.

https://i.ibb.co/SyfYBrd/07-08-2020-21-35-56.png
I am showing the start and end of parentheses with arrow and highlighted internal braces. There is no braces wrapping last && and ||

And only because of this it is showing error.

Note: See TracTickets for help on using tickets.