$wpdb->prepare with LIKE and sprintf

Question

Ok, I know similar questions have been asked before but none of the answers I found seem to work. Here's my code:

$sql = $wpdb->prepare('SELECT ID FROM ' . $ignore_url_table_name . " where url LIKE '%%%s%%'", $urlRequested);
$wpdb->get_row($sql);
if ($wpdb->num_rows > 0) {
    return;
}

Here's the problem: I'm trying to detect when a specific string is found in the URL. One example of the url in the talble to look for is:

doing_wp_cron

An example of the $urlRequested is:

doing_wp_cron=1543678213.5953478813171386718750

The above SQL statement doesn't find a match.

I've also attempted to use

$sql = $wpdb->prepare('SELECT ID FROM ' . $ignore_url_table_name . " where url LIKE '%s'", '%' . $wpdb->esc_like($urlRequested) . '%');

This appears to turn the doing_wp_cron into doing//_wp//_cron which the query also doesn't find.

What am I doing wrong here?

Thanks

*Edit: So I echoed out the sql query and this looked strange.

SELECT ID FROM URL_Ignores where url LIKE '{1b71fb783b53a0ae087bf6ac6b01addd9b80435193728fe4c7c7d70b30748268}/doing_wp_cron=1543682280.1607289314270019531250{1b71fb783b53a0ae087bf6ac6b01addd9b80435193728fe4c7c7d70b30748268}'

I have no idea why but it seems every way I try to $wpdb->prepare this it's converting the % into a random variable!???

Answer 1

Well, since I haven't used a LIKE in a plugin in a while, it seems I missed this one.

WP is now adding 'placeholders' whenever you prepare or esc a query. The short solution I found was to do this. It may not be the proper one, but it works.

$sql = $wpdb->remove_placeholder_escape($wpdb->prepare("SELECT ID FROM $ignore_url_table_name where '%s' LIKE CONCAT('%',url,'%')",$urlRequested));

Answer 2

There are several things going on here

Misuse of $wpdb->prepare

This is a very strange way to use prepare:

$sql = $wpdb->prepare('SELECT ID FROM ' . $ignore_url_table_name . " where url LIKE '%%%s%%'", $urlRequested);

In particular, instead of %s you have '%%%s%%', which is very unusual. prepare expects %s, and will insert quotes for you as it is aware already that it is a string. Additionally, although it bears similarities to sprintf, it is not sprintf and does not function quite the same. Type casting, sanitising, and escaping etc are being applied. This is a security function, not a simple string manipulation function.

For example, this:

$sql = $wpdb->prepare('%s %s', 'hello', 'world' );

results in:

"hello" "world"

WP Cron

You will never find the URL you gave as an example in your database table. This is because doing_cron always has a different value afterwards, so it will not be present in your table. If you put it in your table it won't work either because it will never appear a second time.

Instead, the solution is to realise that this approach is completely incorrect for some situations, and that this would have been a better solution:

if ( wp_doing_cron() ) {
    return;
}

Similarly wp_doing_ajax should be checked, and you might want to catch the REST API and RSS feeds, but because you never provided context to your question and what the purpose of your URL detection is for, I can't comment further