[wp-trac] [WordPress Trac] #41942: Possible wpdb prepare function returning invalid query

[WordPress Trac]

#41942: Possible wpdb prepare function returning invalid query
--------------------------+-----------------------------
 Reporter:  shaddow11ro   |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  General       |    Version:  4.8.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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
 <?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
 ";
 ?>
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/41942>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform