Set character set using MySQLi

Question

I'm fetching data in Arabic from MySQL tables with MySQLi. So I usually use this in procedural style:

mysql_query("SET NAMES 'utf8'"); 
mysql_query('SET CHARACTER SET utf8'); 

Now I am using the OOP style so I am trying to see if there is something I could set rather than the above?

I only found this in PHP manual so I did it, but what about setting names to UTF8?

$mysqli->set_charset("utf8");

Answer 1

It's the same:

$mysqli->query("SET NAMES 'utf8'");

From the manual:

This is the preferred way to change the charset. Using mysqli::query() to execute SET NAMES .. is not recommended.

$mysqli->set_charset("utf8"); is just enough, let the mysqli db driver do the thing for you.

Answer 2

You should use

$mysqli->set_charset("utf8");

Don't use SET NAMES or SET CHARACTER SET explicitly when using MySQLi., and certainly don't use both like the question asker here originally was. Reasons that this is a bad idea:

• Calling SET CHARACTER SET utf8 after SET NAMES utf8 actually just undoes some of the work that SET NAMES did.

• The PHP manual explicitly warns us to use mysqli_set_charset, not SET NAMES:

  This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended. See the MySQL character set concepts section for more information.

• Under the hood, mysqli_set_charset is just a wrapper for mysql_set_character_set from the MySQL C API (or its mysqlnd equivalent). Looking at the docs for that function, we can see the difference between it and SET NAMES:

  This function works like the SET NAMES statement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string()

  In other words, $mysqli->set_charset('foo') will do everything SET NAMES foo does and also ensure that mysqli_real_escape_string respects the new encoding. Admittedly, if you're only using encodings like Latin 1 and UTF 8 that strictly extend ASCII (that is, which encode all ASCII strings exactly as they would be encoded in ASCII), then using SET NAMES instead of set_charset won't break anything. However, if you're using more unusual encodings like GBK, then you could end up garbling your strings or even introducing SQL injection vulnerabilities that bypass mysqli_real_escape_string.

It's thus good practice to only use set_charset, not SET NAMES. There's nothing that SET NAMES does that set_charset doesn't, and set_charset avoids the risk of mysqli_real_escape_string behaving incorrectly (or even insecurely).