Changeset 29933

Timestamp:

10/17/2014 01:19:03 AM (10 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:

• src/wp-includes/date.php (modified) (2 diffs)
• tests/phpunit/tests/date/query.php (modified) (2 diffs)
• tests/phpunit/tests/query/dateQuery.php (modified) (3 diffs)

trunk/src/wp-includes/date.php

@@ -436 +436 @@
      * Validates a column name parameter.
      *
+     * Column names without a table prefix (like 'post_date') are checked against a whitelist of
+     * known tables, and then, if found, have a table prefix (such as 'wp_posts.') prepended.
+     * Prefixed column names (such as 'wp_posts.post_date') bypass this whitelist check,
+     * and are only sanitized to remove illegal characters.
+     *
      * @since 3.7.0
      * @access public
@@ -443 +448 @@
      */
     public function validate_column( $column ) {
+        global $wpdb;
+
         $valid_columns = array(
             'post_date', 'post_date_gmt', 'post_modified',
             'post_modified_gmt', 'comment_date', 'comment_date_gmt'
         );
-        /**
-         * Filter the list of valid date query columns.
-         *
-         * @since 3.7.0
-         *
-         * @param array $valid_columns An array of valid date query columns. Defaults are 'post_date', 'post_date_gmt',
-         *                             'post_modified', 'post_modified_gmt', 'comment_date', 'comment_date_gmt'
-         */
-        if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) )
-            $column = 'post_date';
-
-        return $column;
+
+        // Attempt to detect a table prefix.
+        if ( false === strpos( $column, '.' ) ) {
+            /**
+             * Filter the list of valid date query columns.
+             *
+             * @since 3.7.0
+             *
+             * @param array $valid_columns An array of valid date query columns. Defaults
+             *                 are 'post_date', 'post_date_gmt', 'post_modified',
+             *                 'post_modified_gmt', 'comment_date', 'comment_date_gmt'
+             */
+            if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) ) {
+                $column = 'post_date';
+            }
+
+            $known_columns = array(
+                $wpdb->posts => array(
+                    'post_date',
+                    'post_date_gmt',
+                    'post_modified',
+                    'post_modified_gmt',
+                ),
+                $wpdb->comments => array(
+                    'comment_date',
+                    'comment_date_gmt',
+                ),
+            );
+
+            // If it's a known column name, add the appropriate table prefix.
+            foreach ( $known_columns as $table_name => $table_columns ) {
+                if ( in_array( $column, $table_columns ) ) {
+                    $column = $table_name . '.' . $column;
+                    break;
+                }
+            }
+
+        }
+
+        // Remove unsafe characters.
+        return preg_replace( '/[^a-zA-Z0-9_$\.]/', '', $column );
     }
 

trunk/tests/phpunit/tests/date/query.php

@@ -202 +202 @@
 
     public function test_validate_column_post_date() {
-        $q = new WP_Date_Query( array() );
-
-        $this->assertSame( 'post_date', $q->validate_column( 'post_date' ) );
+        global $wpdb;
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( $wpdb->posts . '.post_date', $q->validate_column( 'post_date' ) );
     }
 
     public function test_validate_column_post_date_gmt() {
-        $q = new WP_Date_Query( array() );
-
-        $this->assertSame( 'post_date_gmt', $q->validate_column( 'post_date_gmt' ) );
+        global $wpdb;
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( $wpdb->posts . '.post_date_gmt', $q->validate_column( 'post_date_gmt' ) );
     }
 
     public function test_validate_column_post_modified() {
-        $q = new WP_Date_Query( array() );
-
-        $this->assertSame( 'post_modified', $q->validate_column( 'post_modified' ) );
+        global $wpdb;
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( $wpdb->posts . '.post_modified', $q->validate_column( 'post_modified' ) );
     }
 
     public function test_validate_column_post_modified_gmt() {
-        $q = new WP_Date_Query( array() );
-
-        $this->assertSame( 'post_modified_gmt', $q->validate_column( 'post_modified_gmt' ) );
+        global $wpdb;
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( $wpdb->posts . '.post_modified_gmt', $q->validate_column( 'post_modified_gmt' ) );
     }
 
     public function test_validate_column_comment_date() {
-        $q = new WP_Date_Query( array() );
-
-        $this->assertSame( 'comment_date', $q->validate_column( 'comment_date' ) );
+        global $wpdb;
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( $wpdb->comments . '.comment_date', $q->validate_column( 'comment_date' ) );
     }
 
     public function test_validate_column_comment_date_gmt() {
-        $q = new WP_Date_Query( array() );
-
-        $this->assertSame( 'comment_date_gmt', $q->validate_column( 'comment_date_gmt' ) );
+        global $wpdb;
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( $wpdb->comments . '.comment_date_gmt', $q->validate_column( 'comment_date_gmt' ) );
     }
 
     public function test_validate_column_invalid() {
-        $q = new WP_Date_Query( array() );
-
-        $this->assertSame( 'post_date', $q->validate_column( 'foo' ) );
+        global $wpdb;
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( $wpdb->posts . '.post_date', $q->validate_column( 'foo' ) );
+    }
+
+    /**
+     * @ticket 25775
+     */
+    public function test_validate_column_with_date_query_valid_columns_filter() {
+        $q = new WP_Date_Query( array() );
+
+        add_filter( 'date_query_valid_columns', array( $this, 'date_query_valid_columns_callback' ) );
+
+        $this->assertSame( 'my_custom_column', $q->validate_column( 'my_custom_column' ) );
+
+        remove_filter( 'date_query_valid_columns', array( $this, 'date_query_valid_columns_callback' ) );
+    }
+
+    /**
+     * @ticket 25775
+     */
+    public function test_validate_column_prefixed_column_name() {
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( 'foo.bar', $q->validate_column( 'foo.bar' ) );
+    }
+
+    /**
+     * @ticket 25775
+     */
+    public function test_validate_column_prefixed_column_name_with_illegal_characters() {
+        $q = new WP_Date_Query( array() );
+
+        $this->assertSame( 'foo.bar', $q->validate_column( 'f"\'oo\/.b;:()ar' ) );
     }
 
     public function test_build_value_value_null() {
+        global $wpdb;
         $q = new WP_Date_Query( array() );
 
@@ -903 +942 @@
         }
     }
+
+    /** Helpers **********************************************************/
+
+    public function date_query_valid_columns_callback( $columns ) {
+        $columns[] = 'my_custom_column';
+        return $columns;
+    }
 }

trunk/tests/phpunit/tests/query/dateQuery.php

@@ -611 +611 @@
 
     public function test_date_params_monthnum_m_duplicate() {
+        global $wpdb;
+
         $this->create_posts();
 
@@ -630 +632 @@
         $this->assertEquals( $expected_dates, wp_list_pluck( $posts, 'post_date' ) );
 
-        $this->assertContains( "MONTH( post_date ) = 5", $this->q->request );
-        $this->assertNotContains( "MONTH( post_date ) = 9", $this->q->request );
+        $this->assertContains( "MONTH( $wpdb->posts.post_date ) = 5", $this->q->request );
+        $this->assertNotContains( "MONTH( $wpdb->posts.post_date ) = 9", $this->q->request );
     }
 
     public function test_date_params_week_w_duplicate() {
+        global $wpdb;
+
         $this->create_posts();
 
@@ -653 +657 @@
         $this->assertEquals( $expected_dates, wp_list_pluck( $posts, 'post_date' ) );
 
-        $this->assertContains( "WEEK( post_date, 1 ) = 21", $this->q->request );
-        $this->assertNotContains( "WEEK( post_date, 1 ) = 22", $this->q->request );
+        $this->assertContains( "WEEK( $wpdb->posts.post_date, 1 ) = 21", $this->q->request );
+        $this->assertNotContains( "WEEK( $wpdb->posts.post_date, 1 ) = 22", $this->q->request );
+    }
+
+    /**
+     * @ticket 25775
+     */
+    public function test_date_query_with_taxonomy_join() {
+        $p1 = $this->factory->post->create( array(
+            'post_date' => '2013-04-27 01:01:01',
+        ) );
+        $p2 = $this->factory->post->create( array(
+            'post_date' => '2013-03-21 01:01:01',
+        ) );
+
+        register_taxonomy( 'foo', 'post' );
+        wp_set_object_terms( $p1, 'bar', 'foo' );
+
+        $posts = $this->_get_query_result( array(
+            'date_query' => array(
+                'year' => 2013,
+            ),
+            'tax_query' => array(
+                array(
+                    'taxonomy' => 'foo',
+                    'terms' => array( 'bar' ),
+                    'field' => 'name',
+                ),
+            ),
+        ) );
+
+        _unregister_taxonomy( 'foo' );
+
+        $this->assertEquals( array( $p1 ), wp_list_pluck( $posts, 'ID' ) );
     }