Make WordPress Core

Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#32108 closed defect (bug) (duplicate)

Cannot perform non-ascii selects when COLLATE is being used

Reported by: willstedt's profile 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)

#1 @willstedt
10 years ago

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.

#2 follow-up: @pento
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 @willstedt
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 a schema.table format, instead of just table.

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.

Note: See TracTickets for help on using tickets.