Changeset 29933 for trunk/src/wp-includes/date.php

Timestamp:

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

File:

• trunk/src/wp-includes/date.php (modified) (2 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 );
         }