WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

#36649 closed defect (bug) (fixed)

Right-hand CAST in a MySQL query produces DB error

Reported by: ericlewis Owned by: ericlewis
Milestone: 4.6 Priority: normal
Severity: normal Version: 2.8
Component: Database Keywords: has-patch needs-testing has-unit-tests
Focuses: Cc:

Description

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

Attachments (3)

36649.diff (1.3 KB) - added by ericlewis 4 years ago.
36649.2.diff (2.4 KB) - added by ericlewis 4 years ago.
36649.3.diff (2.4 KB) - added by ericlewis 4 years ago.

Download all attachments as: .zip

Change History (10)

#1 @ericlewis
4 years ago

  • Version set to 2.8

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
4 years ago

#2 @ericlewis
4 years ago

  • Keywords has-patch needs-testing dev-feedback added

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.

#3 @boonebgorges
4 years ago

  • Component changed from Query to Database

cc @pento

#4 @pento
4 years ago

  • Keywords needs-unit-tests added; dev-feedback removed
  • Milestone changed from Awaiting Review to 4.6

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
4 years ago

#5 @ericlewis
4 years ago

  • Keywords has-unit-tests added; needs-unit-tests removed

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

#6 @pento
4 years ago

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

@ericlewis
4 years ago

#7 @ericlewis
4 years ago

  • Owner set to ericlewis
  • Resolution set to fixed
  • Status changed from new to closed

In 37320:

Database: Set MySQL connection collation.

Fixes #36649.

Note: See TracTickets for help on using tickets.