WordPress.org

Make WordPress Core

Changeset 29933


Ignore:
Timestamp:
10/17/2014 01:19:03 AM (6 years ago)
Author:
boonebgorges
Message:

Use table aliases for columns in SQL generated by WP_Date_Query.

The use of non-aliased column names (eg 'post_date' instead of 'wp_posts.post_date')
in WP_Date_Query causes SQL notices and other failures when queries involve
table joins, such as date_query combined with tax_query or meta_query. This
changeset modifies WP_Date_Query::validate_column() to add the table alias when
it can be detected from the column name (ie, in the case of core columns).

A side effect of this change is that it is now possible to use WP_Date_Query
to build WHERE clauses across multiple tables, though there is currently no
core support for the automatic generation of the necessary JOIN clauses. See

Props ew_holmes, wonderboymusic, neoxx, Viper007Bond, boonebgorges.
Fixes #25775.

Location:
trunk
Files:
3 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/wp-includes/date.php

    r29925 r29933  
    436436     * Validates a column name parameter.
    437437     *
     438     * Column names without a table prefix (like 'post_date') are checked against a whitelist of
     439     * known tables, and then, if found, have a table prefix (such as 'wp_posts.') prepended.
     440     * Prefixed column names (such as 'wp_posts.post_date') bypass this whitelist check,
     441     * and are only sanitized to remove illegal characters.
     442     *
    438443     * @since 3.7.0
    439444     * @access public
     
    443448     */
    444449    public function validate_column( $column ) {
     450        global $wpdb;
     451
    445452        $valid_columns = array(
    446453            'post_date', 'post_date_gmt', 'post_modified',
    447454            'post_modified_gmt', 'comment_date', 'comment_date_gmt'
    448455        );
    449         /**
    450          * Filter the list of valid date query columns.
    451          *
    452          * @since 3.7.0
    453          *
    454          * @param array $valid_columns An array of valid date query columns. Defaults are 'post_date', 'post_date_gmt',
    455          *                             'post_modified', 'post_modified_gmt', 'comment_date', 'comment_date_gmt'
    456          */
    457         if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) )
    458             $column = 'post_date';
    459 
    460         return $column;
     456
     457        // Attempt to detect a table prefix.
     458        if ( false === strpos( $column, '.' ) ) {
     459            /**
     460             * Filter the list of valid date query columns.
     461             *
     462             * @since 3.7.0
     463             *
     464             * @param array $valid_columns An array of valid date query columns. Defaults
     465             *                 are 'post_date', 'post_date_gmt', 'post_modified',
     466             *                 'post_modified_gmt', 'comment_date', 'comment_date_gmt'
     467             */
     468            if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) ) {
     469                $column = 'post_date';
     470            }
     471
     472            $known_columns = array(
     473                $wpdb->posts => array(
     474                    'post_date',
     475                    'post_date_gmt',
     476                    'post_modified',
     477                    'post_modified_gmt',
     478                ),
     479                $wpdb->comments => array(
     480                    'comment_date',
     481                    'comment_date_gmt',
     482                ),
     483            );
     484
     485            // If it's a known column name, add the appropriate table prefix.
     486            foreach ( $known_columns as $table_name => $table_columns ) {
     487                if ( in_array( $column, $table_columns ) ) {
     488                    $column = $table_name . '.' . $column;
     489                    break;
     490                }
     491            }
     492
     493        }
     494
     495        // Remove unsafe characters.
     496        return preg_replace( '/[^a-zA-Z0-9_$\.]/', '', $column );
    461497    }
    462498
  • trunk/tests/phpunit/tests/date/query.php

    r29925 r29933  
    202202
    203203    public function test_validate_column_post_date() {
    204         $q = new WP_Date_Query( array() );
    205 
    206         $this->assertSame( 'post_date', $q->validate_column( 'post_date' ) );
     204        global $wpdb;
     205        $q = new WP_Date_Query( array() );
     206
     207        $this->assertSame( $wpdb->posts . '.post_date', $q->validate_column( 'post_date' ) );
    207208    }
    208209
    209210    public function test_validate_column_post_date_gmt() {
    210         $q = new WP_Date_Query( array() );
    211 
    212         $this->assertSame( 'post_date_gmt', $q->validate_column( 'post_date_gmt' ) );
     211        global $wpdb;
     212        $q = new WP_Date_Query( array() );
     213
     214        $this->assertSame( $wpdb->posts . '.post_date_gmt', $q->validate_column( 'post_date_gmt' ) );
    213215    }
    214216
    215217    public function test_validate_column_post_modified() {
    216         $q = new WP_Date_Query( array() );
    217 
    218         $this->assertSame( 'post_modified', $q->validate_column( 'post_modified' ) );
     218        global $wpdb;
     219        $q = new WP_Date_Query( array() );
     220
     221        $this->assertSame( $wpdb->posts . '.post_modified', $q->validate_column( 'post_modified' ) );
    219222    }
    220223
    221224    public function test_validate_column_post_modified_gmt() {
    222         $q = new WP_Date_Query( array() );
    223 
    224         $this->assertSame( 'post_modified_gmt', $q->validate_column( 'post_modified_gmt' ) );
     225        global $wpdb;
     226        $q = new WP_Date_Query( array() );
     227
     228        $this->assertSame( $wpdb->posts . '.post_modified_gmt', $q->validate_column( 'post_modified_gmt' ) );
    225229    }
    226230
    227231    public function test_validate_column_comment_date() {
    228         $q = new WP_Date_Query( array() );
    229 
    230         $this->assertSame( 'comment_date', $q->validate_column( 'comment_date' ) );
     232        global $wpdb;
     233        $q = new WP_Date_Query( array() );
     234
     235        $this->assertSame( $wpdb->comments . '.comment_date', $q->validate_column( 'comment_date' ) );
    231236    }
    232237
    233238    public function test_validate_column_comment_date_gmt() {
    234         $q = new WP_Date_Query( array() );
    235 
    236         $this->assertSame( 'comment_date_gmt', $q->validate_column( 'comment_date_gmt' ) );
     239        global $wpdb;
     240        $q = new WP_Date_Query( array() );
     241
     242        $this->assertSame( $wpdb->comments . '.comment_date_gmt', $q->validate_column( 'comment_date_gmt' ) );
    237243    }
    238244
    239245    public function test_validate_column_invalid() {
    240         $q = new WP_Date_Query( array() );
    241 
    242         $this->assertSame( 'post_date', $q->validate_column( 'foo' ) );
     246        global $wpdb;
     247        $q = new WP_Date_Query( array() );
     248
     249        $this->assertSame( $wpdb->posts . '.post_date', $q->validate_column( 'foo' ) );
     250    }
     251
     252    /**
     253     * @ticket 25775
     254     */
     255    public function test_validate_column_with_date_query_valid_columns_filter() {
     256        $q = new WP_Date_Query( array() );
     257
     258        add_filter( 'date_query_valid_columns', array( $this, 'date_query_valid_columns_callback' ) );
     259
     260        $this->assertSame( 'my_custom_column', $q->validate_column( 'my_custom_column' ) );
     261
     262        remove_filter( 'date_query_valid_columns', array( $this, 'date_query_valid_columns_callback' ) );
     263    }
     264
     265    /**
     266     * @ticket 25775
     267     */
     268    public function test_validate_column_prefixed_column_name() {
     269        $q = new WP_Date_Query( array() );
     270
     271        $this->assertSame( 'foo.bar', $q->validate_column( 'foo.bar' ) );
     272    }
     273
     274    /**
     275     * @ticket 25775
     276     */
     277    public function test_validate_column_prefixed_column_name_with_illegal_characters() {
     278        $q = new WP_Date_Query( array() );
     279
     280        $this->assertSame( 'foo.bar', $q->validate_column( 'f"\'oo\/.b;:()ar' ) );
    243281    }
    244282
    245283    public function test_build_value_value_null() {
     284        global $wpdb;
    246285        $q = new WP_Date_Query( array() );
    247286
     
    903942        }
    904943    }
     944
     945    /** Helpers **********************************************************/
     946
     947    public function date_query_valid_columns_callback( $columns ) {
     948        $columns[] = 'my_custom_column';
     949        return $columns;
     950    }
    905951}
  • trunk/tests/phpunit/tests/query/dateQuery.php

    r29923 r29933  
    611611
    612612    public function test_date_params_monthnum_m_duplicate() {
     613        global $wpdb;
     614
    613615        $this->create_posts();
    614616
     
    630632        $this->assertEquals( $expected_dates, wp_list_pluck( $posts, 'post_date' ) );
    631633
    632         $this->assertContains( "MONTH( post_date ) = 5", $this->q->request );
    633         $this->assertNotContains( "MONTH( post_date ) = 9", $this->q->request );
     634        $this->assertContains( "MONTH( $wpdb->posts.post_date ) = 5", $this->q->request );
     635        $this->assertNotContains( "MONTH( $wpdb->posts.post_date ) = 9", $this->q->request );
    634636    }
    635637
    636638    public function test_date_params_week_w_duplicate() {
     639        global $wpdb;
     640
    637641        $this->create_posts();
    638642
     
    653657        $this->assertEquals( $expected_dates, wp_list_pluck( $posts, 'post_date' ) );
    654658
    655         $this->assertContains( "WEEK( post_date, 1 ) = 21", $this->q->request );
    656         $this->assertNotContains( "WEEK( post_date, 1 ) = 22", $this->q->request );
     659        $this->assertContains( "WEEK( $wpdb->posts.post_date, 1 ) = 21", $this->q->request );
     660        $this->assertNotContains( "WEEK( $wpdb->posts.post_date, 1 ) = 22", $this->q->request );
     661    }
     662
     663    /**
     664     * @ticket 25775
     665     */
     666    public function test_date_query_with_taxonomy_join() {
     667        $p1 = $this->factory->post->create( array(
     668            'post_date' => '2013-04-27 01:01:01',
     669        ) );
     670        $p2 = $this->factory->post->create( array(
     671            'post_date' => '2013-03-21 01:01:01',
     672        ) );
     673
     674        register_taxonomy( 'foo', 'post' );
     675        wp_set_object_terms( $p1, 'bar', 'foo' );
     676
     677        $posts = $this->_get_query_result( array(
     678            'date_query' => array(
     679                'year' => 2013,
     680            ),
     681            'tax_query' => array(
     682                array(
     683                    'taxonomy' => 'foo',
     684                    'terms' => array( 'bar' ),
     685                    'field' => 'name',
     686                ),
     687            ),
     688        ) );
     689
     690        _unregister_taxonomy( 'foo' );
     691
     692        $this->assertEquals( array( $p1 ), wp_list_pluck( $posts, 'ID' ) );
    657693    }
    658694
Note: See TracChangeset for help on using the changeset viewer.