WordPress.org

Make WordPress Core

Opened 2 years ago

Last modified 2 years ago

#40630 new defect (bug)

Advanced meta compare "like" in WP_Query

Reported by: sebastian.pisula Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 4.7.4
Component: Query Keywords: has-patch
Focuses: Cc:
PR Number:

Description

I try get posts with LIKE compare. For example:

<?php
add_action( 'init', function () {
        $q = new WP_Query(
                [
                        'post_type'    => 'any',
                        'meta_key'     => '_stock_status',
                        'meta_value'   => 'i%st',
                        'meta_compare' => 'LIKE',
                ]
        );
} );

And I have SQL Query:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = '_stock_status' AND wp_postmeta.meta_value LIKE '%i\\%st%' )
) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product') AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

I can't get posts with meta value for example instock and other matched.

Attachments (1)

40630.patch (95 bytes) - added by subrataemfluence 2 years ago.
Prevents WordPress from escaping % sign in meta_value's value

Download all attachments as: .zip

Change History (2)

#1 @subrataemfluence
2 years ago

  • Keywords has-patch added
  • Version set to 4.7.4

WordPress automatically adds leading and trailing % to the text (value) passed via LIKE operator and escapes special characters (_, % and back slash (\)) if found inside the value passed.

In wp-includes\wp-db.php the following snippet does this automatically for us:

<?php
public function esc_like( $text ) {
   return addcslashes( $text, '_%\\' );
}

In your example, the value (i%st) you passed has a % sign in it hence WordPress automatically escapes it by invoking the above function with and converts it to %i\\%st%. Look how WordPress has automatically added one leading and one trailing % sings to your actual value.

My patch will prevent WordPress from escaping % sign inside a value (like yours) and will output your query to

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = '_stock_status' AND wp_postmeta.meta_value LIKE '%i%st%' )
) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product') AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Now it should return your desired result.

@subrataemfluence
2 years ago

Prevents WordPress from escaping % sign in meta_value's value

Note: See TracTickets for help on using tickets.