0

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:

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

Below is the solution I found.

1 Answer 1

0

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);
7
  • It is true that one should use $wpdb->esc_like() to escape % in a LIKE string, however, you should not need to manually call $wpdb->remove_placeholder_escape() because it will actually be automatically called when you run $wpdb->get_results(), $wpdb->get_var() etc. I.e. WordPress will change the placeholder escape strings ({...}) back to percentage signs (%) when the SQL statement/command is sent to MySQL.
    – Sally CJ
    Jun 15 at 6:15
  • So if I were you, I'd remove the $prepared_query = $wpdb->remove_placeholder_escape($prepared_query); and then add var_dump( $wpdb->last_query ); right after the call to $wpdb->get_results(). That can confirm 2 things - 1) Whether the SQL sent to MySQL used the placeholder escape strings or not, and if not, then that means, 2) All you needed to do was to add the $wpdb->esc_like() - which made your query work.
    – Sally CJ
    Jun 15 at 6:18
  • Oh hey Sally CJ ) Examining the last query was the first thing I did, and the query had placeholders. That's kind of strange (why would it, having them removed automatically is what I'd expect), but it led me to the solution. Actually, it happened on two sites, one under WordPress, another used a standalone $wpdb class from here github.com/gavinlight/wpdb_standalone (the latter was even getting data from a non-WP table). Also, in the example on esc_like() page the wildcards were directly added to the string. Hovewer, the placeholders were still present in my case.
    – Artem
    Jun 15 at 8:13
  • Query: $wpdb->prepare("SELECT meta_key, meta_value FROM ".DB_PREFIX_ACCOUNT."user_meta WHERE user_id=".$this->current_user_ID()." AND (meta_key LIKE %s AND meta_key LIKE %s) ", array( '%robokassa_shop_password%', '%'.$wpdb->esc_like($storeId).'%')); $wpdb->last_query produces: "....AND (meta_key LIKE '{HASH}robokassa_shop_password{HASH}'\r\n AND meta_key LIKE '{HASH}█{HASH}')\r\n "
    – Artem
    Jun 15 at 8:55
  • "it happened on two sites, one under WordPress" - that's strange, but what's the WordPress version there - maybe there's a plugin/theme conflict causing the issue? (PS: Sorry about my previous comments, which I've deleted..)
    – Sally CJ
    Jun 18 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.