WordPress.org

Make WordPress Core

Opened 3 years ago

Last modified 14 months 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.1
Component: Database Keywords:
Focuses: 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" }

Change History (6)

comment:1 anmari3 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 anmari3 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 xknown3 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.

Version 0, edited 3 years ago by xknown (next)

comment:4 anmari3 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 brokentone14 months ago

  • Cc kenton.jacobsen@… added

comment:6 nacin14 months ago

  • Component changed from General to Database
Note: See TracTickets for help on using tickets.