Right-hand CAST in a MySQL query produces DB error

[ericlewis]

A right-hand CAST like this

<?php
add_action( 'init', function() {
  global $wpdb;
  $r = $wpdb->get_results( "SELECT * FROM $wpdb->postmeta WHERE meta_value=CAST('default' AS CHAR);");
});

produces this error

WordPress database error: [Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=']
SELECT * FROM wp_postmeta WHERE meta_value=CAST('default' AS CHAR);

[ericlewis]

Before r10597, the collation was always set and ​still is if mysqli_set_charset() is undefined or the version of MySQL is < 5.0.7.

Setting charset via mysql_set_charset() has been preferred since r10597, presumably to make mysql_real_escape_string() aware of the MySQL connection charset for charset-specific escaping (see #5455#comment:20) thus avoiding a security vulnerability. However, this does not set the MySQL connection's collation.

[ericlewis]

In attachment:36649.diff​, we can use mysqli_set_charset() so that mysqli_real_escape_string() is aware of the character set (see the Caution section in ​the `mysqli_real_escape_string()` docs), and then set the character set (again) but this time with the collation.

[boonebgorges]

cc @pento

[pento]

Fun times.

This looks like a relatively sane solution to the problem, we'll need it for #32405, too. We probably haven't seen any historical reports of this, because the default MySQL collation almost always matched the connection collation, prior to [31349].

[ericlewis]

Added a test in attachment:36649.2.diff​. Is it useful?

[pento]

Need to reset back to the original charset/collation at the end. Apart from that, looks good to me.

[ericlewis]

In 37320:

Database: Set MySQL connection collation.

Fixes #36649.