WordPress.org

Make WordPress Core

Opened 4 years ago

Last modified 11 days ago

#18210 new defect (bug)

Update_post_meta is case insensitive on meta_key, but get_post_meta is NOT

Reported by: anmari Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 3.2
Component: Database Keywords: dev-feedback needs-patch needs-unit-tests
Focuses: performance Cc:

Description

In WordPress 3.3-aortic-dissection and 3.2.1

get_post_meta is case sensitive on the meta-key

BUT

update_post_meta is NOT case sensitive

Thus If there is a pre-existing meta record with a key in say UPPERCASE, then one can issue an update for a lowercase key that one can not then fetch as it does not exist - only the uppercase key exists.

Example Code

	$meta = get_post_meta ($post->ID, '_allday');
	if ($meta ) { echo '<br />got lower: '; var_dump($meta);
	}
	$meta = get_post_meta ($post->ID, '_ALLDAY');
	if ($meta ) { echo '<br />got upper: '; var_dump($meta);
	}
	update_post_meta (21, '_allday','Tried to update lowercase');
	$meta = get_post_meta ($post->ID, '_allday');
	if ($meta ) { echo '<br />got lower: '; var_dump($meta);
	}
	else { echo '<br />Tried to get lower but no go';
	}
	$meta = get_post_meta ($post->ID, '_ALLDAY');
	if ($meta ) { echo '<br />Still have upper: '; var_dump($meta);
	}


Output of above:

got upper: array(1) { [0]=> string(14) "tis the upper." }
Tried to get lower but no go
Still have upper: array(1) { [0]=> string(25) "Tried to update lowercase" }

Attachments (1)

18210.diff (1.2 KB) - added by kdoole 11 days ago.
Added tests for the cases @anmari describes

Download all attachments as: .zip

Change History (10)

comment:1 @anmari4 years ago

Further..

delete_post_meta also appears to be case_insensitive.

IE: one can delete issue

delete_post_meta(21, '_ALLDAY');

and any lowercase entries will be deleted too.

comment:2 @anmari4 years ago

get_posts also NOT case sensitive:

	$args = array(
	'posts_per_page' => '-1',
	'post_type'=>'any',
	'meta_key'=> $key  // update to your key
);
	$allpostwith = get_posts( $args );

will return a MIX of lower and uppercase.

Is it caused by a mysql 'feature' perhaps? Similar behaviour in phpmyadmin? by why get_post_meta works ie is case sensitive ?

comment:3 @xknown4 years ago

Yes, it is due to the collation used by default in WP (utf8_general_ci), which is case insensitive. There are more cases than case insensivity, for example the following query returns true:

select ('_allday' COLLATE utf8_general_ci) = ('_Älldây        ' COLLATE utf8_general_ci)

The reason why get_post_meta does not work for you is because the meta data are first cached in PHP using an array with key/value pairs and as you know, the string comparison in PHP is case sensitive.

Last edited 4 years ago by xknown (previous) (diff)

comment:4 @anmari4 years ago

I just very quickly learnt a whole heap more about collation and the sorting and matching issues especially for foreign languages.

I gather the _ci (case insenstive) collations are faster than the _cs (case sensitive) and different collations define different orders of search results. Eg: _bin the binary ones do not sort the capital 'A' with the lower 'a'.

It seems weird to give up accuracy for speed, and it seems that in mysql one can assign a collation (if available) at query time. Is there a case that functions that work together should do the 'same' thing. Eg: maybe use a binary collation where 'matching' keys is important?

Are there some 'best practises' around appropriate way to handle this that could be communicated. So far I've added a couple of edits to the get_post_meta and update_post_meta codex to highlight the risks in mixing case, or changing case of one's meta key.

For others who may read, these are useful
http://dev.mysql.com/doc/refman/5.0/en/charset-collations.html
http://hakre.wordpress.com/2010/12/26/wordpress-database-charset-and-collation-configuration/

comment:5 @brokentone2 years ago

  • Cc kenton.jacobsen@… added

comment:6 @nacin2 years ago

  • Component changed from General to Database

comment:7 @chriscct77 weeks ago

  • Focuses performance added
  • Keywords dev-feedback needs-patch added
  • Version changed from 3.2.1 to 3.2

comment:8 @netweb7 weeks ago

  • Keywords needs-unit-tests added

comment:9 @kdoole11 days ago

I don't know of / couldn't find best practices around this.

The collation can be changed in the query statement, so one option would be adding COLLATE utf8_general_cs when adding / updating / deleting meta. Would it be worthwhile to benchmark the performance difference between ci and cs?

Another option would be allowing users to set this themselves with a parameter when using the various get/update/insert/delete post meta fns. Seems like overkill though. Is it at all useful to let people make meta fields case sensitive?

Perhaps it's simply a matter of updating get_post_meta to be case INsensitive (and noting in the codex that post meta keys are case insensitive) so that everything works consistently.

@kdoole11 days ago

Added tests for the cases @anmari describes

Note: See TracTickets for help on using tickets.