Error in SQL syntax search page

[salvoaranzulla]

Hi,

After I have updated Wordpress to last version WordPress 4.4.1, Wordpress sometimes crashes on search page:

[07-Jan-2016 13:05:32 UTC] WordPress errore sul database 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 'ELSE 5 END), sa2013_posts.post_date DESC LIMIT 0, 25' at line 1 per la query SELECT SQL_CALC_FOUND_ROWS  sa2013_posts.ID FROM sa2013_posts  WHERE 1=1  AND (((sa2013_posts.post_title LIKE '%lenovo%') OR (sa2013_posts.post_content LIKE '%lenovo%')) AND ((sa2013_posts.post_title LIKE '%lenovo%') OR (sa2013_posts.post_content LIKE '%lenovo%')) AND ((sa2013_posts.post_title LIKE '%tab%') OR (sa2013_posts.post_content LIKE '%tab%')) AND ((sa2013_posts.post_title LIKE '%2%') OR (sa2013_posts.post_content LIKE '%2%')) AND ((sa2013_posts.post_title LIKE '%a10-70l%') OR (sa2013_posts.post_content LIKE '%a10-70l%')) AND ((sa2013_posts.post_title LIKE '%za010058de%') OR (sa2013_posts.post_content LIKE '%za010058de%')) AND ((sa2013_posts.post_title NOT LIKE '%%') AND (sa2013_posts.post_content NOT LIKE '%%')) AND ((sa2013_posts.post_title LIKE '%blu%') OR (sa2013_posts.post_content LIKE '%blu%')))  AND (sa2013_posts.post_password = '')  AND sa2013_posts.post_type = 'post' AND (sa2013_posts.post_status = 'publish')  ORDER BY (CASE ELSE 5 END), sa2013_posts.post_date DESC LIMIT 0, 25 fatta da require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[07-Jan-2016 16:57:01 UTC] WordPress errore sul database 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 'ELSE 5 END), sa2013_posts.post_date DESC LIMIT 0, 25' at line 1 per la query SELECT SQL_CALC_FOUND_ROWS  sa2013_posts.ID FROM sa2013_posts  WHERE 1=1  AND (((sa2013_posts.post_title LIKE '%Rito%') OR (sa2013_posts.post_content LIKE '%Rito%')) AND ((sa2013_posts.post_title LIKE '%Trailer%') OR (sa2013_posts.post_content LIKE '%Trailer%')) AND ((sa2013_posts.post_title LIKE '%Italiano%') OR (sa2013_posts.post_content LIKE '%Italiano%')) AND ((sa2013_posts.post_title LIKE '%Finale%') OR (sa2013_posts.post_content LIKE '%Finale%')) AND ((sa2013_posts.post_title LIKE '%Ufficiale%') OR (sa2013_posts.post_content LIKE '%Ufficiale%')) AND ((sa2013_posts.post_title LIKE '%HD%') OR (sa2013_posts.post_content LIKE '%HD%')) AND ((sa2013_posts.post_title NOT LIKE '%%') AND (sa2013_posts.post_content NOT LIKE '%%')) AND ((sa2013_posts.post_title LIKE '%TopCinema.it%') OR (sa2013_posts.post_content LIKE '%TopCinema.it%')))  AND (sa2013_posts.post_password = '')  AND sa2013_posts.post_type = 'post' AND (sa2013_posts.post_status = 'publish')  ORDER BY (CASE ELSE 5 END), sa2013_posts.post_date DESC LIMIT 0, 25 fatta da require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts

[salvoaranzulla]

debug.log

[afercia]

Hello Aranzulla :) Does it happen only when the search query contains a hyphen ("-")? cc @boonebgorges

[salvoaranzulla]

Hi,

I have tried to reproduce this error without success. I have tried terms used by users but no error is generated in debug.log. This kind of error appears many times after Wordpress update to Wordpress 4.4.

The problem seems to be related to "ELSE 5 END" which appears on query.php.

Thanks
Salvatore

[afercia]

Not sure about the crash, but a search for a string like

LENOVO Lenovo TAB 2 A10-70L ZA010058DE blu

(including the hyphen which is part of the model name) may result in excluding the terms after the hyphen, see [34934]

[salvoaranzulla]

The problems is in query.php:

if ( $q['search_terms_count'] > 1 ) {
            $num_terms = count( $q['search_orderby_title'] );

            // If the search terms contain negative queries, don't bother ordering by sentence matches.
            $like = '';
            if ( ! preg_match( '/(?:\s|^)\-/', $q['s'] ) ) {
                $like = '%' . $wpdb->esc_like( $q['s'] ) . '%';
            }

            $search_orderby = '(CASE ';

            // sentence match in 'post_title'
            if ( $like ) {
                $search_orderby .= $wpdb->prepare( "WHEN $wpdb->posts.post_title LIKE %s THEN 1 ", $like );
            }

            // sanity limit, sort as sentence when more than 6 terms
            // (few searches are longer than 6 terms and most titles are not)
            if ( $num_terms < 7 ) {
                // all words in title
                $search_orderby .= 'WHEN ' . implode( ' AND ', $q['search_orderby_title'] ) . ' THEN 2 ';
                // any word in title, not needed when $num_terms == 1
                if ( $num_terms > 1 )
                    $search_orderby .= 'WHEN ' . implode( ' OR ', $q['search_orderby_title'] ) . ' THEN 3 ';
            }

            // sentence match in 'post_content'
            if ( $like ) {
                $search_orderby .= $wpdb->prepare( "WHEN $wpdb->posts.post_content LIKE %s THEN 4 ", $like );
            }
            $search_orderby .= 'ELSE 5 END)';
        } else {
            // single word or sentence search
            $search_orderby = reset( $q['search_orderby_title'] ) . ' DESC';
        }

If $like is empty Wordpress generates an error because $num_terms >= 7.

You can reproduce this error searching:

LENOVO Lenovo TAB 2 A10 -70L ZA010058DE blu

A solution could be :

Old: if ( $num_terms < 7 ) {
New: if ( empty( $like ) || $num_terms < 7 ) {

Thanks

[boonebgorges]

@salvoaranzulla Thanks very much for the ticket, and welcome to WordPress Trac!

I have confirmed the problem. As you note, it happens when the number of LIKE terms is greater than 6 *and* the search string contains at least one negated term. In your case (LENOVO Lenovo TAB 2 A10 -70L ZA010058DE blu) the negated term is -70L.

35361.diff​ should solve the issue, using more or less the same logic as you've proposed. Could you verify on your installation?

Negated search terms were introduced in 4.4, so I'm marking this for 4.4.2 consideration.

[salvoaranzulla]

Bug is fixed with your code 35361.diff! :-))

Your patch works perfectly.

Thanks :-))

[boonebgorges]

In 36251:

Avoid invalid SQL when building ORDER BY clause using long search strings.

The introduction of negative search terms in 4.4 [34934] introduced the
possibility that the ORDER BY clause of a search query could be assembled in
such a way as to create invalid syntax. The current changeset fixes this by
ensuring that the ORDER BY clause corresponding to the search terms is
excluded when it would otherwise be empty.

Props salvoaranzulla.
Fixes #35361.

[boonebgorges]

Reopening for 4.4.2.

[dd32]

In 36354:

Query: Avoid invalid SQL when building ORDER BY clause using long search strings.

The introduction of negative search terms in 4.4 [34934] introduced the
possibility that the ORDER BY clause of a search query could be assembled in
such a way as to create invalid syntax. The current changeset fixes this by
ensuring that the ORDER BY clause corresponding to the search terms is
excluded when it would otherwise be empty.

Merges [36251] to the 4.4 branch.
Props salvoaranzulla, boonebgorges.
Fixes #35361.