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

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.

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 );
     }