#32108 closed defect (bug) (duplicate)
Cannot perform non-ascii selects when COLLATE is being used
Reported by: | willstedt | Owned by: | |
---|---|---|---|
Milestone: | Priority: | normal | |
Severity: | normal | Version: | 4.1.2 |
Component: | Database | Keywords: | |
Focuses: | Cc: |
Description
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 #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");
Change History (3)
#2
follow-up:
↓ 3
@
10 years ago
- Milestone Awaiting Review deleted
- Resolution set to duplicate
- Status changed from new to closed
Thanks for the bug report! This appears to be the same issue as #32090 - wpdb::get_table_from_query()
doesn't find the table name if you're using a schema.table
format, instead of just table
.
#3
in reply to:
↑ 2
@
10 years ago
Replying to pento:
Thanks for the bug report! This appears to be the same issue as #32090 -
wpdb::get_table_from_query()
doesn't find the table name if you're using aschema.table
format, instead of justtable
.
You are of course right, it is most probably the same issue. I guess it is one of the other pregs in the get_table_from_query function - I only fixed the first one in the quick fix I showed in #32090.
Just noticed that the reason for using COLLATE in the query was not for the sorting of the results, but to exclude false results. Searching for "aaaa" would return a hit when the text contains "åååå" even if the characters are different and should be treated differently. This doesn't change anything though, as the bug is still there, but maybe clarifies the use case a little bit.