I am not understandinhg $wpdb->prepare correctly

Question

I have a query that runs fine in raw PHP but I cannot run using $wpdb. I seem to be missing something in the advice provided at How do you properly prepare a %LIKE% SQL statement?

My code is

global $wpdb;
// Create a SQL statement with placeholders for the string input.
$sql3 = "SELECT id, post_date, post_title, guid FROM $wpdb->posts where post_type='attachment' and post_mime_type like %s;";
// Prepare the SQL statement so the string input gets escaped for security.
$sql3 = $wpdb->prepare( $sql3, $wpdb->esc_like('image').'%' );
echo $sql3;
$result3 = $wpdb->get_results( $sql3, OBJECT );
$wpdb->print_error();
echo "<script>var NumImages=".$result3->num_rows."</script>\n"; //pass global variable

$sql2 = "SELECT id, post_date, post_title, guid FROM wp_posts where post_type='attachment' and post_mime_type like 'image/%' ";
$result2 = $mx_conn->query($sql2);
echo "<script>var NumImages2=".$result2->num_rows."</script>\n"; //pass global variable 

and the results are

SELECT id, post_date, post_title, guid FROM wp_posts where post_type='attachment' and post_mime_type like 'image{eba84c0d1eba4e4059d0be4f26c5b63f33813b7eb78af4b6237419122fccde3f}'; 
<script>var NumImages=</script>
<script>var NumImages2=917</script>

I have tried re-escaping the percentage sign '\%' to no avail.

Answer 1

Ok, so there is one major problem with your code and it has nothing to do with escaping LIKE statements in SQL. But let me start from that...

There is nothing wrong with your escaping. You should do it exactly like that:

global $wpdb;
// Create a SQL statement with placeholders for the string input.
$sql3 = "SELECT id, post_date, post_title, guid FROM $wpdb->posts where 
post_type='attachment' and post_mime_type like %s;";
// Prepare the SQL statement so the string input gets escaped for security.
$sql3 = $wpdb->prepare( $sql3, $wpdb->esc_like('image').'%' );
echo $sql3;
$result3 = $wpdb->get_results( $sql3, OBJECT );

Although there is no point in escaping this time - you know exactly what string is passed in there and that it is secure.

So why $wpdb generates such strange SQL?

It's a fix for that vulnerability: https://blog.ircmaxell.com/2017/10/disclosure-wordpress-wpdb-sql-injection-technical.html

This behavior was introduced in WP 4.8.3 and it's making "double-preparing" sql-injection-safe...

So why is your code working incorrect?

$wpdb->get_results( $sql3, OBJECT );

is returning an array of objects (one for each selected row). It's an array, so you can't call ->num_rows on it...

If you want to count the rows, you can use:

count( $result3 );

or

$wpdb->num_rows;