$wpdb->get_results(...) with $wpdb->prepare(...) returns empty array despite correct query

Question

My MySQL query is correct, but $wpdb->get_results(...) produces an empty array. Here's my code:

$key = 'user-input-key';
$value = 'user-input-value';
$value = '%' . $value . '%';
$query = "SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key=%s AND meta_value LIKE %s";
$prepared_query = $wpdb->prepare($query, array($key,$value));
$query = $wpdb->get_results($prepared_query);
// result: array()

I found the following Q&A that looked similar but did not address this exact problem:

• $wpdb->get_results(...) returns empty array despite correct query (where $wpdb->prepare() was not used)
• $wpdb->prepare returns empty array (where $wpdb->prepare() did not contain LIKE)

As well as many other cases of empty results, none of which was quite what I needed.

Below is the solution I found.

Answer 1

This string, meta_value LIKE '%user-input-value%'", contains percentage wilcards (%) that indicate that we're looking for a row where meta_value column contains user-input-value as part of its value.

$wpdb->prepare converts them into hashes, and the MySQL server gets something like this: meta_value LIKE '{399038939300d2c307c53b29a166ee90101da7b2aba7978a898694010bf1bfe6}user-input-value{399038939300d2c307c53b29a166ee90101da7b2aba7978a898694010bf1bfe6}'. Naturally, I was getting no results!

The wpdb::prepare page reads, 'Literal percentage signs (%) in the query string must be written as %%. Percentage wildcards (for example, to use in LIKE syntax) must be passed via a substitution argument containing the complete LIKE string, these cannot be inserted directly in the query string. Also see wpdb::esc_like().'

So, the LIKE part must be prepared like this (example from the wpdb::esc_like() page):

$wild = '%';
$find = 'only 43% of planets';
$like = $wild . $wpdb->esc_like( $find ) . $wild;
$sql  = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", $like );

Where $wpdb->esc_like() should go before $wpdb->prepare().

And if one wants to replace percentage wildcard placeholders manually, they should use $wpdb->remove_placeholder_escape() after $wpdb->prepare().

I ended up with this code, which worked:

$key = 'user-input-key';
$value = 'user-input-value';
$value = '%' . $wpdb->esc_like($value) . '%';
$query = "SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key=%s AND meta_value LIKE %s";
$prepared_query = $wpdb->prepare($query, array($key,$value));
$prepared_query = $wpdb->remove_placeholder_escape($prepared_query);
$query = $wpdb->get_results($prepared_query);