Is a user_meta research case-sensitive or not

Question

I am using the Wordpress get_users() function to search for users with a specific meta_key value.

$wordpress_user = get_users( array('meta_key' => 'unique_id', 
                                   'meta_value' => 'unique_id_value') );

According to the result of the preceding, I am performing the creation of a new wordpress user, or if the user already exists, I am simply logging in that user.

The 'meta_key' is about 10 character's length, with letters and number and i am retrieving it from an external source : for more secure behaviour, I would like to know if the get_users function will use case-sensitive research. (If not I have a potential risk in the future that I retrieve 2 similar meta_key, with an upper case / lower case difference...)

In addition I have noticed all wordpress table, including wp_users_meta do have "_ci" so that is an additional reason why I do not feel secure with my present code.

Thank you in advance if you can provide me some information about that !

Answer 1

Solution found : to make a case-sensitive search for a meta_key / meta_value pair : a hardcoded mysql query on the usermeta table with the BINARY keyword, with get_results() method of $wpdb object:

global $wpdb;
$sql = $wpdb->prepare( "SELECT user_id FROM {$wpdb->prefix}usermeta 
                        WHERE meta_key = 'unique_id'    
                        AND BINARY meta_value = 'unique_id_value'");

$wordpress_user_query = $wpdb->get_results( $sql);

Answer 2

Edit: The meta_key is case insensitive by default because the default collation on the relevant tables is "utf8_general_ci", which uses case insensitive sorting for text comparisons.

If you change the collation of the rows in the database, you can change the sort order and thus whether it is case sensitive or not. You can also use the DB_COLLATE define in the wp-config.php file to specify a default collation other than utf8_general_ci, for creating the initial database.