Possible wpdb prepare function returning invalid query

[shaddow11ro]

Since yesterday i've noticed that some queries are not executing in my site and managed to narrow down the issue to wp-includes/wp-db.php (who had a modified date of Sept 09 2017) function prepare at this line:

$query = preg_replace( '/%(?:%|$|([^dsF]))/', '%%\\1', $query ); // escape any unescaped percents 

When running with an older version it was working as it should.
This new line is the differenc from older version to this new version.
I have also manually escaped the query and it was working.
I've attached the code i've used for test below:

<?php
include 'wp-load.php';
$args = array(
        '%Y-%m-%d %H:%i:%s', 
        '%Y-%m-%d %H:%i:%s', 
        '2017-08-28 00:00:00', 
        '2017-10-01 23:59:59', 
        '35965,35967,35857,35866,35856,35865,35854,35863,36144,35852,35862,36102,35963,35968,36071,35851,35860,35849,35858,36015,35890'
);

$output_date_format = '%Y-%m-%d %H:%i:%s';
$start_date = '2017-08-28 00:00:00';
$end_date = '2017-10-01 23:59:59';
$post_ids = '35965,35967,35857,35866,35856,35865,35854,35863,36144,35852,35862,36102,35963,35968,36071,35851,35860,35849,35858,36015,35890';


$sqlPrepared = $wpdb->prepare(
        "
        SELECT  tribe_event_start.post_id as ID, 
                        tribe_event_start.meta_value as EventStartDate, 
                        DATE_FORMAT( tribe_event_end_date.meta_value, '%1\$s') as EventEndDate,
                        {$wpdb->posts}.menu_order as menu_order
        FROM $wpdb->postmeta AS tribe_event_start
                        LEFT JOIN $wpdb->posts ON (tribe_event_start.post_id = {$wpdb->posts}.ID)
        LEFT JOIN $wpdb->postmeta as tribe_event_end_date ON ( tribe_event_start.post_id = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = '_EventEndDate' )
        WHERE tribe_event_start.meta_key = '_EventStartDate'
        AND tribe_event_start.post_id IN ( %5\$s )
        AND ( (tribe_event_start.meta_value >= '%3\$s' AND  tribe_event_start.meta_value <= '%4\$s')
                OR (tribe_event_start.meta_value <= '%3\$s' AND tribe_event_end_date.meta_value >= '%3\$s')
                OR ( tribe_event_start.meta_value >= '%3\$s' AND  tribe_event_start.meta_value <= '%4\$s')
        )
        ORDER BY menu_order ASC, DATE(tribe_event_start.meta_value) ASC, TIME(tribe_event_start.meta_value) ASC;",
                $output_date_format,
                $output_date_format,
                $start_date,
                $end_date,
                $post_ids
);

$sqlManualEscaped =
        "
        SELECT  tribe_event_start.post_id as ID, 
                        tribe_event_start.meta_value as EventStartDate, 
                        DATE_FORMAT( tribe_event_end_date.meta_value, '%1\$s') as EventEndDate,
                        {$wpdb->posts}.menu_order as menu_order
        FROM $wpdb->postmeta AS tribe_event_start
                        LEFT JOIN $wpdb->posts ON (tribe_event_start.post_id = {$wpdb->posts}.ID)
        LEFT JOIN $wpdb->postmeta as tribe_event_end_date ON ( tribe_event_start.post_id = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = '_EventEndDate' )
        WHERE tribe_event_start.meta_key = '_EventStartDate'
        AND tribe_event_start.post_id IN ( %5\$s )
        AND ( (tribe_event_start.meta_value >= '%3\$s' AND  tribe_event_start.meta_value <= '%4\$s')
                OR (tribe_event_start.meta_value <= '%3\$s' AND tribe_event_end_date.meta_value >= '%3\$s')
                OR ( tribe_event_start.meta_value >= '%3\$s' AND  tribe_event_start.meta_value <= '%4\$s')
        )
        ORDER BY menu_order ASC, DATE(tribe_event_start.meta_value) ASC, TIME(tribe_event_start.meta_value) ASC;";

$sqlManualEscaped = vsprintf( $sqlManualEscaped, $args );

echo "Prepare function output:<br/>
$sqlPrepared
<br/><br/>
Manual escape output:<br/>
$sqlManualEscaped
";
?>

[danieltj]

This issue is related to the recent changes made in version 4.8.2 to fix a security issue. I believe it's to do with numbered placeholders no longer being accepted. This was asked yesterday on Slack I think and the answer from that is that things need to be updated to support the new code.

I'm sure someone else can elaborate on this but that's the general gist of this issue so far.

[SergeyBiryukov]

Hi @shaddow11ro, welcome to WordPress Trac!

Thanks for the report, we're already tracking this issue in #41925.

[shaddow11ro]

Thank you!
I will change the code accordingly!