diff -r 3d4e9c994f10 -r a86126ab1dd4 wp/wp-includes/class-wp-meta-query.php --- a/wp/wp-includes/class-wp-meta-query.php Tue Oct 22 16:11:46 2019 +0200 +++ b/wp/wp-includes/class-wp-meta-query.php Tue Dec 15 13:49:49 2020 +0100 @@ -100,6 +100,8 @@ * @since 3.2.0 * @since 4.2.0 Introduced support for naming query clauses by associative array keys. * @since 5.1.0 Introduced $compare_key clause parameter, which enables LIKE key matches. + * @since 5.3.0 Increased the number of operators available to $compare_key. Introduced $type_key, + * which enables the $key to be cast to a new data type for comparisons. * * @param array $meta_query { * Array of meta query clauses. When first-order clauses or sub-clauses use strings as @@ -107,12 +109,17 @@ * * @type string $relation Optional. The MySQL keyword used to join * the clauses of the query. Accepts 'AND', or 'OR'. Default 'AND'. - * @type array { + * @type array ...$0 { * Optional. An array of first-order clause parameters, or another fully-formed meta query. * * @type string $key Meta key to filter by. - * @type string $compare_key MySQL operator used for comparing the $key. Accepts '=' and 'LIKE'. - * Default '='. + * @type string $compare_key MySQL operator used for comparing the $key. Accepts '=', '!=' + * 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'REGEXP', 'NOT REGEXP', 'RLIKE', + * 'EXISTS' (alias of '=') or 'NOT EXISTS' (alias of '!='). + * Default is 'IN' when `$key` is an array, '=' otherwise. + * @type string $type_key MySQL data type that the meta_key column will be CAST to for + * comparisons. Accepts 'BINARY' for case-sensitive regular expression + * comparisons. Default is ''. * @type string $value Meta value to filter by. * @type string $compare MySQL operator used for comparing the $value. Accepts '=', * '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', @@ -131,7 +138,7 @@ return; } - if ( isset( $meta_query['relation'] ) && strtoupper( $meta_query['relation'] ) == 'OR' ) { + if ( isset( $meta_query['relation'] ) && 'OR' === strtoupper( $meta_query['relation'] ) ) { $this->relation = 'OR'; } else { $this->relation = 'AND'; @@ -239,7 +246,7 @@ * the rest of the meta_query). */ $primary_meta_query = array(); - foreach ( array( 'key', 'compare', 'type', 'compare_key' ) as $key ) { + foreach ( array( 'key', 'compare', 'type', 'compare_key', 'type_key' ) as $key ) { if ( ! empty( $qv[ "meta_$key" ] ) ) { $primary_meta_query[ $key ] = $qv[ "meta_$key" ]; } @@ -288,7 +295,7 @@ return 'CHAR'; } - if ( 'NUMERIC' == $meta_type ) { + if ( 'NUMERIC' === $meta_type ) { $meta_type = 'SIGNED'; } @@ -304,7 +311,7 @@ * @param string $primary_table Database table where the object being filtered is stored (eg wp_users). * @param string $primary_id_column ID column for the filtered object in $primary_table. * @param object $context Optional. The main query object. - * @return false|array { + * @return array|false { * Array containing JOIN and WHERE SQL clauses to append to the main query. * * @type string $join SQL fragment to append to the main JOIN clause. @@ -312,7 +319,8 @@ * } */ public function get_sql( $type, $primary_table, $primary_id_column, $context = null ) { - if ( ! $meta_table = _get_meta_table( $type ) ) { + $meta_table = _get_meta_table( $type ); + if ( ! $meta_table ) { return false; } @@ -497,34 +505,40 @@ $clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '='; } - if ( ! in_array( - $clause['compare'], - array( - '=', - '!=', - '>', - '>=', - '<', - '<=', - 'LIKE', - 'NOT LIKE', - 'IN', - 'NOT IN', - 'BETWEEN', - 'NOT BETWEEN', - 'EXISTS', - 'NOT EXISTS', - 'REGEXP', - 'NOT REGEXP', - 'RLIKE', - ) - ) ) { + $non_numeric_operators = array( + '=', + '!=', + 'LIKE', + 'NOT LIKE', + 'IN', + 'NOT IN', + 'EXISTS', + 'NOT EXISTS', + 'RLIKE', + 'REGEXP', + 'NOT REGEXP', + ); + + $numeric_operators = array( + '>', + '>=', + '<', + '<=', + 'BETWEEN', + 'NOT BETWEEN', + ); + + if ( ! in_array( $clause['compare'], $non_numeric_operators, true ) && ! in_array( $clause['compare'], $numeric_operators, true ) ) { $clause['compare'] = '='; } - if ( isset( $clause['compare_key'] ) && 'LIKE' === strtoupper( $clause['compare_key'] ) ) { + if ( isset( $clause['compare_key'] ) ) { $clause['compare_key'] = strtoupper( $clause['compare_key'] ); } else { + $clause['compare_key'] = isset( $clause['key'] ) && is_array( $clause['key'] ) ? 'IN' : '='; + } + + if ( ! in_array( $clause['compare_key'], $non_numeric_operators, true ) ) { $clause['compare_key'] = '='; } @@ -593,11 +607,79 @@ if ( 'NOT EXISTS' === $meta_compare ) { $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL'; } else { - if ( 'LIKE' === $meta_compare_key ) { - $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key LIKE %s", '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%' ); - } else { - $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) ); + /** + * In joined clauses negative operators have to be nested into a + * NOT EXISTS clause and flipped, to avoid returning records with + * matching post IDs but different meta keys. Here we prepare the + * nested clause. + */ + if ( in_array( $meta_compare_key, array( '!=', 'NOT IN', 'NOT LIKE', 'NOT EXISTS', 'NOT REGEXP' ), true ) ) { + // Negative clauses may be reused. + $i = count( $this->table_aliases ); + $subquery_alias = $i ? 'mt' . $i : $this->meta_table; + $this->table_aliases[] = $subquery_alias; + + $meta_compare_string_start = 'NOT EXISTS ('; + $meta_compare_string_start .= "SELECT 1 FROM $wpdb->postmeta $subquery_alias "; + $meta_compare_string_start .= "WHERE $subquery_alias.post_ID = $alias.post_ID "; + $meta_compare_string_end = 'LIMIT 1'; + $meta_compare_string_end .= ')'; } + + switch ( $meta_compare_key ) { + case '=': + case 'EXISTS': + $where = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared + break; + case 'LIKE': + $meta_compare_value = '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%'; + $where = $wpdb->prepare( "$alias.meta_key LIKE %s", $meta_compare_value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared + break; + case 'IN': + $meta_compare_string = "$alias.meta_key IN (" . substr( str_repeat( ',%s', count( $clause['key'] ) ), 1 ) . ')'; + $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared + break; + case 'RLIKE': + case 'REGEXP': + $operator = $meta_compare_key; + if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) { + $cast = 'BINARY'; + } else { + $cast = ''; + } + $where = $wpdb->prepare( "$alias.meta_key $operator $cast %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared + break; + + case '!=': + case 'NOT EXISTS': + $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key = %s " . $meta_compare_string_end; + $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared + break; + case 'NOT LIKE': + $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key LIKE %s " . $meta_compare_string_end; + + $meta_compare_value = '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%'; + $where = $wpdb->prepare( $meta_compare_string, $meta_compare_value ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared + break; + case 'NOT IN': + $array_subclause = '(' . substr( str_repeat( ',%s', count( $clause['key'] ) ), 1 ) . ') '; + $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key IN " . $array_subclause . $meta_compare_string_end; + $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared + break; + case 'NOT REGEXP': + $operator = $meta_compare_key; + if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) { + $cast = 'BINARY'; + } else { + $cast = ''; + } + + $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key REGEXP $cast %s " . $meta_compare_string_end; + $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared + break; + } + + $sql_chunks['where'][] = $where; } } @@ -605,7 +687,7 @@ if ( array_key_exists( 'value', $clause ) ) { $meta_value = $clause['value']; - if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) { + if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ), true ) ) { if ( ! is_array( $meta_value ) ) { $meta_value = preg_split( '/[,\s]+/', $meta_value ); } @@ -622,8 +704,7 @@ case 'BETWEEN': case 'NOT BETWEEN': - $meta_value = array_slice( $meta_value, 0, 2 ); - $where = $wpdb->prepare( '%s AND %s', $meta_value ); + $where = $wpdb->prepare( '%s AND %s', $meta_value[0], $meta_value[1] ); break; case 'LIKE': @@ -699,9 +780,9 @@ * * @since 4.1.0 * - * @param array $clause Query clause. - * @param array $parent_query Parent query of $clause. - * @return string|bool Table alias if found, otherwise false. + * @param array $clause Query clause. + * @param array $parent_query Parent query of $clause. + * @return string|false Table alias if found, otherwise false. */ protected function find_compatible_table_alias( $clause, $parent_query ) { $alias = false; @@ -730,7 +811,7 @@ $clause_compare = strtoupper( $clause['compare'] ); $sibling_compare = strtoupper( $sibling['compare'] ); - if ( in_array( $clause_compare, $compatible_compares ) && in_array( $sibling_compare, $compatible_compares ) ) { + if ( in_array( $clause_compare, $compatible_compares, true ) && in_array( $sibling_compare, $compatible_compares, true ) ) { $alias = $sibling['alias']; break; } @@ -741,10 +822,10 @@ * * @since 4.1.0 * - * @param string|bool $alias Table alias, or false if none was found. - * @param array $clause First-order query clause. - * @param array $parent_query Parent of $clause. - * @param object $this WP_Meta_Query object. + * @param string|bool $alias Table alias, or false if none was found. + * @param array $clause First-order query clause. + * @param array $parent_query Parent of $clause. + * @param WP_Meta_Query $this WP_Meta_Query object. */ return apply_filters( 'meta_query_find_compatible_table_alias', $alias, $clause, $parent_query, $this ); }