WP_Query with LIKE in meta gives strange query

Question

$results = new WP_Query( array(
    'post_type' => 'questions',
    'post_status' => 'publish',
    'no_found_rows' => '1',
    'nopaging' => '1',
    'ignore_sticky_posts' => '1',
    'orderby' => 'rand',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'reference-1',
            'value' => 'lucht',
            'compare' => '='
        ),
        array(
            'key' => 'reference-2',
            'value' => 'lucht',
            'compare' => '='
        )
    )
));
echo $results->request;

gives: SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'response' AND wp_postmeta.meta_value = 'lucht' ) OR ( wp_postmeta.meta_key = 'answers' AND wp_postmeta.meta_value = 'lucht' ) ) AND wp_posts.post_type = 'questions' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY RAND()

Now replace the meta_query compare = to LIKE:

...
'meta_query' => array(
    'relation' => 'OR',
    array(
        'key' => 'reference-1',
        'value' => 'lucht',
        'compare' => 'LIKE'
    ),
    array(
        'key' => 'reference-2',
        'value' => 'lucht',
        'compare' => 'LIKE'
    )
)
...

gives: SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'response' AND wp_postmeta.meta_value LIKE '{c33232364fb7d50ba632c808436b28560295aade9067a11e1fc4ae1c5879c13d}lucht{c33232364fb7d50ba632c808436b28560295aade9067a11e1fc4ae1c5879c13d}' ) OR ( wp_postmeta.meta_key = 'answers' AND wp_postmeta.meta_value LIKE '{c33232364fb7d50ba632c808436b28560295aade9067a11e1fc4ae1c5879c13d}lucht{c33232364fb7d50ba632c808436b28560295aade9067a11e1fc4ae1c5879c13d}' ) ) AND wp_posts.post_type = 'questions' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY RAND()

The {c33232364fb7d50ba632c808436b28560295aade9067a11e1fc4ae1c5879c13d} seems to be some random value 64 byte value in curly brackets. Had it been a % instead, the query would work just fine.

I have tested this on two installs on different servers (4.9.5), turned off all plugins.

The question: Am I doing anything wrong here or should I create a bug ticket?

Answer 1

No, you're doing everything right and this should actually work as those placeholders are removed at a later point before actually executing the query.

This is part of a security measure introduced with WordPress 4.8.3. Quoting from the corresponding developer note:

As part of the WordPress 4.8.3 release, there is a change in esc_sql() behaviour that may affect plugin developers who expect esc_sql() to return a string that’s usable outside of the context of building a query to send to WPDB.

Source: https://make.wordpress.org/core/2017/10/31/changed-behaviour-of-esc_sql-in-wordpress-4-8-3/

If you're really curious about what is going on read the blog post by Anthony Ferrara who discovered the underlying vulnerability:https://blog.ircmaxell.com/2017/10/disclosure-wordpress-wpdb-sql-injection-technical.html