Make WordPress Core


Ignore:
Timestamp:
08/17/2022 01:58:22 PM (22 months ago)
Author:
SergeyBiryukov
Message:

Query: Cast the meta key to BINARY for case-sensitive key comparisons in WP_Meta_Query.

This addresses an error on MySQL 8.0.22 or later:

Character set 'utf8mb4_unicode_520_ci' cannot be used in conjunction with 'binary' in call to regexp_like

From the MySQL 8.0.22 changelog:

Regular expression functions such as REGEXP_LIKE() yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (Bug #98951, Bug #98950)

WordPress meta queries use the BINARY data type for case-sensitive meta key comparisons using regular expressions. By explicitly casting the meta key to BINARY, we can make sure the values being compared use the same character set and produce consistent results.

The change is covered by existing meta query unit tests: three tests which previously failed on MySQL 8.0.22 or later now pass.

References:

Follow-up to [46188].

Fixes #51740.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/wp-includes/class-wp-meta-query.php

    r52455 r53901  
    680680                        $operator = $meta_compare_key;
    681681                        if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) {
    682                             $cast = 'BINARY';
     682                            $cast     = 'BINARY';
     683                            $meta_key = "CAST($alias.meta_key AS BINARY)";
    683684                        } else {
    684                             $cast = '';
     685                            $cast     = '';
     686                            $meta_key = "$alias.meta_key";
    685687                        }
    686                         $where = $wpdb->prepare( "$alias.meta_key $operator $cast %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
     688                        $where = $wpdb->prepare( "$meta_key $operator $cast %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    687689                        break;
    688690
     
    706708                        $operator = $meta_compare_key;
    707709                        if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) {
    708                             $cast = 'BINARY';
     710                            $cast     = 'BINARY';
     711                            $meta_key = "CAST($subquery_alias.meta_key AS BINARY)";
    709712                        } else {
    710                             $cast = '';
     713                            $cast     = '';
     714                            $meta_key = "$subquery_alias.meta_key";
    711715                        }
    712716
    713                         $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key REGEXP $cast %s " . $meta_compare_string_end;
     717                        $meta_compare_string = $meta_compare_string_start . "AND $meta_key REGEXP $cast %s " . $meta_compare_string_end;
    714718                        $where               = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
    715719                        break;
Note: See TracChangeset for help on using the changeset viewer.