[wp-hackers] Why do some user meta entries use the table prefix in the meta key?

[David Anderson]

On 12/05/13 13:00, Andrew Nacin wrote:
> The reason it's like this is to support entries which can have
> different values on different sites in a Multisite network. For
> example, in Multisite a user can have different privileges on
> different sites within the network. Each site has its own database
> prefix (wp_1_, wp_2_... wp_n_) and the user meta key is based off of
> this.
I don't get this.... as long as all the sites within the network are 
still using the same database and table prefix, then the meta key prefix 
is still an invariant, isn't it? i.e. It only provides something useful 
if the different sites within the network are actually using a different 
table prefix. Otherwise, the same effect could be achieved by just using 
a fixed string instead of the $table_prefix setting. Or am I missing 
something?

Whatever the reason, it occurs to me that this implies a silent 
limitation on use of meta keys - that you must be careful to not use a 
meta key that could match a table prefix, because of errors if/when your 
site is migrated. Alternatively, it could be considered as a limitation 
on migrating - that you shouldn't change your table prefix.

Making this even uglier is the fact that underscore - used in almost 
100% of table prefixes - needs escaping in SQL LIKE statements. If you 
search for all the usermeta entries beginning with the table prefix, 
then naively issuing SQL like this:

SELECT meta_key from ${table_prefix}usermeta WHERE meta_key LIKE 
'${table_prefix}%'

will get you false positives. e.g. if your table prefix is wp_ then that 
SQL actually matches all keys beginning simply with just wp - which 
given that it's WordPress, could be quite a lot.

Very ugly.

David



> -- 
> WordShell - WordPress fast from the CLI - www.wordshell.net