WordPress.org

Make WordPress Core

Opened 13 years ago

Closed 13 years ago

#6410 closed enhancement (fixed)

DB_COLLATE is not used in database queries

Reported by: bertilow Owned by: westi
Milestone: 2.6 Priority: normal
Severity: normal Version: 2.3
Component: General Keywords:
Focuses: Cc:

Description

Testing WP-2.5RC1 I found that the collation set in "wp-config.php" (DB_COLLATE) is not being used in searches. I think it should.

This can easily be fixed. In "wp-includes/query.php" the following part can be changed as indicated here:

// If a search pattern is specified, load the posts that match
if ( !empty($q['s']) ) {

  // [...]

  foreach((array)$q['search_terms'] as $term) {
    $term = addslashes_gpc($term);
    // The following like has been added
    if ($wpdb->collate) $collatesql = ' COLLATE ' . $wpdb->collate;
    // The following line has been changed
    // $collatesql was inserted in two places
    $search .= "{$searchand}(($wpdb->posts.post_title LIKE '{$n}{$term}{$n}'$collatesql) OR ($wpdb->posts.post_content LIKE '{$n}{$term}{$n}'$collatesql))";
    $searchand = ' AND ';
  }

  // [...]
} 

There is probably a more general way to fix this, e.g. by including a "COLLATE" statement (using DB_COLLATE) along with "SET NAME" $this->query("SET NAMES '$this->charset'") in "wp-db.php", but the above solution should also work.

Attachments (1)

patch-for-ticket-6410 (825 bytes) - added by bertilow 13 years ago.
Added a patch implementing my solution

Download all attachments as: .zip

Change History (10)

#1 @lloydbudd
13 years ago

bertilow, can you attach a patch?

@bertilow
13 years ago

Added a patch implementing my solution

#2 @bertilow
13 years ago

  • Milestone changed from 2.7 to 2.5.1

#3 @westi
13 years ago

Relates to #3517 and [4860].

At present the DB_COLLATE define is only used when creating the tables at install time.

#4 @westi
13 years ago

(In [7579]) Specify collation as well when connecting to the database if it is provided. See #6410 for trunk.

#5 @westi
13 years ago

  • Cc ryan added
  • Owner changed from anonymous to westi
  • Status changed from new to assigned
  • Summary changed from DB_COLLATE is not used in searches to DB_COLLATE is not used in database queries
  • Version set to 2.3

That does it for trunk.

Ryan can you give this the once over before we possibly merge into 2.5.1

#6 @Denis-de-Bernardy
13 years ago

when SAVEQUERIES is turned on, the first query always is:

[0]=>
  array(3) {
    [0]=>
    string(16) "SET NAMES 'utf8'"
    [1]=>
    float(0.00020217895507812)
    [2]=>
    string(12) "require_once"

doesn't this mean db_collate is actually used?

D.

#7 follow-up: @DD32
13 years ago

doesn't this mean db_collate is actually used?

"SET NAMES 'utf8'" sets the charset to utf8
"SET NAMES 'utf8' COLLATE 'abcde'" sets the charset to utf8 AND the collate to abcde(I'm not sure of a valid collate, so i'm just using that as an example)

#8 in reply to: ↑ 7 @westi
13 years ago

Replying to DD32:

doesn't this mean db_collate is actually used?

"SET NAMES 'utf8'" sets the charset to utf8
"SET NAMES 'utf8' COLLATE 'abcde'" sets the charset to utf8 AND the collate to abcde(I'm not sure of a valid collate, so i'm just using that as an example)

This is correct. Collation is rarely needed unless the db charset has many possible collations.

#9 @ryan
13 years ago

  • Milestone changed from 2.5.2 to 2.6
  • Resolution set to fixed
  • Status changed from assigned to closed
Note: See TracTickets for help on using tickets.