WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

#35361 closed defect (bug) (fixed)

Error in SQL syntax search page

Reported by: salvoaranzulla Owned by: boonebgorges
Milestone: 4.4.2 Priority: normal
Severity: normal Version: 4.4
Component: Query Keywords: fixed-major
Focuses: Cc:
PR Number:

Description (last modified by SergeyBiryukov)

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

Attachments (2)

debug.log.txt (2.7 KB) - added by salvoaranzulla 4 years ago.
debug.log
35361.diff (1.4 KB) - added by boonebgorges 4 years ago.

Download all attachments as: .zip

Change History (13)

@salvoaranzulla
4 years ago

debug.log

#1 @afercia
4 years ago

  • Keywords reporter-feedback added

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

#2 @salvoaranzulla
4 years ago

  • Summary changed from Error in SQL syntax search pagr to Error in SQL syntax search page

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

#3 @afercia
4 years ago

  • Keywords dev-feedback added; reporter-feedback removed

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]

#4 @salvoaranzulla
4 years ago

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

Last edited 4 years ago by SergeyBiryukov (previous) (diff)

#5 @SergeyBiryukov
4 years ago

  • Description modified (diff)

#6 @boonebgorges
4 years ago

  • Keywords has-patch needs-testing added; dev-feedback removed
  • Milestone changed from Awaiting Review to 4.4.2
  • Version changed from 4.4.1 to 4.4

@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.

@boonebgorges
4 years ago

This ticket was mentioned in Slack in #core by afercia. View the logs.


4 years ago

#8 @salvoaranzulla
4 years ago

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

Your patch works perfectly.

Thanks :-))

#9 @boonebgorges
4 years ago

  • Owner set to boonebgorges
  • Resolution set to fixed
  • Status changed from new to closed

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.

#10 @boonebgorges
4 years ago

  • Keywords fixed-major added; has-patch needs-testing removed
  • Resolution fixed deleted
  • Status changed from closed to reopened

Reopening for 4.4.2.

#11 @dd32
4 years ago

  • Resolution set to fixed
  • Status changed from reopened to closed

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.

Note: See TracTickets for help on using tickets.