2

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.

1 Answer 1

2

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;
1
  • 1
    Perfect, thank you. That worked straight away and also helped with my understanding of the $wpdb class. I did some more reading about the changes to secure double prepare.
    – Geoff Ebbs
    Jun 29, 2018 at 2:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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