[wp-trac] [WordPress Trac] #32108: Cannot perform non-ascii selects when COLLATE is being used

[WordPress Trac]

#32108: Cannot perform non-ascii selects when COLLATE is being used
--------------------------+-----------------------------
 Reporter:  willstedt     |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  4.1.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 When working with non-ascii characters, one often need to be able to sort
 the search results the way the local user is expecting it, and then
 COLLATE is being used on the query. In this bug, that was implemented in
 the 4.1.2 security release, COLLATE doesn't work anymore on SELECT
 queries. I haven't digged into the reason for this, but suspect it is the
 preg_match in the get_table_from_query function (the same as
 [https://core.trac.wordpress.org/ticket/32090 #32090]) that can't handle
 queries that includes COLLATE.

 Example of results sorting using COLLATE utf8_swedish_ci:
 ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ (this is the correct way to represent the
 swedish alphabet)

 Example of results sorting without COLLATE utf8_swedish_ci:
 AÅÄBCDEFGHIJKLMNOÖPQRSTUVWXYZ (this is wrong)

 ''This fails:''
 {{{
 $customers = $wpdb->get_results("SELECT
                         client.firstname,
                         client.lastname,
                         client.email,
                         client.image,
                         client.zipcode
                         FROM
                         {$current_client}.{$wpdb->base_prefix}clients
 client,
                         WHERE
                         (client.firstname LIKE '%$searchterm%' COLLATE
 utf8_swedish_ci OR client.lastname LIKE '%$searchterm%' COLLATE
 utf8_swedish_ci OR client.email LIKE '%$searchterm%' COLLATE
 utf8_swedish_ci) ORDER BY firstname DESC $limit");
 }}}

 ''This works:''
 {{{
         $customers = $wpdb->get_results("SELECT
                         client.firstname,
                         client.lastname,
                         client.email,
                         client.image,
                         client.zipcode
                         FROM
                         {$current_client}.{$wpdb->base_prefix}clients
 client,
                         WHERE
                         (client.firstname LIKE '%$searchterm%' OR
 client.lastname LIKE '%$searchterm%' OR client.email LIKE '%$searchterm%')
 ORDER BY firstname DESC $limit");
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/32108>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform