In #11819 I was so hardly looking for a clear documentation for mysql_real_escape_string() combined with the usage of SET NAMES. I finally found it where I should have looked first: within the MySQL manual. PHP is using the MySQL client library in it’s standard mysql extension. That is the one that WordPress is using in it’s database class right now (which should be replaced as of now but that is another story).
Quoted from the MySQL manual:
If you need to change the character set of the connection, you should use the mysql_set_character_set() function rather than executing a SET NAMES (or SET CHARACTER SET) statement. mysql_set_character_set() works like SET NAMES but also affects the character set used by mysql_real_escape_string(), which SET NAMES does not.
This has been already reported here and there so is no news. Mysql_set_character_set() is named mysql_set_charset() in the PHP MySQL extension and it is currently used when you use the PHP version 5.2.3 or above and your MySQL server has the version 5.0.7 or later. It gets applied to values passed to prepare(), insert() or update() but it is not offered by escape() (! [ <- Exclamation Mark] the function name might be misleading, the term escape is an alias to addslashes in the WordPress project historically). Keep that in mind when you use these functions, do not trust their naming.
To summarize: If you like proper escaping for your database queries in wordpress, ensure you have at least PHP 5.2.3 and MySQL 5.0.7 on your server. Then use wpdb::prepare(), wpdb::insert() or wpdb::update() to query the database. If you have plugins running, check with their authors and/or the code, that it is using the WPDB class properly.